The Spatial Extension appears to break ODBC bind parameters

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:
```C
#include
#include
#include

#ifdef WIN32
#include
#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 \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:
```C

#include
#include
#include

#ifdef WIN32
#include
#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 \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.

Start by compiling and running the supplied C ODBC reproducer with the DuckDB Spatial extension installed and loaded. Compare the SQLPrepare and SQLExecute behavior for the INSERT and spatial SELECT statements; done means bound parameters work with spatial functions without the reported preparation or execution errors.

Written by the indexing model from the issue text.