SQLite Forum

Cannot bind text variable to insert query
Login

Cannot bind text variable to insert query

(1) By anonymous on 2021-02-23 19:19:40 [link] [source]

Hi

I am unable to insert a record where one of the fields is a text field. All the other fields are integer fields. I am using C/C++.

in my table called alarms, it has the following fields; id = int primary key alarmtime = text zone1 to zone8 are all integer field

The text field called alarmtime It needs to get its value from a global string variable called alarmtimex I can see is passed to the function as I can print out its value.

My question is how do I need to modify the function to be able to pass the value of alarmtimex and store it into the alarmtime field at the same time storing the values of zone1 to zone8 ? I can insert a new record without issue if I exclude the text field. I do need to keep alarmtime as a text field. Thank you in advance. Chazza

Code-------------------------------------

void insertalarmtime() {

Serial.print("Alarm Time to insert is : ");Serial.println(alarmtimex);

sqlite3* db1;
int rc;
if (db_open("/spiffs/alarms", &db1))
	return;

char* sql = "INSERT INTO alarmtimes (alarmtime, zone1, zone2, zone3, zone4, zone5, zone6, zone7, zone8) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

rc = sqlite3_prepare_v2(db1, sql, strlen(sql), &res, &tail);

if (rc != SQLITE_OK) {
	Serial.printf("ERROR preparing sql: %s\n", sqlite3_errmsg(db1));
	sqlite3_close(db1);
	return;
}

sqlite3_bind_text(res, 1, alarmtimex); // alarmtimex is a global string variable
sqlite3_bind_int(res, 2, zone1x); // zone1x to zone8x are global int variables
sqlite3_bind_int(res, 3, zone2x); 
sqlite3_bind_int(res, 4, zone3x); 
sqlite3_bind_int(res, 5, zone4x); 
sqlite3_bind_int(res, 6, zone5x); 
sqlite3_bind_int(res, 7, zone6x); 
sqlite3_bind_int(res, 8, zone7x); 
sqlite3_bind_int(res, 9, zone8x); 


if (sqlite3_step(res) != SQLITE_DONE) {
	Serial.printf("ERROR executing stmt: %s\n", sqlite3_errmsg(db1));
	sqlite3_close(db1);
	return;
}

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

//free the Statement 
sqlite3_finalize(res);
sqlite3_close(db1);

}

End code------------------------------------------------------

(2) By Stephan Beal (stephan) on 2021-02-23 19:32:41 in reply to 1 [link] [source]

sqlite3_bind_text(res, 1, alarmtimex); // alarmtimex is a global string variable

That cannot possibly have compiled, as that function requires more arguments:

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

(3) By anonymous on 2021-02-23 19:36:38 in reply to 2 [link] [source]

Hi Stephan, It does not compile unless I leave out that line and modify the line char* sql = "INSERT INTO alarmtimes (alarmtime, zone1, zone2, zone3, zone4, zone5, zone6, zone7, zone8) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

to

char* sql = "INSERT INTO alarmtimes (zone1, zone2, zone3, zone4, zone5, zone6, zone7, zone8) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

What I would welcome is any help to make it work so it will compile.

Thank you Chazza

(4) By Larry Brasfield (larrybr) on 2021-02-23 19:46:31 in reply to 1 [link] [source]

I am unable to insert a record where one of the fields is a text field.

...

sqlite3_bind_text(res, 1, alarmtimex); // alarmtimex is a global string variable

I see two problems here. The low level problem is that sqlite3_bind_text() requires at least 4 parameters, 4th of which is an integer indicating the content length in bytes, or a negative value to indicate a content zero-terminator should be used. You need to consult the bind_() docs.

The high level problem is that your code should not have compiled if you used #include "sqlite3.h" to declare the SQLite library API objects. I infer that you must be ignoring a flurry of messages stating that functions are being used without being declared and that certain assumptions are made about them. One, particularly wrong in the usual case, is that they accept any number of arguments of any type.

I strongly advise you to learn why *.h files are ubiquitously published and used. To forgo the advantage of using them is much like taking a stroll along a suspension bridge's main catenary cables, when they're icy.

(5) By anonymous on 2021-02-23 20:17:35 in reply to 4 [link] [source]

Hi My problem is that it will not compile. Maybe I should have been clearer in that. I am using sqlite3.h in my include at the beginning of my program.

What I am really struggling with is what should the line

rc = sqlite3_prepare_v2(db1, sql, strlen(sql), &res, &tail);

and the line

sqlite3_bind_text(res, 1, alarmtimex);

be in order for it to work. I know I have to get the right combination of the two for it to work and have spent a long time reading up on bind statements but am not getting anywhere.

Regards Chazza

(6) By Larry Brasfield (larrybr) on 2021-02-23 20:29:33 in reply to 5 [link] [source]

Go to the doc page that Stephan and I both linked. Then read about the sqlite3_bind_text() function and the arguments is must have and what information they convey. Then revise your call to conform to what you have learned. Do not guess. Do not keep trying things you hope might work without good reason to believe they will work.

