Starting with SQLite

[sandybassett]

I'm trying to create an SQL file with SQLite. So I copied the examples from the lessons into my stack, put them on the edit card page, and get an error both on the create file and creat table routines. The code on the card is:
command databaseConnect
local tDatabasePath, tDatabaseID
put specialFolderPath("documents") & "/customers.sqlite" into tDatabasePath
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
setDatabaseID tDatabaseID
end databaseConnect

local sDatabaseID

command setDatabaseID pDatabaseID
put pDatabaseID into sDatabaseID
end setDatabaseID

function getDatabaseID
return sDatabaseID
end getDatabaseID

on databaseCreateTable
put getDatabaseID() into tDatabaseID
put "CREATE TABLE contact_details (name char(50), email char(50))" into tSQL
revExecuteSQL tDatabaseID, tSQL
end databaseCreateTable

and the code on the two buttons is:
on mouseUp
database connect
end mouseUp

and:
on mouseUp
databaseCreateTable
end mouseUp
The error message on the first is:
Can't find handler near database char 1
The error message on the second is:
Execution error at line n/a (Execution Handler exception) near "revdberr,invalid connection id" which is on the
revExecuteSQL tDatabaseID, tSQL line.
So I think this means there's no db connection id. If so, (1) why would that happen when it was copied directly from the lesson and (2) how do I fix it? Thanks for the help.

[Traxgeek]

Hi SandyBassett,

I'm no expert (and so stand to be corrected) but have spent some time with SQLLite recently, furthermore I have not undertaken the exercise you mention.

BUT, from a quick look-over your code,
-1- your first button code should NOT have a space between ' Database connect '. This should be the name of your command (the very first code snippet of your code : 'databaseConnect'. This is why you're getting the Can't find handler message...

-2-you;'re getting an invalid connection ID error because the value in tDatabaseID is null or < 1. The DBID should, I believe, be 1 or greater for a valid DB connection. I normally put a check after my initial 'getDataBaseID' and before my first 'revExecuteSQL' snippets that check for a DBConnection value greater than 0 in my DBID variable, something like :

if tDataBaseID > 1 then
##do my DB stuff...
else
answer "No DB Connection ID (" & tDataBaseID & ")" with "OK" titled "MIssing DataBase"
end if

Carry out step -1- (and I'd strongly suggest making the code a little more robust with step -2-) then revert with success/fail/progress...

Hope this helps.

Regards.

[Klaus]

Hi Sandy,

looks like your variable declaration is not correct, change your script like this:

Code: Select all

local tDatabasePath, tDatabaseID

command databaseConnect
  put specialFolderPath("documents") & "/customers.sqlite" into tDatabasePath
  put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
  setDatabaseID tDatabaseID
end databaseConnect

command setDatabaseID pDatabaseID
  put pDatabaseID into sDatabaseID
end setDatabaseID

function getDatabaseID
  return sDatabaseID
end getDatabaseID
...

Note the local declaration at the TOP of the script and not inside of the "databaseconnect" handler.
Not sure if this is the problem, but is worth a try

And Trax is of course correct, "databaseconnect" is ONE word.


Best

Klaus

[sandybassett]

Thanks guys for the help; now have the create file, create table, insert record, and display file all working fine. Next step to get delete record, show one record working and then just move some of the scripts to different buttons. This sure is easier in LC than it was in any previous language.

[Klaus]

Hi Sandy,

well, that is more SQL than Livecode
In case you are new to SQL, I recommend this site, which got me started:
http://www.w3schools.com/sql/default.asp

Additionally:
Go to menu "Help" and select "Example Stacks and Resources"
That will open a folder and there you fin another folder named "Examples"
with the stack "SQLite Sampler.rev" in it.
Anpother great lewarning resource for SQL(ite).

P.S.
Could you please tell us what the error was?


Best

Klaus

[FourthWorld]

One book I found especially useful when I was getting started with SQLite is O'Reilly's "Using SQLite":
http://shop.oreilly.com/product/9780596521196.do

The author has a good sense for breaking things down in an orderly flow that made it easy to get started, building on what's learned chapter by chapter in a way I found very helpful.

It covers not only the basics of SQLite and the SQL that drives it, but also covers some of the unique attributes of SQLite that differentiate it from other SQL implementations, sometimes quite powerfully.

[sandybassett]

Klous: Not completely sure but I believe the two errors were the things you'all pointed out to me: i.e. DatabaseConnect is one word not two, and declared the local variable before all the other code. After correcting those, the first two buttons worked, Open DB and Insert Table. Then added the Insert Record and Display without any trouble.

One thing I still don't understand is why 3 variables necessary for one piece of data, tDatabaseID, pDatabaseID, and sDatabaseID? Why wouldn't just one do? As Roger? said, it seems like most of it was SQL not LC. I've used SQLite before on other systems, most recently REALBasic, and its easy once you understand it. What I'm still unclear about is which commands should I use in LC and which in SQL? What I ultimately need to do is have a user button "Save" insert a record of all the data from 7 fields on the Dealer card, then clear the card; then use that table as a scrolling field from which the user picks one line to display that data and connect it with other data on the Customer card, connecting the two.

[Klaus]

Hi Sandy,

Klous: Not completely sure but I believe the two errors were the things you'all pointed out to me: i.e. DatabaseConnect is one word not two, and declared the local variable before all the other code. After correcting those, the first two buttons worked, Open DB and Insert Table. Then added the Insert Record and Display without any trouble.

Glad I could help!

One thing I still don't understand is why 3 variables necessary for one piece of data, tDatabaseID, pDatabaseID, and sDatabaseID? Why wouldn't just one do?

One variable WILL do!

No idea where you got this script from, but this will also work:

Code: Select all

## Only ONE local variable neccessary :-)
local tDatabasePath, tDatabaseID

command databaseConnect
    put specialFolderPath("documents") & "/customers.sqlite" into tDatabasePath
    put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
 end databaseConnect

function getDatabaseID
    return tDatabaseID
end getDatabaseID

on databaseCreateTable
  put "CREATE TABLE contact_details (name char(50), email char(50))" into tSQL
  revExecuteSQL tDatabaseID, tSQL
end databaseCreateTable

Best

Klaus