The Spatial Extension appears to break ODBC bind parameters

[PeterAronson]

When executing a SQL statement via the DuckDB ODBC interface using bind parameters, the presence of a Spatial function causes all sorts of errors, many of which are not obvious.

Let's start with the following C program which we use to test simple spatial capabilities of databases with a spatial type that support an ODBC interface:

#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       usr[32];
  SQLCHAR       pwd[32];
  SQLCHAR       sql[256];
  SQLRETURN     rc = 0;

  SQLINTEGER    c1;
  SQLCHAR       *c2;
  SQLCHAR       *c3;
  SQLLEN        ind = 0;
  SQLCHAR       outshape[201];

  if (argc == 4)
  {
    /* Use specified dsn */
    strcpy ((char *)dsn, (char *)argv[1]);
    strcpy ((char *)usr, (char *)argv[2]);
    strcpy ((char *)pwd, (char *)argv[3]);
    fprintf (stdout, "\nConnecting to DSN:   %s\n", dsn);
  }
  else
  {
    fprintf (stdout,
             "\n"
             "Usage : %s <dsn> <usr> <pwd>\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 ("
          " OBJECTID INT64 NOT NULL ,"
          " TAG STRING ,"
          " SHAPE GEOMETRY) ");
  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;

  /* Prepare the INSERT statement */

  sprintf(sql,
          "INSERT INTO d ( OBJECTID , TAG , SHAPE ) "
          "VALUES ( ? , ? , ST_GEOMFROMTEXT(?))");
  fprintf(stdout, "\nSQL: %s\n", sql);

  rc = SQLPrepare (hstmt, sql, SQL_NTS);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLPrepare failed\n"))
    goto Exit;

  /* Bind the parameter markers */

  c1 = 1;
  c2 = "first row";
  c3 = "POINT (1.1 1.1)";

  rc = SQLBindParameter (hstmt,
                         1,
                         SQL_PARAM_INPUT,
                         SQL_C_SLONG,
                         SQL_INTEGER,
                         0,
                         0,
                         &c1,
                         0,
                         0);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindParameter failed (column 1)\n"))
    goto Exit;

  rc = SQLBindParameter (hstmt,
                         2,
                         SQL_PARAM_INPUT,
                         SQL_C_CHAR,
                         SQL_VARCHAR,
                         10,
                         0,
                         c2,
                         10,
                         0);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindParameter failed (column 2)\n"))
    goto Exit;

  rc = SQLBindParameter (hstmt,
                         3,
                         SQL_PARAM_INPUT,
                         SQL_C_CHAR,
                         SQL_VARCHAR,
                         15,
                         0,
                         c3,
                         15,
                         0);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindParameter failed (column 3)\n"))
    goto Exit;

  /* Execute the INSERT statement */

  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed\n"))
    goto Exit;

  fprintf (stdout, "\nInserted row.\n");

  c1 = 2;
  c2 = "second row";
  c3 = "POINT (1.2 1.2)";

  rc = SQLBindParameter (hstmt,
                         2,
                         SQL_PARAM_INPUT,
                         SQL_C_CHAR,
                         SQL_VARCHAR,
                         10,
                         0,
                         c2,
                         10,
                         0);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindParameter failed (column 1)\n"))
    goto Exit;

  rc = SQLBindParameter (hstmt,
                         3,
                         SQL_PARAM_INPUT,
                         SQL_C_CHAR,
                         SQL_VARCHAR,
                         15,
                         0,
                         c3,
                         15,
                         0);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindParameter failed (column 1)\n"))
    goto Exit;

  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed\n"))
    goto Exit;

  fprintf (stdout, "\nInserted row.\n");
  rc = SQLFreeStmt (hstmt,SQL_DROP);
  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);

  /* Prepare SELECT statement. */

  sprintf (sql,"SELECT objectid,shape FROM d WHERE ST_Intersects (ST_GEOMFROMTEXT(?,true),shape)");

  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!!"))
    return (1);

  /* Bind the argument. */

  c3 = "POLYGON ((1 1,3 1,3 3,1 2,1 1))";

  rc = SQLBindParameter (hstmt,
                         1,
                         SQL_PARAM_INPUT,
                         SQL_C_CHAR,
                         SQL_VARCHAR,
                         strlen(c3),
                         0,
                         c3,
                         strlen(c3),
                         0);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindParameter failed (column 1)\n"))
    goto Exit;

  /* Bind the output. */

  rc = SQLBindCol (hstmt,
                   (SQLUSMALLINT) 1,
                   (SQLSMALLINT) SQL_C_SLONG,
                   &c1,
                   sizeof(c1),
                   &ind);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindCol failed (column 1 Output)\n"))
    goto Exit;

  rc = SQLBindCol (hstmt,
                   (SQLUSMALLINT) 2,
                   (SQLSMALLINT) SQL_C_CHAR,
                   outshape,
                   sizeof(outshape),
                   &ind);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindCol failed (column 2 Output)\n"))
    goto Exit;

  /* Execute the statement. */

  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed\n"))
    goto Exit;

  do {
    rc = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 0);
    if (rc == SQL_NO_DATA)
      break;
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLFetchScroll failed\n"))
      break;
    fprintf(stdout,"C1 = %d,C3 = %s\n",c1,outshape);
    /* Stop after first row. */
    break;
  } while (SQL_SUCCESS == rc || SQL_SUCCESS_WITH_INFO == rc);

  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);
}

