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: FourthWorld, heatherlaine, Klaus, kevinmiller

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 Jun 08, 2011 6:18 pm

I finally finished this SQlite database example. It's chock full of tight,
good-as-can-be code. I wrote it to be my own personal reference,
but I'm sure other LiveCode beginners will find a lot good code here,
to cut and paste into your own application. Maybe save some time,
instead of trying to figure this stuff out from scratch.
108 CRUD SQLite example 4.3.JPG
I've added new Buttons and Handlers to the main Stack.

AND created a new Sub-Stack called Preferences. This is a single
function called dbData-- that you can use-- to easily save and retrieve
any value in your application, including entire DataGrids. As a single
function, you can simply copy and paste the whole thing into any application.

It's clean, well commented code. Also as is often the case when coding,
we must choose between better efficiency or better readability.
I always opt for better readability and ease of maintenance,
especially in a compiled environment like LiveCode.
Attachments
CRUD_SQLite_example_v4.3.zip
(11.26 KiB) Downloaded 1189 times

jesse
Posts: 205
Joined: Thu Nov 11, 2010 6:32 pm

Re: CRUD SQLite example

Post by jesse » Thu Jun 09, 2011 4:47 am

Great job! Only problem I see so far is when I try to edit a row and click the update button it says updated but when i refresh the grid the row is not
updated anymore. am i missing something?

also just a silly spelling error but the comment "row in table control updated sussefully" should be successfully of course.
Deving on WinVista Home Prem. SP2 32 bit. Using LiveCode 4.6.1 Pro Build 1392

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 Jun 09, 2011 3:07 pm

Got that typo corrected-- but the Update Row seems to work okay.

First highlight a Row, then press the Update button.

Refresh, brings it back fine over here.

Anyone else having this problem?

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 » Sun Jun 12, 2011 4:54 pm

I found more typos. The name Entry was originally Value, but was changed to
avoid confusion with the SQL keyword VALUE. These are the corrected Usage
Instructions for the dbData function.
This Stack contains a single funcion, which Inserts, Updates,
and Reads rows in a SQLIte file called, System.data.
Usage: dbData (Handle, Entry)
If 'Handle' already exists 'Entry' is Updated instead of Inserted.
Null in 'Handle' closes connection.
Null in 'Entry' returns retrieved Entry.
Successful Inserts & Updates return 'ok'.
Any errors, return details, prefixed with the word, 'ERROR'.
If you use this stack, just paste them into the Contents of the Display field.

paul_gr
Posts: 319
Joined: Fri Dec 08, 2006 7:38 pm
Location: Blenheim, New Zealand

Re: CRUD SQLite example

Post by paul_gr » Sun Jun 12, 2011 8:12 pm

Thanks Townsend,
useful example -- it's appreciated.

Paul

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 » Sun Aug 07, 2011 11:44 pm

This weekend I did a short presentation going over this CRUD SQLite Example.

Here's the link to the video: http://dl.dropbox.com/u/35256958/CRUD-Example-video.mp4
(Note: this link should be good until the end of the month. Aug-2011.)

Also here's a minor update.Version 4.4.
Attachments
CRUD_SQLite_example_v4.4.zip
(11.81 KiB) Downloaded 754 times

BarrySumpter
Posts: 1201
Joined: Sun Apr 24, 2011 2:17 am

Re: CRUD SQLite example

Post by BarrySumpter » Mon Aug 08, 2011 2:59 am

Nice!
Great work on the video!
And thanks for the update!
All my best,
Barry G. Sumpter

Deving on WinXP sp3-32 bit. LC 5.5 Professional Build 1477
Android/iOS/Server Add Ons. OmegaBundle 2011 value ROCKS!
2 HTC HD2 Latest DorimanX Roms
Might have to reconsider LiveCode iOS Developer Program.

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 » Tue Aug 09, 2011 2:56 pm

The video is also available from the livecode.tv site:

http://blog.livecode.tv/2011/08/event-35-wrap-up/
Various teststacks and stuff:
http://bjoernke.com

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

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: CRUD SQLite example

Post by marksmithhfx » Thu Aug 11, 2011 3:43 am

townsend wrote:
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

Hey, thats really cool. I can think of a place where I can use that immediately.

Cheers,

-- Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: CRUD SQLite example

Post by jpottsx1 » Mon Sep 12, 2011 8:55 pm

This may seem like a silly question but I see fields named sEntry and sHandle, what I do not see is how the values of these fields are transferred tot he database. I see iEntry and iHandle but no corresponding code to set these values.

Also, if I am trying to retrieve a table full of data do I then need to code the corresponding sXXXXX fields or is there a less cumbersome method to bring back say 30 fields from a particular database without using the syntax surrounding the use of Double Quotes "'".

Can I bring back the entire table into corresponding fields with only checking for the existence of one field such as sEntry.
Jeff G potts

BarrySumpter
Posts: 1201
Joined: Sun Apr 24, 2011 2:17 am

Re: CRUD SQLite example

Post by BarrySumpter » Mon Sep 12, 2011 9:47 pm

Select * from myTable
All my best,
Barry G. Sumpter

