Using sqlite

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

Post Reply
Quinton B.
Posts: 108
Joined: Mon Mar 20, 2017 5:13 am

Using sqlite

Post by Quinton B. » Tue Apr 23, 2019 6:59 am

Good day, I have been trying to connect, add, and display data from an SQLite database. So far my efforts are not successful. Here is what I have:
Connect:

Code: Select all

on mouseUp
   local tDatabasePath, tDatabaseID
   
   ## The database must be in a writeable location
   put specialFolderPath("documents") & "/localinfo.sqlite" into tDatabasePath
   
   ## Open a connection to the database
   ## If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
   
   ## Store the database id so other handlers can access it
   setDatabaseID tDatabaseID
end mouseUp

local sDatabaseID
command setDatabaseID pDatabaseID
	put pDatabaseID into sDatabaseID
end setDatabaseID
function getDatabaseID
   return sDatabaseID
end getDatabaseID
Create:

Code: Select all

on databaseCreateTable
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "CREATE TABLE local_info (State char(50), City char(50), Login_count (50), User_Name (50), )" into tSQL
   revExecuteSQL tDatabaseID, tSQL
end databaseCreateTable
Insert:

Code: Select all

global gState, gCity, gLogin_Count, gUser_Name
on databaseInsertlocal_info
   put the text of field "State" into gState
   put the text of field "City" into gCity
   put the text of field "Login_Count" into gLogin_Count
   put the text of field "User_Name" into gUser_Name
   put getDatabaseID() into tDatabaseID
   put "INSERT into local_info VALUES (" && "'" & gState & "'" && "," && "'" & gCity & "'" && "," && "'" & gLogin_Count & "'" && "," && "'" & gUser_Name & "'" && ")" into tSQL
   revExecuteSQL tDatabaseID, tSQL
end databaseInsertlocal_info
Get Info:

Code: Select all

on mouseUp
   put databaseGetlocal_info() into field "local_info"
end mouseUp

function databaseGetlocal_info
   ## Query the database for contact details to be displayed in the field
   put getDatabaseID() into tDatabaseID
   put "SELECT * from local_info" into tSQL
   put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
   return tRecords
end databaseGetlocal_info
Attachments
Untitled.png

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Using sqlite

Post by AxWald » Tue Apr 23, 2019 9:02 am

Hi,
Quinton B. wrote:
Tue Apr 23, 2019 6:59 am
So far my efforts are not successful.
What doesn't work? Hard to determine w/o recreating the whole stuff ...

What I see:
  1. Save yourself the hassle with setDatabaseID/ getDatabaseID. Instead, once you have the dbID, put it into a global variable or in a custom property (the dbID of this stack ?)
    .
  2. Add some error checking - each time you call a rev* database function/ command, check what it returns & act accordingly!
    .
  3. You may use the database functions/ commands in your script, but be careful, these are mixed - functions & commands. If you use the "revdb_*" equivalents, these are ALL functions AND share common first chars - so a search for "revdb_" in your script will find all!
    .
  4. For sure you're free to create your database from within LC. But why not create it before, with a more suitable tool? SQLite Manager, or SQLite Browser.
    You have a lot more control this way. Think of indices, views and the additional database properties - LC only ever gives you a default SQLite & you have to go long ways to customize it.
    And, having such a db tool, you can easily inspect your tables while you change 'em from LC ...
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”