SQLite using Variables with SELECT and WHERE
SQLite using Variables with SELECT and WHERE
Hello everyone,,
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
_________________
If it's falling over......just remember the computer is never wrong!
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
_________________
If it's falling over......just remember the computer is never wrong!
Re: SQLite using Variables with SELECT and WHERE
Code: Select all
If DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,...... FROM PWS10 WHERE RECNO = " + str(DBRECNO))
Re: SQLite using Variables with SELECT and WHERE
From reading through your description of you application logic/setup you may also be interested in doing things differently (this would not be the best solution if your database had millions of records, but works very well for a couple of thousand records):
Define a structure that mimics your db table schema and then build a list of that structure type.
Upon program start, load all your database records into the structure elements of that list and use that list for navigation and not the database. After the initial load, you would only talk to the database for the purpose of deleting records, inserting new records or updating existing records.
Advantages:
Ability to close the database after initial read; only reopen it if 'real' work is to be done = better crash/corruption protection should you lose power or have other soft-/hardware issues
Incredible speed boost because all navigation happens in memory and does not require database access.
Entirely independent of the underlying database (though PB already does a good job of insulating you from that).
While not applicable to SQLite: If you were to run against a SQL server of sorts, you can run into nasty issues with connection timeouts (very vendor specific). Since you only open the database immediately prior to performing some action, you have excellent problem detection/handling right then and there
But. like I said, this may not be the best solution for all cases...
Define a structure that mimics your db table schema and then build a list of that structure type.
Upon program start, load all your database records into the structure elements of that list and use that list for navigation and not the database. After the initial load, you would only talk to the database for the purpose of deleting records, inserting new records or updating existing records.
Advantages:
Ability to close the database after initial read; only reopen it if 'real' work is to be done = better crash/corruption protection should you lose power or have other soft-/hardware issues
Incredible speed boost because all navigation happens in memory and does not require database access.
Entirely independent of the underlying database (though PB already does a good job of insulating you from that).
While not applicable to SQLite: If you were to run against a SQL server of sorts, you can run into nasty issues with connection timeouts (very vendor specific). Since you only open the database immediately prior to performing some action, you have excellent problem detection/handling right then and there
But. like I said, this may not be the best solution for all cases...
PB 5.73 on Windows 10 & OS X High Sierra
Re: SQLite using Variables with SELECT and WHERE
Or, open and work with an in :memory: db and make updates to the file db when necessary. Nearly identical queries work on both db's, so less code to carry.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Re: SQLite using Variables with SELECT and WHERE
Are you using a bind variable, with a question mark placeholder? As shown in the example on this page: https://www.purebasic.com/documentation ... query.htmlLincon wrote:Hello everyone,,
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
_________________
If it's falling over......just remember the computer is never wrong!
If you are then can you answer these questions:
1. What is the exact SetDatabase... command used.
2. What is the type of the corresponding database field.
3. What message is DatabaseError() giving you?
Sent from my iPhone using Tapatalk
Re: SQLite using Variables with SELECT and WHERE
From 2018: viewtopic.php?f=13&t=71763
Texas Instruments TI-99/4A Home Computer: the first home computer with a 16bit processor, crammed into an 8bit architecture. Great hardware - Poor design - Wonderful BASIC engine. And it could talk too! Please visit my YouTube Channel
Re: SQLite using Variables with SELECT and WHERE
Format like sprintf
Link: viewtopic.php?f=12&t=32026
Link: viewtopic.php?f=12&t=32026
Code: Select all
Define column.s = "Pron_1_Pers_Sg"
Define goal.s = "gender"
Define constrain.s = "Mann"
sql.s= Format("SELECT %s FROM Data where %s = '%s'", @column, @goal, @constrain)
Debug sql
;DatabaseQuery(..., sql)
My Projects ThreadToGUI / OOP-BaseClass / EventDesigner V3
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
PB v3.30 / v5.75 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace / OneDrive
Re: SQLite using Variables with SELECT and WHERE
The one thing to be careful of with this approach is SQL Injection Attacks: https://en.m.wikipedia.org/wiki/SQL_injectionmk-soft wrote:Format like sprintf
Link: viewtopic.php?f=12&t=32026
Code: Select all
Define column.s = "Pron_1_Pers_Sg" Define goal.s = "gender" Define constrain.s = "Mann" sql.s= Format("SELECT %s FROM Data where %s = '%s'", @column, @goal, @constrain) Debug sql ;DatabaseQuery(..., sql)
Make sure that any external strings you take as input and include in your sql are properly cleansed.
Using bind variables reduces the risk of injection, but not completely: https://stackoverflow.com/questions/432 ... -variables
Sent from my iPhone using Tapatalk
Re: SQLite using Variables with SELECT and WHERE
Okay... Better with your human text.
First, you have to download compiler :
- free demo or
- licensed compiler
I recommand you the license which allows you to have a life guaranted product without having to pay when you do not want to do it, or when you cannot do it. It is the way since near 20 years, already.
The author of the compiler is Fred and you can contact him through mail if you want to say him "Hello" or others more important things. alphasnd@gmail.com
Free compiler demo is here.
Then you install it to your computer whatever the OS is : Windows, Linux or Mac. Just choose the right compressed file in the downloading page indicated above.
Once you installed the compiler (demo or licensed), you just have to tap your source code and press F5 key to execute it immediately.
No including file to prepare : Fred and his team prepared all that for you, I repeat it, whatever the 3 main OS you have.
Now, you want to create a window : search OpenWindow() function (or statement, that is the same result).
You want a combo box : see ComboBoxGadget()
You want to display a table, a list, an array or a data base chunk : please see ListIconGadget() function.
You want to check several events the OS returns : see WaitWindowEvent()
First, you have to download compiler :
- free demo or
- licensed compiler
I recommand you the license which allows you to have a life guaranted product without having to pay when you do not want to do it, or when you cannot do it. It is the way since near 20 years, already.
The author of the compiler is Fred and you can contact him through mail if you want to say him "Hello" or others more important things. alphasnd@gmail.com
Free compiler demo is here.
Then you install it to your computer whatever the OS is : Windows, Linux or Mac. Just choose the right compressed file in the downloading page indicated above.
Once you installed the compiler (demo or licensed), you just have to tap your source code and press F5 key to execute it immediately.
No including file to prepare : Fred and his team prepared all that for you, I repeat it, whatever the 3 main OS you have.
Now, you want to create a window : search OpenWindow() function (or statement, that is the same result).
You want a combo box : see ComboBoxGadget()
You want to display a table, a list, an array or a data base chunk : please see ListIconGadget() function.
You want to check several events the OS returns : see WaitWindowEvent()
-
- New User
- Posts: 1
- Joined: Mon Jun 10, 2019 3:53 pm
Re: SQLite using Variables with SELECT and WHERE
Hello,
Is there any way to do it something like this in Sqlite?
I want to declar a variable and append columns to it.
But Sqlite dows not allows you to use variables.
Is there any way to do it something like this in Sqlite?
I want to declar a variable and append columns to it.
But Sqlite dows not allows you to use variables.
Re: SQLite using Variables with SELECT and WHERE
You cannot modify or specify Table columns with variables in a prepared statement.
They must be explicitly named in sql statements.
They must be explicitly named in sql statements.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum