I have a table with about 90 fields, and am trying to use a SQLite prepared statement to insert into the table. The prepared statement executes without raising any exceptions, but nothing is inserted into the table.
So, before I start commenting out fields one-by-one, does anyone have an idea what the problem might be? Is there some limit to the number of parameters a prepared statement can handle?
FWIW, I have done an ExecuteSQL() with limited fields and that works fine.
Better build a sample showing the silent fail and we could try to find some cause. Maybe it’s even a bug needing a report.
That may mean that the statement is not doing what you trying to do but in fact is doing what your code is telling it, add nothing. This has happened to me more times that I would like to admit. Good luck finding the problem.
If you share the code, someone may be able to spot the problem.
If you’re using SQLitePreparedStatement for optimum efficiency (rather than using the implicit prepared statements offered by API 2), those don’t execute as API 2. This means they don’t raise exceptions. You would need to check SQLiteDatabase.Error, SQLiteDatabase.ErrorCode, and SQLiteDatabase.ErrorMessage.
Source: I just tested this.
Try not to make too much noise about this issue, those of us who understand why / when you should use SQLitePreparedStatement do not want the feature being removed. Xojo has made some very short sighted decisions with SQLite changes recently.
have a look at addrow and DatabaseRow its OOP
https://documentation.xojo.com/api/databases/databaserow.html
@Tim_Parnell - I didn’t know it wouldn’t throw an exception - so thanks for that bit of wisdom. But it is also not setting the error flag either.
I reduced the prepared statement to about 25 fields and it worked. So, either there is a quantity limit, or there is something about one of the fields I didn’t include.
I guess I’m going to get to adding fields in one-by-one and see what the problem really is.
@AlbertoD - I am pretty sure it’s doing what I intend it to do. The prepared statement is built by a class I have been using for years. Although, this is probably double the number of fields I have ever had to process.
Let us know what you find.
If a bug, please create a sample project and upload to Issues.
Good luck.
The problem was a Null DateTime being passed as a parameter value. I’ve added a little code to check for that and it’s working now.
That’s interesting news to me. I rarely have an issue with this because I’m working with ActiveRecord. I must have forgotten how AR performs inserts of nils as NULL. I had thought it was the PreparedStatement doing so, but I guess not. Silent failure here is… not great.
Edit: Reviewing the code… it’s definitely supposed to be the prepared statement doing nil->null. We intentionally bind a nil value for Doubles set to the DoubleNullSentinal. Now I have a ton of investigation to do. If this is a regression of prepared statements I’m going to be very mad.
Update 2: It looks like we reworked ActiveRecord to use API 2.0 for nil->null functionality. API 1.0 / SQLitePreparedStatement must use the SQLitePreparedStatement.SQLITE_NULL bind type for nulls.
Well… I was a little less sophisticated with my fix. I just skipped that field if the value was Nil 
