You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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>#ifdefWIN32#include<windows.h>#endif/*WIN32*/#include"sqlext.h"#defineERRMSG_LEN 200
SQLINTEGERcheckError (SQLRETURNrc,
SQLSMALLINThandleType,
SQLHANDLEhandle,
SQLCHAR*errmsg)
{
SQLRETURNretcode=SQL_SUCCESS;
SQLSMALLINTerrNum=1;
SQLCHARsqlState[6];
SQLINTEGERnativeError;
SQLCHARerrMsg[ERRMSG_LEN];
SQLSMALLINTtextLengthPtr;
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");
return1;
}
if ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO))
fprintf (stderr, "ERROR: %d: %s : %s \n", nativeError, sqlState, errMsg);
errNum++;
}
if (nativeError==-204)
{
return0; /* no errors to report */
}
else
{
fprintf (stderr, "%s\n", errmsg);
return1; /* all errors on this handle have been reported */
}
}
elsereturn0; /* no errors to report */
}
intmain (longargc,
char*argv[])
{
/* Handles */SQLHDBChdbc;
SQLHENVhenv;
SQLHSTMThstmt;
/* Miscellaneous variables */SQLCHARdsn[512];
SQLCHARsql[256];
SQLRETURNrc=0;
SQLLENind=0;
SQLSMALLINTcolumnNameLen;
SQLULENColumnSizeLen;
SQLUSMALLINTcolumn;
/* From SQLColumns */#defineSTR_LEN 1024 + 1
#defineREM_LEN 1024 + 1
// Declare buffers for result set dataSQLCHARszColumnName[STR_LEN];
SQLSMALLINTDecimalDigits;
SQLSMALLINTNullable;
SQLSMALLINTSQLDataType;
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?
The text was updated successfully, but these errors were encountered:
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?
The text was updated successfully, but these errors were encountered: