I'm not entirely sure if this is a question, a bug report or an enhancement request. And if it goes here or in the DuckDB ODBC repository.
GEOMETRY columns are described as VARBINARY when describing a query using ODBC's SQLDescribeCol() function. This makes them indistinguishable from a BLOB column.
This program (run at 1.0.0 on Windows 10 x64):
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#ifdef WIN32
#include <windows.h>
#endif /*WIN32*/
#include "sqlext.h"
#define ERRMSG_LEN 200
SQLINTEGER checkError (SQLRETURN rc,
SQLSMALLINT handleType,
SQLHANDLE handle,
SQLCHAR* errmsg)
{
SQLRETURN retcode = SQL_SUCCESS;
SQLSMALLINT errNum = 1;
SQLCHAR sqlState[6];
SQLINTEGER nativeError;
SQLCHAR errMsg[ERRMSG_LEN];
SQLSMALLINT textLengthPtr;
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
{
while (retcode != SQL_NO_DATA)
{
retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState, &nativeError, errMsg, ERRMSG_LEN, &textLengthPtr);
if (retcode == SQL_INVALID_HANDLE)
{
fprintf (stderr, "checkError function was called with an invalid handle!!\n");
return 1;
}
if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
fprintf (stderr, "ERROR: %d: %s : %s \n", nativeError, sqlState, errMsg);
errNum++;
}
if (nativeError == -204)
{
return 0; /* no errors to report */
}
else
{
fprintf (stderr, "%s\n", errmsg);
return 1; /* all errors on this handle have been reported */
}
}
else
return 0; /* no errors to report */
}
int main (long argc,
char* argv[])
{
/* Handles */
SQLHDBC hdbc;
SQLHENV henv;
SQLHSTMT hstmt;
/* Miscellaneous variables */
SQLCHAR dsn[512];
SQLCHAR sql[256];
SQLRETURN rc = 0;
SQLLEN ind = 0;
SQLSMALLINT columnNameLen;
SQLULEN ColumnSizeLen;
SQLUSMALLINT column;
/* From SQLColumns */
#define STR_LEN 1024 + 1
#define REM_LEN 1024 + 1
// Declare buffers for result set data
SQLCHAR szColumnName[STR_LEN];
SQLSMALLINT DecimalDigits;
SQLSMALLINT Nullable;
SQLSMALLINT SQLDataType;
if (argc == 2)
{
/* Use specified dsn */
strcpy ((char *)dsn, (char *)argv[1]);
fprintf (stdout, "\nConnecting to DSN: %s\n", dsn);
}
else
{
fprintf (stdout,
"\n"
"Usage : %s <dsn>\n"
"\n", argv[0]);
exit(0);
}
/* Allocate the Environment handle */
rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Environment Handle Allocation failed\nExiting!!");
return (1);
}
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "Environment Handle Setting failed\nExiting!!");
return (1);
}
/* Allocate the connection handle */
rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error -- Connection Handle Allocation failed\nExiting!!"))
return (1);
/* Connect to the database */
rc = SQLDriverConnect (hdbc, (SQLPOINTER)NULL, dsn, SQL_NTS, (SQLCHAR *)NULL, (SQLSMALLINT)0, (SQLSMALLINT *)NULL, (SQLSMALLINT)0);
if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error -- SQLDriverConnect failed\nExiting!!"))
return (1);
/* Allocate the statement handle */
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt );
if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error -- Statement Handle Allocation failed\nExiting!!"))
return (1);
/* If this is DuckDB, set up the spatial type. */
if (strstr(dsn,"DuckDB")) {
rc = SQLExecDirect (hstmt, "INSTALL SPATIAL", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecDirect failed\n"))
goto Exit;
rc = SQLExecDirect (hstmt, "LOAD SPATIAL", SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecDirect failed\n"))
goto Exit;
}
/* Drop the table */
sprintf(sql, "DROP TABLE d ");
fprintf(stdout, "\nSQL: %s\n", sql);
rc = SQLExecDirect (hstmt, sql, SQL_NTS);
/* Create the table */
sprintf(sql,
"CREATE TABLE d ("
" SHAPE GEOMETRY,"
" NOT_SHAPE BLOB)");
fprintf(stdout, "\nSQL: %s\n", sql);
rc = SQLExecDirect (hstmt, sql, SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecDirect failed\n"))
goto Exit;
/* Allocate the statement handle */
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt );
if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error -- Statement Handle Allocation failed\nExiting!!"))
goto Exit;
/* Prepare SELECT statement. */
sprintf (sql,"SELECT shape,not_shape FROM d");
fprintf(stdout, "\nSQL: %s\n", sql);
/* Prepare the statement. */
rc = SQLPrepare (hstmt,sql,SQL_NTS);
if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error -- Statement prepare failed\nExiting!!"))
goto Exit;
/* Describe the output. */
for (column = 1; column <= 2;column++)
{
rc = SQLDescribeCol (hstmt,
column,
szColumnName,
STR_LEN,
&columnNameLen,
&SQLDataType,
&ColumnSizeLen,
&DecimalDigits,
&Nullable);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLDescribeCol failed\n"))
goto Exit;
fprintf (stdout, "\nSQLDescribeCol Output:\n");
fprintf (stdout, "\n");
fprintf (stdout, "ColumnName : %s\n", szColumnName);
fprintf (stdout, "SQLDataType : %d\n", SQLDataType);
fprintf (stdout, "ColumnSize : %d\n", (int)ColumnSizeLen);
fprintf (stdout, "DecimalDigits : %d\n", DecimalDigits);
fprintf (stdout, "Nullable : %d\n", Nullable);
}
rc = SQLFreeHandle (SQL_HANDLE_STMT,hstmt);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLFreeStmt failed\n"))
goto Exit;
/* Allocate the statement handle */
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt );
if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error -- Statement Handle Allocation failed\nExiting!!"))
return (1);
/* Drop the table */
sprintf(sql, "DROP TABLE d ");
fprintf(stdout, "\nSQL: %s\n", sql);
rc = SQLExecDirect (hstmt, sql, SQL_NTS);
if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecDirect failed\n"))
goto Exit;
Exit:
/* Free the statement handle */
SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
/* Disconnect from the data source */
SQLDisconnect (hdbc);
/* Free the environment handle and the database connection handle */
SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
SQLFreeHandle (SQL_HANDLE_ENV, henv);
return (rc);
}
Produces this output:
SQL: DROP TABLE d
SQL: CREATE TABLE d ( SHAPE GEOMETRY, NOT_SHAPE BLOB)
SQL: SELECT shape,not_shape FROM d
SQLDescribeCol Output:
ColumnName : SHAPE
SQLDataType : -3
ColumnSize : 512
DecimalDigits : 0
Nullable : 2
SQLDescribeCol Output:
ColumnName : NOT_SHAPE
SQLDataType : -3
ColumnSize : 512
DecimalDigits : 0
Nullable : 2
SQL: DROP TABLE d
Note that the geometry and the blob columns have the same description. This is not true when using SQLColumns(), but sometimes when working on general purpose software you don't have the luxury of having the table name, not to mention that there are functions that create shapes that could be part of the query.
Is there a way to distinguish geometry columns from blob columns?
I'm not entirely sure if this is a question, a bug report or an enhancement request. And if it goes here or in the DuckDB ODBC repository.
GEOMETRY columns are described as VARBINARY when describing a query using ODBC's SQLDescribeCol() function. This makes them indistinguishable from a BLOB column.
This program (run at 1.0.0 on Windows 10 x64):
Produces this output:
Note that the geometry and the blob columns have the same description. This is not true when using SQLColumns(), but sometimes when working on general purpose software you don't have the luxury of having the table name, not to mention that there are functions that create shapes that could be part of the query.
Is there a way to distinguish geometry columns from blob columns?