Rev4 Studio SQLite problem

[jkrische]

Hello gang; long time no see...

I've got a rev app I've been working on in my spare time for months now and was very near release when rev4 came out (I'm on 3.5 normally). I downloaded the trial and installed it to see what was new, especially the new dynamic image effects. Very handy, simple to use, all is well there.

Unfortunately, something must have changed in a serious way in the DB library, at least wrt sqlite. A dead-simple "select a_field from a_table where the_ID=" & myID & ";" type of query no longer works at all. I'm getting the dreaded invalid cursorID error, which makes no sense because just a couple lines previously I got and ANSWERED out a normal, good connection ID.

So my script, which was working perfectly in 3.5, is barfing for no good reason that I can tell in 4.0, and my code didn't change by even 1 character. Is this merely trial version weirdness or have I stumbled into something more serious?

I don't think you'll need it, but here is some sample code that is not working in 4.0 but is just peachy in 3.5 (note that there are some debugging stubs left in it):

Code: Select all

... other script code...

put "my_file/path/to/dbfile.db" into theDBPath

put revOpenDatabase("sqlite",theDBPath,,,,,,) into tConID 
   //answer "Connection: " & tConID
   
   if tConID is "" then 
      answer warning "Problem creating or accessing database (Initial Load)!"
   else
      //answer information "DB Connected! Your connection ID is: " & tConID
      put tConID into gConID
      
      // get global variables from the DB which record the "last working state" of everything      
      put "SELECT * FROM my_table WHERE myRecID=1" into tSQL
      put revQueryDatabase(tConID,tSQL) into tResultID
      //answer tSQL & cr & cr & tResultID
      
      put revDatabaseColumnNamed(tResultID,"oneOfMyFields") into gSomeVariableName

... more unrelated code ...

So... any ideas?

[jkrische]

OK, well, solved my own problem again and thought I would post what it was just in case it might benefit some other developer who hits the same issue. I should also note that I had this same problem with revstudio 4 trial version on both Win and Mac: exactly the same error results, and exactly the same solution fixes it.

The issue had very little to do with my code per se; it had more to do with something that has apparently changed in 4.0 that I've yet to find any mention of in the changenotes. It seems that the way Rev handles file paths is a little different in 4.0 than it was in 3.5.

For my solution in 3.5, authored in Windows, I have a little block of code in preOpenStack on the main stack that detects the platform, for purposes of correctly identifying where the sqlite db file will be located. For Mac I tell my script that the DB will be located on a relative file path inside the .app package. For any other platform, the script assigns a relative path of the same directory the executable is in. This location step must be completed before the app can connect to the DB file correctly and get the full schema and any data already in storage. The block of code looks like this:

Code: Select all

    if(the platform is "MacOS") then 
      put "./myAppName.app/Contents/MacOS/xyz.db" into theDBPath
   else 
      put "./xyz.db" into theDBPath
   end if

Under 3.5, this worked perfectly. But, as mentioned in the OP, it failed under 4.0 with baffling results, wherein the inital connection would succeed but any query within that connection would fail.

By happenstance, I opened the directory where revstudio 4 itself lives (4.0.0-gm-1) and... hello hello hello, what's all this then? A file that should not be there: the xyz.db file! Instead of looking in the same directory of the .rev file I'm working with, revstudio4 was looking in its own binary directory. Not finding said file in its own binary directory, Rev had created a new, empty copy of this file. So, yes, a connection would work just fine, but there'd be no schema nor data in it, so all queries against it would fail.

So, a new file path detector method was needed in my script. Here's what I came up with. Any revgurus out there who want to suggest something better, by all means...

Code: Select all

   put the filename of this stack into tFileName
   replace "/MyRevFileName.rev" with "" in tFileName
   put the longFilePath of tFileName into tFilePath
   put tFilePath & "/xyz.db" into theDBPath

Of course, for the final build I'll need to tweak that a good bit, but it will work for now.

I do think this exposes a serious flaw, though. If a DB (or any other kind of) file cannot be found, I should get an error to that effect, rather than the app creating a new, empty copy of said file. If I want it to create the file, I should have to explicitly tell it to do so. Imagine going to the store and asking for a banana, and the clerk, not finding any on the shelf, hands you an empty banana skin as if that's good enough. Is that not exactly what's going on here? I thought software was only supposed to do what it is instructed to do. Of course, I have no idea if it's rev or if its the sqlite engine creating the new empty file on its own accord; I'll let some revguru answer that issue.

[malte]

Hi,

this has always been the case if I recall correctly. If the db file does not exist, it is automagically created. I recall vaguely to have seen that in some docs somewhere, though today I am unable to find it. To check if the db exiss, I always use there is a file "xyz.db"

Cheers,

Malte