Record not saved through sessions of app run

Harris

Expert
Licensed User
Longtime User
B4X: 
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
 
Sort by date Sort by votes

barx

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
B4X: 
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
 
Upvote 0
E

edgar_ortiz

Active Member
Licensed User
Longtime User
Just for know what is the error:


B4X: 
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
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
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.
 
Upvote 0

Harris

Expert
Licensed User
Longtime User
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).

B4X: 
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.

B4X: 
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...
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…