SQLite using Variables with SELECT and WHERE

[C87]

I have a form with data from SQLite. Vertically on the left I am showing all of the fields for a single record. As I can only do a Next and not a Previous in SQLite I have a ListIconGadget() to the right containing all of the records from the database showing just three or four fields. This allows the user to locate and select any record. They can then click or double-click on a row in the ListIconGadget() which reads the unique record number in the Row contained in the database field RecNo. This is saved to a memory variable and that variable is used in the DatabaseQuery() which falls over and goes to the error, showing zero.
If I use a literal it works just fine so the code is Ok. I've looked on the net and all of the examples for SQLite use literals with SELECT and a WHERE, which isn't too helpful. I also have the book Using SQLite by O'Reilly which doesn't even touch on this issue. (the RecNo field in the database isn't the SQLite System number but a unique number added with the other fields when a new record is added.)

If DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,...... FROM PWS10 WHERE RECNO = DBRECNO")
......
...code
Else
mDBErr = DatabaseError() ; -> returns zero unless a literal
Endif

Entering a literal : this with any number entered, matching a record number works Ok and displays the correct record

If DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,......... FROM PWS10 WHERE RECNO = 28")

Help on this most appreciated, C87

[Martin Verlaan]

Try this:

Code: Select all

DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,...... FROM PWS10 WHERE RECNO = " + Str(DBRECNO)) 

[skywalk]

Download DB Browser For SQLite and open your database. Then cut and paste your SQL query into the 'Execute SQL' panel. DB4S will show you any errors or the results in a grid.

[C87]

Hello Martin, Well I'd never have thought to try that!
The database and memory fields are all numeric. At one point many hours ago I did consider in desperation to converting both to a string but couldn't see the logic. I changed it as you advised and was amazed to see it work. In fact it is much better using the ListIconGadget() than with [NEXT] & [BACK] buttons. The result from the ListIconGadget() doesn't seem to differentiate between a double-click and a single but it isn't a problem in this case, in fact more of an advantage.

Many thank again Martin

[C87]

Hello Skywalk, I have had that software for a while now. In fact I have used it to add test data before I wrote any code.
With the variable DBRECNO or the Str(DBRECNO) it throws the error 'No such column DBRECNO' and of course works if I remove the var and put in a valid number.

Have to say I still can't understand quite how the Str() is working.....but it is!

Regards, C87

[skywalk]

Ok, I am impressed you got this far without understanding string formatting functions
Str(1234) --> "1234"
StrD(1234) --> "1234.0"
Your program is building the SQL query text from your specific inputs and database tables.
In DB4S, you can only execute valid SQL code. None of the PB variable names or Str(this) can be included.
Just insert a 'Debug MySQL$' BEFORE it's sent to SQLite.
Then paste that into DB4S to verify it works.

[citystate]

try something like this, perhaps?

Code: Select all

SetDatabaseLong(0,0,DBRECNO)
If DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,...... FROM PWS10 WHERE RECNO = ?") 
......
...code
Else 
mDBErr = DatabaseError() ; -> returns zero unless a literal
Endif

the SetDatabaseX commands allow us to insert values directly into the SQL query without having to worry about messing around with string manipulations

[C87]

Hello citystate, As you said it is a solution, a brilliant second way of doing it. (Martin's Str() works) Have to say I've not come across the SetDataBaseLong() etc. functions.
It's called reading the HELP file again and again I suppose.

Many thanks again fro your help, C87