SQLite Forum

remark about sqlite3_result_blob and zero-lenght blob
Login

remark about sqlite3_result_blob and zero-lenght blob

(1) By anonymous on 2020-12-22 21:51:54 [link] [source]

Using SQLite verion 3.33 I found this undocumented behavior:
  sqlite3_result_blob yields a NULL result if the char pointer and the specified length are both zero. I expected a zero-length BLOB. 
For instance: 
   sqlite3_result_blob(ctx,(char*)0,0,SQLITE_TRANSIENT);    // is null.
In order to return a zero-length BLOB, I had to pass a non zero char pointer:
   sqlite3_result_blob(ctx,"x",0,SQLITE_TRANSIENT);  // =cast('' as blob).
I assume that the behavior where NULL is returned may change in new versions and that it is better to use sqlite3_result_null if NULL is intended.
Thanks.

(2.1) By Keith Medcalf (kmedcalf) on 2020-12-22 22:41:22 edited from 2.0 in reply to 1 [source]

This is perfectly consistent with the sqlite3_column_blob/text*/bytes interfaces.

There is a difference between an "empty" blob and a NULL blob. The former is a valid blob that does not contain anything, and the latter is a blob that does not exist.

Though I suppose I could agree that it could be documented that if these interfaces are passed a NUL data pointer that the result value is NULL.