SQL changes?

Andras · Aug 1, 2012

Does the current version of B4A handle SQL databases in a different way from the previous 1.x version?

I ask because code that used to work perfectly (and the compiled version still does) now throws up 'SQLiteException:no such table' messages, although the table sure as heck exists...

Any help gratefully received!

John

Ricky D · Aug 1, 2012

To help we'll need to see your code.

Regards, Ricky

Andras · Aug 1, 2012

Ricky D said:
To help we'll need to see your code.

Regards, Ricky

I understand that - the problem is that there's rather a lot of it, and I may not get the troublesome bit right away! But this is the SQL section that actually throws up the error:

B4X:

Sub NamesList_ItemClick (Position As Int, Value As Object)
      Dim Name, Town, Description, Picture, Web, txt, Answ As String
      Panel3.Visible = True
      WebView1.Visible = True
      ScvDescription.ScrollPosition = 0
      Panel2.Visible = False             

      mCursor = mSQL.ExecQuery("SELECT * FROM places WHERE Name = '" & Value & "'")
      mCursor.Position = 0
            
      Name = mCursor.GetString("Name")
      Town = mCursor.GetString("Town")
      Picture = mCursor.GetString("Picture")
      Description = mCursor.GetString("Description")
      WebAddress = mCursor.GetString("Web")
      Latitude = mCursor.GetString("Latitude")
      Longitude = mCursor.GetString("Longitude")
            
      LblDescription.Text = Description
      LblNameandTown.Text = Name & ", " & Town
      ToastMessageShow("Tap name for website or picture for map." ,False)
      If File.exists(File.DirAssets, Picture)= True Then
         ImgPicture.SetBackgroundImage(LoadBitmap(File.DirAssets, Picture))
      Else
         If File.exists(pfilepath, Picture)= True Then
            ImgPicture.SetBackgroundImage(LoadBitmap(pfilepath, Picture))
         Else 
            ImgPicture.SetBackgroundImage(LoadBitmap(File.DirAssets, "default.jpg"))
         End If
      End If
      WebView1.LoadHtml("<html><body>Loading Page - please wait...</body></html>")
End Sub

Of course, the actual problem may lie elsewhere, and I'll happily post more code if that would help!

John

mc73 · Aug 1, 2012

perhaps you should firstly dim a string variable, as
dim tempname as string: tempname=value
and then execute the query using tempname. also you may want to try execquery2.

Erel · Aug 1, 2012

Does the current version of B4A handle SQL databases in a different way from the previous 1.x version?

No. SQL library wasn't changed in v2.x.

Andras · Aug 1, 2012

Erel said:
No. SQL library wasn't changed in v2.x.

Thanks Erel! - good to know it's not that anyway!

John

Andras · Aug 1, 2012

mc73 said:
perhaps you should firstly dim a string variable, as
dim tempname as string: tempname=value
and then execute the query using tempname. also you may want to try execquery2.

Thanks - good points!

DIMMing the strings is something I should have done, of course, but it actually makes no difference.

Changing the appropriate line to

B4X:

mCursor = mSQL.ExecQuery2("SELECT * FROM places WHERE Name = '" & Value & "'")

simply throws up a different set of error messages, namely

B4X:

Error compiling program.
Error description: Missing parameter.
Occurred on line: 391
mCursor = mSQL.ExecQuery2("SELECT * FROM places WHERE Name = '" & Value & "'")
Word: )

John

imbault · Aug 1, 2012

try:

mCursor = mSQL.ExecQuery2("SELECT * FROM places WHERE Name = ? ", Array As String (Value) )

Patrick

Erel · Aug 1, 2012

ExecQuery2 expects another parameter.

You can use this program to browse the database: Android SQLite Viewer

My guess is that the table is indeed missing from the database.

Andras · Aug 3, 2012

Erel said:
ExecQuery2 expects another parameter.

You can use this program to browse the database: Android SQLite Viewer

My guess is that the table is indeed missing from the database.

Thanks, Erel.

That would make sense except that a day of hunting things through shows that it's most certainly there and is findable on both Android and other devices, and careful use of Log statements shows that the program is executing correctly and is finding the database in the right place just like it ought to. Running a vacuum and a repair over the database makes no difference.

