Creating SQLite table

[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.

[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

[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

[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.

[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

[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

[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

[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.

[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