LiveCode Forums.

Posted: **Thu Apr 14, 2011 6:46 pm**

For some reason I'm having trouble connecting to SQLite. I'm getting the error "is execution error at line n/a () near: "revdberr, invalid connection id" on the following line of code: revExecuteSQL gConID, tSQL.

I basically copied the code for this from the SQLite demo stack that comes with LvieCode 4.6. There is code before it that opens the database and checks to make sure that there is access to it.

Does anyone have any suggestions?

Thanks in advance,

Peter

Posted: **Thu Apr 14, 2011 10:37 pm**

Hi Peter,

without any code snippet we can only guess!
Did you define "gConID" as a global variable?

Best

Klaus

Posted: **Tue Apr 19, 2011 1:23 am**

I started with the information that came with the SQLite sampler in LiveCode 4.6.

To connect to a database I've got the code:

Code: Select all

on mouseUp
   global gConID
   local tConId
   put revOpenDatabase("sqlite","mortgage.db",,,,,,) into tConID
   if tConID is "" then 
     answer warning "Problem creating or accessing mortgage database!"
   else
     answer information "Mortgage database Connected! Your connection ID is: " & tConID
     put tConID into gConID
   end if
end mouseUp

Everything seems ok with this.

To create a table I use the following code. I put it together using code from the SQLite sample code that comes with LiveCode 4.6. There are parts of the code that I've included that I don't really understand and they don't tell you a lot about it in the SQLite sample code. What exactly does the handleRevDBerror do? Why do you need it? When the code runs the variable tResult is blank (at least nothing shows up during the two answer statements). I think something is wrong.

Code: Select all

on mouseUp
   global gConID
   if gConID is "" then
      answer information "No database is connected to, please go back to step one"
      exit mouseUP
   end if
   
   ## Delete current tabe before recreating it 4/18/11 PBK
   local tSQL
   local tTMP
   
   put "DROP TABLE borrowers" into tSQL
   put the result into tResult
   
   put revdb_execute (gConID, tSQL) into tTMP
   handleRevDBerror tTMP
   if the result is not empty then
      answer warning the result
      exit mouseUp
   else
      answer information "Number of tables deleted: "  &tResult
   end if
    
   ## Re-Create the table 4/18/11 PBK 
   put "CREATE TABLE borrowers (userID integer primary key, name text, home_addr text, email text, prop_addr text, pur_price integer, fin_amt integer, prop_type text, date text)" into tSQL
   put the result into tResult
   put revdb_execute(gConID, tSQL) into tTMP
   
   handleRevDBerror tTMP
      
   if the result is not empty then
      answer warning the result
      exit mouseUp
   end if
   answer information "Number of tables added: "  &tResult 
   revCloseDatabase gConID
end mouseUp

on handleRevDBerror pError
   switch
      case item 1 of pError is "revdberr"
         return "revDBError: " &pError
         break
      case "syntax error" is in pError
         return "Database Error: " &pError
         break
   end switch
end handleRevDBerror

FInally when I take information from a data entry screen and try to insert it into the borrowers table nothing happens. There are two answer statements in the code. The first (answer "tConID = " &tConID) tells me that tConID has a value of 1. The revOpenDatabase command seems to be working. The second, (answer information "Number of rows added: " &return &tResult) the return value is blank and tResult states that "there is no such table: borrowers".

Code: Select all

on mouseUp
   
   ## enter borrowers information in the borrower table ##
   global gConID
   local tConID
   
   put revOpenDatabase("sqlite", "borrowers.db",,,,,,) into tConID
   # put revOpenDatabase("sqlite", "mortgage.db",,,,,,) into tConID
   if tConID is "" then
      answer warning "Problem creating or asscesing mortgage database!"
   else
      answer "tConID = " &tConID
      put tConId into gConID
            
      local tSQL
      local tResult
      
      global borr_nm
      global email_addr
      global borr_addr1
      global borr_addr2
      global prop_addr
      global purch_price
      global fin_amt
      global prop_type
      
      put fld m_borr_name into borr_nm
      put fld m_borr_email_addr into email_addr
      put fld m_borr_prop_addr1 into borr_addr1
      put fld m_borr_prop_addr2 into borr_addr2
      put fld m_prop_addr into prop_addr
      put fld m_purch_price into purch_price
      put fld m_fin_amt into fin_amt
      put fld m_prop_type into prop_type
      
      put "INSERT INTO borrowers VALUES ('1', borr_nm, email_addr, borr_addr1, borr_addr2, prop_addr, purch_price, fin_amt, prop_type, date())" into tSQL
      put revdb_execute (gConID, tSQL) into tTMP
       
      put the result into tResult
      
      handleRevDBerror tTMP
      if the result is not empty then 
         answer warning "it didn't work: " &tTMP
         exit mouseUp
      end if
      answer information "Number of rows added: " &return &tResult
   end if
   
   ## view data in table borrowers
   put "Select * from borrowers" into tSQL
   put revdb_querylist (,,gConID, tSQL) into tList
   handleRevDBerror tList
   if the result is not empty then 
         answer warning "it didn't work: " &tTMP
         exit mouseUp
      end if
      
      put tList into fld "Users"
      
      ## Close database
   revCloseDatabase gConId
   
end mouseUp

on handleRevDBerror pError
   switch
      case item 1 of pError is "revdberr"
         return "revDBError: " &pError
         break
      case "syntax error" is in pError
         return "Database Error: " &pError
         break
   end switch
end handleRevDBerror

