SQL changes?

[Andras]

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

 

[klaus]

You should post the whole project including the database file (IDE menu Files / Export As Zip) so we know exactly what you have done and how and we can test it in the same conditions as you. Otherwise we are turning in circle.

Best regards.

 

[mc73]

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

Sure, the source is here, but there are many views. In order to test and see what happens, it would take that we reconstruct your layout and your db. you can use file->export as zip. As Klaus just said, let's not make circles

 

[Andras]

OK - thanks!

Here it is!

John

 

[mc73]

I don't understand. This is a sql dump file.

 

[Andras]

I don't understand. This is a sql dump file.

Sorry - here's a zip of the original db file just as it is in the Files folder:

John

 

[klaus]

Why don't you post the WHOLE PROJECT (IDE menu Files / Export As Zip) as a zip file as requested ?
Or at least a smaller one showing the problem.

Best regards.

 

[Andras]

Why don't you post the WHOLE PROJECT (IDE menu Files / Export As Zip) as a zip file as requested ?
Or at least a smaller one showing the problem.

Best regards.

Sorry - because I hadn't understood what was being asked for!

This OK?

Thanks!

John

 

[mc73]

Ok. Sure you folllowed my instructions, here? When I made the movement, the db is loaded properly, I can see all of your elements in your forms

 

[Andras]

Ok. Sure you folllowed my instructions, here? When I made the movement, the db is loaded properly, I can see all of your elements in your forms

Yes, I had made the change - and found it didn't work, so I reverted to the way it was on the grounds that it's foolish to mess with more than one thing at a time.

Following your helpful comment above I went right back and put it where you'd originally said, and Lo and Behold - it still doesn't work and generates the same error message!

Zip file attached again! I really am grateful!

John

 

[mc73]

It should work. At least it worked for me. Take in consideration that before reinstalling the corrected version, you should manually delete the database from the installation folder.

 

[Andras]

It should work. At least it worked for me. Take in consideration that before reinstalling the corrected version, you should manually delete the database from the installation folder.

And this time, although I've gone through exactly the same steps as before, it does work! There's something seriously strange going on here, and I'm inclined to put it down to a system bug or glitch of some sort, of which I've seen far too many in my time!

What I find rather surprising is that the ios version of the app - not programmed by me, this time - sometimes has different - but not that different - tantrums. All part of the fun and games of programming, I suppose!

Either way, very many thanks. This really is a superb forum, thanks to the very helpful people on it. I am most grateful!

John

 

[mc73]

By the way, I've noticed another bug, but not sure whether you've already taken care of it. When you're loading images after the click_event in your first list, sometimes the image doesn't exist, causing an error, which you should catch of course, or even load a blank.gif.

 

[Andras]

By the way, I've noticed another bug, but not sure whether you've already taken care of it. When you're loading images after the click_event in your first list, sometimes the image doesn't exist, causing an error, which you should catch of course, or even load a blank.gif.

Yes, there's a 'default.jpg' picture which catches any occasion when the picture doesn't turn up for some reason. Seemed the easiest solution! This is the code that does it:

If File.exists(pfilepath, Picture)= True Then
            ImgPicture.SetBackgroundImage(LoadBitmap(pfilepath, Picture))
         Else 
            ImgPicture.SetBackgroundImage(LoadBitmap(File.DirAssets, "default.jpg"))
         End If

I really need to move the two relevant click events (on the name and on the piccy) onto buttons - which is what the ios version already does - so that will need a revision at some point, probably later this month.

But thanks for the heads-up; bugs do like to hide in the dark where you can't spot them until they've bitten you, don't they!

John

 

[mc73]

But thanks for the heads-up; bugs do like to hide in the dark where you can't spot them until they've bitten you, don't they!

John

haha, true!

 

[Andras]

Actually your question brings up the whole thing that got me rummaging about in this (previously working!) code anyway.

As you remark, the pictures don't always 'exist'; actually they always do - they're up on the server from which the updates are taken together with the latest version of the database - but sometimes, for reasons I haven't managed to work out, they don't download.

This is the code that ought to do it (and has done before this month!)

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

Basically this checks whether or not a picture exists in the appropriate directory on the device and then calls HttpUtils if it doesn't. So far, so good - and this does work (it goes a little around the houses, but since we don't know anything about the state of the data on the device that's acceptable).

Unfortunately HttpUtils is now downloading the updated database fine, but is throwing up an error message when it comes to looking for the pictures, though they are certainly physically there on the server. This is the relevant HttpUtils code, and we're looking at Job2:

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

What I'm now getting is the final Toast Message instead of the pictures being downloaded. It was this that I was trying to sort out to start with - any thoughts?

John

 

[mc73]

First of all, I think you should move this

 Dim Picture, Dlfilename As String

inside your 'for I' loop. By the way, what does counter count? I see that you set it to 0 inside your loop, I guess you shouldn't do this.
Another notice about your first routine. Why you check for dir.assets too? aren't you supposed to simply copy from it in your activity_create, when the file is absent from pFilePath?
Finally, but I can't be sure about this, I cannot say for sure, that all your downloads will be done. Seems to be that they are performed in parallel, while I would choose (just to be sure) a serial way of doing this. I mean, use the htpputils.download, then continue to next download only after finishing the first and so on.

 

[Andras]

Thanks for the thoughtful reply. Just a couple of quick thoughts before I get into the code in depth.

First, is it actually legitimate to have a DIM statement inside a loop? Doesn't seem right to me, though admittedly it does seem to work...

Second, I see your point about serial / parallel loading; I'll check the code in detail and see if I can tighten it up to remove any bottlenecks there.

Third, what does Counter do? I think the answer is probably what the biologists call 'Junk DNA' - something that may have been useful at some earlier stage of evolution, but has been bypassed by later developments - a bit like the human appendix! And like the appendix, I should probably take it out!

John

 

[mc73]

Though dimming inside the loop will not be a problem, I think that I got confused with other cases, and in your case it won't make a difference.
About serial download, I think that if you get trouble, you could try to initiate every single httputils job, with something like job1,job2,jobN, where N stands for the downloaded N-file. Now, if httpUtils can handle this type of thing, I am not the one to say, I think other members of this forum can help.