sql - Oracle Pro*C 用游标更新表失败

Question

我有一个这样的表:

CREATE TABLE book_info (
     book_id                 VARCHAR(32)     not null,
     title                   varchar(255)    not null,
     author                  varchar(255)    not null,
     folder_path         varchar(255)    not null,
     primary key(book_id)
);

我在上面插入了这些数据:

insert into book_info values('BOOK1', 'APUE', 'Richard Stevens', '/home/user1/unix_programming_books');
insert into book_info values('BOOK2', 'Unix Network programming', 'Richard Stevens', '/home/user1/unix_programming_books');
insert into book_info values('BOOK3', 'Core Python Applications Programming', 'Wesley J. Chun', '/home/user1/python_programming_books');

我正在尝试使用 Oracle PRO*C 更新此表，但我不能!下面是我的代码:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE ORACA;

#define USER_LEN                        10                          
#define PASS_LEN                        10                          

VARCHAR user[USER_LEN];
VARCHAR pass[PASS_LEN];

#define STRCPY_TO_ORA(dest, source)\
        dest.len = strlen(source);\
        strcpy((char *)dest.arr, (const char *)source)

#define STRCPY_FROM_ORA(dest, source)\
        source.arr[source.len] = 0;\
        strcpy((char *)dest,(const char *)source.arr)

/* Connecting to the database */
int db_connection(char *db_user, char *db_pass)
{
        strncpy((char *) user.arr, db_user, USER_LEN);
        user.len = strlen((char *) user.arr);
        strncpy((char *) pass.arr, db_pass, PASS_LEN);
        pass.len = strlen((char *) pass.arr);

    EXEC SQL CONNECT :user IDENTIFIED BY :pass;
        if (sqlca.sqlcode != 0)
        {
        fprintf(stdout, "Connection failed:%s\n", sqlca.sqlerrm.sqlerrmc);
                return(sqlca.sqlcode);
        }
    fprintf(stdout, "Connected to ORACLE as user:%s\n", user.arr);
        return (sqlca.sqlcode);
}

int book_not_found_function(char *path)
{
    fprintf(stdout, "%s\n", __FUNCTION__);
}

int path_update_success_function(char *book_id, char *new_path)
{
    fprintf(stdout, "Update book %s path to %s\n", book_id, new_path);
}

void other_function(void)
{
    fprintf(stdout, "%s\n", __FUNCTION__);  
}

/* Updating books path */
int books_path_updating(char *old_path, char *new_path) 
{
    char book_id_string[32];
        EXEC SQL BEGIN DECLARE SECTION;
        varchar sql_old_path[255];
        varchar sql_new_path[255];
    varchar sql_book_id[32];
        EXEC SQL END DECLARE SECTION;

        STRCPY_TO_ORA(sql_old_path, old_path);
        STRCPY_TO_ORA(sql_new_path, new_path);

    /* Declare a cursor for the FETCH statement. */
    EXEC SQL DECLARE books_cursor CURSOR FOR
    SELECT BOOK_ID
        FROM BOOK_INFO 
        WHERE FOLDER_PATH = :sql_old_path;

    if (sqlca.sqlcode != 0)
    {
            fprintf(stdout, "Declare cursor failed\n");
        fprintf(stdout, "Oracle error %s\n", sqlca.sqlerrm.sqlerrmc);
            return(sqlca.sqlcode);
    }

    EXEC SQL OPEN books_cursor;

    if (sqlca.sqlcode != 0)
    {
            fprintf(stdout, "Open cursor failed\n");
        fprintf(stdout, "Oracle error %s\n", sqlca.sqlerrm.sqlerrmc);
            return(sqlca.sqlcode);
    }

    for ( ;; )
    {
        //EXEC SQL WHENEVER NOT FOUND DO break; // I used it but still nothing
        //EXEC SQL WHENEVER NOT FOUND GOTO not_found; // I used this too
        //EXEC SQL WHENEVER NOT FOUND DO continue; // I used this too

            /* Fetching data */
            EXEC SQL FETCH books_cursor 
            INTO :sql_book_id;
        if (sqlca.sqlcode == 1403)
        {
            fprintf(stdout, "No book found for this folder %s\n", old_path);    
            book_not_found_function(old_path);

            return 0;
        }

            else if (sqlca.sqlcode != 0)
            {
            fprintf(stdout, "Oracle error %s\n", sqlca.sqlerrm.sqlerrmc);
                    EXEC SQL CLOSE books_cursor;
                    return (sqlca.sqlcode);
            }

        else
        {
            STRCPY_FROM_ORA(book_id_string, sql_book_id);
                fprintf(stdout, "BOOK_ID = %s\n", book_id_string);
            /* Updating the path */
            EXEC SQL UPDATE BOOK_INFO
            SET FOLDER_PATH =:sql_new_path
            WHERE BOOK_ID =:sql_book_id;
            if (sqlca.sqlcode != 0)
                {
                fprintf(stdout, "Oracle error %s\n", sqlca.sqlerrm.sqlerrmc);
                        EXEC SQL CLOSE books_cursor;
                        return (sqlca.sqlcode);
                }
            else
            {
                path_update_success_function(book_id_string, new_path); 
            }
        }
    }
    EXEC SQL CLOSE books_cursor;
    other_function();

    EXEC SQL COMMIT WORK RELEASE;   
    return 0;   
}

int main(int argc, char **argv)
{
    db_connection("evariste", "123456");

    books_path_updating("/home/user1/unix_programming_books", "/home/user1/UNIX_PROGRAMMING_BOOKS");
    books_path_updating("/non_existing_path", "/non_existing_path");

    return 0;
}

该程序产生输出:

