A way to save a type to sqlite?

[Ricky D]

I use types to keep track of sqlite records.

ie

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

 

[Merlot2309]

Hi Ricky,

Maybe missing single quotes are causing the error

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

Succes,
Helen

 

[Ricky D]

thanks but....

how do I retrieve the ID?

   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

 

[Merlot2309]

Hi,

Change details to Details

Helen

 

[Ricky D]

did that but myid = null

:sign0163: please?

regards, Ricky

 

[Ricky D]

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

 

[Ricky D]

well here it is working

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

 

[Ricky D]

Final edition

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

 

[Merlot2309]

Hi,

Good job that you worked it out yourself.

Helen.

 

[Ricky D]

thanks.

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

With a little help from you. Thanks!

regards, Ricky