Changeset: 9722e2f78284 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/9722e2f78284 Modified Files: sql/backends/monet5/vaults/odbc/odbc_loader.c sql/test/proto_loader/odbc/Tests/monetodbc_datatypes.test Branch: Mar2025 Log Message:
Improve the reading of interval data. We now use the SQL_INTERVAL_STRUCT as
defined in ODBC.
Added tests for some interval types.
diffs (truncated from 426 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
@@ -38,8 +38,8 @@
#define ODBC_LOADER 2
#define QUERY_MAX_COLUMNS 4096
-#define MAX_COL_NAME_LEN 1024
-#define MAX_TBL_NAME_LEN 1024
+#define MAX_COL_NAME_LEN 1023
+#define MAX_TBL_NAME_LEN 1023
#ifdef HAVE_HGE
#define MAX_PREC 38
@@ -291,6 +291,33 @@ str_to_hge(const char *s) {
}
#endif
+/* an ODBC function call returned an error, get the error msg from the ODBC
driver */
+static char *
+getErrMsg(SQLHANDLE stmt) {
+ SQLRETURN ret = SQL_ERROR;
+ SQLCHAR state[SQL_SQLSTATE_SIZE +1];
+ SQLINTEGER errnr;
+ SQLCHAR msg[4096];
+ SQLSMALLINT msglen;
+
+ if (stmt == SQL_NULL_HSTMT)
+ return NULL;
+
+ // TODO use ODBC W function
+ ret = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, state, &errnr, msg,
(sizeof(msg) -1), &msglen);
+ if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
+ char * format = "odbc_loader SQLstate %s, Errnr %d, Message %s";
+ char * retmsg = (char *) GDKmalloc(strlen(format) + msglen);
+ if (retmsg != NULL) {
+ if (state[SQL_SQLSTATE_SIZE] != '\0')
+ state[SQL_SQLSTATE_SIZE] = '\0';
+ sprintf(retmsg, format, (char*)state, errnr,
(char*)msg);
+ return retmsg;
+ }
+ }
+ return NULL;
+}
+
/* utility function to safely close all opened ODBC resources */
static void
odbc_cleanup(SQLHANDLE env, SQLHANDLE dbc, SQLHANDLE stmt) {
@@ -495,8 +522,8 @@ odbc_query(int caller, mvc *sql, sql_sub
/* when called from odbc_relation() */
if (caller == ODBC_RELATION) {
- char tname[MAX_TBL_NAME_LEN];
- char cname[MAX_COL_NAME_LEN];
+ char tname[MAX_TBL_NAME_LEN +1];
+ char cname[MAX_COL_NAME_LEN +1];
char * tblname;
char * colname;
SQLSMALLINT dataType = 0;
@@ -508,7 +535,7 @@ odbc_query(int caller, mvc *sql, sql_sub
for (SQLUSMALLINT col = 1; col <= (SQLUSMALLINT) nr_cols;
col++) {
/* for each result column get name, datatype, size and
decdigits */
// TODO use ODBC W function
- ret = SQLDescribeCol(stmt, col, (SQLCHAR *) cname,
(SQLSMALLINT) sizeof(cname) -1,
+ ret = SQLDescribeCol(stmt, col, (SQLCHAR *) cname,
(SQLSMALLINT) MAX_COL_NAME_LEN,
NULL, &dataType, &columnSize,
&decimalDigits, NULL);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
errmsg = "SQLDescribeCol failed.";
@@ -525,7 +552,7 @@ odbc_query(int caller, mvc *sql, sql_sub
if (res_exps) {
/* also get the table name for this result
column */
// TODO use ODBC W function
- ret = SQLColAttribute(stmt, col,
SQL_DESC_TABLE_NAME, (SQLPOINTER) tname, (SQLSMALLINT) sizeof(tname) -1, NULL,
NULL);
+ ret = SQLColAttribute(stmt, col,
SQL_DESC_TABLE_NAME, (SQLPOINTER) tname, (SQLSMALLINT) MAX_TBL_NAME_LEN, NULL,
NULL);
if (ret != SQL_SUCCESS && ret !=
SQL_SUCCESS_WITH_INFO) {
strcpy(tname, "");
}
@@ -557,7 +584,7 @@ odbc_query(int caller, mvc *sql, sql_sub
SQLULEN largestBlobSize = 0;
/* make bats with right atom type */
for (SQLUSMALLINT col = 0; col < (SQLUSMALLINT) nr_cols; col++)
{
- char cname[MAX_COL_NAME_LEN];
+ char cname[MAX_COL_NAME_LEN +1];
SQLSMALLINT dataType = 0;
SQLULEN columnSize = 0;
SQLSMALLINT decimalDigits = 0;
@@ -566,7 +593,7 @@ odbc_query(int caller, mvc *sql, sql_sub
/* for each result column get SQL datatype, size and
decdigits */
// TODO use ODBC W function
- ret = SQLDescribeCol(stmt, col+1, (SQLCHAR *) cname,
(SQLSMALLINT) sizeof(cname) -1,
+ ret = SQLDescribeCol(stmt, col+1, (SQLCHAR *) cname,
(SQLSMALLINT) MAX_COL_NAME_LEN,
NULL, &dataType, &columnSize,
&decimalDigits, NULL);
if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
{
errmsg = "SQLDescribeCol failed.";
@@ -656,13 +683,17 @@ odbc_query(int caller, mvc *sql, sql_sub
DATE_STRUCT date_val;
TIME_STRUCT time_val;
TIMESTAMP_STRUCT ts_val;
+ SQL_INTERVAL_STRUCT itv_val;
SQLGUID guid_val;
+ uuid uuid_val = uuid_nil;
/* allocate storage for all the var sized atom types. */
char * str_val = NULL; // TODO: change to wchar
bte * blob_val = NULL;
if (largestStringSize == 0) // no valid string length, use
65535 (64kB) as default
largestStringSize = 65535;
+ if (largestStringSize < 256)
+ largestStringSize = 256;
if (largestStringSize > 16777215) // string length too large,
limit to 16MB
largestStringSize = 16777215;
str_val = (char *)GDKzalloc((largestStringSize +1) *
sizeof(char)); // +1 for the eos char
@@ -778,28 +809,66 @@ odbc_query(int caller, mvc *sql, sql_sub
targetType =
SQL_C_TYPE_TIMESTAMP;
targetValuePtr = (SQLPOINTER *)
&ts_val;
break;
- case SQL_INTERVAL_MONTH:
case SQL_INTERVAL_YEAR:
+ targetType =
SQL_C_INTERVAL_YEAR;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_YEAR_TO_MONTH:
- targetType = SQL_C_SLONG;
- targetValuePtr = (SQLPOINTER *)
&int_val;
+ targetType =
SQL_C_INTERVAL_YEAR_TO_MONTH;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
+ case SQL_INTERVAL_MONTH:
+ targetType =
SQL_C_INTERVAL_MONTH;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
break;
case SQL_INTERVAL_DAY:
+ targetType = SQL_C_INTERVAL_DAY;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_HOUR:
+ targetType =
SQL_C_INTERVAL_HOUR;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_MINUTE:
+ targetType =
SQL_C_INTERVAL_MINUTE;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_SECOND:
+ targetType =
SQL_C_INTERVAL_SECOND;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_DAY_TO_HOUR:
+ targetType =
SQL_C_INTERVAL_DAY_TO_HOUR;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_DAY_TO_MINUTE:
+ targetType =
SQL_C_INTERVAL_DAY_TO_MINUTE;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_DAY_TO_SECOND:
+ targetType =
SQL_C_INTERVAL_DAY_TO_SECOND;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_HOUR_TO_MINUTE:
+ targetType =
SQL_C_INTERVAL_HOUR_TO_MINUTE;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_HOUR_TO_SECOND:
+ targetType =
SQL_C_INTERVAL_HOUR_TO_SECOND;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
+ break;
case SQL_INTERVAL_MINUTE_TO_SECOND:
- targetType = SQL_C_SBIGINT;
- targetValuePtr = (SQLPOINTER *)
&lng_val;
+ targetType =
SQL_C_INTERVAL_MINUTE_TO_SECOND;
+ targetValuePtr = (SQLPOINTER *)
&itv_val;
break;
case SQL_GUID:
+ /* read guid data as string
data */
+// targetType = SQL_C_CHAR;
+// targetValuePtr = (SQLPOINTER *)
str_val;
+// bufferLength =
largestStringSize;
targetType = SQL_C_GUID;
targetValuePtr = (SQLPOINTER *)
&guid_val;
+ bufferLength = 16;
break;
case SQL_BINARY:
case SQL_VARBINARY:
@@ -809,10 +878,17 @@ odbc_query(int caller, mvc *sql, sql_sub
bufferLength = largestBlobSize;
break;
}
+ if (trace_enabled)
+ printf("Before SQLGetData(col %u C_type
%d buflen %ld\n", col+1, targetType, bufferLength);
ret = SQLGetData(stmt, col+1, targetType,
targetValuePtr, bufferLength, &strLen);
if (ret != SQL_SUCCESS && ret !=
SQL_SUCCESS_WITH_INFO) {
- if (trace_enabled || true)
- printf("Failed to get data for
col %u of row %lu\n", col+1, row);
+ if (trace_enabled) {
+ char * ODBCmsg =
getErrMsg(stmt);
+ printf("Failed to get C_type %d
data for col %u of row %lu. ODBCmsg: %s\n",
+ targetType, col+1, row,
(ODBCmsg != NULL) ? ODBCmsg : "");
+ if (ODBCmsg)
+ GDKfree(ODBCmsg);
+ }
/* as all bats need to be the correct
length, append NULL value */
if (BUNappend(b, ATOMnilptr(b->ttype),
false) != GDK_SUCCEED)
if (trace_enabled)
@@ -965,32 +1041,154 @@ odbc_query(int caller, mvc *sql, sql_sub
gdkret =
BUNappend(b, (void *) ×tamp_val, false);
break;
}
+ case SQL_INTERVAL_YEAR:
+ {
+ int_val = (int)
itv_val.intval.year_month.year *12;
+ if
(itv_val.interval_sign == SQL_TRUE)
+ int_val
= -int_val;
+ if
(trace_enabled)
+
printf("Data row %lu col %u: %d\n", row, col+1, int_val);
+ gdkret =
BUNappend(b, (void *) &int_val, false);
+ break;
+ }
+ case
SQL_INTERVAL_YEAR_TO_MONTH:
+ {
+ int_val = (int)
(itv_val.intval.year_month.year *12) + itv_val.intval.year_month.month;
+ if
(itv_val.interval_sign == SQL_TRUE)
+ int_val
= -int_val;
+ if
(trace_enabled)
+
printf("Data row %lu col %u: %d\n", row, col+1, int_val);
+ gdkret =
BUNappend(b, (void *) &int_val, false);
+ break;
+ }
case SQL_INTERVAL_MONTH:
- case SQL_INTERVAL_YEAR:
- case
SQL_INTERVAL_YEAR_TO_MONTH:
+ {
+ int_val = (int)
itv_val.intval.year_month.month;
+ if
(itv_val.interval_sign == SQL_TRUE)
+ int_val
= -int_val;
if
(trace_enabled)
printf("Data row %lu col %u: %d\n", row, col+1, int_val);
gdkret =
BUNappend(b, (void *) &int_val, false);
break;
+ }
case SQL_INTERVAL_DAY:
+ {
+ lng_val = (lng)
itv_val.intval.day_second.day * (24*60*60*1000);
+ if
(itv_val.interval_sign == SQL_TRUE)
+ lng_val
= -lng_val;
+ if
(trace_enabled)
+
printf("Data row %lu col %u: %" PRId64 "\n", row, col+1, lng_val);
+ gdkret =
BUNappend(b, (void *) &lng_val, false);
+ break;
+ }
case SQL_INTERVAL_HOUR:
+ {
+ lng_val = (lng)
itv_val.intval.day_second.hour * (60*60*1000);
+ if
(itv_val.interval_sign == SQL_TRUE)
+ lng_val
= -lng_val;
+ if
(trace_enabled)
+
printf("Data row %lu col %u: %" PRId64 "\n", row, col+1, lng_val);
+ gdkret =
BUNappend(b, (void *) &lng_val, false);
+ break;
+ }
case
SQL_INTERVAL_MINUTE:
+ {
+ lng_val = (lng)
itv_val.intval.day_second.minute * (60*1000);
+ if
(itv_val.interval_sign == SQL_TRUE)
+ lng_val
= -lng_val;
+ if
(trace_enabled)
+
printf("Data row %lu col %u: %" PRId64 "\n", row, col+1, lng_val);
+ gdkret =
BUNappend(b, (void *) &lng_val, false);
+ break;
+ }
case
SQL_INTERVAL_SECOND:
+ {
+ lng_val = (lng)
itv_val.intval.day_second.second * 1000;
+ if
(itv_val.interval_sign == SQL_TRUE)
+ lng_val
= -lng_val;
+ if
(trace_enabled)
+
printf("Data row %lu col %u: %" PRId64 "\n", row, col+1, lng_val);
+ gdkret =
BUNappend(b, (void *) &lng_val, false);
+ break;
+ }
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:
+ {
+ lng_val = (lng)
((itv_val.intval.day_second.day *24)
+ +
itv_val.intval.day_second.hour) * (60*60*1000);
+ if
(itv_val.interval_sign == SQL_TRUE)
+ lng_val
= -lng_val;
if
(trace_enabled)
printf("Data row %lu col %u: %" PRId64 "\n", row, col+1, lng_val);
gdkret =
BUNappend(b, (void *) &lng_val, false);
break;
+ }
+ case
SQL_INTERVAL_DAY_TO_MINUTE:
+ {
+ lng_val = (lng)
((((itv_val.intval.day_second.day *24)
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
