SQLite Forum

Correct way to test the return value of sqlite_column_... before attemting tt use it?
Login

Correct way to test the return value of sqlite_column_... before attempting to use it?

(1.2) By Gary (1codedebugger) on 2021-01-03 06:02:58 edited from 1.1 [source]

When retrieving column values using sqlite3_column_..., where the documentation reads that it returns undefined, would you please tell me what that means in regards to error checking?

Does it mean it can return a pointer other than NULL, that might point to a valid value from a different area of memory?

I tried some bad column indexes and NULL was returned.

Excluding programmer error, such as attempting to get column values after resetting a statement, what's the correct way to test the result of sqlite_column_ before using it?

Thank you.

(2) By Stephan Beal (stephan) on 2021-01-03 06:04:23 in reply to 1.0 [link] [source]

... would you please tell me what that means in regards to error checking?

Per the docs:

https://sqlite.org/c3ref/column_blob.html


As long as the input parameters are correct, these routines will only fail if an out-of-memory error occurs during a format conversion. Only the following subset of interfaces are subject to out-of-memory errors:

    sqlite3_column_blob()
    sqlite3_column_text()
    sqlite3_column_text16()
    sqlite3_column_bytes()
    sqlite3_column_bytes16() 

If an out-of-memory error occurs, then the return value from these routines is the same as if the column had contained an SQL NULL value. Valid SQL NULL returns can be distinguished from out-of-memory errors by invoking the sqlite3_errcode() immediately after the suspect return value is obtained and before any other SQLite interface is called on the same database connection. 

(4.2) By Gary (1codedebugger) on 2021-01-04 02:42:39 edited from 4.1 in reply to 2 [link] [source]

Thanks, I didn't realize. I thought that section had to do with type conversions only.

If one is not converting types, the column indexes are not variable, and the data does not include NULL values, then no error checking is required, as far as testing that the result was successfully retrieved?

I was checking that pointers were not NULL, but can't do that for sqlite3_column_int, of course; but if get a zero can check the errcode. For example, a bad column index in a sqlite3_column_int returns 0 but the errcode is 25. But that is programmer error and not what I was asking about.

(6) By Stephan Beal (stephan) on 2021-01-03 07:52:58 in reply to 4.1 [link] [source]

If one is not converting types, the column indexes are not variable, and the data does not include NULL values, then no error checking is required, as far as testing that the result was successfully retrieved?

If the preceding step() succeeded, those values are already in memory owned by the library (until the next step()/reset()/finalize() on the statement (and maybe some other APIs which are documented as invalidating that memory)). Barring range errors and numeric truncation, like Keith mentions, there are no error conditions (provided the API is used as documented).

I was checking that pointers were not NULL, but can't do that for sqlite3_column_int, of course; but if get a zero can check the errcode.

Zero's a valid value, but if the step succeeded, you'll only get zero if zero was the stored value or it was truncated/overflowed/underflowed to zero due to data type size limits. e.g. calling sqlite3_column_int() on a column with a floating point value of 0.1. At the point where you fetch a numeric column's value, there's no allocation going on, so nothing which can fail except for range-related limit violations (which will simply result in silent truncation/overflow/whatever, not an error result from the library).

(7) By Keith Medcalf (kmedcalf) on 2021-01-03 09:12:16 in reply to 4.1 [link] [source]

Well, if you care what the type is, you should always call sqlite3_column_type first to find out the underlying type.

If you use any API to retrieve the value for the column that is not the one appropriate for that type, then an implicit conversion will occur. For example, if you use any of sqlite3_column_int/sqlite3_column_double on a column that is of type SQLITE_NULL the returned value will be 0 and sqlite3_column_bytes/sqlite3_column_bytes16 will return 0, and sqlite3_column_text* and sqlite3_column_blob will return null pointers.

Error results are only possible from conversions which use or involve any text format conversion as the destination type. You actually have no way of knowing if sqlite3_column_bytes/sqlite3_column_text or sqlite3_column_bytes16/sqlite3_column_text16 will perform a conversion operation because you do not know the underlying database encoding (unless you check), so you should always check for a null return pointer (as you should when using sqlite3_column_blob).

Note the same thing applies to all the other functions which "retrieve" values across the interface between your application and the library (the sqlite3_value* functions, for example).

The same applies to "sending" values to the library (the sqlite3_bind* or sqlite3_result* family), except that some errors (OOM errors for example) may only show up in the sqlite3_step that uses the values as that is where the conversion is performed and fails, not necessarily when you send the value.