But I notice that the Emulator is throwing up a Provider Disabled message when the program starts; is that relevant?

John

Erel · Aug 3, 2012

But I notice that the Emulator is throwing up a Provider Disabled message when the program starts; is that relevant?

I don't think that it is relevant. If you can try to test it with a real device. Can you upload your program and database?

Andras · Aug 3, 2012

Erel said:
I don't think that it is relevant. If you can try to test it with a real device. Can you upload your program and database?

More head-scratching at this end! And a sort of result...

On a real device the uploaded database shows as having zero bytes despite being loaded in the regular way using B4A Bridge. Copying across the real database file into the Files directory on the device makes it work properly; the question therefore becomes, 'Why isn't the db copying across as it ought to?

Here's the full program code - rather a lot of it, I'm afraid:

B4X:

'Activity module
Sub Process_Globals
   'These global variables will be declared once when the application starts.
   'These variables can be accessed from all modules.
      Dim mSQL As SQL
      Dim mCursor As Cursor
   
End Sub

Sub Globals
   'These global variables will be redeclared each time the activity is created.
   'These variables can only be accessed from this module.
   
   Dim Panel1, Panel2, Panel3, Panel4, Panel6, Panel7, Panel10, Panel11 As Panel
   Dim BtnArts, BtnContact, BtnCrafts, BtnEvents, BtnFamily As Button
   Dim BtnGardens, BtnPlaces, BtnPosh, BtnPub, BtnShopping As Button
   Dim BtnTea, BtnTrains, BtnZoo As Button
   Dim UpdateSuccessFlag, UpdateOrNotFlag, UpdatingFlag As Boolean
   Dim NamesList As ListView
   Dim ImgPicture As ImageView
   Dim ScvDescription, ScvChoose As ScrollView
   Dim LblDescription, LblNameandTown, LblPlace, LblWaiting As Label
   Dim WebAddress, Latitude, Longitude, Datasrc, Datafile As String
   Dim WebView1, MapWebView As WebView
   Dim ScaleLat, ScaleLong, CurrentLat, CurrentLong As Float
   Dim lm As LocationManager
   Dim Local, SeenBefore, ChooseLocal As Boolean
   Dim Mess As PhoneSms
   Dim p As PhoneCalls
   Dim BtnPhone As Button
   Dim BtnSMS As Button
   Dim EdtSMS As EditText
   Dim ProgressBar1 As ProgressBar
   Dim pfilepath As  String
   End Sub

Sub Activity_Create(FirstTime As Boolean)
   Dim txt, Info, Dlfilename As String
   Dim FullDate As Long
   Dim Year, Month, ThisYear, ThisMonth As Int

If File.ExternalWritable = True Then
     '   SD Card Installed
        pfilepath = File.DirDefaultExternal
    Else
     '   No SD Card
        pfilepath = File.DirInternal
    End If
   
   ' Log(pfilepath)

 
   lm.Initialize("Location")
   lm.requestLocation
   
   ChooseLocal = False
   UpdateSuccessFlag = True
   UpdateOrNotFlag=False

   Info = DateTime.Date(File.LastModified(pfilepath, "places.db"))

   
   FullDate = DateTime.DateParse(Info)
   Month = DateTime.GetMonth(FullDate)
   Year = DateTime.GetYear(FullDate)
   Info = DateTime.Now
   ThisMonth = DateTime.GetMonth(Info)
   ThisYear = DateTime.GetYear(Info)
   
   mSQL.Initialize(pfilepath, "places.db", True)

   Activity.LoadLayout("Main")
   ScvDescription.Panel.LoadLayout("Detail")
   ScvChoose.Panel.LoadLayout("Choose")
   MapWebView.Initialize("")
   Activity.AddView(MapWebView,0,0,100%x,100%y)
   MapWebView.Visible = False
   

   If ThisYear <> Year OR ThisMonth <> Month Then
      UpdateOrNot
   End If
   
   
   If UpdateOrNotFlag=True Then 

      Datasrc = "http://caron-promotions.co.uk/"
      Datafile = "update.db"
      Dlfilename = Datasrc & Datafile
      ToastMessageShow("Downloading Data Update - Please wait. . .",True)
      HttpUtils.CallbackActivity = "Main" 'Current activity name.
   HttpUtils.CallbackJobDoneSub = "JobDone"
   HttpUtils.Download("Job1", Dlfilename)
   
   End If
      
   If File.Exists(pfilepath, "places.db") = False Then
      File.Copy(File.DirAssets, "places.db", pfilepath, "places.db")
   End If



   
   If File.Exists(pfilepath, "places.bak") = True Then
      UpdatingFlag=True
      Checkpix
      File.Delete(pfilepath, "places.bak")
   End If
   

         

