Using SQLBindParameter and SQLExecDirect fail on CitusDB

[Quân Nguyễn Hồng]

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]

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