Oracle Pro * C更新表与游标失败 [英] Oracle Pro*C updating table with cursor failed
问题描述
我有这样的表：
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更新此表，但我不能！以下是我的代码：
I'm trying to update this table using Oracle PRO*C, but i can't! below is my code:
#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 为什么？
The table is not updated and the functions path_update_success_function and other_function are never executed! Why this?
感谢您的帮助。
推荐答案
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 被调用。
You've fetched past the end of the resultset. Two rows match the cursor this time, so the first two fetches succeed; the third gets no data. This is expected and you shouldn't treat this as an error - just break the loop, which will also cause other_function to be called.
book_not_found_function // WHY THIS
因为将1403视为错误。如果您想在没有匹配项时调用此函数，则需要在循环中计数，并在需要时调用。
Because you treat 1403 as an error. If you want to call this function when there are no matches, you'll need to count in the loop, and call it afterwards if needed.
Declare cursor failed // WHY THIS
Oracle error ORA-01403: no data found // WHY THIS
sqlca.sqlcode 似乎仍然是从早期的抓取设置，是误导的。
sqlca.sqlcode seems to still be set from the earlier fetch, so this is misleading.
据我记忆，你通常在文件中声明一次光标，而不是每次调用函数时;不知道Pro * C是否忽略重定义。你可以看看生成的文件，看看它是如何处理它。
As far as I can remember you'd normally declare the cursor once in the file, not each time the function is called; not sure if Pro*C just ignores the redefinition. You can look at the generated file to see how it deals with it. You also won't get a runtime error from this, if it's wrong it won't (pre-)compile.
该表是一个没有更新和函数
path_update_success_function和other_function从不执行！
为什么？
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 断开你，所以第二次你会得到一个未连接到Oracle的错误。你应该决定提交/回滚一次真的，大概就在 main 结束。
path_update_success is called for the first two fetches, but not for the third which fails, and not for the second path because the function returns due to the apparent declare cursor before it gets near it. other_function isn't called because for both calls you return from the function before you can reach it. Similarly the table seems to not be updated because you return before you commit. Unlike SQL*Plus, Pro*C doesn't automatically commit on exit, so there is an implicit rollback. Note also that if you did get to the commit, the release disconnects you, so the second time you'd get a not-connected-to-Oracle error. You should decide to commit/rollback once really, probably right at the end of 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;
}
这篇关于Oracle Pro * C更新表与游标失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
