Error while Bind BLOB values in ODBC(PostgreSQL), C/C++

Question

I have some problems about ODBC programming with C/C++, PostgreSQL connector.

I just want to put some BLOB data in my PostgreSQL with C/C++, ODBC

here's my DB Table Create Query

CREATE TABLE BLOB_TEST
(
    id number(20),
    data BYTEA 
);

And, here's my code

.
.
.
int retcode = 0;

SQLCHAR  sql[1024] =
        "BEGIN \n"
            "insert into BLOB_TEST "
        "values(9, ?); \n"
        "EXCEPTION \n"
            "when DUP_VAL_ON_INDEX then \n"
            "dbms_output.put_line(1); \n"
        "END; ";


char * temp_str = "this is a BLOB input TEST";

retcode = SQLPrepareA(hstmt, sql, SQL_NTS);

.
.
.

SQLBindParameter(hstmt,
        1, /* Parameter number, starting at 1 */
        SQL_PARAM_INPUT, /* in, out, inout */
        SQL_C_BINARY, /* C data type of the parameter */
        SQL_LONGVARBINARY, /* SQL data type of the parameter : char(8)*/
        0,          /* size of the column or expression, precision */
        0,          /* The decimal digits, scale */
        temp_str,        /* A pointer to a buffer for the parameter’s data */
        0,          /* Length of the ParameterValuePtr buffer in bytes */
        NULL        /* indicator */
    );

.
.
.

retcode = SQLExecute(hstmt);

if (retcode == SQL_SUCCESS)
{
    printf("Query Execute Success\n");
}
else 
{
        
    SQLGetDiagRecA(SQL_HANDLE_STMT, hstmt, ++rec, state, &native, message, sizeof(message), &length);
    printf("%s : %ld : %ld : %s\n", state, rec, native, message);

    printf("Query Execute ERROR : %d\n", retcode);
}

SQLExecute return -1(SQL_ERROR) and ERROR Message says:

SQLSTATE 42601, Missing ";" at the end of Expression

I know that PostgreSQL BLOB(BYTEA) type matched SQL_LONGVARBINARY Option when using SQLBindParameter, But that makes ERROR...

Is there any odd expression in my prepared query?

Or, Is there any way to check value-combind Query that SQLExcute function made?

I'm very confused, Cause the query that I prepared works well when using PgAdmin Querying tools...

So, I Want to check value-combind Query that SQLExcute function made.

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-11-26T07:20:56

You are trying to run a PL/SQL block on a database that is not Oracle. How is that supposed to work?

PostgreSQL has a DO statement that serves a similar purpose, but you cannot use parameters with it. You should send only the INSERT statement and do the exception handling in your C client code.

Error while Bind BLOB values in ODBC(PostgreSQL), C/C++

1 Answers