duckdb_spatial
duckdb_spatial copied to clipboard
duckdb
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:
#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.