(3.2) By Keith Medcalf (kmedcalf) on 2021-01-03 09:32:21 edited from 3.1 in reply to 1.2 [link] [source]

First you call sqlite3_column_type. That will tell you the storage type of the value. If it returns SQLITE_NULL then the data is NULL and you are done.

If the type is SQLITE_INTEGER or SQLITE_REAL then you call sqlite3_column_double, sqlite3_column_int or sqlite3_column_int64 to fetch the value depending on what type it was and what type you want. If the value happens to be larger than will fit in a 32-bit integer and you use the sqlite3_column_int function, then only the lower 32-bits of the value will be returned to you. Similarly if the value is an integer that is bigger than will fit in the 53 bit significand when converted to double because you used sqlite3_column_double to retrieve the value then low-order bits of precision will be lost. Similarly, if the actual type is SQLITE_REAL and you request it be converted to an integer, then it will be converted to a 64-bit integer and any fractional part will be lost, and then if you used sqlite3_column_int only the lower 32-bits of the integer will be returned to you.

If the type is SQLITE_TEXT or SQLITE_BLOB then you call sqlite3_column_bytes or sqlite3_column_bytes16 to find out how many bytes there are exclusive of the null terminator, and to perform conversion to either UTF-8 or UTF-16 format. You then use sqlite3_column_blob, sqlite3_column_text, or sqlite3_column_text16 to retrieve the blob or the null terminated string in UTF-8 or UTF-16 as you requested. If the return from the sqlite3_column_text* is a NULL pointer, then an error occurred. If the return from sqlite3_column_blob is a NULL pointer, then either an error occurred or the blob is empty (and you do not know which).

If you use a function call sequence which is not representative of the underlying type as returned by sqlite3_column_type, then the appropriate conversion will be carried out for you to attempt to get you what you asked for.

So for example if you call sqlite3_column_type and it returns SQLITE_TEXT and you then call sqlite3_column_text you will get a pointer to the data converted to a UTF-8 text string (or NULL if the conversion fails with an error). If you then call sqlite3_column_bytes16, which requires the data to be converted to UTF-16 so that length can be returned, will invalidate the previously obtained pointer to the data in UTF-8 format.

"undefined" is a shortcode for "there is a perfectly good reason for getting whatever result you get but it is far to complicated to explain to anyone who needs to ask the question" and is meant that you should assume that the value is the one that would trigger the worst possible outcome that you can imagine -- sort of like ancient maps that said "beyond here be dragons".

(5) By Gary (1codedebugger) on 2021-01-03 07:48:18 in reply to 3.1 [link] [source]

Thank you for the explanation.

(8) By Keith Medcalf (kmedcalf) on 2021-01-03 09:29:53 in reply to 5 [link] [source]

Note that NULL and EMPTY string/blob are different things, even though they may "look" the same.

SQLite version 3.35.0 2021-01-03 00:48:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> insert into x values (null),(1),(1.1),('text'),(X'626C6F62'),(''),(X'');
sqlite> select typeof(x), quote(x), x from x;
┌───────────┬─────────────┬──────┐
│ typeof(x) │  quote(x)   │  x   │
├───────────┼─────────────┼──────┤
│ null      │ NULL        │      │
│ integer   │ 1           │ 1    │
│ real      │ 1.1         │ 1.1  │
│ text      │ 'text'      │ text │
│ blob      │ X'626C6F62' │ blob │
│ text      │ ''          │      │
│ blob      │ X''         │      │
└───────────┴─────────────┴──────┘

(9) By Gary (1codedebugger) on 2021-01-04 02:40:54 in reply to 8 [link] [source]

Thank you for the clarification of the distinction.

(10.1) By Keith Medcalf (kmedcalf) on 2021-01-04 04:13:33 edited from 10.0 in reply to 9 [link] [source]

No problem.

Note that for a NULL you will get sqlite3_column_type == SQLITE_NULL.

For an "empty string" you will get sqlite3_column_type == SQLITE_TEXT, sqlite3_column_bytes/sqlite3_column_bytes16 will return 0, and sqlite3_column_text/sqlite3_column_text16 will return a valid pointer which points to a NULL character (0). A NULL pointer return indicates an error.

For an "empty blob" you will get sqlite3_column_type == SQLITE_BLOB, sqlite3_column_bytes == 0 and sqlite3_column_blob will return a NULL pointer. In this specific instance you have no way of distinguishing between an "empty blob" and an error. Although if you are not "converting" types you cannot get an error, so a NULL pointer has to mean an empty blob.