Some troubles with ODBC (Win) virtual table
(1) By little-brother on 2020-09-30 11:56:09 [link] [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
thencolumn3
in the resultset contains onlyNULL
.
I try to outputcoalesce(column3, 'ABC')
and gotNULL
again. Socolumn3
is a notNULL
when it was fetched.
Feel like I missed something. What is can be wrong? - Why is
argv
-values ofxCreate
quotted (e.g.'ABC'
instead ofABC
)?
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 [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.