How do you identify a GEOMETRY column in a query when using ODBC?

[PeterAronson]

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?