SQLite Questions

[chuckm]

If the default folder is pointing to the location that contains your "data" folder, I think it should locate and open up your database correctly. If this is happening in the IDE, its likely that the default folder is pointing to a wrong, and likely un-writeable location. Check to see what the default folder is set to. If its correct, you can check to see if: there is a file "data/database.sqlite" If both of these check out, you likely don't have permission to write directly to the folder in question. (what os, and where is the file? ) Depending on the OS, it might be necessary to copy the file to a better location for use. My guess is a permissions issue with the location because if you have permission in LC to open a file, it would have created it with no error rather than telling you it couldn't do it.

You've just answered your own questions.

Open the DB when opening the stack
close it when closing the stack
If it does not exist, create it

So, I just added the following code to the first card per the openStack Api in the docs:

Code: Select all

on openStack
   global gConId
   put revOpenDatabase("sqlite", "data/database.sqlite") into tConId
   if tConId is "" then
      answer warning "Problem creating or accessing database!"
   else
      answer information "Database connected. Your connection id is: " & tConId
      put tConId into gConId
   end if
end openStack

on closeStackRequest
   revCloseDatabase gConId
end closeStackRequest

I got the following message in an answer box:

Database connected. Your connection id is: Database Error: Unable to open the database file.

So obviously something is rotten in the state of Denmark. The database exists, and to the best of my knowledge is valid because I can query it from the command line as well as from several different GUI tools. There's no password, so I'm wondering if the database has to be in the same directory as the LiveCode app. This is a great example of posted code in the forums, StackOverflow, etc. that fails to work although it may largely be due to my lack of understanding. All I'm trying to do to start is just open the database and yet the code fails and to be honest, the error message is worthless. I'll apologize in advance if the comment comes across as offensive because it is not meant to be so, but I speak plainly.

Thanks for your response!

It turns out that I needed to use a backslash instead of a forward slash in the relative path. I work under Unix (MacOS, Linux) a lot more than I do Windows so it didn't occur to me that the path delimiter would be the issue. I actually found it by trial and error before I found anything from searching the web. Not a great use of my time, but I noted it in my dev journal for future reference.

Best regards,

Chuck

[sphere]

I'm using this and works good:

Code: Select all

on preOpenStack
if the platform is "Linux" then
      put specialFolderPath("home") & "/myDB.sqlite" into gDatabasePath
   else
      put specialFolderPath("support") & "/myDB.sqlite" into gDatabasePath
   end if
   end if
   put revOpenDatabase("sqlite", gDatabasePath, , , , ) into gLocalDbID
   dbcreate --call a handler
   end preOpenStack
   

Code: Select all

on dbcreate
put "CREATE TABLE settings (id INT PRIMARY KEY, anumber INT, some TEXT, etcetera INT)" into tSQL 
   revExecuteSQL gLocalDbID, tSQL
   end dbcreate

insert a row with data from variables, the nr of placeholders (:1,:2 etc) must equal the columns/variables

Code: Select all

put "id","anumber","some","etcetera" into tFields
   put "INSERT INTO pw (" & tFields & ") VALUES (:1,:2,:3,:4)" into tSQL
   revExecuteSQL gLocalDbID, tSQL, "tId, tNr, tSometext, gAglobal"

Code: Select all

on closeStack
if gLocalDbID is a number then
         revCloseDatabase gLocalDbID
         put empty into gLocalDbID
      end if
      end closeStack

if you declare this global gLocalDbID in each card or control you want to access the DB, you can use it anywhere in your stack

[bogs]

It turns out that I needed to use a backslash instead of a forward slash in the relative path. I work under Unix (MacOS, Linux) a lot more than I do Windows so it didn't occur to me that the path delimiter would be the issue. I actually found it by trial and error before I found anything from searching the web. Not a great use of my time, but I noted it in my dev journal for future reference.

Hm. I think your running into similar problems that I had coming to this language from other languages, and that is your way of thinking does need to adjust a bit.

Often times, it is better to use standard methods to get the information you need, then put it into a variable and use that for the end result. The backslash worked, but isn't necessarily the best way to tackle absolute and relative paths.

Here are two lessons that might help explain it better :
http://lessons.livecode.com/m/2592/l/12 ... olute-path
http://lessons.livecode.com/m/2592/l/12 ... ative-path

It is unfortunate, but I've forgotten where I've seen the explanation of the path setup in Lc. Since Mc was started as a 'nix development setup then ported to other OSes, the path easiest for it is 'nix style slashes, even on Windows.

[jacque]

For example, I would have thought that the openStack message would need to come to the stack rather than be sent to a card. Likewise, I was surprised that I need to trap the closeStackRequest rather than the closeStack method as well as trapping an Apple event for the Quit option.

You're right there's a learning curve, but that's true in any language. I do takes your point though about the nature of teenagers. However, the first place to start is the user guide (under the Help menu) which does a pretty good job of outlining the basics.

A couple of things you mentioned:

