Some troubles with ODBC (Win) virtual table

(1) By little-brother on 2020-09-30 11:56:09 [source]

I want to get data througth ODBC drivers. I wrote the code (see below) based on templatevtab.c and encountered with two problems.

Query as select * from mytable works fine.
But when I use some where-condition e.g. column3 = 4 then column3 in the resultset contains only NULL.
I try to output coalesce(column3, 'ABC') and got NULL again. So column3 is a not NULL when it was fetched.
Feel like I missed something. What is can be wrong?
Why is argv-values of xCreate quotted (e.g. 'ABC' instead of ABC)?
I remove quotes before passing them next. But it's a little bit strange for me.

Build
gcc -I ../include -g -shared odbc.c -o odbc.dll -s "C:\Program Files (x86)\CodeBlocks\MinGW\lib\libodbc32.a"

Usage
CREATE VIRTUAL TABLE temp.mytable USING odbc('Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=D:/csv/;Extensions=asc,csv,tab,txt','select * from data.csv')
select * from temp.mytable -- where column3 = 4

// main.c
#define UNICODE
#define _UNICODE

#define MAX_DATA_LENGTH 32000

#include <windows.h>
#include <stdio.h>
#include <tchar.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>

#if !defined(SQLITEINT_H)
#include "sqlite3ext.h"
#endif
SQLITE_EXTENSION_INIT1
#include <windows.h>
#include <tchar.h>
#include <string.h>
#include <assert.h>

typedef struct odbc_vtab odbc_vtab;
struct odbc_vtab {
	sqlite3_vtab base;
	SQLHANDLE hEnv;
	SQLHANDLE hConn;
	TCHAR* query;
};

typedef struct odbc_cursor odbc_cursor;
struct odbc_cursor {
	sqlite3_vtab_cursor base;

	sqlite3_int64 rowId;
	SQLSMALLINT colCount;
	SQLHANDLE hStmt;
	BOOL isEof;
};

char* utf16to8(const TCHAR* in) {
	char* out;
	if (!in || _tcslen(in) == 0) {
		out = (char*)calloc (1, sizeof(char));
	} else  {
		int len = WideCharToMultiByte(CP_UTF8, 0, in, -1, NULL, 0, 0, 0);
		out = (char*)calloc (len, sizeof(char));
		WideCharToMultiByte(CP_UTF8, 0, in, -1, out, len, 0, 0);
	}
	return out;
}

TCHAR* utf8to16(const char* in) {
	TCHAR *out;
	if (!in || strlen(in) == 0) {
		out = (TCHAR*)calloc (1, sizeof (TCHAR));
	} else  {
		DWORD size = MultiByteToWideChar(CP_UTF8, 0, in, -1, NULL, 0);
		out = (TCHAR*)calloc (size, sizeof (TCHAR));
		MultiByteToWideChar(CP_UTF8, 0, in, -1, out, size);
	}
	return out;
}