Connected to ORACLE as user:evariste
BOOK_ID = BOOK1
Update book BOOK1 path to /home/user1/UNIX_PROGRAMMING_BOOKS
BOOK_ID = BOOK2
Update book BOOK2 path to /home/user1/UNIX_PROGRAMMING_BOOKS
No book found for this folder /home/user1/unix_programming_books // WHEY THIS?
book_not_found_function // WHY THIS
Declare cursor failed   // WHY THIS 
Oracle error ORA-01403: no data found // WHY THIS

表未更新，并且函数 path_update_success_function 和 other_function 从未执行!为什么会这样？

感谢您的帮助。

Answer 1

Connected to ORACLE as user:evariste
BOOK_ID = BOOK1
Update book BOOK1 path to /home/user1/UNIX_PROGRAMMING_BOOKS
BOOK_ID = BOOK2
Update book BOOK2 path to /home/user1/UNIX_PROGRAMMING_BOOKS
No book found for this folder /home/user1/unix_programming_books // WHEY THIS?

您已获取超过结果集末尾的内容。这次有两行与游标匹配，因此前两次获取成功；第三个没有得到数据。这是预期的结果，您不应将其视为错误 - 只需中断循环，这也会导致调用 other_function。

book_not_found_function // WHY THIS

因为你将1403视为错误。如果您想在没有匹配项时调用此函数，则需要在循环中进行计数，然后在需要时调用它。

Declare cursor failed   // WHY THIS 
Oracle error ORA-01403: no data found // WHY THIS

sqlca.sqlcode 似乎仍然是从之前的获取中设置的，因此这是误导性的。

据我所知，您通常会在文件中声明一次光标，而不是每次调用该函数时；不确定 Pro*C 是否只是忽略重新定义。您可以查看生成的文件以了解它是如何处理的。您也不会从中得到运行时错误，如果错误，它将不会(预)编译。

The table is not updated and the functions path_update_success_function and other_function are never executed! Why this?

path_update_success 会在前两次提取时被调用，但在第三次提取失败时不会被调用，并且不会在第二条路径上被调用，因为该函数由于明显的声明光标而返回在它接近它之前。 other_function 未被调用，因为对于这两个调用，您在到达函数之前都会从该函数返回。同样，该表似乎没有更新，因为您在提交之前返回。与 SQL*Plus 不同，Pro*C 不会在退出时自动提交，因此存在隐式回滚。另请注意，如果您确实进行了commit，则release 会断开您的连接，因此第二次您会收到 not-connected-to-Oracle 错误。您应该真正决定提交/回滚一次，可能就在 main 的末尾。

<小时/>

未经测试的修改:

int books_path_updating(char *old_path, char *new_path)
{
    char book_id_string[32];
    int books_found;

    EXEC SQL BEGIN DECLARE SECTION;
        varchar sql_old_path[255];
        varchar sql_new_path[255];
        varchar sql_book_id[32];
    EXEC SQL END DECLARE SECTION;

    STRCPY_TO_ORA(sql_old_path, old_path);
    STRCPY_TO_ORA(sql_new_path, new_path);

    /* Declare a cursor for the FETCH statement */
    EXEC SQL DECLARE books_cursor CURSOR FOR
        SELECT BOOK_ID
        FROM BOOK_INFO
        WHERE FOLDER_PATH = :sql_old_path;

    EXEC SQL OPEN books_cursor;

    if (sqlca.sqlcode != 0)
    {
        fprintf(stdout, "Open cursor failed\n");
    }

    books_found = 0;
    while (sqlca.sqlcode == 0)
    {
        /* Fetching data */
        EXEC SQL FETCH books_cursor
        INTO :sql_book_id;

        if (sqlca.sqlcode != 0)
        {
            break;
        }

        STRCPY_FROM_ORA(book_id_string, sql_book_id);
            fprintf(stdout, "BOOK_ID = %s\n", book_id_string);

        /* Updating the path */
        EXEC SQL UPDATE BOOK_INFO
            SET FOLDER_PATH = :sql_new_path
            WHERE BOOK_ID = :sql_book_id;

        if (sqlca.sqlcode != 0)
        {
            break;
        }

        /* Track how many books we found, though we only really care later that
         * this is non-zero */
        books_found++;

        path_update_success_function(book_id_string, new_path);
    }

    EXEC SQL CLOSE books_cursor;

    /* Check for and display error, but ignore 1403 as this just indicates the
     * end of the result set */
    if ((sqlca.sqlcode != 0) && (sqlca.sqlcode != 1403))
    {
        fprintf(stdout, "Oracle error %s\n", sqlca.sqlerrm.sqlerrmc);
        return 1;
    }

    if (books_found == 0)
    {
        fprintf(stdout, "No book found for this folder %s\n", old_path);
        book_not_found_function(old_path);
        return 0;
    }

    other_function();

    return 0;
}

int main(int argc, char **argv)
{
    int rc;

    rc = db_connection("evariste", "123456");

    /* Only do the first path if we didn't get an error connecting */
    if (rc == 0)
    {
        rc == books_path_updating("/home/user1/unix_programming_books",
            "/home/user1/UNIX_PROGRAMMING_BOOKS");
    }

    /* Only do the next path if we didn't get an error from the previous one */
    if (rc == 0)
    {
        rc = books_path_updating("/non_existing_path",
            "/non_existing_path");
    }

    /* Decide whether to rollback or commit; this assumes you don't want to
     * keep any changes if there are any errors */
    if (rc != 0)
    {
        EXEC SQL ROLLBACK WORK RELEASE;
        return 1;
    }

    EXEC SQL COMMIT WORK RELEASE;
    return 0;
}