Record not saved through sessions of app run

[Harris]

Sub SaveforServer( srv As String )

Dim np As Long
Dim c As Cursor

np = GetPkey
  
SQL1.BeginTransaction
Try
  SQL1.ExecNonQuery2("INSERT INTO ForServer VALUES(?,?,0)", Array As String(np, srv ))
  SQL1.TransactionSuccessful
Catch
  Msgbox(" ForServ not saved"," Error ")
End Try
SQL1.EndTransaction


End Sub

The code above is used to insert a record into the ForServer table in my DB.
Fields: PID - Long, Qry - Text, Sent - Bool.

It is the same as any other table in my DB, yet everytime I compile and update the app on my test device(s), the records I saved in the previous session are not there - table is empty!!? If I shut down the device and start it back up, the same - none of the records I just inserted are there.

I use:

WebView1.LoadHtml(DBUtils.ExecuteHtml(Defs.SQL1, _
"SELECT * FROM ForServer" , Null, 0, True))

to see the data I just inserted in a webview (Thanks Erel - this is sweeeet..).

No code exists to ever delete this records.
The records in all other tables are still intact - no problem - ever.

Any Suggestions?
Never had this issue before with many tables and records I have created in this and other apps.

I could post the entire project if need be.

Thanks

 

[barx]

What code do you use to copy the database to a writable location in the first place?

Maybe each time your db is 'wiping' it is in fact re-copying the database from assets.

 

[Harris]

If File.Exists(File.DirDefaultExternal, "MyDB.db") = False Then
      File.Copy(File.DirAssets, "MyDB.db", File.DirDefaultExternal, "MyDB.db")
      Log(" Copying MyDB.db to SD card")
   End If
 
   If SQL1.IsInitialized = False Then
      SQL1.Initialize(File.DirDefaultExternal, "MyDB.db", False)
      Log(" ***** - - - Initing MyDB.db if not already inited **********")
   End If

MyDB is a generic name.
I don't copy the file to dir external if it already exists.
No other table is affect - they still contain records I added from previous sessions.

As I make structure changes to my tables, I often "force" a new db to replace the old db by commenting the if/end if of the first statement. - After this all tables will be empty. This is not my problem in this case. It is just weird...

The data in the Qry text field is actually a Query statement I prepared.
It contains the text like: INSERT INTO Table1 (PID, Name, Address, lot, lat) VALUES("1","John","123 Main Street","-123.9987","50.233"). Depending on the table I am populating, this statement can be 200 characters long - but the Text field type should handle this(?).

I will then use this statement to to populate my hosted MySQL database using the method described in MySQL tutorial Erel produced. That works fine as well.


Thanks

 

[edgar_ortiz]

Just for know what is the error:

SQL1.BeginTransaction
Try
  SQL1.ExecNonQuery2("INSERT INTO ForServer VALUES(?,?,0)", Array As String(np, srv ))
  SQL1.TransactionSuccessful
Catch
  Msgbox(" ForServ not saved" & LastException.Message," Error ")
End Try
SQL1.EndTransaction

 

[Harris]

Just for know what is the error:

There is no error generated... It just doesn't commit the record.

Is there a "Flush to Disk" in SQLlite I should try?

 

[Erel]

Your code looks correct. Are you running Android 2.2? There is a bug on this version where File.DirDefaultExternal gets deleted when you reinstall your app.

 

[Harris]

I am running 2.3.3 on my Samsung I896.

Unfortunately, the same thing happens on my Nexus 7 - 4.2.1

I shall keep plugging away - has to be an explanation in the code somewhere...
If you never hear from me again - you will know this drove me insane.

 

[Harris]

Found It!!!!!

I used Erel's "InsertMaps" in DBUtils as a model for my own "InsertServerMaps".

Instead of inserting a record, it returns the entire SQL statement (as string) that would have inserted a record (formatted to pass to php in MySQL server).

sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
         If i1 = 0 Then Log("InsertServerMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
         'SQL.ExecNonQuery2( sb.ToString, listOfValues)
         
         Return sb.ToString
         
      Next
      SQL.TransactionSuccessful
      ToastMessageShow("Record Saved", True)
      
   Catch
      ToastMessageShow(LastException.Message, True)
      Log(LastException)
   End Try
   SQL.EndTransaction

Notice my stupidity: 'SQL.ExecNonQuery2( sb.ToString, listOfValues)
and directly - Return sb.ToString
I commented out the Insert and returned the string...
This BROKE the Begin - End Transaction (left it open / incomplete) so the next attempt (called in the next line) failed to "save to disk" miserably.

Sub SaveforServer( srv As String, Pk As Long, DT As Long, Comp As Long )

   Dim np As Long
   'Dim c As Cursor

   np = GetPkey
     
   SQL1.BeginTransaction
   Try
     SQL1.ExecNonQuery2("INSERT INTO ForServer VALUES(?,?,0,?,?,?)", Array As String(np, srv,Pk,DT,Comp ))
     SQL1.TransactionSuccessful
   Catch
     Msgbox(" ForServer record not saved"," Error ")
   End Try
   SQL1.EndTransaction


End Sub

I removed All SQL.(whatever) form here - moved the "Return sb.ToString" to the end and now all is fine...

In the help for BeginTransaction it states: "It is very important to handle transaction carefully and close them" - (or suffer the consequences....)

Now - on to a new post - proper Handling of multiple calls to ExecuteRemoteQuery using httputils...