ERROR: Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.

[Dhg Associates]

Does anyone know how the internal cfwheels code in
   /wheels/model/adapters/cfquery.cfm works? if so, can you please help me with a hack.?

In cfwheels Ver 2.0, I am getting an error:
   Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.
The error occurred in /wheels/model/adapters/cfquery.cfm: line 37 :

35 : Replace(PreserveSingleQuotes(local.$wheels.i), "[[comma]]", ",", "all")>#PreserveSingleQuotes(local.$wheels.i)#</cfif>#chr(13)##chr(10)#</cfloop><cfif arguments.limit>LIMIT #arguments.limit#<cfif
36 : arguments.offset>#chr(13)##chr(10)#OFFSET #arguments.offset#</cfif></cfif><cfif
37 : Len(arguments.comment)>#arguments.comment#</cfif></cfquery>
38 : <cfif StructKeyExists(local, arguments.debugName)>
39 : <cfset local.$wheels.rv.query = local[arguments.debugName]>

I have been working at this problem for over 16 hours and cannot resolve it.  I have lost my patience.   I am in a hurry to get this problem resolved, so I do NOT have time to debug this problem properly in my code.  I know the "correct" solution is to find the mistake in my mysql table structure vs model vs view/new.cfm - I think it has something to do with "UNSIGNED" INT in mysql table, but I need a temporary "hack" for now, until I have more time to debug.  There is too much complex code to post my code here.  I still have no idea which INT field is causing the problem.  

For the "hack", I know most of the how to, but I do not know the "where to".  I do not know / understand the cfwheels internal code.

The "hack" solution is to add the "Val() function to the cfqueryparam
in /wheels/model/adapters/cfquery.cfm WHEN and IF the

    cfsqltype="cf_sql_integer" for example :

<cfqueryparam
 value="#Int(Val( fieldname...value ))#"
 cfsqltype="cf_sql_integer"
 />

    Thanks to Ben Nadel and Tony Petruzzi for the solution, see https://www.bennadel.com/blog/1092-coldfusion-cfqueryparam-binding-vs-sql-execution.htm

To "hack" my solution, some where in /wheels/model/adapters/cfquery.cfm , I need to add some code like the following;

<cfif ( local.$wheels.???cfsqltype??? = "cf_sql_integer" )  ><cfqueryparam
 value="#Int(Val( local.$wheels.???fieldname...value???  ))#"
 cfsqltype="cf_sql_integer"
 /><cfelse>  ...  </cfif>

QUESTION 1) what variable do I use in <cfif ( local.$wheels.???cfsqltype??? = "cf_sql_integer" )

QUESTION 2) what variable do I use in value="#Int(Val( local.$wheels.???fieldname...value???  ))#" 

QUESTION 3) WHERE / HOW do I insert this code it the <cffunction name="$executeQuery" ...

[Reuben Brown]

I think your "hack" solution is going to cause you more problems than you desire.  I suggest you try to limit your form to only post a few fields at a time and see which one breaks the page.

Reuben

--
You received this message because you are subscribed to the Google Groups "CFWheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cfwheels/e772a880-4ae2-4f19-b88e-6b901b63c116%40googlegroups.com.

[Dhg Associates]

Thanks Reuben, I was able to solve my problem by adding, in my /views/new.cfm, 

<!---  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --->
<cfif (false) > <!--- SKIP THIS CODE BELOW  --->
<!---  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --->
   . . . textField( ... )
<!--- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --->  
</cfif>   <!--- SKIP THIS CODE above  --->
<!---  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --->

around each   textField( ... ) that was not required  in my table (i.e. NULL allowed=TRUE).

My stupid mistake what that I had accidently copied a line from my /views/edit.cfm file to my .views/new.cfm.  I had;

    #hiddenField(objectName="pfstmt", property="id")#

I wish cfwheels would have given a nicer error. 

It would have been nice if when (environment="development")  AND in controller create() AND executing save() function,  and primary key (aka "id" field) exists ,

that cfwheels would give a nice error like "Hey stupid, do not include a primary key ('id') in your /views/new.cfm file." :-)

Do you think this would be a worthwhile improvement suggestion? or would it be too much of a performance hit for production?

In SUMMARY, the SOLUTION  was to NOT  put the primary key property (  property="id" ) in /views/new.cfm  file.

Thanks for the help.

[Reuben Brown]

I don't think that would be worth the extra processing.  Do you have the debug output setup on your dev environment?

To me, I would just add a structDelete() to the create() to remove the "id" value if it exists.  That is more a matter of cleaning the data than it is of preventing the error so I am not sure if it is a good "fix" or not.

Reuben

--

You received this message because you are subscribed to the Google Groups "CFWheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/cfwheels/70d3b1a1-68e5-41f2-924f-4046fbef1a3e%40googlegroups.com.