title: Oracle Pro*C updating table with cursor failed
body: <p>I have a table like this:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>And i insert this data on it:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>I'm trying to update this table using Oracle PRO*C, but i can't! below is my code:</p>
  
  <pre><code>#include &lt;stdio.h&gt;
  #include &lt;stdlib.h&gt;
  #include &lt;string.h&gt;
  
  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;
  }
  </code></pre>
  
  <p>This program produce the output :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The table is not updated and the functions <strong>path_update_success_function</strong> and <strong>other_function</strong> are never executed! Why this?</p>
  
  <p>Thanks for your help.</p>