I have a number of table-valued functions that require one or more parameters, and I'd like to give a helpful error message if a required parameter is not specified. I've tried setting sqlite3_vtab*'s zErrMsg, but it's ignored when returning SQLITE_CONSTRAINT, which is what the documentation says should be returned for this case. (If I return anything else, it makes the query planner unhappy with at least some queries involving this function.)
Specifically, the docs say:
The SQLITE_CONSTRAINT return is useful for table-valued functions that have required parameters. If the aConstraint[].usable field is false for one of the required parameter, then the xBestIndex method should return SQLITE_CONSTRAINT.
(from [section 2.3.3](https://sqlite.org/vtab.html))
Below is sample source that reproduces the problem. It shows a nice error message when the parameter passed is wrong, but only a generic "no query solution" when the required parameter is missing. (Near the end, change "SELECT count(*) FROM testvtab(NULL);" to "SELECT count(*) FROM testvtab();" to see the case where a custom error message cannot be provided back to the application.)
Could SQLite add support for setting the error message when all solutions fail with SQLITE_CONSTRAINT? I recognize there are multiple possible invocations to xBestIndex in this case, and it would have to choose one of them (say, the first or the last that failed with SQLITE_CONSTRAINT), but which one wouldn't matter for my purposes.
Thanks,
David
```c
#include <assert.h>
#include <stdbool.h>
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"
enum test_vtab_column
{
test_vtab_column_value = 0,
test_vtab_column_source
};
static int testVTabConnect(sqlite3* connection, void* pAux, int argc, const char* const* argv, sqlite3_vtab** ppVtab,
char** pzErr)
{
(void)pAux;
(void)argc;
(void)argv;
sqlite3_vtab* table = (sqlite3_vtab*)sqlite3_malloc(sizeof(*table));
if (table == NULL)
{
return SQLITE_NOMEM;
}
memset(table, 0, sizeof(*table));
int rc = sqlite3_declare_vtab(connection, "CREATE TABLE x("
"Value INTEGER PRIMARY KEY, "
"Source HIDDEN INTEGER NOT NULL);");
if (rc != SQLITE_OK)
{
sqlite3_free(table);
const char* connectionError = sqlite3_errmsg(connection);
size_t errorLength = strlen(connectionError);
char* errorMessageCopy = (char*)sqlite3_malloc64((sqlite3_uint64)errorLength + 1);
if (errorMessageCopy == NULL)
{
return SQLITE_NOMEM;
}
strncpy_s(errorMessageCopy, errorLength + 1, connectionError, errorLength);
*pzErr = errorMessageCopy;
return rc;
}
*ppVtab = table;
return SQLITE_OK;
}
static int testVTabDisconnect(sqlite3_vtab* pVtab)
{
sqlite3_free(pVtab);
return SQLITE_OK;
}
static bool try_set_error_message(sqlite3_vtab* table, const char* message)
{
size_t length = strlen(message);
char* copy = (char*)sqlite3_malloc64((sqlite3_uint64)length + 1);
if (copy == NULL)
{
return false;
}
strncpy_s(copy, length + 1, message, length);
sqlite3_free(table->zErrMsg);
table->zErrMsg = copy;
return true;
}
static int testVTabBestIndex(sqlite3_vtab* tab, sqlite3_index_info* pIdxInfo)
{
int sourceDataConstraintIndex = -1;
for (int index = 0; index < pIdxInfo->nConstraint; ++index)
{
struct sqlite3_index_constraint* constraint = &pIdxInfo->aConstraint[index];
if (!constraint->usable)
{
continue;
}
if (constraint->iColumn == test_vtab_column_source && constraint->op == SQLITE_INDEX_CONSTRAINT_EQ)
{
sourceDataConstraintIndex = index;
}
}
if (sourceDataConstraintIndex == -1)
{
if (!try_set_error_message(tab, "testvtab requires a Source value"))
{
return SQLITE_NOMEM;
}
return SQLITE_CONSTRAINT;
}
if (pIdxInfo->nOrderBy == 1 && pIdxInfo->aOrderBy[0].iColumn == test_vtab_column_value &&
pIdxInfo->aOrderBy[0].desc == 0)
{
pIdxInfo->orderByConsumed = 1;
}
pIdxInfo->idxNum = 0;
pIdxInfo->idxStr = NULL;
pIdxInfo->aConstraintUsage[sourceDataConstraintIndex].argvIndex = 1;
pIdxInfo->aConstraintUsage[sourceDataConstraintIndex].omit = 1;
pIdxInfo->estimatedCost = 1;
return SQLITE_OK;
}
struct test_vtab_cursor
{
sqlite3_vtab_cursor base; // This base struct must come first.
sqlite_int64 rowid;
sqlite_int64 source;
};
typedef struct test_vtab_cursor test_vtab_cursor;
static int testVTabOpen(sqlite3_vtab* p, sqlite3_vtab_cursor** ppCursor)
{
(void)p;
test_vtab_cursor* cursor = (test_vtab_cursor*)sqlite3_malloc(sizeof(*cursor));
if (cursor == NULL)
{
return SQLITE_NOMEM;
}
memset(cursor, 0, sizeof(*cursor));
*ppCursor = &cursor->base;
return SQLITE_OK;
}
static int testVTabClose(sqlite3_vtab_cursor* cur)
{
test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
sqlite3_free(cursor);
return SQLITE_OK;
}
static int testVTabFilter(sqlite3_vtab_cursor* pVtabCursor, int idxNum, const char* idxStr, int argc,
sqlite3_value** argv)
{
assert(idxNum == 0);
(void)idxNum;
assert(idxStr == NULL);
(void)idxStr;
assert(argc == 1);
(void)argc;
test_vtab_cursor* cursor = (test_vtab_cursor*)pVtabCursor;
sqlite3_vtab* table = cursor->base.pVtab;
sqlite3_value* arg = argv[0];
if (sqlite3_value_type(arg) != SQLITE_INTEGER)
{
return try_set_error_message(table, "testvtab requires a Source value that is an INTEGER") ?
SQLITE_ERROR : SQLITE_NOMEM;
}
cursor->rowid = 0;
cursor->source = sqlite3_value_int64(arg);
return SQLITE_OK;
}
static int testVTabNext(sqlite3_vtab_cursor* cur)
{
test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
++(cursor->rowid);
return SQLITE_OK;
}
static int testVTabEof(sqlite3_vtab_cursor* cur)
{
test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
return cursor->rowid < cursor->source ? 0 : 1;
}
static int testVTabColumn(sqlite3_vtab_cursor* cur, sqlite3_context* ctx, int i)
{
test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
assert(i <= test_vtab_column_source);
switch (i)
{
case test_vtab_column_value:
sqlite3_result_int64(ctx, cursor->rowid);
return SQLITE_OK;
default:
assert(i == test_vtab_column_source);
sqlite3_result_int64(ctx, cursor->source);
return SQLITE_OK;
}
}
static int testVTabRowid(sqlite3_vtab_cursor* cur, sqlite_int64* pRowid)
{
test_vtab_cursor* cursor = (test_vtab_cursor*)cur;
*pRowid = cursor->rowid;
return SQLITE_OK;
}
static sqlite3_module testVTabModule = {
3, // iVersion
NULL, // xCreate
testVTabConnect, // xConnect
testVTabBestIndex, // xBestIndex
testVTabDisconnect, //xDisconnect
NULL, // xDestroy
testVTabOpen, //int xOpen
testVTabClose, //int xClose
testVTabFilter, //int xFilter
testVTabNext, // int xNext
testVTabEof, // int xEof
testVTabColumn, // xColumn
testVTabRowid, // xRowid
NULL, // xUpdate
NULL, // xBegin
NULL, // xSync
NULL, // xCommit
NULL, // xRollback
NULL, // xFindFunction
NULL, // xRename
NULL, // xSavepoint
NULL, // xRelease
NULL, // xRollbackTo
NULL, // xShadowName
};
int main()
{
int rc = 0;
int rcCleanup = 0;
int rcCurrentCleanup = 0;
sqlite3* db = NULL;
sqlite3_stmt* stmt = NULL;
rc = sqlite3_open("D:\\test.db", &db);
if (rc)
{
goto cleanup;
}
rc = sqlite3_create_module_v2(db, "testvtab", &testVTabModule, NULL, NULL);
if (rc)
{
goto cleanup;
}
rc = sqlite3_prepare_v2(db, "SELECT count(*) FROM testvtab(NULL);", -1, &stmt, NULL);
if (rc)
{
goto cleanup;
}
rc = sqlite3_step(stmt);
if (rc != SQLITE_ROW)
{
goto cleanup;
}
printf("%i\n", sqlite3_column_int(stmt, 0));
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE)
{
goto cleanup;
}
rc = SQLITE_OK;
cleanup:
if (rc && db)
{
fprintf(stderr, "%s\n", sqlite3_errmsg(db));
}
if (stmt)
{
rcCurrentCleanup = sqlite3_finalize(stmt);
if (rcCurrentCleanup && !rcCleanup)
{
rcCleanup = rcCurrentCleanup;
}
}
if (db)
{
rcCurrentCleanup = sqlite3_close_v2(db);
if (rcCurrentCleanup && !rcCleanup)
{
rcCleanup = rcCurrentCleanup;
}
}
if (rcCleanup)
{
return rcCleanup;
}
return rc;
}
```