End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
   lm.stopListening
End Sub

Sub LocalOrNot
   Dim txt, Answ As String
   
   If ChooseLocal = False Then
      ChooseLocal = True
      txt = "Results within 30 miles only?"
      Answ = Msgbox2(txt, "", "Yes", "", "No", Null)
      If Answ = DialogResponse.NEGATIVE Then
         Local = False
         Return True
      Else
         Local = True
         Return False   
      End If
   End If
End Sub

Sub UpdateOrNot
   Dim txt, Answ As String
      txt = "Download data update?"
      Answ = Msgbox2(txt, "", "Yes", "", "No", Null)
      If Answ = DialogResponse.NEGATIVE Then
         UpdateOrNotFlag = False
         Return True
      Else
         UpdateOrNotFlag = True
         Return False   
      End If
End Sub

Sub BtnZoo_Click
   LocalOrNot
   PopulateList(12)
   End Sub
Sub BtnTrains_Click
   LocalOrNot
   PopulateList(8)
End Sub
Sub BtnTea_Click
   LocalOrNot
   PopulateList(5)
   End Sub
Sub BtnShopping_Click
   LocalOrNot
   PopulateList(10)
End Sub
Sub BtnPub_Click
   LocalOrNot
   PopulateList(3)
End Sub
Sub BtnPosh_Click
   LocalOrNot
   PopulateList(4)
   End Sub
Sub BtnPlaces_Click
   LocalOrNot
   PopulateList(7)
End Sub
Sub BtnGardens_Click
   LocalOrNot
   PopulateList(9)
End Sub
Sub BtnFamily_Click
   LocalOrNot
   PopulateList(1)
End Sub
Sub BtnEvents_Click
   LocalOrNot
   PopulateList(2)
End Sub
Sub BtnCrafts_Click
   LocalOrNot
   PopulateList(11)
End Sub
Sub BtnContact_Click
   Panel1.Visible = False
   If EdtSMS.Text = "" Then
      EdtSMS.Visible = False
      BtnSMS.Text = "Create Text Message"
   Else
      EdtSMS.Visible = True
      BtnSMS.Text = "Send Text Message"
   End If
   
   Panel6.Visible = True

End Sub
Sub BtnSMS_Click
   Dim SMSText As String
   If BtnSMS.Text = "Create Text Message" Then
      EdtSMS.Visible = True
   Else
      If EdtSMS.Text<>"" Then
      SMSText=EdtSMS.Text
         Mess.Send("07974535783",SMSText)
         ToastMessageShow("Message sent!", False)
         EdtSMS.Text=""
         EdtSMS.Visible = False
         BtnSMS.Text = "Create Text Message"
      End If
   End If
End Sub
Sub BtnPhone_Click
   StartActivity(p.Call("01974299039"))
End Sub

Sub Checkpix


   Dim Counter As Int
   Dim Picture, Dlfilename As String
   

   mCursor = mSQL.ExecQuery("SELECT * FROM places")

   For I = 0 To mCursor.RowCount - 1
      Counter = 0      
      mCursor.Position = I
      Picture = mCursor.GetString("Picture")
      If File.Exists(pfilepath, Picture) = False AND File.Exists(File.DirAssets, Picture) = False Then
         Datasrc = "http://caron-promotions.co.uk/"
         Datafile = Picture
         Dlfilename = Datasrc & Datafile
         ToastMessageShow("Almost done...",True)
         HttpUtils.CallbackActivity = "Main" 'Current activity name.
         HttpUtils.CallbackJobDoneSub = "JobDone"
         HttpUtils.Download("Job2", Dlfilename)
      End If
      Counter = Counter + 1
    Next

