SQLite on Linux?

[bmcgonag]

I've been using the SQLite tutorial to get my database ready and set on the first run of my application. It works great on Windows and Mac, but in Linux, the database is not created.

So, again I ask if there is a special way this has to be done on linux. Thanks to the help from my last post, I can tell whether the user is running from Mac, Windows, or Linux.

Here's my code for creating the db.

Code: Select all

if sysPlatform is "Linux" Then
      put ("~/Documents/CrossNotes") into sqlPathMain
   Else
      put specialFolderPath("documents") & "/CrossNotes" into sqlPathMain
   End if
   
   put sqlPathMain & "/crossNotes.sqlite" into tDBPath
         ## Open a connection to the database
         ## If the database doesn't already exist, create it
         put revOpenDatabase("sqlite", tDBPath,,,,) into tDBaseID
         answer "The database should now be created at " & tDBPath
         ## Store the database ID returned from above
         setDBaseID tDBaseID
         answer "tDBaseID is " & tDBaseID

I just get an error, "Database Error: Unable to open the database file"

Any help is, as always, greatly appreciated.

[bmcgonag]

So, just to check, I used sqlite3 to create a database with the same name as that in my app, and created the 1 table and field I need to store. I then ran my app again and it failed at the revOpenDatabase() line.

Is this not compatible with Ubuntu 14.04 64 bit perhaps? I've just upgraded to the LiveCode 7 dp 8 64 bit, with the same results.

I don't currently have a 32 bit Linux system to try it on. I'll see what I can setup tonight. Still seeking any help. Thanks,

[bmcgonag]

Alrighty. got a 32 bit linux system going, and still no luck - using LIveCode version 7 dp 8. Still creates the folder, but doesn't create the sqlite db, which as I said before is working perfectly on mac and Windows systems.

[FourthWorld]

Can your app create any other file in that folder?

[Mark]

Hi,

I se that you're using "~/Documents/CrossNotes" into sqlPathMain as a path. If your file system is case sensitive and the actual name of t he documents folder is documents instead of Documents, LiveCode would be unable to find the file in the documents folder. I don't know if this will solve the problem, but you might want to check this.

Kind regards,

Mark

[bmcgonag]

@Richard - great question. I will try it tonight and see.

@Mark - excellent thought, but in checking that the folder names are capitalized correctly.

In fact the application when run, will first create the 'CrossNotes' folder inside the 'Documents' folder. This works just fine, but once created, no sqlite db is created inside teh 'CrossNotes' folder.

[bmcgonag]

@Richard - I am able to write a .txt file into the location, but still cannot get the .sqlite db to be created there.

Any other thoughts or ideas I might try?

[MaxV]

If you want to create a real cross-platform application, thus I mean that works on Win,Linux,Mac; I suggest you to use relative paths.
To use relative path, you can set in the main stack this code:

Code: Select all

on preOpenStack
  set itemDel to "/"
  set the defaultFolder to item 1 to -2 of (the effective fileName of this stack)
end preOpenStack

[bmcgonag]

@MaxV - Can you explain what that code does please, and how it helps with the issue of creating a .sqlite file in my Linux system?

Always appreciative of more knowledge.

[MaxV]

OK, I'll explain you.
First of all put in your main stack this code:

Code: Select all

on preOpenStack
    set itemDel to "/"
    set the defaultFolder to item 1 to -2 of  (the effective fileName of this stack)
     #check folder CrossNotes
     If there is a folder "./CrossNotes"
        Answer "folder  CrossNotes found"
    Else
        Answer "folder not found,creating a new one"
        Create folder "./CrossNotes"
    End if
end preOpenStack

Now you have to use always relative path, I mean "./CrossNotes/crossNotes.sqlite".
This solution works on any OS: wherever user put the program, he has also the right to create file and folder.

Sent from my mobile phone

[sturgis]

Actually, relative pathing may not be such a good idea. If the executable is going to be available to all users, that means it will be in /bin or /sbin or.. somewhere in the path, and its doubtful that a regular user will have write rights to that folder. If its only in userspace, then not such a big deal.

I'm wondering, what permissions are set on the file and folder that are created. They SHOULD be user writable but it doesn't hurt to check.

One other thing you might do is to adjust (just for testing) how you are creating the folder and file.

So, you might.., set the defaultfolder to "~/Documents"
create the folder (no pathing needed, you're already in the right spot due to setting the defaultfolder)
and then pop up a dialog listing "the folders"

Then set the defaultfolder to "~/Documents/yourFolderName"
and try to create the database. Again, no pathing needed, just the filename because you're already sitting in the correct folder (having set it to the defaultfolder)
then pop up a dialog to list the files.

Also, and probably the best method to sort this out (having thought about it while typing) your code doesn't check your tDbaseId after doing the revdatabaseopen() If the db wasn't created, the variable you used will contain an error message, and in fact, you should always do a check after trying to open the database.

something like..
put revopendatabase("sqlite",tpath,,,,) into tTempId
if tTempId is not an integer then
answer information "There was an error: " && tTempId"
else
answer information "Database created with ID: " && tTempId
put tTempId into tDbaseId
end if

This way, if an error occurs it'll pop up the dialog and show you the error. Otherwise, if it worked it will tell you the ID and put the id into a more permanent variable. You might also look at naming conventions. It helps if you name your variables in ways that indicate their scope. Many people name temp vars (handler only, then gone) with a preceeding t tTempVar Globals with a g
and script locals (persistent in the script) with an s. Makes it SO much easier to keep track of things.

[Mark]

You can also make a relative path like ../../user/folder/file.xyz. So, technically, it is wrong to say that one shouldn't use a relative path. You have a point though. On any platform, no only Linux, one should keep writable files in accessible places.

Kind regards,

Mark

[sturgis]

Agreed, relative pathing is fine as long as it leads to a location the user can write to. Thanks for the clarification!

Hopefully I'll have a Linux box set up in a day or two so I can try the 64 bit lc.

[Simon]

[OT]
Good to see you back sturgis, where have you been?

Simon

[sturgis]

Hey Simon, good to see you too. I'm afraid life and health sometimes runs me over and I go to ground for a bit. Things are perkin up though so thought i'd try to get back into things again.