c - 正确查询的 ODBC 中存在语法错误

Question

我必须将文件的内容插入数据库(要求使用 odbc 而不是使用 COPY 指令来执行此操作)

一切都运行良好，直到我尝试提取将查询发送到新函数的部分。奇怪的是，如果我在 psql 中手动执行查询，则查询运行时不会出现任何问题，但我遇到了语法错误。

新功能是这样的:

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;
    }
}

运行程序时得到的输出是这样的:

[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

传递给函数的SQLHSTMT在其调用函数中分配。输出中打印的查询工作得很好。

我的错误是什么？

Answer 1

问题在于您使用 sizeof() 来确定查询字符串的长度，但变量 query 被声明为 char * >，因此这将为您提供 4 或 8，具体取决于架构。

改用strlen()。