I'm lost. How can the reOpenDatabase statement work but the system can't find the table borrowers? Does anyone have any ideas?

How can I tell if there's a table called borrower? Is there an independent way to do this?

If you look at this code and can give me tips on better way to get things done, please don't hesitate to tell me.

Thanks,

Peter

Posted: **Tue Apr 19, 2011 8:01 am**

Try this :

-put your borrowers.db file at the root of your C drive

-then in your last script, replace
put revOpenDatabase("sqlite", "borrowers.db",,,,,,) into tConID
by
put revOpenDatabase("sqlite", "C:/borrowers.db",,,,) into tConID [with only 4 commas, not 6]

Posted: **Tue Apr 19, 2011 12:49 pm**

Hi Peter,

supply an absolute path to your DB calls like this:
## Mac:
revOpenDatabase("sqlite", "/Users/peter/Documents/borrowers.db",,,,)
## Win:
revOpenDatabase("sqlite", "C:/folder/subfolder/borrowers.db",,,,)

Looks like the database is just not found, maybe because "the defaultfolder" got changed or whatever.

Best

Klaus

Posted: **Tue Apr 19, 2011 4:45 pm**

Thanks for all of the help. Directing the system to the correct folder helped. I seem to be having a problem with my INSERT statment now.

If the insert statement is put "INSERT INTO borrowers VALUES (field1, field2, field3, field4, field5)into tSQL I get an error message that tells me that there is no such table borrowers.

If I change the insert statement to be put "INSERT INTO /Users/peter/Documents/borrower VALUES (field1, field2, fiedl3, field4 field5)" into tSQL I get an error message that tells me "insert didn't work: near "/": syntax error".

Any ideas?

Peter

Posted: **Tue Apr 19, 2011 5:44 pm**

Hi Peter,

you "INSERT INTO" a TABLE and not into the database(file).
Is the name of the table the same as the name of the database?

I am no database expert, but this syntax cannot work:
put "INSERT INTO borrowers VALUES (field1, field2, field3, field4, field5) into tSQL

Should be like this:
put "INSERT INTO borrowers VALUES" && fld 1 & "," fld 2 & "," fld 3 & "," & fld 4 & "," & fld 5 into tSQL

But then the names of the database fields werher you want to insert data into are also missing.

Best

Klaus

Posted: **Tue Apr 19, 2011 6:44 pm**

Klaus -

I've checked the SQLite sampler that comes with Livecode 4.6. The insert statement that they use is the following:
put "INSERT INTO users(userID, name, email, emailList)" & \
"VALUES(null," & tRowData & ")"

I've also checked a website that the SQLite sampler tells you to go to for information on SQL statements. I think that I'm ok.

I'll play around with what you are telling me to do. Are you using a European keyboard? Where is the key that you are using for the character before and after fld1?

Thanks,

Peter

Posted: **Tue Apr 19, 2011 6:56 pm**

Peter K wrote: If I change the insert statement to be put "INSERT INTO /Users/peter/Documents/borrower VALUES (field1, field2, fiedl3, field4 field5)" into tSQL I get an error message that tells me "insert didn't work: near "/": syntax error".

Any ideas?

Peter

My advice : keep it simple.

Try first to look around SQL syntax, with simple queries.

Like :

Code: Select all

put "INSERT INTO borrowers (home_addr,email) VALUES ('my home','My Email')" into dbSQL

Then, use some LiveCode fields to create your query.

Code: Select all

-- be careful with double and single quote. Double quotes are for string with LiveCode, and single quote is to separate strings in SQL queries
put "my Home" into field "home_addr"
put "my Email" into field "email"
put "INSERT INTO borrowers (home_addr,email) VALUES ('"&field "home_addr"&"','"&field "email"&"')" into dbSQL
---check if the query is okay
answer dbSQL

Don't bother to start with dozen of columns, with very long queries. It will confuse you. Then, peruse the gazillons SQL websites available in order to learn more about SQL language, the data types etc.

Posted: **Tue Apr 19, 2011 8:24 pm**

I followed your example and it seemed to work. There are a lot of single and double parenthesis to keep track of, but I think I'm ok.

There are two columns that I'm are part of the insert statement that are not variables that were part of the data entry. The first is an integer field that is a counter so that I know the record number. The second is the date that the record is being added. SQLite does not have a date type, so the date is a text field. Before the insert statement, I have the put statement for the date field (put date() into dte).

I'm having problems with this. I'm getting an error for the counter field, "no such column: counter"

Any ideas?

thanks,

Peter

Posted: **Fri Apr 22, 2011 8:33 pm**

This is a bit of a tangent from your question and I'm sorry I don't know exactly what's going on to suggest a correct solution. I just couldn't help offering the thought that "put the date" as a text field is not really very robust - prone to text entry errors if user input is allowed, and open to misinterpretation with different date formatting possibilities. In the absence of true date handling in SQLite, you could at least convert a date to seconds before storing as an integer in the table, and converting the seconds to your selected date format on retrieval.

Posted: **Fri Apr 22, 2011 11:10 pm**

Peter K wrote: I'm having problems with this. I'm getting an error for the counter field, "no such column: counter"

Give us your whole SQL query, so we can have a look.

In any case, you should try date('NOW') within your INSERT query.

Code: Select all

put "INSERT INTO borrowers (thecounter,thedate) VALUES ('1',date('NOW'))" into tSQL

LiveCode Forums.

Trouble connecting to SQLite

Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite

Re: Trouble connecting to SQLite