Deving on WinXP sp3-32 bit. LC 5.5 Professional Build 1477
Android/iOS/Server Add Ons. OmegaBundle 2011 value ROCKS!
2 HTC HD2 Latest DorimanX Roms
Might have to reconsider LiveCode iOS Developer Program.

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: CRUD SQLite example

Post by jpottsx1 » Mon Sep 12, 2011 10:14 pm

I understand the "select *" business, what I'm looking for is an answer within the context of the code from the "CRUD SQLite example" database.

I'm trying to figure out the relationship between the actual display fields "sEntry" and "sHandle" and the retrieval of the data from a database and the use of iHandle which I do not understand.

I want to understand how to retrieve the data from more than two fields and display the results in specific fields.
Jeff G potts

BarrySumpter
Posts: 1201
Joined: Sun Apr 24, 2011 2:17 am

Re: CRUD SQLite example

Post by BarrySumpter » Mon Sep 12, 2011 10:31 pm

Not knowing the exact script you're referring to.

I often name the stack field
fldSex after the database field Sex.
fldAge after Age.
fldDOB after DOB.
fldMobile after Mobile.
etc.

Than have a repeat look steping thru the list of the database table field names and assigning the corresponding data to the corresponding stack fields.

Otherwise, posting the script might be easier.

hth
All my best,
Barry G. Sumpter

Deving on WinXP sp3-32 bit. LC 5.5 Professional Build 1477
Android/iOS/Server Add Ons. OmegaBundle 2011 value ROCKS!
2 HTC HD2 Latest DorimanX Roms
Might have to reconsider LiveCode iOS Developer Program.

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: CRUD SQLite example

Post by jpottsx1 » Mon Sep 12, 2011 10:37 pm

I'm referring to the following script:

Code: Select all

function dbData iHandle, iEntry
     global conID
     -- Step 1: Check for Close Connection Request
     if iHandle is empty then
          revCloseDatabase conID
          put the result into temp
          if the result is empty then
               return "ok"
          else
               else return the result
          end if
          put empty into conID
     end if
     -- Step 2: Build Full Path and SQLite file name
     set itemDel to slash  -- start to build current path for SQlite DB
     put the effective filename of this stack into realpath  --gets current path
     delete last item of realpath --removes .livecode file name on this stack
     put  realpath & "/System.data" into pathNdb -- adds SQLite file name to path
     -- Step 3: Open Connection -- close if all values are empty
     put revopendatabases("sqlite",,,,) into dbIDs --gets a list of open databases
     if not (conID is among the items of dbIDs) then  -- needs new connection id
          put revOpenDatabase("sqlite", pathNdb,,,,,,) into conID
          put the result into temp
          if the result is not a number then
               answer "ERROR=" & conID
          end if
     end if
     -- Step 4: Retrieve Data if Entry is empty
     put "'" & iHandle & "'" into iHandle -- add single quotes
     put "'" & iEntry & "'" into iEntry  -- required for SQL data
     if iEntry ="''" then  -- two single quotes is a request to retrieve data
          put "SELECT Entry FROM control WHERE Handle="  & iHandle into tSQL
          --revExecuteSQL conID, tSQL
          put revdb_querylist(,,conID,tSQL) into ptext
          if "revdberr" is in ptext then
               return "ERROR=" & ptext
          end if
          return ptext  -- valid a valid entry
     end if
     -- Step 5: Create Table
     put  "CREATE TABLE control (Handle text primary key, Entry text);" into tsql
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if "already exists" is in the result then
          -- good
     end if
     -- Step 6: Try to Insert New Row with SQL Insert statement
     put "INSERT INTO control (Handle, Entry)"  into tSQL
     put "VALUES (" & iHandle & ", " & iEntry & ");" after tSQL
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if "is not unique" is in the result then -- row already exits
          -- then go on to update row
     else if the result is 1 then
          return "ok"
     else
          return "ERROR=" & the result  --- exit with error
     end if         
     -- Step 7: Try to Update the Row Instead
     put "UPDATE control SET Entry=" & iEntry &  " WHERE Handle="  & iHandle into tSQL
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if the result is not 1 then
          return "ERROR=" & the result  --- exit with error
     end if         
     return "ok"
end dbData

s
I'm referring to Step 4 in the script where the data is returned based upon the content of the field "sEntry". I'm seeking to discover how to return any number of fields from the DB if I added more for my own purposes and place the data into the corresponding fields on my card.

THX
Jeff G potts

BarrySumpter
Posts: 1201
Joined: Sun Apr 24, 2011 2:17 am

Re: CRUD SQLite example

Post by BarrySumpter » Mon Sep 12, 2011 11:06 pm

SELECT Entry, Sex, DOB, AGE, myOtherDBField1 FROM control WHERE ...

Don't know if ptext returns the DB field names as well or not.
If not the field values should be returned in the order specified in the above select statement.

You can place a stop trace on the
if "revdberr" script line
run the app and when the script stops
mouse over the pText to see its content in a popup.
And go from there.
specificily the
return ptext script line
hth
All my best,
Barry G. Sumpter

Deving on WinXP sp3-32 bit. LC 5.5 Professional Build 1477
Android/iOS/Server Add Ons. OmegaBundle 2011 value ROCKS!
2 HTC HD2 Latest DorimanX Roms
Might have to reconsider LiveCode iOS Developer Program.

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”