Page 1 of 1
SQLite - connection error
Posted: Fri May 01, 2015 5:19 am
by erical12
I've been struggling to get my database to connect to a SQLite database I have.
I have my specialFolderPath and I put it into a variable but when I look under the variables tab, tDatabasePath is empty. Why is it empty? I'm still relatively new to LiveCode so I might be missing something big.
When I try to run this code, I get an error on databaseGetIngredients on the line "put revDataFromQuery..." saying "revdberr invalid connection id". I assume that has to do with the empty tDatabasePath?? How can I get it to work? Am I missing something?
Please help me
Code: Select all
on databaseConnect
local tDatabasePath, tDatabaseID
## The database must be in a writeable location
put specialFolderPath("Document") & "/Users/S17600087/Document/ingredients2.sqlite" into tDatabasePath
put revOpenDatabase("sqlite", tDatabasePath,,,,) into tDatabaseID
## Store the database id so other handlers can access it
end databaseConnect
on databaseGetIngredients
local tSQLQuery
put empty into tSQLQuery
## Query the database for details to be displayed in the field
put "SELECT * FROM ingredients where name = 'tName'" into tSQLQuery
put revDataFromQuery(tab,return,tDatabaseID,tSQLQuery) into field "Outcome"
end databaseGetIngredients
Re: SQLite - connection error
Posted: Fri May 01, 2015 1:09 pm
by Klaus
Hi erical12,
1. welcome to the forum!
2. Try to declare you locals OUTSIDE of the handlers at the top of the script:
Code: Select all
local xxxx,yyyy,zzzz
on databseconnect
...
Although that shouldn't matter...
3.Make sure the database connection was SUCCESSFUL!
Means check if tDatabaseID is a number!
Best
Klaus
Re: SQLite - connection error
Posted: Fri May 01, 2015 1:10 pm
by Klaus
HA, WAIT!
It should read: specialFolderPath("
Documents")
With a trailing
S! Looks like this could be it
Re: SQLite - connection error
Posted: Fri May 01, 2015 5:51 pm
by erical12
Klaus wrote:HA, WAIT!
It should read: specialFolderPath("
Documents")
With a trailing
S! Looks like this could be it
My problem is that tDatabaseID is not a number--I fixed "Documents" and relocated my variables. I wrote an if statement to test if tDatabase was a number, and it's not... How do I fix that?
Re: SQLite - connection error
Posted: Fri May 01, 2015 10:02 pm
by Simon
Hi erical12,
Try this;
Code: Select all
put "SELECT * FROM ingredients where name =" && tName & ";" into tSQLQuery
And double check your location
Code: Select all
on mouseUp
answer file "Select your DB"
put it
end mouseUp
Simon
Edit... sorry missed your last question
I use the same connection as you have except I have spaces between the commas;
Code: Select all
put revOpenDatabase("sqlite", tDatabasePath,,,,) into tDatabaseID --vs
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
Re: SQLite - connection error
Posted: Sat May 02, 2015 5:09 am
by erical12
Simon wrote:Hi erical12,
Try this;
Code: Select all
put "SELECT * FROM ingredients where name =" && tName & ";" into tSQLQuery
And double check your location
Code: Select all
on mouseUp
answer file "Select your DB"
put it
end mouseUp
Simon
Edit... sorry missed your last question
I use the same connection as you have except I have spaces between the commas;
Code: Select all
put revOpenDatabase("sqlite", tDatabasePath,,,,) into tDatabaseID --vs
put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseID
Hi Simon,
Thanks for helping! My databaseID is working fine now, but tSQLQuery is still empty... I am trying to put the contents of a field into gName (I made it global) and then putting gName into tSQLQuery, everything up to tSQLQuery is working great
Erica
Re: SQLite - connection error
Posted: Sat May 02, 2015 5:13 am
by Simon
Hi Erica,
You see how tName (gName) is outside of the quotes?
Has to be.
Simon
Re: SQLite - connection error
Posted: Mon May 04, 2015 8:22 pm
by erical12
Simon wrote:Hi Erica,
You see how tName (gName) is outside of the quotes?
Has to be.
Simon
Hi Simon,
gName is outside of the quotes:
Code: Select all
put "SELECT * FROM ingredients where name =" && gName & ";" into tSQLQuery
put revDataFromQuery(tab,return,gDatabaseID,tSQLQuery) into field "Outcome"
When I breakpointed that line, tSQLQuery returned "SELECT * FROM ingredients where name = ;" . Does that include gName?
Re: SQLite - connection error
Posted: Mon May 04, 2015 8:26 pm
by Simon
That says gName is empty.
Check it in the variable watcher.
Simon
Edit; Remember that gName must be declared as a global everywhere it is used.
Re: SQLite - connection error
Posted: Mon May 04, 2015 8:47 pm
by erical12
Simon wrote:That says gName is empty.
Check it in the variable watcher.
Simon
Edit; Remember that gName must be declared as a global everywhere it is used.
I got it to work! Thank you so much for your help! Is there any chance you know how to return a row of data from SQLite? Instead of a column?
Re: SQLite - connection error
Posted: Mon May 04, 2015 8:59 pm
by Simon
Here is a great resource;
http://www.w3schools.com/sql/default.asp
From that, this works
Code: Select all
SELECT * FROM Customers WHERE CustomerID=1;
But you have to have a unique element in the row.
The example table looks like
Code: Select all
CustomerID CustomerName ContactName Address City PostalCode Country
Where the CustomerID is unique.
Simon
Re: SQLite - connection error
Posted: Wed May 06, 2015 7:56 pm
by erical12
Simon wrote:Here is a great resource;
http://www.w3schools.com/sql/default.asp
From that, this works
Code: Select all
SELECT * FROM Customers WHERE CustomerID=1;
But you have to have a unique element in the row.
The example table looks like
Code: Select all
CustomerID CustomerName ContactName Address City PostalCode Country
Where the CustomerID is unique.
Simon
Thanks Simon! I've figured out how to select a row, however my gName variable has to be in quotations in the database.
For example, if gName has "Acid" under it, when I go into SQLite to select the row with "Acid," there has to be quotations around it. In my code, there isn't quotations, which is another problem for me.
How can I get quotations around gName without making gName my actual entry?
Thanks,
Erica
Re: SQLite - connection error
Posted: Wed May 06, 2015 8:46 pm
by Simon
Hi Erica,
Look closley at the code below
Code: Select all
put "SELECT * FROM ingredients where name ='" & gName & "';" into tSQLQuery
put revDataFromQuery(tab,return,gDatabaseID,tSQLQuery) into field "Outcome"
There is an extra single quote inside of the quotes.
Simon