SQLite using Variables with SELECT and WHERE

Just starting out? Need help? Post your questions and find answers here.
Lincon
New User
New User
Posts: 3
Joined: Thu May 16, 2019 1:02 pm

SQLite using Variables with SELECT and WHERE

Post by Lincon »

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!
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: SQLite using Variables with SELECT and WHERE

Post by infratec »

Code: Select all

If DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,...... FROM PWS10 WHERE RECNO = " + str(DBRECNO))
User avatar
kpeters58
Enthusiast
Enthusiast
Posts: 341
Joined: Tue Nov 22, 2011 5:11 pm
Location: Kelowna, BC, Canada

Re: SQLite using Variables with SELECT and WHERE

Post by kpeters58 »

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...
PB 5.73 on Windows 10 & OS X High Sierra
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite using Variables with SELECT and WHERE

Post by skywalk »

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
User avatar
GedB
Addict
Addict
Posts: 1312
Joined: Fri May 16, 2003 3:47 pm
Location: England
Contact:

Re: SQLite using Variables with SELECT and WHERE

Post by GedB »

Lincon 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!
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.html

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
User avatar
TI-994A
Addict
Addict
Posts: 2512
Joined: Sat Feb 19, 2011 3:47 am
Location: Singapore
Contact:

Re: SQLite using Variables with SELECT and WHERE

Post by TI-994A »

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 :D
User avatar
mk-soft
Always Here
Always Here
Posts: 5335
Joined: Fri May 12, 2006 6:51 pm
Location: Germany

Re: SQLite using Variables with SELECT and WHERE

Post by mk-soft »

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)
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
User avatar
GedB
Addict
Addict
Posts: 1312
Joined: Fri May 16, 2003 3:47 pm
Location: England
Contact:

Re: SQLite using Variables with SELECT and WHERE

Post by GedB »

mk-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)
The one thing to be careful of with this approach is SQL Injection Attacks: https://en.m.wikipedia.org/wiki/SQL_injection

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
User avatar
Olliv
Enthusiast
Enthusiast
Posts: 542
Joined: Tue Sep 22, 2009 10:41 pm

Re: SQLite using Variables with SELECT and WHERE

Post by Olliv »

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()
angelagates29
New User
New User
Posts: 1
Joined: Mon Jun 10, 2019 3:53 pm

Re: SQLite using Variables with SELECT and WHERE

Post by angelagates29 »

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.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: SQLite using Variables with SELECT and WHERE

Post by skywalk »

You cannot modify or specify Table columns with variables in a prepared statement.
They must be explicitly named in sql statements.
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Post Reply