End Sub

Sub JobDone (Job As String)
    Dim updatestatus, Dlfilename As String
    Dim In As InputStream, Out As OutputStream
    Dlfilename = Datasrc & Datafile
   
   UpdatingFlag=True
    
   If HttpUtils.IsSuccess(Dlfilename) Then
        Select Job 
         Case "Job1"
                  
                ' Data

                If HttpUtils.IsSuccess(Dlfilename) Then
                    In = HttpUtils.GetInputStream(Dlfilename)
                    
                    Out = File.OpenOutput(pfilepath, Datafile, False)
                     File.copy2(In,Out )
                    Out.Flush
                    Out.Close

                    File.copy(pfilepath,"places.db",pfilepath,"places.bak")
                '    File.copy(pfilepath,"squiggle.db",pfilepath,"places.db")
                File.copy(pfilepath,"update.db",pfilepath,"places.db")
               File.Delete(pfilepath, "update.db")
                    
                Else
                    ToastMessageShow("Problem downloading data, will retry later!", False)
                   UpdateSuccessFlag = False
               UpdatingFlag=False
            End If
            
            UpdatingFlag=False
              
              
            Case "Job2"
                ' Pictures

                If HttpUtils.IsSuccess(Dlfilename) Then
                    In = HttpUtils.GetInputStream(Dlfilename)
                    
                    Out = File.OpenOutput(pfilepath, Datafile, False)
                     File.copy2(In,Out )
                    Out.Flush
                    Out.Close


                    ToastMessageShow("Data downloaded OK!", False)
                  Else
                    ToastMessageShow("Problem Downloading Data!", False)
                End If
            UpdatingFlag=False
        End Select
   Else
       ToastMessageShow("Can't Connect to Server: Check Internet Connection or Try Later!",True)
   End If
   SeenBefore = True
   Activity_Create(True)
   UpdatingFlag=False
End Sub

Sub BtnArts_Click
   LocalOrNot
   PopulateList(6)
End Sub
Sub BntBack_Click
End Sub
Sub PopulateList(a As Int)   ' was a As 
   Dim m, LatMost, LatLeast, LongMost, LongLeast As Float
   Dim Found As Boolean
   Dim Counter As Int
   Dim Name, Town, Description, Picture, Web, txt, Answ As String
   Found = False
   NamesList.Clear
   m=0.5
   If Local = False Then
       mCursor = mSQL.ExecQuery("SELECT * FROM places where Type  = '" & a & "' order by Name")
      
   
   Else
      LatMost = CurrentLat+m
      LatLeast = CurrentLat-m
      LongMost = CurrentLong+m
      LongLeast = CurrentLong-m
      mCursor = mSQL.ExecQuery("SELECT * FROM places where Type  = '" & a & "' and Latitude < '" & LatMost & "' and Latitude > '" & LatLeast & "' and Longitude < '" & LatLeast & "' and Longitude > '" & LongMost & "'  order by Name")
   End If
   
   For I = 0 To mCursor.RowCount - 1
      Counter = 0      
      mCursor.Position = I
      Name = mCursor.GetString("Name")
      Town = mCursor.GetString("Town")
      Description = mCursor.GetString("Description")
      Picture = mCursor.GetString("Picture")
      Web = mCursor.GetString("Web")
      NamesList.AddSingleLine(Name)
      Found = True
      Counter = Counter + 1
    Next
   
   If Found = False AND Local = False Then
      ToastMessageShow("Sorry - we didn't find any matches!",False)
      LblDescription.Text = ""
   End If

   If Found = False AND Local = True Then
      txt = "No match; widen search?"
      Answ = Msgbox2(txt, "", "Yes", "", "No", Null)
      If Answ = DialogResponse.POSITIVE Then
         Local = False
      End If
   End If
            
      If Found = True Then      
         
         Panel2.Visible= True
         
         
      Else
         Panel1.Visible = True
      End If
      mCursor.Close