static int odbcCreate(sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr) {
	if (argc != 5) {
		*pzErr = sqlite3_mprintf("odbc-module requires two argument: DSN and query to source");
		return SQLITE_ERROR;
	}

	SQLHANDLE hEnv;
	SQLHANDLE hConn;

	int rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
		*pzErr = sqlite3_mprintf("Can't get access to ODBC");
		return SQLITE_ERROR;
	}

	rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
		return SQLITE_ERROR;

	rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hConn);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
		*pzErr = sqlite3_mprintf("Can't establish a connection.");
		return SQLITE_ERROR;
	}

	TCHAR quotes[] = TEXT("\"'`[");
	TCHAR* dsn = utf8to16(argv[3]);
	if (!_tcschr(quotes, dsn)) {
		dsn[0] = TEXT(' ');
		dsn[_tcslen(dsn) - 1] = TEXT(' ');
	}
	rc = SQLDriverConnect(hConn, NULL, dsn, _tcslen(dsn), SQL_NTS, 0, NULL, SQL_DRIVER_NOPROMPT);
	free(dsn);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
		*pzErr = sqlite3_mprintf("Can't connect. Check DNS-string.");
		return SQLITE_ERROR;
	}

	TCHAR* query = utf8to16(argv[4]);
	if (!_tcschr(quotes, query)) {
		query[0] = TEXT(' ');
		query[_tcslen(query) - 1] = TEXT(' ');
	}

	SQLHANDLE hStmt = 0;
	rc = SQLAllocHandle(SQL_HANDLE_STMT, hConn, &hStmt);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
		*pzErr = sqlite3_mprintf("Syntax error. Check the query text.");
		return SQLITE_ERROR;
	}
	SQLExecDirect(hStmt, query, SQL_NTS);

	TCHAR createQuery16[2000] = TEXT("create table x(\"");
	SQLSMALLINT colCount = 0;
	SQLNumResultCols(hStmt, &colCount);

	for (int colNo = 1; colNo <= colCount; colNo++) {
		TCHAR colName[512] = {0};
		SQLSMALLINT colType = 0;
		SQLDescribeCol(hStmt, colNo, colName, 511, 0, &colType, 0, 0, 0);
		_tcscat(createQuery16, colName);

		if (colType == SQL_DECIMAL || colType == SQL_NUMERIC || colType == SQL_REAL || colType == SQL_FLOAT || colType == SQL_DOUBLE)
			_tcscat(createQuery16, TEXT("\" real"));
		else if (colType == SQL_SMALLINT || colType == SQL_INTEGER || colType == SQL_BIT || colType == SQL_TINYINT || colType == SQL_BIGINT)
			_tcscat(createQuery16, TEXT("\" integer"));
		else if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_LONGVARCHAR || colType == SQL_WCHAR || colType == SQL_WVARCHAR || colType == SQL_WLONGVARCHAR)
			_tcscat(createQuery16, TEXT("\" text"));
		else if (colType == SQL_BINARY || colType == SQL_VARBINARY)
			_tcscat(createQuery16, TEXT("\" blob"));
		else
			_tcscat(createQuery16, TEXT("\""));

		if (colNo != colCount)
			_tcscat(createQuery16, TEXT(",\""));
	}
	_tcscat(createQuery16, TEXT(")"));
	char* createQuery8 = utf16to8(createQuery16);
	SQLFreeHandle(SQL_HANDLE_STMT, hStmt);

	odbc_vtab *pTab;
	rc = sqlite3_declare_vtab(db, createQuery8);
	free(createQuery8);
	if (rc == SQLITE_OK) {
		pTab = sqlite3_malloc(sizeof(*pTab));
		*ppVtab = (sqlite3_vtab*)pTab;
		if (pTab == 0)
			rc = SQLITE_NOMEM;
	}
	if (rc == SQLITE_OK) {
		memset(pTab, 0, sizeof(*pTab));

		pTab->query = query;
		pTab->hEnv = hEnv;
		pTab->hConn = hConn;
	} else {
		SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
		SQLFreeHandle(SQL_HANDLE_DBC, hConn);
		free(query);
	}

	return rc;
}

static int odbcDestroy(sqlite3_vtab *pVtab){
	odbc_vtab *pTab = (odbc_vtab*)pVtab;
	SQLFreeHandle(SQL_HANDLE_ENV, pTab->hEnv);
	SQLDisconnect(pTab->hConn);
	SQLFreeHandle(SQL_HANDLE_DBC, pTab->hConn);
	free(pTab->query);
	sqlite3_free(pTab);

	return SQLITE_OK;
}

static int odbcOpen(sqlite3_vtab *pVtab, sqlite3_vtab_cursor **ppCursor){
	odbc_vtab *pTab = (odbc_vtab*)pVtab;
	odbc_cursor *pCur;

	pCur = sqlite3_malloc (sizeof(*pCur));
	if (pCur == 0)
		return SQLITE_NOMEM;
	memset(pCur, 0, sizeof(*pCur));
	*ppCursor = &pCur->base;

	int rc = SQLAllocHandle(SQL_HANDLE_STMT, pTab->hConn, &(pCur->hStmt));
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
		return SQLITE_ERROR;


	rc = SQLExecDirect(pCur->hStmt, (SQLWCHAR*)pTab->query, SQL_NTS);
	if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
		return SQLITE_ERROR;

	SQLNumResultCols(pCur->hStmt, &(pCur->colCount));
	pCur->isEof = 0;
	return SQLITE_OK;
}

static int odbcClose(sqlite3_vtab_cursor *cur){
	odbc_cursor *pCur = (odbc_cursor*)cur;
	SQLFreeHandle(SQL_HANDLE_STMT, pCur->hStmt);
	sqlite3_free(pCur);
	return SQLITE_OK;
}

