Using cfqueryparam with postgres bit attributes

[Randy]

I am trying to do a select from a postgres database:

SELECT message
FROM "error"."error"
WHERE "isReported" = <cfqueryparam cfsqltype="cf_sql_bit"
value="#arguments.isReported#" />

I've tried using a boolean value and a 0/1 but they both give the same
error:

ERROR: operator does not exist: bit = boolean

Has anyone else had this problem? Is there a way to do this without
the hardcoding the B'0' or B'1'?

Thanks,

Randy

[Todd Rafferty]

http://www.petefreitag.com/item/54.cfm

~Todd Rafferty * Railo Community Manager -- Volunteer * http://getRailo.org/

[Todd Rafferty]

Make sure you see Pete's note:

UPDATE the boolean type requires single quotes around 1 or 0, so in order to keep SQL code consistant I will be using the integer datatype.

[Andrew]

just guessing... could you cast the cfqueryparam as bit?

WHERE "isReported" = (<cfqueryparam cfsqltype="cf_sql_bit"
value="#arguments.isReported#" />)::bit

or you can use the sql standard CAST()...

On Fri, Aug 21, 2009 at 4:13 PM, Randy<zora...@gmail.com> wrote:
>

[Andrew]

I may be missing something... what's Pete's note?

From the error message it says that isReported is of type bit, but
when Randy tries to use the cf_sql_bit railo changes it to boolean
type.

So i'm confused about the "boolean type requires"

Is there a link to Pete's note?

[Randy]

I'm curious why the <cfqueryparam> tag would not be able to convert a
boolean to the bit type? It looks like it is passing through a JDBC
driver...? Maybe I just need to learn more about how that actually
works for the JDBC.

@Andrew I tried that but it didn't seem to work. It is still being
passed through to the DB as "isReported" = (0)::bit which gives the
same error.

Randy

[Todd Rafferty]

er, I put Pete's note right there:

UPDATE: the boolean type requires single quotes around 1 or 0, so in order to keep SQL code consistant I will be using the integer datatype.

Read closer. :)

~Todd Rafferty * Railo Community Manager -- Volunteer * http://getRailo.org/

[Randy]

Yes, I read that... I am aware that the syntax is a bit strange in
postgres, but isn't that part of why you have the cfqueryparam? to
format the variable in the way that it is used in the db? You don't
put the ' around strings that are being passed as varchar...

I even tried "isReported" = B'<cfqueryparam cfsqltype="cf_sql_bit"
value="0" />' but it dies with a different error: The column index is
out of range: 1, number of columns: 0.

It just seems dumb to have a type for the bit but not have it work for
bit types...

</rant>

I tried it CF9 and it seems to suffer from the same problem, so it not
just railo.

I'll probably just use a terinary operator for it:

"isReported" = B'#(arguments.isReported ? 1 : 0)#'

Randy

PS... it seems like Google Groups has been struggling with this
thread... I haven't got half of the messages from it yet, but they
should all catch up eventually...

[Andrew]

Sorry Todd, your second email came in before your first... I'm not
confused anymore :)

[Todd Rafferty]

Understood. I'm assuming you got this all worked out?

~Todd Rafferty * Railo Community Manager -- Volunteer * http://getRailo.org/

[Andrew]

I assume so...

I've seen this issue before when moving from a non-postgres DB to
postgres, like Peter's links.

If you're able Randy... changing the data type of isReported to
boolean would solve your problem.



Also, just a FYI, I've found that the below code works:

select * from test_table where bit_val = <cfqueryparam
cfsqltype="cf_sql_varchar" value="#1#" />::bit

if you really want to use cfqueryparam... :)

[Gert Franz]

Even if it did not work out… At least Andrew isn’t confused anymore J

 

Greetings from Switzerland

Gert Franz

 

Railo Technologies       Professional Open Source

skype: gert.franz            ge...@getrailo.com

+41 76 5680 231               www.getrailo.ch

[Todd Rafferty]

Er.. are the #'s around the 1 really necessary? o_O

[Andrew]

No... #'s were left in by accident.

[Todd Rafferty]

Thought so. I've been seeing a lot of rogue #'s in code lately on the mailing list. Was curious.

To # or not to # - that is the question.

[Randy]

Andrew, awesome suggestion. I just started using postgres and saw the
bit and thought it would be like MSSQL's bit... didn't even see the
boolean. I really don't care if it's a boolean or bit, just didn't
want to have to use an entire smallinteger or integer to say yes or
no.

I tested it out and it works like a charm! And I can still use the
cfqueryparam for the cf_sql_bit.

Randy