End Sub
Sub NamesList_ItemClick (Position As Int, Value As Object)
      Dim Name, Town, Description, Picture, Web, txt, Answ As String
      Panel3.Visible = True
      WebView1.Visible = True
      ScvDescription.ScrollPosition = 0
      Panel2.Visible = False             

      mCursor = mSQL.ExecQuery("SELECT * FROM places WHERE Name = '" & Value & "'")
      mCursor.Position = 0
            
      Name = mCursor.GetString("Name")
      Town = mCursor.GetString("Town")
      Picture = mCursor.GetString("Picture")
      Description = mCursor.GetString("Description")
      WebAddress = mCursor.GetString("Web")
      Latitude = mCursor.GetString("Latitude")
      Longitude = mCursor.GetString("Longitude")
            
      LblDescription.Text = Description
      LblNameandTown.Text = Name & ", " & Town
      ToastMessageShow("Tap name for website or picture for map." ,False)
      If File.exists(File.DirAssets, Picture)= True Then
         ImgPicture.SetBackgroundImage(LoadBitmap(File.DirAssets, Picture))
      Else
         If File.exists(pfilepath, Picture)= True Then
            ImgPicture.SetBackgroundImage(LoadBitmap(pfilepath, Picture))
         Else 
            ImgPicture.SetBackgroundImage(LoadBitmap(File.DirAssets, "default.jpg"))
         End If
      End If
      WebView1.LoadHtml("<html><body>Loading Page - please wait...</body></html>")
End Sub

Sub LblNameandTown_Click
   Panel4.Visible = True
End Sub
Sub ImgPicture_Click
   ToastMessageShow("Fetching new map - this may take a few moments. . .",True)

   ShowMap(Latitude, Longitude, 15, True, True, True, "TOP_LEFT", True)
   
End Sub
Sub WebView1_PageFinished (Url As String)
   If Url = "file:///" Then
       WebView1.LoadUrl(WebAddress)

    End If
End Sub
Sub ShowMap(CenterLat As Float, CenterLong As Float, Zoom As Int, MapTypeControl As Boolean, DispZoomControl As Boolean, DispScaleControl As Boolean, ScaleControlPosition As String, DispMarkerCenter As Boolean)
   
    Dim HtmlCode As String
   Dim i, j As Int
   MapWebView.Visible = True
   
   HtmlCode = "<!DOCTYPE html><html><head><meta name='viewport' content='initial-scale=1.0, user-scalable=no' /><style type='text/css'>  html { height: 100% }  body { height: 100%; margin: 0px; padding: 0px }#map_canvas { height: 100% }</style><script type='text/javascript' src='http://maps.google.com/maps/api/js?sensor=true'></script><script type='text/javascript'> function initialize() {var latlng = new google.maps.LatLng(" & CenterLat & "," & CenterLong & "); var myOptions = { zoom: "&Zoom&", center: latlng, disableDefaultUI: true, zoomControl: "& DispZoomControl & ", scaleControl: "& DispScaleControl & ", scaleControlOptions: {position: google.maps.ControlPosition." & ScaleControlPosition & "}, mapTypeControl: "& MapTypeControl& ", mapTypeId: google.maps.MapTypeId.ROADMAP }; var map = new google.maps.Map(document.getElementById('map_canvas'),  myOptions)" 

   ' displays a marker on the map center
   If DispMarkerCenter = True Then   
      HtmlCode = HtmlCode & "; var markerc = new google.maps.Marker({   position: new google.maps.LatLng(" & CenterLat & "," & CenterLong & "),map: map, title: '',clickable: false,icon: 'http://www.google.com/mapfiles/arrow.png' })"
   End If
   

   HtmlCode = HtmlCode & "; }</script></head><body onload='initialize()'>  <div id='map_canvas' style='width:100%; height:100%'></div></body></html>"

   MapWebView.LoadHtml(HtmlCode)
End Sub

Sub Location_LocationChanged (Longi As Double, Lati As Double, Altitude As Double, Accuracy As Float, Bearing As Float, Provider As String, Speed As Float, Time As Long) 
   CurrentLat = Lati
   CurrentLong = Longi

End Sub
Sub Location_ProviderDisabled (Provider As String)
   Msgbox("Provider","Provider Disabled")
End Sub

