SQLite Forum

Passing variable to SQLite3 fields
Login

Passing variable to SQLite3 fields

(1) By Chazza on 2021-01-20 16:05:39 [link] [source]

Hi I am using SQLite with an ESP32 and using the Arduino IDE

My question is how do I pass the value of string and integer to a field on an SqLite3 table so I can enter a new record.

The example below generates both a number and also a string but I cannot find a way to record these values. The reading value should be stored in readvalue field and the output of statusStr should be stored in the status field. I

Any help will be greatly appreciated. Thank you in advance

Chazza

void insertrecord() {

int reading = random(0, 1023); String statusStr = ""; if (reading >= 750) { statusStr = "HIGH"; } else if (reading <= 749 and reading >= 500) { statusStr = "MEDIUM"; } else if (reading <= 499 and reading >= 0) { statusStr = "LOW"; }

Serial.print("AO reading is : "); Serial.println(reading); Serial.print("Status is : "); Serial.println(statusStr);

// insert values

sqlite3 *db1; int rc ;

if (db_open("/spiffs/test.db", &db1)) return;

Serial.println("Inserting new record");

// line below to add new record with default values when button pushed for testing this works fine rc = db_exec(db1, "INSERT INTO aoreadings VALUES (1, 2, 'abcdefg');");

// line below add new record with value 1 in id field, reading in readvalue field and StatusStr into status field when button pushed. This does not work

rc = db_exec(db1, "INSERT INTO aoreadings VALUES (id, readvalue, status);(1, reading, statusStr)");

if (rc != SQLITE_OK) { sqlite3_close(db1); return; }

}

(2) By Stephan Beal (stephan) on 2021-01-20 16:13:46 in reply to 1 [link] [source]

This does not work

It cannot possibly work: there's no way for that code to associate the contents of the string with specific variables.

Firstly, to bind values to a statement, you want the "bind" APIs:

https://www.sqlite.org/c3ref/bind_blob.html

Secondly, your code demonstrates a serious misuse of the API: you are opening the db on each call to your function and only closing it if the insert fails. That will end up producing a major memory leak. Opening it on each call is tremendously inefficient unless it's only called once during the life of the app. Each time a db is opened, it must eventually be closed, which is not happening in your code.

(3) By Chazza on 2021-01-20 18:32:30 in reply to 2 [link] [source]

Hi Stephan Thank you for the prompt reply which is very useful. I note your point on closing the db and have amended the code accordingly.

I am read reading through the link you provided regarding the "bind" APIs

Do you by any chance have some example code in C or C++ I could look at regarding bind ?

Thank you again for your input Kind regards Chazza

(4) By Stephan Beal (stephan) on 2021-01-20 18:36:14 in reply to 3 [link] [source]

Do you by any chance have some example code in C or C++ I could look at regarding bind ?

i'm on a tablet with no code readily handy, but googling for "sqlite3_bind example c++" finds oodles of it.

(5) By Chazza on 2021-01-20 20:16:20 in reply to 4 [link] [source]

Hi Stephan Thanks again. I will follow your advice and try and work through the examples Kind Regards Chazza

(6) By Gunter Hick (gunter_hick) on 2021-01-21 07:32:08 in reply to 1 [source]

Your SQL syntax is ass backwards.

IF you are SURE that the source of your data is reliable, you can just sqlite3_snprintf your statement with a pattern of

"INSERT INTO aoreadings (id, readvalue, status) VALUES (%d,%d,%q);"

and the values you woudl like to insert. Unless you really want to generate id values, you could also declare "id integer primary key" and have SQLite do that for you. In this case, do not provide an id in your statement.

Otherwise you need to call one of the sqlite3_prepare() routines (once only) with an argument of (or similar to)

"INSERT INTO aoreadings (id, readvalue, status) VALUES (?,?,?);"

Then call the sqlite3_bind_xxx() functions suitable for the types of values you are binding to assign values to the parameters.

Then call sqlite3_step() to actually perform the insert.

Repeat the bind and step for however many records you want to insert.

remember to call sqlite3_finalize() when you are done with the statement.

NOTE: To avoid SQL injection attacks, do not pass user input to SQL without checking for malicious content.

(7) By Chazza on 2021-01-28 23:55:21 in reply to 6 [link] [source]

Thanks Gunter

Very useful advice. I am working slowly through SQLite and am grateful for the advice you have passed on and it has helped me get through this step. All the best Chazza..