CRUD SQLite example

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

CRUD SQLite example

Post by townsend » Mon Feb 28, 2011 12:18 am

I creating a CRUD SQLite example. (Create, Read, Update, & Delete)
I'll post the final stack stack in this thread, when it's done.
This code is basically just copied from the SQLite Samplier.rev example.
http://downloads.runrev.com/distributio ... er.rev.zip

I'm putting all the code in the Stack Script area. Each button has a single command,
which refers the the handler in the Stack Script area.

doConnect & doDissconnect work fine. I'm having a little trouble with the doCreate.

Code: Select all

on doCreate
  if gConID is "" then  -- defined at the top of the stack as a Global
    answer information "No Database is Connected to. Press the Connect button first."
    exit doCreate
  end if
  put "CREATE TABLE users(userID integer primary key, name text ,email text, emailList boolean)" into tSQL
  put revExecuteSQL (gConID, tSQL) into tTmp
  handleRevDBerror tTmp
  if the result is not empty then 
    answer warning the result
    exit doCreate
  end if
  answer information "First Table Added: " & tTmp
end doCreate
On the: put revExecuteSQL (gConID, tSQL) into tTmp I'm getting this error:
stack "myDBtest": execution error at line 32 (Function: error in function handler) near "revExecuteSQL", char 7

I tried removing the paren as per the revExecuteSQL example in the dictionary.
I am missing something obvious? I'm not sure what that "near" means in the error message.
And, is "char 7" from the beginning of the line?

Dixie
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 1336
Joined: Sun Jul 12, 2009 10:53 am
Location: Bordeaux, France

Re: CRUD SQLite example

Post by Dixie » Mon Feb 28, 2011 1:48 am

Hi townsend...

Look at the attached stack...

be well

Dixie
Attachments
make SQLiteDB.zip
(1.47 KiB) Downloaded 389 times

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 834
Joined: Fri Aug 15, 2008 7:15 am

Re: CRUD SQLite example

Post by bangkok » Mon Feb 28, 2011 6:31 am

townsend wrote: On the: put revExecuteSQL (gConID, tSQL) into tTmp I'm getting this error:
stack "myDBtest": execution error at line 32 (Function: error in function handler) near "revExecuteSQL", char 7
don't write "put revExecuteSQL", but

revExecuteSQL (gConID, tSQL)
put the result into tTmp

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Mon Feb 28, 2011 7:32 pm

Thanks Dixie-- that stack helped a lot. The doCreate handler is working now.

