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