SQLite - connection error

[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

[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

[Klaus]

HA, WAIT!
It should read: specialFolderPath("Documents")
With a trailing S! Looks like this could be it

[erical12]

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?

[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

[erical12]

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

[Simon]

Hi Erica,
You see how tName (gName) is outside of the quotes?
Has to be.

Simon

[erical12]

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?

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

[erical12]

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?

[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

[erical12]

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

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

='" & gName & "';"
There is an extra single quote inside of the quotes.

Simon