It is currently Thu Jul 18, 2019 7:43 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: SQLite using Variables with SELECT and WHERE
PostPosted: Thu May 16, 2019 1:08 pm 
Offline
New User
New User

Joined: Thu May 16, 2019 1:02 pm
Posts: 1
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!

_________________
youporn.wiki/ xhamster.vip/ redtube.onl/


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Thu May 16, 2019 1:10 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4255
Location: Germany
Code:
If DatabaseQuery(0,"SELECT RecNo, GroupBy, Email, Name,...... FROM PWS10 WHERE RECNO = " + str(DBRECNO))


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Fri May 17, 2019 2:05 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Nov 22, 2011 5:11 pm
Posts: 259
Location: Kelowna, BC, Canada
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.70 LTS on Windows 10 & OS X High Sierra


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Fri May 17, 2019 3:04 am 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 2967
Location: Boston, MA
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


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Fri May 17, 2019 2:03 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 16, 2003 3:47 pm
Posts: 1310
Location: England
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


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Fri May 17, 2019 2:13 pm 
Offline
Addict
Addict
User avatar

Joined: Sat Feb 19, 2011 3:47 am
Posts: 2213
Location: Singapore
From 2018: https://www.purebasic.fr/english/viewto ... 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!


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Tue Jun 04, 2019 4:45 pm 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 1759
Location: Germany
Format like sprintf
Link: viewtopic.php?f=12&t=32026

Code:
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 / OOP-BaseClassDispatch / Event-Designer
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Sat Jun 08, 2019 8:52 am 
Offline
Addict
Addict
User avatar

Joined: Fri May 16, 2003 3:47 pm
Posts: 1310
Location: England
mk-soft wrote:
Format like sprintf
Link: viewtopic.php?f=12&t=32026

Code:
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


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Sat Jun 08, 2019 10:18 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Tue Sep 22, 2009 10:41 pm
Posts: 415
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()


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Mon Jun 10, 2019 3:57 pm 
Offline
New User
New User

Joined: Mon Jun 10, 2019 3:53 pm
Posts: 1
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite using Variables with SELECT and WHERE
PostPosted: Mon Jun 10, 2019 4:59 pm 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 2967
Location: Boston, MA
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


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Exabot [Bot] and 13 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye