A way to save a type to sqlite?

Ricky D

Well-Known Member
Licensed User
Longtime User
I use types to keep track of sqlite records.

ie

B4X: 
Type tID(TableID As Int, TableName As String, LastID As Int, AddingNew As Boolean)
Dim ID As tID

   Dim s As String
   
   DBUtils.DropTable(mySQL,"IDs")

   'IDs
   Dim m As Map
   m.Initialize
   m.Put("TableID",DBUtils.DB_INTEGER)
   m.Put("Details",DBUtils.DB_BLOB)
   DBUtils.CreateTable(mySQL,"IDs",m,"TableID")
   'Insert some IDs
   ID.Initialize 
   ID.TableID = 1
   ID.TableName = "IDs"
   ID.LastID = 1
   
   s = "INSERT INTO IDs (TableID,Details) VALUES(1," & ID & ")"
   mySQL.ExecNonQuery(s)

How can I store the type ID into a sqlite table?

I've tried creating an IDs table that is simply TableID Int and Details Blob

doing this causes a crash at the ExecNonQuery call

mySQL.ExecNonQuery(s)


the Type is a string of the fields plus an IsInitialised=True

:sign0163: help?

regards, Ricky
 
Sort by date Sort by votes
M

Merlot2309

Active Member
Licensed User
Longtime User
Hi Ricky,

Maybe missing single quotes are causing the error

B4X: 
 s = "INSERT INTO IDs (TableID,Details) VALUES(1, '" & ID & "')"

Succes,
Helen
 
Upvote 0

Ricky D

Well-Known Member
Licensed User
Longtime User
thanks but....

how do I retrieve the ID?

B4X: 
   Dim myid As tID
   Dim m As Map
   
   m.Initialize 
   m = DBUtils.ExecuteMap(mySQL,"Select TableID, Details FROM IDs",Null)
   
   myid.Initialize 
   myid = m.Get("details")

crashes on last line

java.lang.ClassCastException: java.lang.String

what does that mean?

regards, Ricky
 
Upvote 0

Ricky D

Well-Known Member
Licensed User
Longtime User
did that but myid = null

:sign0163: please?

regards, Ricky
 
Upvote 0

Ricky D

Well-Known Member
Licensed User
Longtime User
the Details is saved as a string in the table and retrieves

[TableName=IDs, IsInitialized=true, LastID=1, TableID=1, AddingNew=false]

How to convert it?

Do I strip off the [] then somehow parse the string to put the values into my type?

How would I do that?

regards, Ricky
 
Upvote 0

Ricky D

Well-Known Member
Licensed User
Longtime User
well here it is working

B4X: 
Sub CreateDatabase
   Dim s As String
   
   DBUtils.DropTable(mySQL,"IDs")

   'IDs
   Dim m As Map
   m.Initialize
   m.Put("TableID",DBUtils.DB_INTEGER)
   m.Put("Details",DBUtils.DB_BLOB)
   DBUtils.CreateTable(mySQL,"IDs",m,"TableID")
   'Insert some IDs
   ID.Initialize 
   ID.TableID = 2
   ID.TableName = "Fares"
   ID.LastID = 23
   
   s = "INSERT INTO IDs (TableID,Details) VALUES(1,'" & ID & "')"
   mySQL.ExecNonQuery(s)
   
End Sub

Sub bReadID_Click
   Dim myid As tID
   Dim m As Map
   
   m.Initialize 
   m = DBUtils.ExecuteMap(mySQL,"Select TableID, Details FROM IDs",Null)
   
   Dim m1 As String 
   m1 = m.Get("details")
   
              'strip off the surrounding []s
   m1 = m1.SubString2(1,m1.Length-1)
   
   Dim com() As String
   
   com = Regex.Split(",",m1)
   
   myid.Initialize 
   
   Dim i As Int
   
   For i=0 To com.Length-1
      Dim s1() As String
      
      If com(i).Contains("TableID") Then
         s1 = Regex.Split("=",com(i))
         myid.TableID = s1(1)
      End If

      If com(i).Contains("TableName") Then
         s1 = Regex.Split("=",com(i))
         myid.TableName = s1(1)
      End If

      If com(i).Contains("LastID") Then
         s1 = Regex.Split("=",com(i))
         myid.LastID = s1(1)
      End If
   
      If com(i).Contains("AddingNew") Then
         s1 = Regex.Split("=",com(i))
         myid.AddingNew = (s1(1)="true")
      End If
   Next
   
   Msgbox(myid.TableID & " : " & myid.TableName & " : " & myid.LastID & " : " & myid.AddingNew ,"")
End Sub

cheers, Ricky
 
Upvote 0

Ricky D

Well-Known Member
Licensed User
Longtime User
Final edition

B4X: 
Sub CreateDatabase
   Dim s As String
   
   DBUtils.DropTable(mySQL,"IDs")

   'IDs
   Dim m As Map
   m.Initialize
   m.Put("TableID",DBUtils.DB_INTEGER)
   m.Put("Details",DBUtils.DB_BLOB)
   DBUtils.CreateTable(mySQL,"IDs",m,"TableID")
   'Insert some IDs
   ID.Initialize 
   ID.TableID = 2
   ID.TableName = "Fares"
   ID.LastID = 23
   
   s = "INSERT INTO IDs (TableID,Details) VALUES(1,'" & ID & "')"
   mySQL.ExecNonQuery(s)
   
End Sub

Sub GetIDDetails(details As String) As tID
   Dim myid As tID
   Dim m1 As String
   
   m1 = details
   
   m1 = m1.SubString2(1,m1.Length-1)
   
   Dim com() As String
   
   com = Regex.Split(",",m1)
   
   myid.Initialize 
   
   Dim i As Int
   
   For i=0 To com.Length-1
      Dim s1() As String
      
      If com(i).Contains("TableID") Then
         s1 = Regex.Split("=",com(i))
         myid.TableID = s1(1)
      End If

      If com(i).Contains("TableName") Then
         s1 = Regex.Split("=",com(i))
         myid.TableName = s1(1)
      End If

      If com(i).Contains("LastID") Then
         s1 = Regex.Split("=",com(i))
         myid.LastID = s1(1)
      End If
   
      If com(i).Contains("AddingNew") Then
         s1 = Regex.Split("=",com(i))
         myid.AddingNew = (s1(1)="true")
      End If
   Next

   Return myid
End Sub

Sub bReadID_Click
   Dim m As Map
   
   m = DBUtils.ExecuteMap(mySQL,"Select TableID, Details FROM IDs WHERE TableID=1",Null)
   
   Dim m1 As String 
   m1 = m.Get("details")
   
   Msgbox(GetIDDetail(m1),"")
End Sub

now I can code for all my tables.

so instead of writing a line of code for each field in a table I'll save it's type with the primary key and then use the Get...Details sub I will create for each table.

This make much cleaner code all over my app.

btw I don't create apps to sell - this is for my own use to help me keep track of work I do as a taxi driver in Brisbane Australia.

The app is data intensive with very few bells and whistles. I currently own a HTC Diamond Touch windows 7 phone and have created this app in Visual studio in Silverlight with C#

I intend to get something like a Samsung Galaxy s2 and this android app is being written in readiness for when I finally get the phone.

Testing on the emulator is good but can't wait to try it's paces out on the real thing.

regards, Ricky
 
Upvote 0

Ricky D

Well-Known Member
Licensed User
Longtime User
thanks.

Perserverance (have I spelt that right?) pays off.

With a little help from you. Thanks!

regards, Ricky
 
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…