Running it with a DSN of the form: DRIVER={DuckDB Driver};Database=C:\xxx\experiment1.duckdb produces the following error:

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( ? , ? , ST_GEOMFROMTEXT(?))
ERROR: 0:  42000 : ODBC_DuckDB->PrepareStmt
Invalid Input Error: ST_GeomFromText requires a string argument
Error -- SQLPrepare failed

Which is an odd error to get from SQLPrepare.

Interestingly, if we change ST_GEOMFROMTEXT to ST_GEOMFROMGEOJSON or ST_GEOMFROMWKB, we get:

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( ? , ? , ST_GEOMFROMGEOJSON(?))
ERROR: 0:  42000 : ODBC_DuckDB->PrepareStmt
Not all parameters are bound
Error -- SQLPrepare failed

Which is also a strange error to get from SQLPrepare.

Changing the function to ST_GEOMFROMHEXWKB gets us past the SQLPrepare:

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( ? , ? , ST_GEOMFROMHEXWKB(?))
ERROR: 0:  HY000 : ODBC_DuckDB->SingleExecuteStmt
Constraint Error: NOT NULL constraint failed: d.OBJECTID
Error -- SQLExecute failed

But the new error is not what one would expect, either.

Making the argument to ST_GEOMFROMTEXT fixed to 'POINT (1.1 1.1)' and removing the corresponding SQLBindParameter call, we get:

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( ? , ? , ST_GEOMFROMTEXT('point (1.1 1.1)'))
ERROR: 0:  HY000 : ODBC_DuckDB->SingleExecuteStmt
Constraint Error: NOT NULL constraint failed: d.OBJECTID
Error -- SQLExecute failed

Which is not right.

Taking the NOT NULL specifier off of OBJECTID (and keeping the above change), the program hangs on SQLExecute with the following call stack:

ntdll.dll!NtFlushBuffersFile()
KernelBase.dll!FlushFileBuffers()
duckdb_odbc.dll!duckdb::FileSystem::CreateLocal() + 2445 bytes
duckdb_odbc.dll!00007ff9e67ee070()
duckdb_odbc.dll!duckdb::DuckCatalog::ScanSchemas() + 6220 bytes
duckdb_odbc.dll!00007ff9e658c962()
duckdb_odbc.dll!duckdb::ObjectCache::ObjectCacheEnabled() + 4322 bytes
duckdb_odbc.dll!duckdb::MetaTransaction::MetaTransaction() + 671 bytes
duckdb_odbc.dll!duckdb::ColumnDataRowIterationHelper::end() + 1541 bytes
duckdb_odbc.dll!duckdb::ClientContext::EnableProfiling() + 524 bytes
duckdb_odbc.dll!duckdb::ClientContext::Append() + 2126 bytes
duckdb_odbc.dll!duckdb::ClientContext::ExtractPlan() + 858 bytes
duckdb_odbc.dll!duckdb::PendingQueryResult::Execute() + 297 bytes
duckdb_odbc.dll!duckdb::PreparedStatement::Execute() + 167 bytes
duckdb_odbc.dll!SQLExecDirect() + 13721 bytes
duckdb_odbc.dll!00007ff9e625b828()
duckdb_odbc.dll!SQLExecute() + 48 bytes
odbc32.dll!SQLExecute()
SQLSpatialSelect.exe!main(long argc, char * * argv) Line 274
    at C:\ArcGIS\ArcSDE\commands\ODBC\SQLSpatialSelect.c(274)
SQLSpatialSelect.exe!invoke_main() Line 79
    at D:\a\_work\1\s\src\vctools\crt\vcstartup\src\startup\exe_common.inl(79)
SQLSpatialSelect.exe!__scrt_common_main_seh() Line 288
    at D:\a\_work\1\s\src\vctools\crt\vcstartup\src\startup\exe_common.inl(288)
SQLSpatialSelect.exe!__scrt_common_main() Line 331
    at D:\a\_work\1\s\src\vctools\crt\vcstartup\src\startup\exe_common.inl(331)
SQLSpatialSelect.exe!mainCRTStartup(void * __formal) Line 17
    at D:\a\_work\1\s\src\vctools\crt\vcstartup\src\startup\exe_main.cpp(17)
kernel32.dll!BaseThreadInitThunk()
ntdll.dll!RtlUserThreadStart()

However, if we take out all of the binds:

