Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

The Spatial Extension appears to break ODBC bind parameters #324

Open
PeterAronson opened this issue May 29, 2024 · 1 comment
Open

The Spatial Extension appears to break ODBC bind parameters #324

PeterAronson opened this issue May 29, 2024 · 1 comment

Comments

@PeterAronson
Copy link

PeterAronson commented May 29, 2024 •
edited
Loading

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
Copy link
Member

Maxxen commented May 29, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Maxxen @PeterAronson