This is a learning exercise for me, so I'm trying to undersanding each line of code.
For instance, if I do proper check on the gConID (connection ID, which is generated by the
revOpenDatabase funcion, all is well.

But lets say I use a bad connection ID, or maybe I didn't do the preliminary error checking right,
before executing one of the database functions, then execution just stops. So... IF there is an error,
how to I recover? For instance-- in traditional languages there is an Error Event, where you can
examine the error code, and resume, or resume next. Is there some equivalent in LiveCode?

Thanks for your answer Bangkok-- right to the point. I'm still a little confused.

revCloseDatabase(gConID) works fine or...
put revCloseDatabase(gConID) into temp works fine too. This way you save the result.

revExecuteSQL (gConID, tSQL) also works. But for some reason,
put revExecuteSQL (gConID, tSQL) into temp does not work. Can't save the result?

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Mon Feb 28, 2011 9:56 pm

in traditional languages there is an Error Event, where you can examine the error code,
and resume, or resume next. Is there some equivalent in LiveCode?
I just found the answer to this question in the User's Guide. It's the Try/Catch control statement.

Code: Select all

try
    open file tFile
    read from file tFile until eof
    close file
catch someError
     answer "An error occurred reading a file" && someError
end try

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Tue Mar 01, 2011 1:40 am

I've made some progress. Here's what I've discovered-- for other newbies.

1- The Try/Catch control statement works great for catching unrecoverable errors.

2- The put revOpenDatabase("sqlite", pathNdb,,,,,,) into gConID must use
the put into clause, because the connection id is needed to reference
the database in all future requests.

2- But the revExecuteSQL gConID, tSQL statement-- for some reason-- cannot use the put into clause
anymore, as originally indicated in the SQLite Samplier.rev example. So the result much be checked another way.
For instance, trying to create a database that already exists, will not generate an Try/Catch Error.
This works very well though: if the result is not 0 then answer warning the result; exit doCreate
This generates a "Table already exits" error. Notice-- there are two statements on one line separated by a semicolon.

3- Also, be aware that the gConID is only a numeric connection ID. It does not indicate the state or open/closed-ness of the DB.
There's probably is some way to check the state of a DB connection, though I haven't seen that yet.

4- Here's a little oddity. the Result returns zero, as a numeric if everything is okay.
But if there's a problem, the Result returns a string with the description of the problem.
Usually a variable is numeric or a string-- not both. I suspect this aspect of variables is common throughout Live Code.
While you can use Global or Local to define the range of a variable, I have yet to see a variable defined
specifically as a integer, long, real, or string. Maybe it's not necessary.

Here's the code I've got so far. I think-- in many ways-- it's better than the original.

Code: Select all

global gConID
local tSQL

on doConnect
   try
      put the defaultFolder & "/mytest.db" into pathNdb
      put revOpenDatabase("sqlite", pathNdb,,,,,,) into gConID
   catch theError
      answer info "Problem Opening Database: " & pathNdb & "  " & theError
      put 0 into gConID
      exit doConnect
   end try
   answer information "Connected to: " & pathNdb & " ID is: " & gConID
end doConnect ----------------------------------------------------

on doDisconnect
   if gConID is zero then
      answer info "Press the Connect Button First."
      exit doDisconnect
   end if
   try
      revCloseDatabase gConID
   catch theError
      answer warning theError
      exit doDisconnect
   end try
   put 0 into gConID
  answer info "Connection Terminated "
end doDisconnect  ------------------------------------------------------

on doCreate
   if gConID is 0 then
      answer info "Press the Connect button first."
      exit doCreate
   end if
   try
      put "CREATE TABLE users(userID integer primary key, name text,email text, emailList boolean)" into tSQL
      revExecuteSQL gConID, tSQL
      if the result is not 0 then answer warning the result; exit  doCreate
   catch theError
      answer warning theError
      exit doCreate
   end try
   answer information "Table Created Sussessfully"
end doCreate --------------------------------------------------------

-- For successful queries, the revExecuteSQL command returns
-- the # of rows affected for INSERT, UPDATE and DELETE statements. 
-- For all other statements, 0 is returned, so if the result is 0... then everything is good
Next I will use the revExecuteSQL to Insert random data into the Table.
Then I'll read that back with a revQuerySQL and populate a DataGrid.

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Wed Mar 02, 2011 6:28 pm

I'm making good progress, but I have a question:

Code: Select all

if the result is not 0 then
         answer warning the result
         exit  doCreate
end if

-------------------- is not the same as ------------------------------------
if the result is not 0 then answer warning the result; exit  doCreate
My shortened version always executes the "exit doCreate".

Is there any way to attach multiple statements on a single line If statement?

Dixie
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 1336
Joined: Sun Jul 12, 2009 10:53 am
Location: Bordeaux, France

Re: CRUD SQLite example

Post by Dixie » Wed Mar 02, 2011 7:00 pm

Hi...

I'm assuming that your example is from a handler that you have called 'doCreate', and there are lines above this example that are getting a result from issuing a command... if so, then try...

Code: Select all

on doCreate
   --- (some command)
   if the result is not empty then
      answer warning the result
   else
      exit doCreate
   end if
   
end doCreate
Many commands set the value of the result function when they finish. In most cases, if the result is empty, the command was successful; if the result is not empty, the command failed and the result contains an error message. See the specific command for information about whether it sets the result function. -- from the Dictionary.

be well

Dixie
Last edited by Dixie on Wed Mar 02, 2011 7:04 pm, edited 1 time in total.

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Wed Mar 02, 2011 7:04 pm

Got the doPopulate handler routine done-- adds random data to the test DB.
myDBtest.zip
(5.05 KiB) Downloaded 271 times
Oh. I forgot. You must press the Disconnect button for the records to be written.
I forgot the SQL Commit command.

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Thu Mar 03, 2011 6:51 pm

Finally-- it took me a long time to get the DataGrid populated from the SQLite DB.
Note-- here's all the most current DataGrid documentation:
http://lessons.runrev.com/spaces/lesson ... s/datagrid

The confusion was over the use of a special pText DataGrid property.

None the less, I want to thank Dixie, Bangkok, and especially Trevor,
who I understand, wrote the DataGrid object and all the documentation,
for all their help me get this example done.

At this point everything works well, and the code should be very
easy to understand, as it is all separate handlers off the main Stack.
myDBtest_v2.zip
(6.55 KiB) Downloaded 352 times
92 CRUDexample.JPG
92 CRUDexample.JPG (31.57 KiB) Viewed 6946 times

wsamples
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 215
Joined: Mon May 18, 2009 4:12 am

Re: CRUD SQLite example

Post by wsamples » Sat Mar 05, 2011 6:23 pm

Thanks for creating and sharing this little demo. This seems like an appropriate place to ask a question that always crosses my mind when I see Livecode SQLite tutorials; what are the empty parameters in the revOpenDatabase function for? While I have nothing against mystery per se, sometimes knowledge is comforting and I have never seem this clearly and simply explained.

A little testing shows that a minimum of three parameters are required after declaring the dbType and path to SQLite file, but there seems to be no upper limit. (Most examples show at least a few more than the minimum... why? Could it be that lots of people don't really understand what this is about and just put them there, because that's what they've seen?) You can fill these parameters with random stuff and the function works regardless.

My guess, based on the dictionary entry, is that after the db type (sqlite), we have the host (the path to the file for SQLite), the dbName (empty for SQLite, and mentioned in the dictionary), userName for database (empty for SQLite since SQLite doesn't use access restrictions as other dbs do) and finally, userPassword (empty again because there are no access restrictions with SQLite). Any additional empty parameters represent possible (optional, as indicated by the brackets in the dictionary) parameters which may be used for other database types and are there just as a matter of habit, but serve no purpose whatsoever working with SQLite. So: 'sqlite, <path to db file>, empty(dbName), empty(userName), empty(userPassword)'. Is this correct?

I don't mean to send this thread in another direction entirely, but this is an illustration of a frustration I have had with too many Livecode tutorials, where too frequently, "little things" are unexplained, which can cause insecurity and doubt during the learning process. Avoiding this is especially important in written materials where there is no opportunity for clarification and elaboration. (I can accept that townsend wasn't really attempting to present a real tutorial... this is surely not directed especially at him!)

BvG
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1236
Joined: Sat Apr 08, 2006 1:10 pm
Location: Zurich
Contact:

Re: CRUD SQLite example

Post by BvG » Sat Mar 05, 2011 6:36 pm

Read the dictionary entry for revOpenDatabase. The "empty" paremeters are for server databases: login, password, etc. Because sqlite doesn't have those, they're empty. There's even more unused parameters at the end. ;)
Various teststacks and stuff:
http://bjoernke.com

Chat with other RunRev developers:
chat.freenode.net:6666 #livecode

wsamples
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 215
Joined: Mon May 18, 2009 4:12 am

Re: CRUD SQLite example

Post by wsamples » Sat Mar 05, 2011 8:22 pm

Ummmmmmm... I read the dictionary and in my post I provide the conclusion I have drawn from it. However, the dictionary is not perfectly illuminating and hence my request for confirmation :)

The first example given in the dictionary suggests there are five required parameters (minimum, all databases)*. It is explained that for any given database some number of them may be empty. In the SQLite specific example given, there are six parameters, four of which are empty. Why the extra one? What have I missed? Are there extra parameters available to use with SQLite? Why is it so difficult to find a clear and simple explanation in all the various tutorial materials available? Why do I see so many examples where people are loading this function with an unnecessary abundance of empty parameters using SQLite? Could it be that there is widespread lack of understanding? (Not that any damage is being done, obviously, but I personally would really rather "just know".) Keeping in mind which forum you are reading and posting to, is it really unreasonable to hope for a simple, clear and accurate explanation?


*It doesn't actually say there is some number of required parameters to execute successfully, which I find maddening in that it would be soooooo simple to mention this and a "dictionary" should be, by nature, unambiguous, whenever possible.

venanzio@mac.com
Posts: 10
Joined: Wed May 25, 2011 9:17 am

Re: CRUD SQLite example

Post by venanzio@mac.com » Thu May 26, 2011 2:39 pm

I have townsends example and have a couple of newbie questions.

where is all the code for the handler routines, for example, doConnect.

I can't find it in the stack and know it should be there.

thanks

townsend
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 430
Joined: Sun Feb 13, 2011 8:43 pm
Location: Seattle, USA

Re: CRUD SQLite example

Post by townsend » Thu May 26, 2011 3:30 pm

Okay. I finally finished this example! I added the
Update Row in DB Handler. It's very tight code.
Everything done just right-- as best as it can be.

This way, when I copy code, out of here, into my
future projects I'll be starting with a solid foundation.

All the buttons call Handlers in the Main Stack. This way
all the code is in one place, AND if this were going to
be deployed for different screen sizes, all buttons would
call the same Handlers-- thus-- button code would not
need to be copied from one to another.
101 CRUD SQLite 3.2.JPG
101 CRUD SQLite 3.2.JPG (56.28 KiB) Viewed 6730 times
Attachments
CRUD_SQLite_example_v3.2.zip
(8.9 KiB) Downloaded 531 times

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”