我有这样一张桌子:
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 #include #include 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:%sn", sqlca.sqlerrm.sqlerrmc); return(sqlca.sqlcode); } fprintf(stdout, "Connected to ORACLE as user:%sn", user.arr); return (sqlca.sqlcode); } int book_not_found_function(char *path) { fprintf(stdout, "%sn", __FUNCTION__); } int path_update_success_function(char *book_id, char *new_path) { fprintf(stdout, "Update book %s path to %sn", book_id, new_path); } void other_function(void) { fprintf(stdout, "%sn", __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 failedn"); fprintf(stdout, "Oracle error %sn", sqlca.sqlerrm.sqlerrmc); return(sqlca.sqlcode); } EXEC SQL OPEN books_cursor; if (sqlca.sqlcode != 0) { fprintf(stdout, "Open cursor failedn"); fprintf(stdout, "Oracle error %sn", 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 %sn", old_path); book_not_found_function(old_path); return 0; } else if (sqlca.sqlcode != 0) { fprintf(stdout, "Oracle error %sn", 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 = %sn", 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 %sn", 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永远不会执行! 为什么这个?
谢谢你的帮助。
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是否忽略了重新定义。 您可以查看生成的文件以查看它是如何处理它的。 你也不会从中获得运行时错误,如果错误则不会(预)编译。
该表未更新,函数path_update_success_function和other_function永远不会执行! 为什么这个?
path_update_success
是为前两次调用而调用的,但不适用于第三次失败,而不是第二次调用,因为函数在接近它之前返回由于明显的declare cursor
而返回。 不会调用other_function
因为在您到达之前从函数返回的两个调用都是如此。 同样,表似乎没有更新,因为您在提交之前返回。 与SQL * Plus不同,Pro * C在退出时不会自动提交,因此存在隐式回滚。 另请注意,如果您确实进入了commit
,则release
断开连接,因此第二次出现未连接到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 failedn"); } 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 = %sn", 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 %sn", sqlca.sqlerrm.sqlerrmc); return 1; } if (books_found == 0) { fprintf(stdout, "No book found for this folder %sn", 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; }
似乎DECLARE CURSOR没有设置sqlca.sqlcode。 当你完成提取结束时,你得到的错误就是前一个错误。
因此,您不应该在DECLARE CURSOR之后测试错误,而是仅在OPEN CURSOR之后测试错误。
以上就是c/c++开发分享带有游标的Oracle Pro * C更新表失败相关内容,想了解更多C/C++开发(异常处理)及C/C++游戏开发关注计算机技术网(www.ctvol.com)!)。
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/c-cdevelopment/522880.html