#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       usr[32];
  SQLCHAR       pwd[32];
  SQLCHAR       sql[256];
  SQLRETURN     rc = 0;

  SQLINTEGER    c1;
  SQLCHAR       *c2;
  SQLCHAR       *c3;
  SQLLEN        ind = 0;
  SQLCHAR       outshape[201];

  if (argc == 4)
  {
    /* Use specified dsn */
    strcpy ((char *)dsn, (char *)argv[1]);
    strcpy ((char *)usr, (char *)argv[2]);
    strcpy ((char *)pwd, (char *)argv[3]);
    fprintf (stdout, "\nConnecting to DSN:   %s\n", dsn);
  }
  else
  {
    fprintf (stdout,
             "\n"
             "Usage : %s <dsn> <usr> <pwd>\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 ("
          " OBJECTID INT64 ,"
          " TAG STRING ,"
          " SHAPE GEOMETRY) ");
  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;

  /* Prepare the INSERT statement */
  sprintf(sql,
          "INSERT INTO d ( OBJECTID , TAG , SHAPE ) "
          "VALUES ( 1 , 'first row' , ST_GEOMFROMTEXT('POINT (1.1 1.1)'))");
  fprintf(stdout, "\nSQL: %s\n", sql);

  rc = SQLPrepare (hstmt, sql, SQL_NTS);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLPrepare failed\n"))
    goto Exit;

  /* Execute the INSERT statement */

  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed <1>\n"))
    goto Exit;

  fprintf (stdout, "\nInserted row.\n");

  rc = SQLFreeStmt (hstmt,SQL_DROP);
  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);

  sprintf(sql,
          "INSERT INTO d ( OBJECTID , TAG , SHAPE ) "
          "VALUES ( 2 , 'second row' , ST_GEOMFROMTEXT('POINT (1.2 1.2)'))");
  fprintf(stdout, "\nSQL: %s\n", sql);

  rc = SQLPrepare (hstmt, sql, SQL_NTS);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLPrepare failed\n"))
    goto Exit;

  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed <2>\n"))
    goto Exit;

  fprintf (stdout, "\nInserted row.\n");
  rc = SQLFreeStmt (hstmt,SQL_DROP);
  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);

  sprintf(sql,
          "INSERT INTO d ( OBJECTID , TAG , SHAPE ) "
          "VALUES ( 3 , 'third row' , ST_GEOMFROMTEXT('POINT (1.3 1.3)'))");
  fprintf(stdout, "\nSQL: %s\n", sql);

  rc = SQLPrepare (hstmt, sql, SQL_NTS);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLPrepare failed\n"))
    goto Exit;
  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed <2>\n"))
    goto Exit;

  fprintf (stdout, "\nInserted row.\n");

  rc = SQLFreeStmt (hstmt,SQL_DROP);
  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);

  /* Prepare SELECT statement. */

  sprintf (sql,"SELECT objectid,ST_ASTEXT(shape) FROM d WHERE ST_Intersects (ST_GEOMFROMTEXT('POLYGON ((1 1,3 1,3 3,1 2,1 1))'),shape)");

  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!!"))
    return (1);

  /* Bind the output. */

  rc = SQLBindCol (hstmt,
                   (SQLUSMALLINT) 1,
                   (SQLSMALLINT) SQL_C_SLONG,
                   &c1,
                   sizeof(c1),
                   &ind);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindCol failed (column 1 Output)\n"))
    goto Exit;

  rc = SQLBindCol (hstmt,
                   (SQLUSMALLINT) 2,
                   (SQLSMALLINT) SQL_C_CHAR,
                   outshape,
                   sizeof(outshape),
                   &ind);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLBindCol failed (column 2 Output)\n"))
    goto Exit;

  /* Execute the statement. */

  rc = SQLExecute (hstmt);
  if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLExecute failed\n"))
    goto Exit;

  do {
    rc = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 0);
    if (rc == SQL_NO_DATA)
      break;
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error -- SQLFetchScroll failed\n"))
      break;
    fprintf(stdout,"C1 = %d,C3 = %s\n",c1,outshape);
  } while (SQL_SUCCESS == rc || SQL_SUCCESS_WITH_INFO == rc);

//  rc = SQLFreeStmt (hstmt,SQL_DROP);
  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);
}

It runs fine:

SQL: DROP TABLE d

SQL: CREATE TABLE d ( OBJECTID INT64 , TAG STRING , SHAPE GEOMETRY)

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( 1 , 'first row' , ST_GEOMFROMTEXT('POINT (1.1 1.1)'))

Inserted row.

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( 2 , 'second row' , ST_GEOMFROMTEXT('POINT (1.2 1.2)'))

Inserted row.

SQL: INSERT INTO d ( OBJECTID , TAG , SHAPE ) VALUES ( 3 , 'third row' , ST_GEOMFROMTEXT('POINT (1.3 1.3)'))

Inserted row.

SQL: SELECT objectid,ST_ASTEXT(shape) FROM d WHERE ST_Intersects (ST_GEOMFROMTEXT('POLYGON ((1 1,3 1,3 3,1 2,1 1))'),shape)
C1 = 1,C3 = POINT (1.1 1.1)
C1 = 2,C3 = POINT (1.2 1.2)
C1 = 3,C3 = POINT (1.3 1.3)

Tests run on Windows 10 Enterprise, x64 using DuckDB 0.10.2 and 5/28/2024 bleeding edge.

Peter Aronson, Esri.

[Maxxen]

@maiadegraaf maybe we can have a look at this together when I get back