Using SQLBindParameter and SQLExecDirect fail on CitusDB

35 views
Skip to first unread message

Quân Nguyễn Hồng

unread,
Oct 1, 2015, 12:33:59 AM10/1/15
to pg_shard users
Hi all,

I have a question with below code. (Visual C++)

I want to use SQLBindParameter with SQLExecDirect instead of use with SQLPrepare because citusDB doesn't support SQLPrepare (right?)

int main (int argc, char *argv [])
{
    SQLCHAR sQuery2
[512] = "insert into test (resid, status, inoctets) values (?, ?, ?)";

   
string ConnectString ("Driver={PostgreSQL Unicode};Server=192.168.99.121;Port=5432;Database=quandb;UID=postgres;PWD=123456");

    SQLHANDLE henv
= SQL_NULL_HANDLE;
    SQLHANDLE hdbc
= SQL_NULL_HANDLE;
    SQLHANDLE hstmt
= SQL_NULL_HANDLE;
    SQLRETURN rc
=  SQL_ERROR;
    SQLCHAR    
OutConnString[1024];    // Not sure what this is good for.
    SQLSMALLINT nConnectOutLen
= 0;

   
/* CONNECTION */
    rc
= ::SQLAllocHandle ( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv );
    rc
= ::SQLSetEnvAttr ( henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0 );
    rc
= ::SQLAllocHandle ( SQL_HANDLE_DBC, henv, &hdbc );
    rc
= SQLDriverConnect(hdbc, NULL, (SQLCHAR*)ConnectString.data(), SQL_NTS,
       
OutConnString, sizeof(OutConnString) / sizeof(SQLCHAR), &nConnectOutLen, SQL_DRIVER_NOPROMPT);

   
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
        cout
<< "Connection failed. Error:\n";
        SQLCHAR strSQLState
[50];
        SQLINTEGER iNativeError
;
        SQLCHAR strMessageText
[512];
        SQLSMALLINT iTextLength
;
       
SQLGetDiagRec(SQL_HANDLE_DBC, hDbc, 1, strSQLState, &iNativeError, strMessageText, 512, &iTextLength);
        cout
<< "SqlState ="<<strSQLState<<"\n NativeError =" << iNativeError << "\n Message ="<< strMessageText <<endl;
       
CleanUpAndExit(1);
   
}

    rc
= ::SQLAllocHandle ( SQL_HANDLE_STMT, hdbc, &hstmt );

   
/* BINDING */
    SQLSMALLINT value
[3] = {5,5,5};
    SQLINTEGER len
[3] = {0,0,0};
   
   
for(int i=0;i<3;i++)
   
{
        rc
= ::SQLBindParameter(hstmt,i+1, SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_INTEGER,0,0,
        value
+i, 0, len+i);
       
if (rc != SQL_SUCCESS)
       
{
            cout
<< "SQLBindParameter failed.\n";
            SQLCHAR strSQLState
[50];
            SQLINTEGER iNativeError
;
            SQLCHAR strMessageText
[512];
            SQLSMALLINT iTextLength
;
           
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, strSQLState, &iNativeError, strMessageText, 512, &iTextLength);
            cout
<< "SqlState ="<<strSQLState<<"\n NativeError =" << iNativeError << "\n Message ="<< strMessageText <<endl;
           
CleanUpAndExit(1);
       
}
   
}
   
   
/* EXEC DIRECT */
    rc
= ::SQLExecDirect ( hstmt, sQuery2, SQL_NTS );
   
if (rc != SQL_SUCCESS)
   
{
        cout
<< "SQLExecute failed.\n";
        SQLCHAR strSQLState
[50];
        SQLINTEGER iNativeError
;
        SQLCHAR strMessageText
[512];
        SQLSMALLINT iTextLength
;
       
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, strSQLState, &iNativeError, strMessageText, 512, &iTextLength);
        cout
<< "SqlState ="<<strSQLState<<"\n NativeError =" << iNativeError << "\n Message ="<< strMessageText <<endl;
       
CleanUpAndExit(1);
   
}

    cout
<< "OK \n";
   
CleanUpAndExit(0);
}

if I connect to Microsoft SQL with ODBC connection string
string ConnectString2 ("Driver={SQL Server};Server=192.168.94.173;Database=testdb;Uid=dba;Pwd=dba;");
the above code runs perfectly, new record (5,5,5) is created.

But if I run with CitusDB, it fails with this info

SqlState =XX000
NativeError =7
Message =ERROR: unrecognized node type: 2100;

So, I haven't use prepare statement, why this code still fail with CitusDB?

Thanks so much.

Onder Kalaci

unread,
Oct 13, 2015, 3:58:44 AM10/13/15
to pg_shard users
Hi,

CitusDB currently supports PREPARE statements for SELECTs. During INSERTs, we delegate the responsibility to the internal component pg_shard, which currently does not support PREPARE statements.

We now understand this causes confusion for our users, and we're editing our website to clarify. The next release of CitusDB will integrate much seamlessly with pg_shard. We're also tracking this issue here: https://github.com/citusdata/pg_shard/issues/140.

Onder,
Thanks
Reply all
Reply to author
Forward
0 new messages