Except for a few exceptions, mostly on mobile apps, all messages not triggered by a control are sent to the card and follow the normal message path from there. It's common to put an openstack handler in the script of the first card so that it won't trigger when other stacks are opened. The message path is critical to understanding LC so students need to be introduced to it early on.

You don't need Applescript to manage a quit or almost anything else in LC. The "shutdown" message is sent when the user quits by any method, so that's the best way to handle it. A closeStack is also sent, but doesn't necessarily indicate a quit. CloseStackRequest is only sent when a user manually clicks on the close button in the stack titlebar or you issue an explicit "quit" command, and is primarily meant to alert you that the user is closing the window instead of a script. If you don't have any cleanup to do, just pass the message or follow the suggestion in the dictionary to avoid having the message sent at all.

When searching for the appropriate messages to respond to, it's helpful to enter a search term in the dictionary and then examine the "related to" section of the various entries. Searching for "quit" for example should show you the other messages that are related. But yeah, there's a lot of stuff to absorb and it does take some persistence.

[SparkOut]

The message path is critical to understanding LC so students need to be introduced to it early on.

Now there's a sentence that needs to be quoted.

[bogs]

The message path is critical to understanding LC so students need to be introduced to it early on.

Now there's a sentence that needs to be quoted.

...and possibly bronzed. And repeated. Many times. Mantra like

[chuckm]

It turns out that I needed to use a backslash instead of a forward slash in the relative path. I work under Unix (MacOS, Linux) a lot more than I do Windows so it didn't occur to me that the path delimiter would be the issue. I actually found it by trial and error before I found anything from searching the web. Not a great use of my time, but I noted it in my dev journal for future reference.

Hm. I think your running into similar problems that I had coming to this language from other languages, and that is your way of thinking does need to adjust a bit.

Often times, it is better to use standard methods to get the information you need, then put it into a variable and use that for the end result. The backslash worked, but isn't necessarily the best way to tackle absolute and relative paths.

Here are two lessons that might help explain it better :
http://lessons.livecode.com/m/2592/l/12 ... olute-path
http://lessons.livecode.com/m/2592/l/12 ... ative-path

It is unfortunate, but I've forgotten where I've seen the explanation of the path setup in Lc. Since Mc was started as a 'nix development setup then ported to other OSes, the path easiest for it is 'nix style slashes, even on Windows.

Without a doubt LC requires a shift in thinking, but to be honest, it's not unlike the shift that I had to do many years ago when the company I was at begin using RAD tools, Visual Basic, et al., which are similar in philosophy to LC. What I'm trying grok is more about proper best practices, i.e., which method and where in the message flow should it appear. Minor things like backslashes vs. forward slashes for paths are at best minor, time-consuming annoyances. The SQLite thing is an example of trying to suss out "where" to establish the connection if global rather than opening/closing the connection each time. As I mentioned in my first post, my initial thought was the message handler needed to go into the stack, but apparently it needs to go into the first card. That's just the way that LC works.

As with any language, tool, etc., my primary interest is in "why" things work the way they do because all of it comes down to design choices. To be honest, the message path for LC does not differ significantly from any of the object-oriented, event-driven RAD tools that I've used before. It's just the way that this class of tools get designed. So with that, I will continue to delve into LC and try to surmise the "whys" as I compile my own notes on best practices. Hopefully I won't be too annoying to the forum at large.

Thanks again for your assistance and patience!

Chuck

[jacque]

As I mentioned in my first post, my initial thought was the message handler needed to go into the stack, but apparently it needs to go into the first card.

Not exactly. Most messages will be sent to the card, but they pass along the message path from there. The path is a little snakey depending on how you've set up supplemental scripts (backscripts, libraries, etc.) but the most basic path is:

[card control ]-> card -> background groups -> stack -> LiveCode

Any object in that path will trap a message if it has a handler to deal with it and doesn't pass the message. So in your example, an openStack message is sent to the card and if there is no openStack handler there, it will pass on to the other controls in order. If you put the openStack handler in the stack script, it will receive it if there isn't already a card handler that has already caught it.

So you can put the openStack handler almost anywhere except, say, in a button or other card-level control. Anything from the card onward will receive it. The reason some of us put the handler in the card script is usually because we have other stacks in the message path, and they won't receive the message if the card script catches it first.

If I had an openStack handler that I want to run in any stack no matter what, I could put the handler in a library or backscript and it would work everywhere.

[chuckm]

It turns out that I needed to use a backslash instead of a forward slash in the relative path. I work under Unix (MacOS, Linux) a lot more than I do Windows so it didn't occur to me that the path delimiter would be the issue. I actually found it by trial and error before I found anything from searching the web. Not a great use of my time, but I noted it in my dev journal for future reference.

Hm. I think your running into similar problems that I had coming to this language from other languages, and that is your way of thinking does need to adjust a bit.

Often times, it is better to use standard methods to get the information you need, then put it into a variable and use that for the end result. The backslash worked, but isn't necessarily the best way to tackle absolute and relative paths.

Here are two lessons that might help explain it better :
http://lessons.livecode.com/m/2592/l/12 ... olute-path
http://lessons.livecode.com/m/2592/l/12 ... ative-path

