PostgreSQL boolean equivalent in CF_SQL_?

dev google-group-archive
1

Hi,

I have fields in PostgreSQL with type as “boolean”, what should I use in
queryparams ( CF_SQL_ ) ?

I have tried BIT, VARHCAR doesn’t work, based on documentation "
PostgreSQL: Documentation: 9.1: Boolean Type" it look
like should work with VARCHAR but it does not…

Any advice will be greatly appreciated

Thanks

2

Typcast the query,

select id from ttbl where fieldone= ::boolean

notice you do not need the CF_SQL_ prefix.

QueryExecute is a whole different story,

Params = [{value = "TRUE", cfsqltype = "varchar"}]; testSQL = "select id from ttble where fieldone = ? ::boolean"; testbool = QueryExecute(testSQL, Params);

Lucee thinks the :: double colons are query params? if someone knows a
workaround for that please let me know,

or using the “other” datatype should work removing the ::boolean cast.

Params = [{value = “TRUE”, cfsqltype = “other”}];
testSQL = “select id from ttble where fieldone = ?”;
testbool = QueryExecute(testSQL, Params);

3

I have fields in PostgreSQL with type as “boolean”, what should I use in
queryparams ( CF_SQL_ ) ?

Try cf_sql_boolean.

JochemOn Sun, Dec 6, 2015 at 12:47 AM, wrote:

–
Jochem van Dieten
http://jochem.vandieten.net/

4

Thanks it works with queries even if it looking odd, but that approach
seems to be doesn’t work with store procedure call “cfstoredproc” , is
there any advice for that part?

Thank you!On Sunday, December 6, 2015 at 12:09:44 AM UTC-5, mark wrote:

Typcast the query,

select id from ttbl where fieldone= ::boolean

notice you do not need the CF_SQL_ prefix.

QueryExecute is a whole different story,

Params = [{value = "TRUE", cfsqltype = "varchar"}]; testSQL = "select id from ttble where fieldone = ? ::boolean"; testbool = QueryExecute(testSQL, Params);

Lucee thinks the :: double colons are query params? if someone knows a
workaround for that please let me know,

or using the “other” datatype should work removing the ::boolean cast.

Params = [{value = “TRUE”, cfsqltype = “other”}];
testSQL = “select id from ttble where fieldone = ?”;
testbool = QueryExecute(testSQL, Params);

5

CREATE OR REPLACE FUNCTION testbool(value_param varchar) RETURNS setof int

AS $$
SELECT id FROM ttbl WHERE fieldone = $1::boolean;
$$ LANGUAGE SQL;

the data type cast is in the Postgres function which can be executed
through cfstoredproc