PureBasic Forums - English

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!

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

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.

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

From 2018: viewtopic.php?f=13&t=71763

Format like sprintf
Link: viewtopic.php?f=12&t=32026

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

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()

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.

You cannot modify or specify Table columns with variables in a prepared statement.
They must be explicitly named in sql statements.