It is unfortunate, but I've forgotten where I've seen the explanation of the path setup in Lc. Since Mc was started as a 'nix development setup then ported to other OSes, the path easiest for it is 'nix style slashes, even on Windows.

I may be misreading the lessons, but does this mean that I should use a method to essentially ensure that I have an absolute path?

[chuckm]

As I mentioned in my first post, my initial thought was the message handler needed to go into the stack, but apparently it needs to go into the first card.

Not exactly. Most messages will be sent to the card, but they pass along the message path from there. The path is a little snakey depending on how you've set up supplemental scripts (backscripts, libraries, etc.) but the most basic path is:

[card control ]-> card -> background groups -> stack -> LiveCode

Any object in that path will trap a message if it has a handler to deal with it and doesn't pass the message. So in your example, an openStack message is sent to the card and if there is no openStack handler there, it will pass on to the other controls in order. If you put the openStack handler in the stack script, it will receive it if there isn't already a card handler that has already caught it.

So you can put the openStack handler almost anywhere except, say, in a button or other card-level control. Anything from the card onward will receive it. The reason some of us put the handler in the card script is usually because we have other stacks in the message path, and they won't receive the message if the card script catches it first.

If I had an openStack handler that I want to run in any stack no matter what, I could put the handler in a library or backscript and it would work everywhere.

Hmm, not sure that the openStack handler needs to be called from anywhere, but the connection needs to be available globally and if it somehow goes AWOL get re-established. It looks more like an object is needed to encapsulate the connection and its attendant functionality. More reading...

[Klaus]

Hi Chuck,

with all the helpful spcialfolderpath() codes in LC, using an absolute pathname is a snap!
I never rely on relative pathnames.

Best

Klaus

[bogs]

Absolute paths, absolutely!

Kinda kidding, but Klaus does have a point. The lessons I linked to were more than I do when looking for paths, except in certain circumstances. MOSTLY, they are just very thorough.

As a couple of examples, lets say I had a folder my app is in, and wanted to go one more folder relative to that one, so the path looks like ~/myApp/Data (we don't know where the folder is sitting, it could be anywhere the user has permission to put it, home, docs, etc).

Klaus would (properly) never let that situation happen, but lets say it does for whatever reason. To work out where the Data folder was, I would probably put the fileName into a variable, cause I know it is in the base folder I want, then add the relative part of the path to get to Data -

It is all relative...

(Not shown in the picture is that tmpPath is a script local variable outside of the handlers )
Now, something like this wouldn't work too well on OSX, certainly if the fileName your grabbing is inside the app folder, but for Win or Lin it is suitable to most purposes.

Klaus rightly pointed out specialFolders, which there are quite a few of now, and which resolve *most* issues (Richard and I recently learned of a case where they don't, as in someone changes their "Documents" folder name to something else ) so error checking no matter which way you go should be done no matter what.

Hope that helps clear it up a bit more

[Klaus]

Mine is shorter:

Code: Select all

on setRelative
  answer (specialfolderpath("resources") & "/Data")
end setRelative

Works in a standalone and the IDE.

[bogs]

That is why Klaus is a go-to guy, he definitely knows his stuff

The only excuse I have is that the IDEs I work in don't actually have that, so I don't rely on it

For Unix systems:
• Home - The current user's home directory (e.g. "/home/fred").
• Desktop - The current user's desktop (e.g. "/home/fred/Desktop").
• Temporary - For storage of temporary files (typically "/tmp").
• 0x000e - The "My Videos" folder.

Newest IDE i'm likely to be in at any time

And of course, my usual IDE -

Not supported :D

Selection_006.png (11.58 KiB) Viewed 6921 times

[chuckm]

I'm using this and works good:

Code: Select all

on preOpenStack
if the platform is "Linux" then
      put specialFolderPath("home") & "/myDB.sqlite" into gDatabasePath
   else
      put specialFolderPath("support") & "/myDB.sqlite" into gDatabasePath
   end if
   end if
   put revOpenDatabase("sqlite", gDatabasePath, , , , ) into gLocalDbID
   dbcreate --call a handler
   end preOpenStack
   

Code: Select all

on dbcreate
put "CREATE TABLE settings (id INT PRIMARY KEY, anumber INT, some TEXT, etcetera INT)" into tSQL 
   revExecuteSQL gLocalDbID, tSQL
   end dbcreate

insert a row with data from variables, the nr of placeholders (:1,:2 etc) must equal the columns/variables

Code: Select all

put "id","anumber","some","etcetera" into tFields
   put "INSERT INTO pw (" & tFields & ") VALUES (:1,:2,:3,:4)" into tSQL
   revExecuteSQL gLocalDbID, tSQL, "tId, tNr, tSometext, gAglobal"

Code: Select all

on closeStack
if gLocalDbID is a number then
         revCloseDatabase gLocalDbID
         put empty into gLocalDbID
      end if
      end closeStack

if you declare this global gLocalDbID in each card or control you want to access the DB, you can use it anywhere in your stack

Where are you putting the preOpenStack/closeStack handlers?