Sub Location_ProviderEnabled (Provider As String)
   Msgbox("Provider","Provider Enabled")
End Sub

Sub Location_StatusChanged (Provider As String, Status As Int)
   Msgbox("Provider: " & Provider & CRLF & "Status: " & Status,"Status Changed")
End Sub
Sub Activity_KeyPress (KeyCode As Int) As Boolean
   
   ' Add more information here as required!   
   
   If KeyCode = KeyCodes.KEYCODE_BACK Then
      If MapWebView.Visible = True Then
         MapWebView.Visible = False
      Else If Panel6.Visible = True Then
         Panel6.Visible = False
         Panel1.Visible = True
      Else If Panel4.Visible = True Then
         Panel4.Visible = False
      Else If Panel3.Visible = True Then
         LblDescription.Text = ""
         Panel3.Visible=False
         Panel2.Visible = True
      Else If Panel2.Visible = True Then
         Panel2.Visible = False
         ScvChoose.ScrollPosition = 0
         Panel1.Visible = True
      Else If Panel1.Visible = True AND UpdatingFlag = False Then
   '   Else If Panel1.Visible = True Then
         Activity.finish         
        End If
      Return True
    End If
End Sub

Sub EdtSMS_TextChanged (Old As String, New As String)
   BtnSMS.Text = "Send Text Message"   
End Sub

What's particularly strange is that this code - unaltered - was working fine with the same db a month ago!

The db would be rather a pig to copy in, but now I suspect that doesn't matter anyway.

Any thoughts? Thanks anyway!

John

mc73 · Aug 3, 2012

You should move this line

B4X:

mSQL.Initialize(pfilepath, "places.db", True)

below this line

B4X:

If File.Exists(pfilepath, "places.db") = False Then
        File.Copy(File.DirAssets, "places.db", pfilepath, "places.db")
    End If

otherwise, during the initialization, the database is creates, thus, your if returns a true. This way, your db will not get copied from assets. At least. this is what I suppose.

Andras · Aug 4, 2012

An interesting idea, and certainly one that rang a bell with me.

Sadly it doesn't make any difference.....

John

mc73 · Aug 4, 2012

Andras said:
An interesting idea, and certainly one that rang a bell with me.

Sadly it doesn't make any difference.....

John

Did you firstly erase the db that is present in your folder now, before the change I suggested? If not, surely it will make no difference, since no new copy will be installed.

Andras · Aug 4, 2012

mc73 said:
Did you firstly erase the db that is present in your folder now, before the change I suggested? If not, surely it will make no difference, since no new copy will be installed.

Starting with a completely 'new sheet' - that is, compiling and installing the program on a 'clean' device - a new empty db file with the correct name (but no contents) is created in the Files folder, and the existing real db in the Files folder of the originating computer isn't copied over. If it's copied manually into the device's Files folder then it all works fine.

This applies regardless of whether or not the changes you suggest have been made. Cute, isn't it! I'm absolutely tearing my hair out here!

John

mc73 · Aug 4, 2012

Andras said:
Starting with a completely 'new sheet' - that is, compiling and installing the program on a 'clean' device - a new empty db file with the correct name (but no contents) is created in the Files folder, and the existing real db in the Files folder of the originating computer isn't copied over. If it's copied manually into the device's Files folder then it all works fine.

This applies regardless of whether or not the changes you suggest have been made. Cute, isn't it! I'm absolutely tearing my hair out here!

John

Did you try removing your db from the files tab of the IDE and setting it back there?

Andras · Aug 4, 2012

Yes - makes no difference!

John

mc73 · Aug 4, 2012

Can you zip and post here?

Andras · Aug 4, 2012

mc73 said:
Can you zip and post here?

Sorry - post what exactly? The app code's already up, of course, and the db is just a standard SQLite file; if you do mean the db file I'm not quite sure how to post it here; it's only 160k, but of course it isn't easily susceptible to copying as if it were a text file...

I'm really grateful for your help here - it's a real puzzler...

John

SQL changes?

Active Member

Well-Known Member

Active Member

Well-Known Member

B4X founder

Active Member

Active Member

Well-Known Member

B4X founder

Active Member

B4X founder

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Similar Threads