CODEWITHSUNDEEP
cpostgresqlodbcsyntax-error
dieortin
dieortin

Reputation: 522

Syntax error in ODBC for a correct query

I have to insert the contents of a file into a database (it's a requirement to do it using odbc and not using a COPY instruction)

It was all working well until I tried to extract the part where I send the query to a new function. The weird part is I'm getting a syntax error while the queries run without any problem if I execute them by hand in psql.

The new function is this:

int insertRowWithData(const char *purchase_id, const char *username, const char *isbn, const char *date, char *query, SQLHSTMT *stmt) {
    SQLRETURN ret;

    SQLCHAR diag[DIAG_BUFFER_SIZE]; /** To store the error code */
    SQLRETURN diag_ret; /** To store the return code of the diagnostic function, just in case it misbehaves */
    SQLCHAR diag_text[DIAG_BUFFER_SIZE]; /** To store the text explanation of the error */


    /* INSERT INTO purchase VALUES ('purchase_id', (SELECT customer.customer_id from customer where customer.username='username'), 'isbn', 'date'); */
    sprintf(query, "insert into purchase VALUES ('%s', (SELECT customer.customer_id from customer where customer.username='%s'), '%s', '%s');", purchase_id, username, isbn, date);
    if (DEBUG) {
        printf(ANSI_COLOR_YELLOW "[DEBUG]" ANSI_COLOR_RESET " The query to be sent is: %s\n", query);
    }

    ret = SQLExecDirect(*stmt, (SQLCHAR*) query, sizeof(query));
    if (DEBUG && !SQL_SUCCEEDED(ret)) {
        diag_ret = SQLGetDiagRec(SQL_HANDLE_STMT, *stmt, 1, diag, NULL, diag_text, DIAG_BUFFER_SIZE, NULL);
        printf(ANSI_COLOR_RED "[ERROR]" ANSI_COLOR_RESET " %s (error code: %s)\n", diag_text, diag);
    }
    if (SQL_SUCCEEDED(ret)) {
        printf(ANSI_COLOR_GREEN "[SUCCESS]" ANSI_COLOR_RESET " Row with purchase_id=%s, username=%s, isbn=%s and date=%s was successfully inserted in the purchase table\n", purchase_id, username, isbn, date);
        return EXIT_SUCCESS;
    } else {
        printf(ANSI_COLOR_RED "[ERROR]" ANSI_COLOR_RESET " Row with purchase_id=%s, username=%s, isbn=%s and date=%s could not be inserted in the purchase table\n", purchase_id, username, isbn, date);
        return EXIT_FAILURE;
    }
}

And the output I'm getting when running the program is this:

[DEBUG] The query to be sent is: insert into purchase VALUES ('421', (SELECT customer.customer_id from customer where customer.username='765150'), '8441436169', '2014-02-09');
[ERROR] ERROR: syntax error at or near "i";
Error while executing the query (error code: 42601)
[ERROR] Row with purchase_id=421, username=765150, isbn=8441436169 and date=2014-02-09 could not be inserted in the purchase table
[DEBUG] The query to be sent is: insert into purchase VALUES ('422', (SELECT customer.customer_id from customer where customer.username='1000591'), '9061123097', '2015-09-05');
[ERROR] ERROR: syntax error at or near "i";
Error while executing the query (error code: 42601)
[ERROR] Row with purchase_id=422, username=1000591, isbn=9061123097 and date=2015-09-05 could not be inserted in the purchase table

The SQLHSTMT passed to the function is allocated in its caller function. The queries printed in the output work perfectly fine.

What's my mistake?

Upvotes: 0

Views: 100

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

The problem is that you use sizeof() to determine the length of the query string, but the variable query is declared as char *, so this will give you 4 or 8, depending on the architecture.

Use strlen() instead.

Upvotes: 1

Related Questions