I also suggest that you take a close look at your compiler's complaints. Typically, they will show what how the call was specified alongside what the called function has declared as its expected parameters and their types. Even if it is hard to sift through that error message text, doing so is an essential skill for C programmers.

I would just write out a call for you if I imagined that would solve your problem. But I refrain because your problem is deeper than getting this one call to compile. You must learn to read the API docs and apply them to your coding. The alternatives are too dreary to contemplate.

(7.1) By Keith Medcalf (kmedcalf) on 2021-02-23 20:32:41 edited from 7.0 in reply to 5 [link] [source]

Deleted

(8) By anonymous on 2021-02-23 20:36:02 in reply to 4 [link] [source]

Hi My problem is that it will not compile. Maybe I should have been clearer in that. I am using sqlite3.h in my include at the beginning of my program.

What I am really struggling with is what should the line

rc = sqlite3_prepare_v2(db1, sql, strlen(sql), &res, &tail);

and the line

sqlite3_bind_text(res, 1, alarmtimex);

be in order for it to work. I know I have to get the right combination of the two for it to work and have spent a long time reading up on bind statements but am not getting anywhere.

Regards Chazza

(9) By Larry Brasfield (larrybr) on 2021-02-23 20:43:59 in reply to 8 [link] [source]

Please do not duplicate posts. I urge you to delete your #8. Submitting the same thing repeatedly is not going to improve your odds of success; it will degrade them.

(10) By anonymous on 2021-02-23 21:43:11 in reply to 9 [link] [source]

Hi Larry Sorry about the duplicate post it was unintentional.

Good news is that I seem to have got it working.

The alarmtimex string is always 5 characters. The main change was to read the characters into a character array and use that in my bind statement.

I realise that if I was using a string that could be of variable length then I would have to write a more comprehensive solution to calculate the length of the array and adjust the array size and bytes required.

My modified code is below. Any comments would be welcome. Thank for your help Chazza

Code ------------------------------------

void insertalarmtime() {

sqlite3* db1;
int rc;
if (db_open("/spiffs/alarms", &db1))
	return;

char alarmtimechar[5];

for (int i = 0; i <= 4; i++) {
alarmtimechar[i] = (alarmtimex.charAt(i));
}

char* sql = "INSERT INTO alarmtimes (alarmtime, zone1, zone2, zone3, zone4, zone5, zone6, zone7, zone8) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

rc = sqlite3_prepare_v2(db1, sql, strlen(sql), &res, &tail);

if (rc != SQLITE_OK) {
	Serial.printf("ERROR preparing sql: %s\n", sqlite3_errmsg(db1));
	sqlite3_close(db1);
	return;
}

sqlite3_bind_text(res, 1, alarmtimechar, 5, SQLITE_TRANSIENT); // alarmtimex is a global string variable
sqlite3_bind_int(res, 2, zone1x); // zone1x to zone8x are global int variables
sqlite3_bind_int(res, 3, zone2x); 
sqlite3_bind_int(res, 4, zone3x); 
sqlite3_bind_int(res, 5, zone4x); 
sqlite3_bind_int(res, 6, zone5x); 
sqlite3_bind_int(res, 7, zone6x); 
sqlite3_bind_int(res, 8, zone7x); 
sqlite3_bind_int(res, 9, zone8x); 


if (sqlite3_step(res) != SQLITE_DONE) {
	Serial.printf("ERROR executing stmt: %s\n", sqlite3_errmsg(db1));
	sqlite3_close(db1);
	return;
}

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

//free the Statement 
sqlite3_finalize(res);
sqlite3_close(db1);

} //******************************************************

End Code -----------------------------------------------

(11) By Larry Brasfield (larrybr) on 2021-02-23 22:08:55 in reply to 10 [link] [source]

In general, the characters that are coming from languages/environments with a .charAt() on string objects do not deal with just simple, 8-bit C char values as if they were characters. They typically either: return potentially larger codes than will fit into a single C char array element; or are willing to deliver 8-bit values from a string representation in UTF-8, a coding where characters each occupy 1 or more (8-bit) bytes. Either way, your approach is leading to trouble, either now or later. You may get away with it now if your characters happen to be among the ones represented as single-byte characters, then later get goofy results when that circumstance vanishes.

You would do better to use whatever method exists on your incoming alarmtimex string to get its length in bytes (not characters), then pass that into the bind() call without the intermediate copy into a fixed-length buffer.

Of course you will need to study the docs for whatever type that alarmtimex object is to see what needs to be done. I would hope that, if the .charAt() method is returning something larger than a char, the compiler is warning you of a narrowing conversion. You should generally heed those. Sometimes, you may know that it's no problem. Sometimes it is a problem. It's on you to know which.

(12) By anonymous on 2021-02-23 22:13:35 in reply to 11 [source]

Thanks for the quick reply Larry. I will read up more on your suggestions and implement them. Kind Regards Chazza