PreparedStatement issues

I am having issues with PreparedStatements with SQLite. When I try to update a record the PreparedStatement errors out with a very vague error code. And it doesn’t tell me which field is unhappy with. the SQL that is feed to the PreparedStatement looks good.

Is there any way to see what the actual SQL code is that the PreparedStatement is issuing to SQLExecute? Or is PreparedStatement code is completely closed off?

I am not hinting that the issue is with Xojo or its plugins/framework. It could be PEBKAC. I just need to be able to debug it more before pointing fingers.

PS> I have removed all the unique’s/not null/etc from every field except the PKID. So it is not a field issue.




This is my biggest issue with PS… especially if they are more on the complex site…

Are you sure the error is related to the PS itself? or the fact that perhaps the Database just doesn’t like/allow what is being attempted (ie. the PS is fine, the DB rules aren’t)

I thought I saw a line item in a recent release notes about Xojo moving to a newer version of SQLite that would display details of why PreparedStatements failed. Might be in the current pre-release.

In situations like this I will do the SQL statement manually and run it to get a better error message. Not ideal but hopefully you’ll figure it out eventually.

Can you show us your prepared statement? Maybe try creating a small example project to see if the problem persists there and if it does so we can try to replicate the issue?

I have found that barring an outright Syntax Error in your SQL statement, a mismatch between the field names and place holders in the PS, and/or Bind/Bindtypes counts not matching usually result in “CANNOT PREPARE” type error

I would double-check the data types that you’re binding with, and especially the params that involve numeric and boolean values.

There is however a recent feature request to implement a method to return the prepared sql as a string.

Would be great if we had that for debugging. What FB ?


Sorry folks.
Got distacted with other coding projects and havent looked at this.
I would get two different SQLite error codes/messages depending on which version of the IDE i used. 2017r2.1 vs 2018r1

once i can break away from the other drama I will come back to this and let you know what I find out.

PS> I still think its my code.