CRUD SQLite example
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
-
- Livecode Opensource Backer
- Posts: 430
- Joined: Sun Feb 13, 2011 8:43 pm
- Location: Seattle, USA
Re: CRUD SQLite example
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. 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.
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. 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 1216 times
Re: CRUD SQLite example
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.
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
-
- Livecode Opensource Backer
- Posts: 430
- Joined: Sun Feb 13, 2011 8:43 pm
- Location: Seattle, USA
Re: CRUD SQLite example
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?
First highlight a Row, then press the Update button.
Refresh, brings it back fine over here.
Anyone else having this problem?
-
- Livecode Opensource Backer
- Posts: 430
- Joined: Sun Feb 13, 2011 8:43 pm
- Location: Seattle, USA
Re: CRUD SQLite example
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.
avoid confusion with the SQL keyword VALUE. These are the corrected Usage
Instructions for the dbData function.
If you use this stack, just paste them into the Contents of the Display field.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'.
Re: CRUD SQLite example
Thanks Townsend,
useful example -- it's appreciated.
Paul
useful example -- it's appreciated.
Paul
-
- Livecode Opensource Backer
- Posts: 430
- Joined: Sun Feb 13, 2011 8:43 pm
- Location: Seattle, USA
Re: CRUD SQLite example
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.
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 772 times
-
- Posts: 1201
- Joined: Sun Apr 24, 2011 2:17 am
Re: CRUD SQLite example
Nice!
Great work on the video!
And thanks for the update!
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.
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.
-
- VIP Livecode Opensource Backer
- Posts: 1236
- Joined: Sat Apr 08, 2006 1:10 pm
- Location: Zurich
- Contact:
Re: CRUD SQLite example
The video is also available from the livecode.tv site:
http://blog.livecode.tv/2011/08/event-35-wrap-up/
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
http://bjoernke.com
Chat with other RunRev developers:
chat.freenode.net:6666 #livecode
-
- VIP Livecode Opensource Backer
- Posts: 931
- Joined: Thu Nov 13, 2008 6:48 am
- Location: London, UK
Re: CRUD SQLite example
townsend wrote:I just found the answer to this question in the User's Guide. It's the Try/Catch control statement.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?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
Targets: Mac, iOS
Re: CRUD SQLite example
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.
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
-
- Posts: 1201
- Joined: Sun Apr 24, 2011 2:17 am
Re: CRUD SQLite example
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.
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.
Re: CRUD SQLite example
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.
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
-
- Posts: 1201
- Joined: Sun Apr 24, 2011 2:17 am
Re: CRUD SQLite example
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
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.
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.
Re: CRUD SQLite example
I'm referring to the following script:
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
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
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
-
- Posts: 1201
- Joined: Sun Apr 24, 2011 2:17 am
Re: CRUD SQLite example
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
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.
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.