PostgreSQL boolean

[jmail]

I've got a column in PostgreSQL 9.0 DB called active with type
boolean.

When I make

<cfset prizes = model("prize").findAll(where="active = true",
order="name")>

I get an error (on Railo 3.2.2 Stable)

Railo 3.2.2.000 Error (expression)
Message key [VALUE] doesn't exist in struct
(keys:DATATYPE,TYPE,LIST,SCALE)
Stacktrace The Error Occurred in
D:\usr\htdocs\cfpages\wsadmin\wheels\model\adapters\Base.cfc: line
167

165: loc.params = {};
166: loc.params.cfsqltype = arguments.settings.type;
167: loc.params.value = arguments.settings.value;
168: if (StructKeyExists(arguments.settings, "null"))
169: {


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\model\adapters
\Base.cfc: line 228

226: </cfscript>
227:
228: <cfquery attributeCollection="#loc.args#"><cfloop
array="#arguments.sql#" index="loc.i"><cfif IsStruct(loc.i)><cfset
loc.queryParamAttributes = $CFQueryParameters(loc.i)><cfif
StructKeyExists(loc.queryParamAttributes, "useNull")>NULL<cfelseif
StructKeyExists(loc.queryParamAttributes, "list")><cfif
arguments.parameterize>(<cfqueryparam
attributeCollection="#loc.queryParamAttributes#">)<cfelse>(#PreserveSingleQuotes(loc.i.value)#)</
cfif><cfelse><cfif arguments.parameterize><cfqueryparam
attributeCollection="#loc.queryParamAttributes#"><cfelse>#
$quoteValue(loc.i.value)#</cfif></cfif><cfelse><cfset loc.i =
Replace(PreserveSingleQuotes(loc.i), "[[comma]]", ",",
"all")>#PreserveSingleQuotes(loc.i)#</cfif>#chr(13)##chr(10)#</
cfloop><cfif arguments.limit>LIMIT #arguments.limit#<cfif
arguments.offset>#chr(13)##chr(10)#OFFSET #arguments.offset#</cfif></
cfif></cfquery>
229:
230: <cfscript>


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\model\adapters
\PostgreSQL.cfc: line 46

44: arguments.sql = $removeColumnAliasesInOrderClause(arguments.sql);
45: arguments.sql = $addColumnsToSelectAndGroupBy(arguments.sql);
46: loc.returnValue = $performQuery(argumentCollection=arguments);
47: </cfscript>
48: <cfreturn loc.returnValue>


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\model\crud.cfm: line
259

257: if (application.wheels.cacheQueries &&
(IsNumeric(arguments.cache) || (IsBoolean(arguments.cache) &&
arguments.cache)))
258: loc.finderArgs.cachedWithin = $timeSpanForCache(arguments.cache);
259: loc.findAll = variables.wheels.class.adapter.
$query(argumentCollection=loc.finderArgs);
260: request.wheels[loc.queryKey] = loc.findAll; // <- store in
request cache so we never run the exact same query twice in the same
request
261: }


called fromD:\usr\htdocs\cfpages\wsadmin\controllers\auctions.cfc:
line 44

42: <cffunction name="new">
43: <cfset auction = model("auction").new()>
44: <cfset prizes = model("prize").findAll(where="active = true",
order="name")>
45: </cffunction>
46:


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\global\cfml.cfm: line
174

172: <cfset StructDelete(arguments, "invokeArgs")>
173: </cfif>
174: <cfinvoke attributeCollection="#arguments#">
175: <cfif StructKeyExists(loc, "returnValue")>
176: <cfreturn loc.returnValue>


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\controller
\processing.cfm: line 85

83: if (StructKeyExists(this, arguments.action) &&
IsCustomFunction(this[arguments.action]))
84: {
85: $invoke(method=arguments.action);
86: }
87: else if (StructKeyExists(this, "onMissingMethod"))


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\controller
\processing.cfm: line 60

58: else
59: {
60: $callAction(action=params.action);
61: }
62: }


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\dispatch\request.cfm:
line 166

164:
165: // if the controller fails to process, instantiate a new
controller and try again
166: if (!loc.controller.$processAction())
167: {
168: loc.controller = controller(name=loc.params.controller,
params=loc.params);


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\index.cfm: line 1

1: <cfoutput>#application.wheels.dispatch.$request()#</cfoutput>


called fromD:\usr\htdocs\cfpages\wsadmin\rewrite.cfm: line 1

1: <cfinclude template="wheels/index.cfm">


called fromD:\usr\htdocs\cfpages\wsadmin\wheels\events\onrequest.cfm:
line 1

1: <cffunction name="onRequest" returntype="void" access="public"
output="true"><cfargument name="targetpage" type="any"
required="true"><cflock name="wheelsReloadLock" type="readOnly"
timeout="180"><cfinclude template="#arguments.targetpage#"></cflock></
cffunction>

But when I change the column type to smallint I can make this by
active = 1

An now I have checked that when I made

<cfset prizes = model("prize").findAll(where="active = cast(1 as
boolean)", order="name")>

everything is ok. Is it a bug or this should work with cast?

[Thomas DeLoreto]

jmail,

 

Did you try putting the true value in single quotes?

 

<cfset prizes = model("prize").findAll(where="active = 'true'",
order="name")>

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To post to this group, send email to cfwh...@googlegroups.com.
To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

[jmail]

Yes! With following error

Railo 3.2.2.000 Error (database)
Message ERROR: operator does not exist: boolean = character varying

which is expected error

jmail

> > attributeCollection="#loc.queryParamAttributes#">)<cfelse>(#PreserveSingleQ­uotes(loc.i.value)#)</

[Per Djurner]

If PostgreSQL can handle 1 / 0 instead of true / false you can try that too?

[jmail]

I can't

ERROR: column "active" is of type boolean but expression is of type
integer
LINE 1: update prizes set active = 1
^
HINT: You will need to rewrite or cast the expression.

:/

That is something with cf_sql types in CF Wheels

[jmail]

OK. Now I understand ;) that is quite simple :F

I have to make

<cfset prizes = model("prize").findAll(where="active", order="name")>

And everything is ok

when I want to make bool false I need to make

<cfset prizes = model("prize").findAll(where="not active",
order="name")>

This could be a good hint for documentation ;)