SQL library compile limits?

[nl1007]

I have a program which uses an SQLite db with two main tables; one has less than 10 fields and about 1300 rows, and the other has 6000 rows with about 50 fields.
I insert new records or update existing records using parameters, and it works in the IDE, but I get an error when running a compiled exe - on the PC and on the mobile device. Is there a limit to how big the SQL string can be, or the number of parameters, when compiled? My other SQL programs haven't shown this problem, so this is the main difference I can think of.
It reads the data OK, but I use a 'select *' to load the data into a table control.
I will try saving/inserting fewer fields, and see if that works, then it will confirm my suspicions..

 

[Erel]

It is possible that you are hitting some memory limit. What error do you get?
SQL db is much more powerful than the Table control. You should only fetch the rows you currently need.

 

[nl1007]

I am only loading selected data; maybe up to 100 records from the first (small) table, and then only the records associated with one of those, mostly 1 or 2, and (so far), no more than 10 from the second table.
I can load more, on the PC, but I still get an error when running a compiled version - so it shouldn't be any different from running from the IDE.
I have now modified the program, so that it only sets a few fields on the Insert, and then uses a series of 'Update's to modify the new record.
The update seems to be working, but maybe I need a pause after the Insert before trying to do an update..
I don't know if there's any way of getting any error information; all I know is that it reaches my 'errorlabel'..

 

[agraham]

II don't know if there's any way of getting any error information; all I know is that it reaches my 'errorlabel'..

Use my http://www.b4x.com/forum/additional-libraries/2305-exceptions-handling-library.html#post12486 to get the reason for the error.

 

[nl1007]

Thanks, Graham.
I get a 'System.FormatException', "Input string was not in a correct format"
(but it works from the IDE)

 

[agraham]

Have you perhaps got some null (as opposed to empty string) values in the database? The IDE may deal with that better than when compiled.

 

[nl1007]

I test for empty strings, and use 'SetNullParameter' for those.
It now works, sometimes - it seems to depend on the data.. I was using an undeclared variable, to handle string and numeric data, but I've declared it as a String, and it may have helped..

 

[agraham]

I was using an undeclared variable, to handle string and numeric data, but I've declared it as a String, and it may have helped..

It won't have been that, undeclared variables are String variables. You can use the DebugRecompiler from my http://www.b4x.com/forum/additional...-legacy-optimised-applications.html#post27120 to find the number of the line that is causing the error. Use version 2.0 in the Debug2.0.zip if you are using Basic4ppc version 6.90 or use the separate version 1.4 if you are using Basic4ppc version 6.80 or earlier. You don't need to use the Debug library. Just comment out your errolabel code and DebugRecompile and the normal Basic4ppc error messagebox will now show the line number. You can then put your errorhandling back in and look at what data values that line is handling when the error occurs.

The error you are getting is usually caused by Basic4ppc trying to convert a string to a number and failing because the string does not represent a valid number.

 

[nl1007]

I think I've got it.. I thought it was the 'SetParameter' code, because that was the last thing I added - but it seems to have been just before that; I was fetching the new values from the form and updating a table control and also writing it back to the database.
The error seems to be as a result of trying to write "" to a table column declared as a number. I had thought that after an 'ExecuteTable', that the previously created columns would be replaced, but maybe not, if they match the fields..
I hadn't tried the 'save' function from a compiled version before I had copied old data from an Access database and checked the 'read' and 'view' parts were OK.

I changed:

...
   x=txtDP.Text
   tblData.Cell("DoorPos",iRow)=x
   If x = "" Then
      Cmd2.SetNullParameter("DP")
   Else
      Cmd2.SetParameter("DP",x)
   End If
to:
   x=txtDP.Text
   tblData.Cell("DoorPos",iRow)=x
   If x = "" Then x = 0
   Cmd2.SetParameter("DP",x)
...