static int odbcNext(sqlite3_vtab_cursor *cur){
	odbc_cursor *pCur = (odbc_cursor*)cur;
	pCur->isEof = pCur->isEof || SQLFetch(pCur->hStmt) != SQL_SUCCESS;
	pCur->rowId++;
	return SQLITE_OK;
}

static int odbcColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int colNo){
	odbc_cursor *pCur = (odbc_cursor*)cur;

	// ToDo: https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data?view=sql-server-ver15
	SQLWCHAR val16[MAX_DATA_LENGTH + 1] = {0};
	SQLLEN res = 0;
	int rc = SQLGetData(pCur->hStmt, colNo + 1, SQL_WCHAR, val16, MAX_DATA_LENGTH * sizeof(TCHAR), &res);

	char* val8 = utf16to8(val16);
	sqlite3_result_text(ctx, val8, strlen(val8), SQLITE_TRANSIENT);
	free(val8);

	return SQLITE_OK;
}

static int odbcRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid){
	odbc_cursor *pCur = (odbc_cursor*)cur;
	*pRowid = pCur->rowId;
	return SQLITE_OK;
}

static int odbcEof(sqlite3_vtab_cursor *cur) {
	odbc_cursor *pCur = (odbc_cursor*)cur;
	return pCur->isEof;
}

static int odbcFilter(sqlite3_vtab_cursor *cur, int idxNum, const char *idxStr, int argc, sqlite3_value **argv) {
	odbc_cursor *pCur = (odbc_cursor *)cur;
	odbcNext(cur);
	pCur->rowId = 1;
	return SQLITE_OK;
}

static int odbcBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
	pIdxInfo->estimatedCost = (double)10;
	pIdxInfo->estimatedRows = 10;
	return SQLITE_OK;
}

static sqlite3_module odbcModule = {
  /* iVersion    */ 0,
  /* xCreate     */ odbcCreate,
  /* xConnect    */ odbcCreate,
  /* xBestIndex  */ odbcBestIndex,
  /* xDisconnect */ odbcDestroy,
  /* xDestroy    */ odbcDestroy,
  /* xOpen       */ odbcOpen,
  /* xClose      */ odbcClose,
  /* xFilter     */ odbcFilter,
  /* xNext       */ odbcNext,
  /* xEof        */ odbcEof,
  /* xColumn     */ odbcColumn,
  /* xRowid      */ odbcRowid,
  /* xUpdate     */ 0,
  /* xBegin      */ 0,
  /* xSync       */ 0,
  /* xCommit     */ 0,
  /* xRollback   */ 0,
  /* xFindMethod */ 0,
  /* xRename     */ 0,
  /* xSavepoint  */ 0,
  /* xRelease    */ 0,
  /* xRollbackTo */ 0,
  /* xShadowName */ 0
};

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_odbc_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
	int rc = SQLITE_OK;
	SQLITE_EXTENSION_INIT2(pApi);
	rc = sqlite3_create_module(db, "odbc", &odbcModule, 0);
	return rc;
}

(2) By little-brother on 2020-10-11 14:34:09 in reply to 1 [link] [source]

I think I finally figured out what was wrong. Maybe it can be usefull for someone.

ODBC statement has another behaviour to control a cursor than SQLite. 
So it requires to do some addition calls:

1. In odbcOpen set a cursor type to SQL_CURSOR_KEYSET_DRIVEN. 
That allows to move up-down the cursor by the result set
---
int rc = SQLAllocHandle(SQL_HANDLE_STMT, pTab->hConn, &(pCur->hStmt));
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
	return SQLITE_ERROR;

SQLSetStmtAttr(pCur->hStmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_KEYSET_DRIVEN, 0); // <--
rc = SQLExecDirect(pCur->hStmt, (SQLWCHAR*)pTab->query, SQL_NTS);
---
2. In odbcColumn add a command to reset a column position. 
SQLite requests column's data several times in random order. 
Without reset position a request for first column after second 
returns SQL_NO_DATA (100) and empty value.  
---
SQLLEN res = 0;
SQLSetPos(pCur->hStmt, 1, SQL_POSITION, SQL_LOCK_NO_CHANGE); // <--
int rc = SQLGetData(pCur->hStmt, colNo + 1, SQL_WCHAR, val16, MAX_DATA_LENGTH * sizeof(TCHAR), &res);
---
3. odbcFilter requires to reset the up-down cursor position. 
So hStmt should be closed and opened again i.e. 
SQLExecDirect should be executed in odbcFilter, not in odbcOpen.