1.Dd December 19, 2018 2.Dt SQLITE3_COLUMN_BLOB 3 3.Os 4.Sh NAME 5.Nm sqlite3_column_blob , 6.Nm sqlite3_column_double , 7.Nm sqlite3_column_int , 8.Nm sqlite3_column_int64 , 9.Nm sqlite3_column_text , 10.Nm sqlite3_column_text16 , 11.Nm sqlite3_column_value , 12.Nm sqlite3_column_bytes , 13.Nm sqlite3_column_bytes16 , 14.Nm sqlite3_column_type 15.Nd Result Values From A Query 16.Sh SYNOPSIS 17.Ft const void * 18.Fo sqlite3_column_blob 19.Fa "sqlite3_stmt*" 20.Fa "int iCol" 21.Fc 22.Ft double 23.Fo sqlite3_column_double 24.Fa "sqlite3_stmt*" 25.Fa "int iCol" 26.Fc 27.Ft int 28.Fo sqlite3_column_int 29.Fa "sqlite3_stmt*" 30.Fa "int iCol" 31.Fc 32.Ft sqlite3_int64 33.Fo sqlite3_column_int64 34.Fa "sqlite3_stmt*" 35.Fa "int iCol" 36.Fc 37.Ft const unsigned char * 38.Fo sqlite3_column_text 39.Fa "sqlite3_stmt*" 40.Fa "int iCol" 41.Fc 42.Ft const void * 43.Fo sqlite3_column_text16 44.Fa "sqlite3_stmt*" 45.Fa "int iCol" 46.Fc 47.Ft sqlite3_value * 48.Fo sqlite3_column_value 49.Fa "sqlite3_stmt*" 50.Fa "int iCol" 51.Fc 52.Ft int 53.Fo sqlite3_column_bytes 54.Fa "sqlite3_stmt*" 55.Fa "int iCol" 56.Fc 57.Ft int 58.Fo sqlite3_column_bytes16 59.Fa "sqlite3_stmt*" 60.Fa "int iCol" 61.Fc 62.Ft int 63.Fo sqlite3_column_type 64.Fa "sqlite3_stmt*" 65.Fa "int iCol" 66.Fc 67.Sh DESCRIPTION 68\fBSummary:\fP 69.Bd -ragged 70<table border=0 cellpadding=0 cellspacing=0> <tr><td>\fBsqlite3_column_blob\fP<td>→<td>BLOB 71result <tr><td>\fBsqlite3_column_double\fP<td>→<td>REAL result <tr><td>\fBsqlite3_column_int\fP<td>→<td>32-bit 72INTEGER result <tr><td>\fBsqlite3_column_int64\fP<td>→<td>64-bit INTEGER 73result <tr><td>\fBsqlite3_column_text\fP<td>→<td>UTF-8 TEXT result <tr><td>\fBsqlite3_column_text16\fP<td>→<td>UTF-16 74TEXT result <tr><td>\fBsqlite3_column_value\fP<td>→<td>The result as 75an unprotected sqlite3_value object. 76<tr><td> <td> <td> <tr><td>\fBsqlite3_column_bytes\fP<td>→<td>Size 77of a BLOB or a UTF-8 TEXT result in bytes <tr><td>\fBsqlite3_column_bytes16 \fP 78<td>→ <td>Size of UTF-16 TEXT in bytes <tr><td>\fBsqlite3_column_type\fP<td>→<td>Default 79datatype of the result </table> 80.Ed 81.Pp 82\fBDetails:\fP 83.Pp 84These routines return information about a single column of the current 85result row of a query. 86In every case the first argument is a pointer to the prepared statement 87that is being evaluated (the sqlite3_stmt* that was returned 88from sqlite3_prepare_v2() or one of its variants) 89and the second argument is the index of the column for which information 90should be returned. 91The leftmost column of the result set has the index 0. 92The number of columns in the result can be determined using sqlite3_column_count(). 93.Pp 94If the SQL statement does not currently point to a valid row, or if 95the column index is out of range, the result is undefined. 96These routines may only be called when the most recent call to sqlite3_step() 97has returned SQLITE_ROW and neither sqlite3_reset() 98nor sqlite3_finalize() have been called subsequently. 99If any of these routines are called after sqlite3_reset() 100or sqlite3_finalize() or after sqlite3_step() 101has returned something other than SQLITE_ROW, the results 102are undefined. 103If sqlite3_step() or sqlite3_reset() or 104sqlite3_finalize() are called from a different thread 105while any of these routines are pending, then the results are undefined. 106.Pp 107The first six interfaces (_blob, _double, _int, _int64, _text, and 108_text16) each return the value of a result column in a specific data 109format. 110If the result column is not initially in the requested format (for 111example, if the query returns an integer but the sqlite3_column_text() 112interface is used to extract the value) then an automatic type conversion 113is performed. 114.Pp 115The sqlite3_column_type() routine returns the datatype code 116for the initial data type of the result column. 117The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, 118SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. 119The return value of sqlite3_column_type() can be used to decide which 120of the first six interface should be used to extract the column value. 121The value returned by sqlite3_column_type() is only meaningful if no 122automatic type conversions have occurred for the value in question. 123After a type conversion, the result of calling sqlite3_column_type() 124is undefined, though harmless. 125Future versions of SQLite may change the behavior of sqlite3_column_type() 126following a type conversion. 127.Pp 128If the result is a BLOB or a TEXT string, then the sqlite3_column_bytes() 129or sqlite3_column_bytes16() interfaces can be used to determine the 130size of that BLOB or string. 131.Pp 132If the result is a BLOB or UTF-8 string then the sqlite3_column_bytes() 133routine returns the number of bytes in that BLOB or string. 134If the result is a UTF-16 string, then sqlite3_column_bytes() converts 135the string to UTF-8 and then returns the number of bytes. 136If the result is a numeric value then sqlite3_column_bytes() uses sqlite3_snprintf() 137to convert that value to a UTF-8 string and returns the number of bytes 138in that string. 139If the result is NULL, then sqlite3_column_bytes() returns zero. 140.Pp 141If the result is a BLOB or UTF-16 string then the sqlite3_column_bytes16() 142routine returns the number of bytes in that BLOB or string. 143If the result is a UTF-8 string, then sqlite3_column_bytes16() converts 144the string to UTF-16 and then returns the number of bytes. 145If the result is a numeric value then sqlite3_column_bytes16() uses 146sqlite3_snprintf() to convert that value to a UTF-16 147string and returns the number of bytes in that string. 148If the result is NULL, then sqlite3_column_bytes16() returns zero. 149.Pp 150The values returned by sqlite3_column_bytes() 151and sqlite3_column_bytes16() do not include 152the zero terminators at the end of the string. 153For clarity: the values returned by sqlite3_column_bytes() 154and sqlite3_column_bytes16() are the number 155of bytes in the string, not the number of characters. 156.Pp 157Strings returned by sqlite3_column_text() and sqlite3_column_text16(), 158even empty strings, are always zero-terminated. 159The return value from sqlite3_column_blob() for a zero-length BLOB 160is a NULL pointer. 161.Pp 162\fBWarning:\fP The object returned by sqlite3_column_value() 163is an unprotected sqlite3_value object. 164In a multithreaded environment, an unprotected sqlite3_value object 165may only be used safely with sqlite3_bind_value() 166and sqlite3_result_value(). 167If the unprotected sqlite3_value object returned 168by sqlite3_column_value() is used in any other 169way, including calls to routines like sqlite3_value_int(), 170sqlite3_value_text(), or sqlite3_value_bytes(), 171the behavior is not threadsafe. 172Hence, the sqlite3_column_value() interface is normally only useful 173within the implementation of application-defined SQL functions 174or virtual tables, not within top-level application code. 175.Pp 176The these routines may attempt to convert the datatype of the result. 177For example, if the internal representation is FLOAT and a text result 178is requested, sqlite3_snprintf() is used internally 179to perform the conversion automatically. 180The following table details the conversions that are applied: 181.Bd -ragged 182<table border="1"> <tr><th> Internal<br>Type <th> Requested<br>Type 183<th> Conversion 184.Pp 185<tr><td> NULL <td> INTEGER <td> Result is 0 <tr><td> NULL 186<td> FLOAT <td> Result is 0.0 <tr><td> NULL <td> TEXT 187<td> Result is a NULL pointer <tr><td> NULL <td> BLOB <td> 188Result is a NULL pointer <tr><td> INTEGER <td> FLOAT <td> Convert 189from integer to float <tr><td> INTEGER <td> TEXT <td> ASCII rendering 190of the integer <tr><td> INTEGER <td> BLOB <td> Same as INTEGER->TEXT 191<tr><td> FLOAT <td> INTEGER <td> CAST to INTEGER <tr><td> 192FLOAT <td> TEXT <td> ASCII rendering of the float <tr><td> 193FLOAT <td> BLOB <td> CAST to BLOB <tr><td> TEXT <td> 194INTEGER <td> CAST to INTEGER <tr><td> TEXT <td> FLOAT 195<td> CAST to REAL <tr><td> TEXT <td> BLOB <td> No change 196<tr><td> BLOB <td> INTEGER <td> CAST to INTEGER <tr><td> 197BLOB <td> FLOAT <td> CAST to REAL <tr><td> BLOB <td> 198TEXT <td> Add a zero terminator if needed </table> 199.Ed 200.Pp 201Note that when type conversions occur, pointers returned by prior calls 202to sqlite3_column_blob(), sqlite3_column_text(), and/or sqlite3_column_text16() 203may be invalidated. 204Type conversions and pointer invalidations might occur in the following 205cases: 206.Bl -bullet 207.It 208The initial content is a BLOB and sqlite3_column_text() or sqlite3_column_text16() 209is called. 210A zero-terminator might need to be added to the string. 211.It 212The initial content is UTF-8 text and sqlite3_column_bytes16() or sqlite3_column_text16() 213is called. 214The content must be converted to UTF-16. 215.It 216The initial content is UTF-16 text and sqlite3_column_bytes() or sqlite3_column_text() 217is called. 218The content must be converted to UTF-8. 219.El 220.Pp 221Conversions between UTF-16be and UTF-16le are always done in place 222and do not invalidate a prior pointer, though of course the content 223of the buffer that the prior pointer references will have been modified. 224Other kinds of conversion are done in place when it is possible, but 225sometimes they are not possible and in those cases prior pointers are 226invalidated. 227.Pp 228The safest policy is to invoke these routines in one of the following 229ways: 230.Bl -bullet 231.It 232sqlite3_column_text() followed by sqlite3_column_bytes() 233.It 234sqlite3_column_blob() followed by sqlite3_column_bytes() 235.It 236sqlite3_column_text16() followed by sqlite3_column_bytes16() 237.El 238.Pp 239In other words, you should call sqlite3_column_text(), sqlite3_column_blob(), 240or sqlite3_column_text16() first to force the result into the desired 241format, then invoke sqlite3_column_bytes() or sqlite3_column_bytes16() 242to find the size of the result. 243Do not mix calls to sqlite3_column_text() or sqlite3_column_blob() 244with calls to sqlite3_column_bytes16(), and do not mix calls to sqlite3_column_text16() 245with calls to sqlite3_column_bytes(). 246.Pp 247The pointers returned are valid until a type conversion occurs as described 248above, or until sqlite3_step() or sqlite3_reset() 249or sqlite3_finalize() is called. 250The memory space used to hold strings and BLOBs is freed automatically. 251Do not pass the pointers returned from sqlite3_column_blob(), 252sqlite3_column_text(), etc. 253into sqlite3_free(). 254.Pp 255As long as the input parameters are correct, these routines will only 256fail if an out-of-memory error occurs during a format conversion. 257Only the following subset of interfaces are subject to out-of-memory 258errors: 259.Bl -bullet 260.It 261sqlite3_column_blob() 262.It 263sqlite3_column_text() 264.It 265sqlite3_column_text16() 266.It 267sqlite3_column_bytes() 268.It 269sqlite3_column_bytes16() 270.El 271.Pp 272If an out-of-memory error occurs, then the return value from these 273routines is the same as if the column had contained an SQL NULL value. 274Valid SQL NULL returns can be distinguished from out-of-memory errors 275by invoking the sqlite3_errcode() immediately after 276the suspect return value is obtained and before any other SQLite interface 277is called on the same database connection. 278.Sh SEE ALSO 279.Xr sqlite3_create_function 3 , 280.Xr sqlite3 3 , 281.Xr sqlite3_stmt 3 , 282.Xr sqlite3_bind_blob 3 , 283.Xr sqlite3_column_blob 3 , 284.Xr sqlite3_column_count 3 , 285.Xr sqlite3_column_blob 3 , 286.Xr sqlite3_errcode 3 , 287.Xr sqlite3_finalize 3 , 288.Xr sqlite3_malloc 3 , 289.Xr sqlite3_prepare 3 , 290.Xr sqlite3_reset 3 , 291.Xr sqlite3_result_blob 3 , 292.Xr sqlite3_mprintf 3 , 293.Xr sqlite3_step 3 , 294.Xr sqlite3_value 3 , 295.Xr sqlite3_value_blob 3 , 296.Xr SQLITE_INTEGER 3 , 297.Xr SQLITE_OK 3 , 298.Xr SQLITE_INTEGER 3 , 299.Xr sqlite3_value 3 300