Changeset: fa68f2422c70 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/fa68f2422c70
Modified Files:
sql/backends/monet5/vaults/odbc/odbc_loader.c
sql/test/proto_loader/odbc/Tests/sqlite3odbc.test
Branch: Mar2025
Log Message:
Refactored odbc_loader code for creating bats and appending BUNs.
It now works for most TYPE_xyz atom datatypes except blob.
Updated test results for SQLite.
diffs (truncated from 857 to 300 lines):
diff --git a/sql/backends/monet5/vaults/odbc/odbc_loader.c
b/sql/backends/monet5/vaults/odbc/odbc_loader.c
--- a/sql/backends/monet5/vaults/odbc/odbc_loader.c
+++ b/sql/backends/monet5/vaults/odbc/odbc_loader.c
@@ -13,7 +13,8 @@
#include "monetdb_config.h"
#include "rel_proto_loader.h"
#include "rel_exp.h"
-
+#include "gdk.h" // COLnew(), BUNappend()
+#include "gdk_time.h" // date_create(), daytime_create(), timestamp_create()
#include "mal_exception.h"
#include "mal_builder.h"
#include "mal_client.h"
@@ -163,6 +164,72 @@ map_rescol_type(SQLSMALLINT dataType, SQ
return sql_bind_subtype(sql->sa, typenm, interval_type, 0);
}
+/* return atom type for ODBC SQL datatype. */
+/* atom types are defined in gdk/gdh.h enum */
+static int
+map_rescol_mtype(SQLSMALLINT dataType, SQLULEN columnSize)
+{
+ switch (dataType) {
+ case SQL_CHAR:
+ case SQL_VARCHAR:
+ case SQL_LONGVARCHAR:
+ case SQL_WCHAR:
+ case SQL_WVARCHAR:
+ case SQL_WLONGVARCHAR:
+ return TYPE_str;
+ case SQL_BIT:
+ return TYPE_bit;
+ case SQL_TINYINT:
+ return TYPE_bte;
+ case SQL_SMALLINT:
+ return TYPE_sht;
+ case SQL_INTEGER:
+ return TYPE_int;
+ case SQL_BIGINT:
+ return TYPE_lng;
+ case SQL_REAL:
+ return TYPE_flt;
+ case SQL_FLOAT:
+ return (columnSize == 7) ? TYPE_flt : TYPE_dbl;
+ case SQL_DOUBLE:
+ return TYPE_dbl;
+ case SQL_BINARY:
+ case SQL_VARBINARY:
+ case SQL_LONGVARBINARY:
+ return TYPE_blob;
+ case SQL_TYPE_DATE:
+ return TYPE_date;
+ case SQL_TYPE_TIME:
+ return TYPE_daytime;
+ case SQL_DATETIME:
+ case SQL_TYPE_TIMESTAMP:
+ return TYPE_timestamp;
+ case SQL_GUID:
+ return TYPE_uuid;
+ case SQL_INTERVAL_MONTH:
+ case SQL_INTERVAL_YEAR:
+ case SQL_INTERVAL_YEAR_TO_MONTH:
+ return TYPE_int;
+ case SQL_INTERVAL_DAY:
+ case SQL_INTERVAL_HOUR:
+ case SQL_INTERVAL_MINUTE:
+ case SQL_INTERVAL_SECOND:
+ case SQL_INTERVAL_DAY_TO_HOUR:
+ case SQL_INTERVAL_DAY_TO_MINUTE:
+ case SQL_INTERVAL_DAY_TO_SECOND:
+ case SQL_INTERVAL_HOUR_TO_MINUTE:
+ case SQL_INTERVAL_HOUR_TO_SECOND:
+ case SQL_INTERVAL_MINUTE_TO_SECOND:
+ return TYPE_lng;
+
+ case SQL_DECIMAL:
+ case SQL_NUMERIC:
+ // we will fetch decimals as string data
+ default:
+ return TYPE_str;
+ }
+}
+
/* return name for ODBC SQL datatype */
static char *
nameofSQLtype(SQLSMALLINT dataType)
@@ -211,7 +278,7 @@ nameofSQLtype(SQLSMALLINT dataType)
}
}
-/* utility function to nicely close all opened ODBC resources */
+/* utility function to safely close all opened ODBC resources */
static void
odbc_cleanup(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt) {
SQLRETURN ret = SQL_SUCCESS;
@@ -231,23 +298,45 @@ odbc_cleanup(SQLHANDLE env, SQLHANDLE db
}
}
-typedef struct odbc_loader_t {
- SQLHANDLE env;
- SQLHANDLE dbc;
- SQLHANDLE stmt;
- SQLSMALLINT nr_cols;
-} odbc_loader_t;
+/* copied from monetdb5/modules/mal/tablet.c */
+static BAT *
+bat_create(int adt, BUN nr)
+{
+ BAT *b = COLnew(0, adt, nr, TRANSIENT);
+ /* check for correct structures */
+ if (b == NULL)
+ return NULL;
+ if ((b = BATsetaccess(b, BAT_APPEND)) == NULL) {
+ return NULL;
+ }
+
+ /* disable all properties here */
+ b->tsorted = false;
+ b->trevsorted = false;
+ b->tnosorted = 0;
+ b->tnorevsorted = 0;
+ b->tseqbase = oid_nil;
+ b->tkey = false;
+ b->tnokey[0] = 0;
+ b->tnokey[1] = 0;
+ return b;
+}
/*
* odbc_query() contains the logic for both odbc_relation() and ODBCloader()
* the caller arg is 1 when called from odbc_relation and 2 when called from
ODBCloader
*/
static str
-odbc_query(mvc *sql, sql_subfunc *f, char *url, list *res_exps, int caller)
+odbc_query(mvc *sql, sql_subfunc *f, char *url, list *res_exps, MalStkPtr stk,
InstrPtr pci, int caller)
{
bool trace_enabled = false; /* used for development only */
+ if (sql == NULL)
+ return "Missing mvc value.";
+ if (f == NULL)
+ return "Missing sql_subfunc value.";
+
/* check received url and extract the ODBC connection string and the
SQL query */
if (!url || (url && strncasecmp("odbc:", url, 5) != 0))
return "Invalid URI. Must start with 'odbc:'.";
@@ -321,11 +410,14 @@ odbc_query(mvc *sql, sql_subfunc *f, cha
goto finish;
}
- ret = SQLExecDirect(stmt, (SQLCHAR *) query, SQL_NTS);
+ if (caller == 1)
+ ret = SQLPrepare(stmt, (SQLCHAR *) query, SQL_NTS);
+ else
+ ret = SQLExecDirect(stmt, (SQLCHAR *) query, SQL_NTS);
if (trace_enabled)
- printf("After SQLExecDirect(%s) returned %d\n", query, ret);
+ printf("After SQL%s(%s) returned %d\n", (caller == 1) ?
"Prepare" : "ExecDirect", query, ret);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
- errmsg = "SQLExecDirect query failed.";
+ errmsg = (caller == 1) ? "SQLPrepare query failed." :
"SQLExecDirect query failed.";
goto finish;
}
@@ -356,7 +448,7 @@ odbc_query(mvc *sql, sql_subfunc *f, cha
list * nameslist = sa_list(sql->sa);
for (SQLUSMALLINT col = 1; col <= (SQLUSMALLINT) nr_cols;
col++) {
/* for each result column get name, datatype, size and
decdigits */
- ret = SQLDescribeCol(stmt, col, (SQLCHAR *) cname,
(SQLSMALLINT) sizeof(cname),
+ ret = SQLDescribeCol(stmt, col, (SQLCHAR *) cname,
(SQLSMALLINT) sizeof(cname) -1,
NULL, &dataType, &columnSize,
&decimalDigits, NULL);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
errmsg = "SQLDescribeCol failed.";
@@ -370,102 +462,362 @@ odbc_query(mvc *sql, sql_subfunc *f, cha
sql_mtype = map_rescol_type(dataType, columnSize,
decimalDigits, sql);
list_append(typelist, sql_mtype);
- /* also get the table name for this result column */
- ret = SQLColAttribute(stmt, col, SQL_DESC_TABLE_NAME,
(SQLPOINTER) tname, (SQLSMALLINT) sizeof(tname), NULL, NULL);
- if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
- strcpy(tname, "");
+ if (res_exps) {
+ /* also get the table name for this result
column */
+ ret = SQLColAttribute(stmt, col,
SQL_DESC_TABLE_NAME, (SQLPOINTER) tname, (SQLSMALLINT) sizeof(tname) -1, NULL,
NULL);
+ if (ret != SQL_SUCCESS && ret !=
SQL_SUCCESS_WITH_INFO) {
+ strcpy(tname, "");
+ }
+ tblname = sa_strdup(sql->sa, tname);
+ sql_exp *ne = exp_column(sql->sa, tblname,
colname, sql_mtype, CARD_MULTI, 1, 0, 0);
+ set_basecol(ne);
+ ne->alias.label = -(sql->nid++);
+ list_append(res_exps, ne);
}
- tblname = sa_strdup(sql->sa, tname);
- sql_exp *ne = exp_column(sql->sa, tblname, colname,
sql_mtype, CARD_MULTI, 1, 0, 0);
- set_basecol(ne);
- ne->alias.label = -(sql->nid++);
- list_append(res_exps, ne);
}
f->tname = sa_strdup(sql->sa, tname);
f->colnames = nameslist;
f->coltypes = typelist;
f->res = typelist;
-
- odbc_loader_t *r = (odbc_loader_t *)sa_alloc(sql->sa,
sizeof(odbc_loader_t));
- r->env = env;
- r->dbc = dbc;
- r->stmt = stmt;
- r->nr_cols = nr_cols;
- f->sname = (char *)r; /* pass odbc_loader */
-
goto finish;
}
- /* when called from odbc_load() */
+ /* when called from ODBCloader() */
if (caller == 2) {
- sql_table *t;
+ BAT ** bats = (BAT **) GDKzalloc(nr_cols * sizeof(BAT *));
+ if (bats == NULL) {
+ errmsg = "GDKmalloc bats failed.";
+ goto finish;
+ }
+ char * mtypes = (char *) GDKzalloc(nr_cols * sizeof(char));
+ if (mtypes == NULL) {
+ errmsg = "GDKmalloc mtypes failed.";
+ GDKfree(bats);
+ bats = NULL;
+ goto finish;
+ }
+ SQLULEN largestStringSize = 0;
+ bool hasBlobCols = false;
+ SQLULEN largestBlobSize = 0;
+ /* make bats with right atom type */
+ for (int col = 0; col < (int) nr_cols; col++) {
+ char cname[1024];
+ SQLSMALLINT dataType = 0;
+ SQLULEN columnSize = 0;
+ SQLSMALLINT decimalDigits = 0;
+ int mtype = TYPE_str;
+ BAT * b = NULL;
- if (trace_enabled)
- printf("Before mvc_create_table(%s)\n", f->tname);
- // create an internal transient table to store fetched data
- if (mvc_create_table(&t, sql, sql->session->tr->tmp /* misuse
tmp schema */,
- f->tname /*gettable name*/, tt_table, false,
SQL_DECLARED_TABLE, 0, 0, false) != LOG_OK)
- /* alloc error */
- return NULL;
- if (trace_enabled)
- printf("After mvc_create_table()\n");
-
- node *n, *nn = f->colnames->h, *tn = f->coltypes->h;
- int col = 1;
- for (n = f->res->h; n && col <= nr_cols; col++, n = n->next, nn
= nn->next, tn = tn->next) {
- const char *name = nn->data;
- sql_subtype *tp = tn->data;
- sql_column *c = NULL;
+ /* for each result column get SQL datatype, size and
decdigits */
+ ret = SQLDescribeCol(stmt, col+1, (SQLCHAR *) cname,
(SQLSMALLINT) sizeof(cname) -1,
+ NULL, &dataType, &columnSize,
&decimalDigits, NULL);
+ if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
+ errmsg = "SQLDescribeCol failed.";
+ GDKfree(bats);
+ GDKfree(mtypes);
+ goto finish;
+ }
+ mtype = map_rescol_mtype(dataType, columnSize);
+ mtypes[col] = mtype;
+ if (mtype == TYPE_str) {
+ if (columnSize > largestStringSize) {
+ largestStringSize = columnSize;
+ }
+ }
+ if (mtype == TYPE_blob) {
+ hasBlobCols = true;
+ if (columnSize > largestBlobSize) {
+ largestBlobSize = columnSize;
+ }
+ }
+ if (trace_enabled)
+ printf("ResCol %d, name: %s, type %d (%s), size
%d, decdigits %d, atomtype %d\n",
+ col, cname, (int)dataType,
nameofSQLtype(dataType), (int)columnSize, (int)decimalDigits, mtype);
if (trace_enabled)
- printf("%d Before mvc_create_column(%s)\n",
col, name);
- if (!tp || mvc_create_column(&c, sql, t, name, tp) !=
LOG_OK) {
- return NULL;
+ printf("Before create BAT %d\n", col+1);
+ b = bat_create(mtype, 0);
+ if (b) {
+ bats[col] = b;
+ if (trace_enabled)
+ printf("After create BAT %d\n", col+1);
+ } else {
+ errmsg = "Failed to create bat.";
+ /* cleanup already created bats */
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
