Page 1 of 1
Creating SQLite table
Posted: Wed Jun 12, 2013 4:04 am
by sandybassett
Still struggling with SQLite. Got it so it opens the sqlite file. Next it creates a table (it says), and inserts a few records, and the sqlite file shows up in the file list with a size of 2Kb. But when I try to display the table records, it shows error no such table. Here's my routine for creating the table:
on databaseCreateTable
put getDatabaseID() into tDatabaseID
put "CREATE TABLE customers.dealers (name char(50), address char(50), city char(30), state char(2), zip char(5), phone char(12),email char(30))" into tSQL
revExecuteSQL tDatabaseID, tSQL
end databaseCreateTable
and here's my routine for displaying the table:
function databaseGetDealersDetails
## Query the database for contact details to be displayed in the field
put getDatabaseID() into tDatabaseID
put "SELECT * from dealers" into tSQL
put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
return tRecords
end databaseGetDealersDetails
which I copied directly from lessons, changing only table and field names. It must be something I'm not seeing. Any help appreciated.
Re: Creating SQLite table
Posted: Wed Jun 12, 2013 5:17 am
by dave_probertGA6e24
Hi,
put "CREATE TABLE customers.dealers (name char(50), address char(50), city char(30), state char(2), zip char(5), phone char(12),email char(30))" into tSQL
It could be the dot in the table name "customers.dealers" - try taking that out or using an underscore e.g. "customers_dealers"
I can't see any mention of dots in the create specifications in the SQLite docs, but I think the dot notation is reserved for use with Aliases. Therefore you probably can't use then in names (Tables/Fields/whatever).
Read more on:
http://www.sqlite.org/lang.html (But make sure you understand BNF first
)
Hope that helps,
Dave
Re: Creating SQLite table
Posted: Wed Jun 12, 2013 12:43 pm
by Klaus
Hi sSandy,
well:
## put "CREATE TABLE customers.dealers (name char(50), address char(50), city char(30), state char(2), zip char(5), phone char(12),email char(30))" into tSQL
will obviously create a table named "customer.dealers" in the database, so what do you exspect when trying to access a table name "dealers"?
Check this with:
...
answer revDatabaseTableNames(ConnectionID)
...
So you should:
1. create a table named "dealers"
OR
2. Access the created table correctly with "customers.dealers"
Best
Klaus
Re: Creating SQLite table
Posted: Wed Jun 12, 2013 10:56 pm
by sandybassett
Well, both of you were right, corrected the table name. But I still can't see the inserted data with the display button from the lesson. Here's the code for inserting a record and for displaying it:
on databaseInsertDealersDetails
## Insert names and email addresses into the database
put getDatabaseID() into tDatabaseID
answer tDatabaseID
put "INSERT into dealers VALUES ('ABC Computers','123 Ace Ave','Tavares');" into tSQL
put "INSERT into dealers VALUES ('DEF Computers','456 Belt Blvd','Eustis');" into tSQL
put "INSERT into dealers VALUES ('GHI Computers','789 Carrot Cir','Mt Dora');" into tSQL
put "INSERT into dealers VALUES ('JKL Computers','135 David Drive','Leesburg')" into tSQL
revExecuteSQL tDatabaseID, tSQL
answer tDatabaseID, tSQL
end databaseInsertDealersDetails
function databaseGetDealersDetails
## Query the database for contact details to be displayed in the field
put getDatabaseID() into tDatabaseID
put "SELECT * from dealers" into tSQL
answer tSQL
put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
return tRecords
end databaseGetDealersDetails
The answer statements show the tDatabaseID, 1, and the table name, dealers, and he tSQL, SELECT * from dealers, but tRecords is blank and the scrolling field is blank. I can't figure what's causing it. Any help appreciated.
Re: Creating SQLite table
Posted: Thu Jun 13, 2013 10:23 am
by Mark
Hi,
Have you declared tDatabaseID as a global variable?
Do you understand that your databaseInsertDealersDetails handler puts only one(the last) tuple into the database?
Could you please post the code that creates or opens the database?
Kind regards,
Mark
Re: Creating SQLite table
Posted: Thu Jun 13, 2013 3:38 pm
by sandybassett
Hi Mark; yes it is a global variable, and yes I realize its only putting the last line into the database, that's part of my remarking out lines to simplify the code trying to find where the problem lies. I hesitated to post the whole code because its 50+ lines, but see no alternative. Thanks for taking time to look this over.
global tDatabasePath, tDatabaseID, tRecords
command databaseConnect
if there is a file tDatabasePath then
answer "Existing database opened."
else
put specialFolderPath("documents") & "/customers.sqlite" into tDatabasePath
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
setDatabaseID tDatabaseID
answer "New empty database opened"
end if
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 dealers (name char(50), address char(50), city char(30), state char(2), zip char(5), phone char(12),email char(30))" into tSQL
revExecuteSQL tDatabaseID, tSQL
answer revDatabaseTableNames(tDatabaseID)
end databaseCreateTable
on databaseInsertDealersDetails
## Insert names and email addresses into the database
put getDatabaseID() into tDatabaseID
answer tDatabaseID
##put "INSERT into dealers VALUES ('ABC Computers','123 Ace Ave','Tavares');" into tSQL
##put "INSERT into dealers VALUES ('DEF Computers','456 Belt Blvd','Eustis');" into tSQL
##put "INSERT into dealers VALUES ('GHI Computers','789 Carrot Cir','Mt Dora');" into tSQL
put "INSERT into dealers VALUES ('JKL Computers','135 David Drive','Leesburg')" into tSQL
revExecuteSQL tDatabaseID, tSQL
answer tDatabaseID, tSQL
end databaseInsertDealersDetails
function databaseGetDealersDetails
## Query the database for contact details to be displayed in the field
put getDatabaseID() into tDatabaseID
put "SELECT * from dealers" into tSQL
answer tSQL
put revDataFromQuery(tab,return,tDatabaseID,tSQL) into tRecords
answer tDatabaseID, tsql
answer tRecords
return tRecords
end databaseGetDealersDetails
Since I never can see the data in the answer lines of the display routine I'm not sure its even getting there. All help appreciated as I still have to learn UPDATE, DELETE and SELECT...WHERE
Re: Creating SQLite table
Posted: Thu Jun 13, 2013 4:05 pm
by Mark
Hi,
I would remove the getDatabaseID and setDatabaseID handlers and just use the global variable tDatabaseID. That will make everything a little simpler.
Starting with the database create, add some error checking:
Code: Select all
global tDatabasePath, tDatabaseID, tRecords
command databaseConnect
if there is a file tDatabasePath then
answer "Existing database opened."
else
put specialFolderPath("documents") & "/customers.sqlite" into tDatabasePath
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
if tDatabaseID is an integer then
setDatabaseID tDatabaseID
answer "New empty database opened"
else
beep
answer error "An error occurred:" && item 2 of tDatabaseID
end if
end if
end databaseConnect
I'm not sure what you want to do if the database already exists. Just mentioning that it is opened won't do. You still need to use the revOpenDatabase function. I haven't changed this part of your script yet.
Kind regards,
Mark
Re: Creating SQLite table
Posted: Thu Jun 13, 2013 7:34 pm
by sandybassett
Ok, I deleted the set and get routines and everything still works OK.
You said:
"I'm not sure what you want to do if the database already exists."
That was in there just for testing, to let me know whether it was creating a new one or using the one previously created. I was deleting the sqlite file sometimes to check other things and wanted to know where I was.
So now everything looks like its working except that when displaying nothing shows, just blank. So I don't know yet whether the display routine has an error in it, or whether there's just an empty record in the file.
The answer line in the display routine shows tDatabase ID as "1" and tSQL as "SELECT * from dealers". If the data was really put into the file I would think it would show up somewhere. How can I make this work? Thanks for the help.
Re: Creating SQLite table
Posted: Thu Jun 13, 2013 11:01 pm
by Mark
Hi,
This is all you need.
Code: Select all
on mouseUp
put empty
databaseConnect
databaseCreateTable
databaseInsertDealersDetails
put databaseGetDealersDetails() // into msg
closeDatabase // allows me to delete the database manually
end mouseUp
global gDatabaseID
command databaseConnect
// close any opendatabases first
put revOpenDatabases() into myDBs
repeat for each item myDB in myDBs
revCLoseDatabase myDB
end repeat
// open (new) database
put specialFolderPath("desktop") & "/customers.sqlite" into myDatabasePath
put revOpenDatabase("sqlite", myDatabasePath, , , , ) into myDatabaseID
if myDatabaseID is an integer then
put myDatabaseID into gDatabaseID
else
beep
answer error "An error occurred:" && item 2 of myDatabaseID
end if
end databaseConnect
on databaseCreateTable
if gDatabaseID is among the items of revOpenDatabases() then
put "CREATE TABLE IF NOT EXISTS dealers (name char(50) PRIMARY KEY, address char(50), city char(30), state char(2), zip char(5), phone char(12),email char(30))" into mySQL
revExecuteSQL gDatabaseID, mySQL
put the result into rslt
if rslt is not an integer then
beep
answer error "An error occurred:" && item 2 of rslt
end if
else
beep
answer error "No database is open."
end if
end databaseCreateTable
on databaseInsertDealersDetails
## Insert names and email addresses into the database
put "'ABC Computers','123 Ace Ave','Tavares'" into myDataArray[1]
put "'DEF Computers','456 Belt Blvd','Eustis'" into myDataArray[2]
put "'GHI Computers','789 Carrot Cir','Mt Dora'" into myDataArray[3]
put "'JKL Computers','135 David Drive','Leesburg'" into myDataArray[4]
repeat with x = 1 to 4
put "INSERT OR REPLACE into dealers (name,address,city) VALUES (" & myDataArray[x] & ");" into mySQL
revExecuteSQL gDatabaseID, mySQL
put the result into rslt
if rslt is not an integer and rslt is not empty then
beep
answer error "An error occurred:" && rslt
exit repeat
end if
end repeat
end databaseInsertDealersDetails
function databaseGetDealersDetails
## Query the database for contact details to be displayed in the field
put "SELECT * from dealers" into mySQL
put revDataFromQuery(tab,return,gDatabaseID,mySQL) into myRecords
put the result into rslt
if item 1 of rslt is "revdberr" then
beep
answer error "An error occurred:" && item 2 of rslt
return empty
else
return myRecords
end if
end databaseGetDealersDetails
on closeDatabase
if gDatabaseID is among the items of revOpenDatabases() then revCloseDatabase gDatabaseID
end closeDatabase
Your main problems were in the SQLite syntax.
For testing, I created the database on the desktop instead of in the documents folder. You can change this easily.
Kind regards,
Mark