CURSOR Related SQLCODES

When you open a cursor , and if there are no rows to be selected, the cursor will not return NO DATA FOUND at this stage.
After fetching all the rows in a cursor :
sqlca.sqlcode returns 1403 NO DATA FOUND:It indicates the end of a cursor.

Connect to DB

The pro*c/c++ tool from oracle enables users to develop database driven application using C and C++.
The first step in writing a pro*c/c++ program is connecting to the database.
Below is the source code and compilation instructions for doing that.

FileName:-connect.pc
#include
#include
#include
#include
#include
#include
#include
EXEC SQL DECLARE MD_DEV DATABASE;
void sql_error()
{
size_t buff_len,msg_len;
char err_msg[250];
buff_len=sizeof(err_msg);
sqlglm(err_msg,&buff_len,&msg_len);
printf("SQL ERROR %s\n",err_msg);
exit(0);
}

int main()
{
EXEC SQL BEGIN DECLARE SECTION;
varchar usrname[20];
varchar password[20];
varchar loc[20];
EXEC SQL END DECLARE SECTION;
char sys_date[20];
memset(sys_date,' ',20);
EXEC SQL WHENEVER SQLERROR DO sql_error();

strcpy((char *) usrname.arr,"pbatch_md_int");
usrname.len=strlen( (char*)usrname.arr);
strcpy( (char *) password.arr,"pbatch_md_int");
password.len=strlen( (char*)password.arr);
strcpy( (char *) loc.arr,"oltpdev");
loc.len=strlen( (char*)loc.arr);

EXEC SQL CONNECT :usrname IDENTIFIED BY :password AT MD_DEV USING :loc;
EXEC SQL at MD_DEV select to_char(sysdate,'yyyy-mm-dd') into :sys_date from dual;
printf("SYSDATE is %.10s\n",sys_date);
return 0;
}

Compilation :
step1)
proc MODE=ORACLE SQLCHECK=SEMANTICS CHAR_MAP=VARCHAR2 CLOSE_ON_COMMIT=NO AUTO_CONNECT=NO DEFINE=UNIX INCLUDE=/usr/lib/gcc/x86_64-redhat-linux/4.1.2/include INCLUDE=/app/vector/include INCLUDE=/app/oracle/product/10.2.0/db_1/rdbms/public INCLUDE=/app/oracle/product/10.2.0/db_1/lib INCLUDE=/usr/include/linux RELEASE_CURSOR=NO HOLD_CURSOR=YES THREADS=NO PARSE=FULL method2.pc
step2)
gcc -g -I/app/oracle/product/10.2.0/db_1/precomp/public -L/app/oracle/product/10.2.0/db_1/lib -lclntsh method2.c

SQL WHENEVER SQLERROR

The SQL WHENEVER statement can be written anywhere in the program. The selected action remains in effect until the next time it's re-defined.

SQL WHENEVER provides a mechanism for error handling in a program. The sql whenever statement causes the program to take corrective acction when pro*c/oracle returns an error.

The syntax is

SQL WHENEVER SQLERROR  ;

The actions that can be taken are
1)CONTINUE in which case the program continues to execute the next instruction of the program.

SQL WHENEVER SQLERROR GOTO l2;
2) GOTO label: In which the program jumps to that label.These labels are what you define in you C program.
 
3)SQL WHENEVER SQLERROR DO sql_error();
In this case you specify a function name with or without arguments.So when oracle returns an error the code calls this function and inside the function you can call exit to quit the program Or return to try and execute the next statement in the program.

4) SQL WHENEVER SQLERROR STOP;
This statement would cause the program to exit immediately.

Source Code Example:
/*

* method1.pc
*
* Example using method 1 dynamic sql statements
*/
#include
#include
#include
#include
#include
#include
#include

/* Define constants for VARCHAR lengths. */
#define UNAME_LEN 20
#define PWD_LEN 40
EXEC SQL DECLARE DB_NAME DATABASE;
/* Declare variables.No declare section is needed if MODE=ORACLE.*/

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
/* VARCHAR is an Oracle-supplied struct */
varchar password[40];
varchar loc[40];
/* varchar can be in lower case also. */
/* Include the SQL Communications Area.
You can use #include or EXEC SQL INCLUDE. */
EXEC SQL END DECLARE SECTION;

/* Declare error handling function. */
void sql_error();
int total_queried=0;
main()
{
char temp_char[32];
char upd_sql[250]="update t_viol_tx_event set etc_account_id=12 where lane_tx_id=3990950";
/* Connect to ORACLE--
*/
strncpy((char *) username.arr, "int", 20);
/* Set the length component of the VARCHAR. */
username.len = strlen((char *) username.arr);
/* Copy the password. */
strncpy((char *) password.arr, "int",40);
password.len = strlen((char *) password.arr);
strncpy((char*)loc.arr,"OLTPDEV",40);
loc.len=strlen((char*)loc.arr);

/* Register sql_error() as the error handler. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
/* Connect to ORACLE. Program will call sql_error()
* if an error occurs when connecting to the default database.
*/

EXEC SQL CONNECT :username IDENTIFIED BY :password
at DB_NAME using :loc;
/* printf("\nConnected to ORACLE as user: %s\n", username.arr);*/
EXEC ORACLE OPTION (CHAR_MAP=CHARZ);
EXEC SQL AT DB_NAME EXECUTE IMMEDIATE :upd_sql;
/* Disconnect from ORACLE. */
EXEC SQL at DB_NAME COMMIT WORK RELEASE;
exit(0);
}

void sql_error()
{
char err_msg[128];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE; //you can change the action
buf_len=sizeof(err_msg);
sqlglm(err_msg,&buf_len,&msg_len);
printf("\n%s\n",err_msg);
EXEC SQL at DB_NAME ROLLBACK RELEASE;
exit(1); // you can use return
}

helpful sqlglm

While trying to debug an error message from some of my programs.
I've found the sqlglm() function to be very useful and more informative than SQLCODE.
I've never been able to find SQLCODE values in any oracle pro*c documentation.

For example the following statement gives an error.

exec sql at DB_NAME select 'XE' into :o_value from t_tran;
printf("sqlcode %ld \n",SQLCODE);
sqlcode -2112

Using sqlglm( ) after each sql statement I was able to understand the error message.
buff_len=sizeof(buff);
sqlglm(buff, &buff_len, &msg_len);
printf( "SQL ERROR %s\n",buff);

SQL ERROR SQL-02112: SELECT..INTO returns too many rows

Compilation of pro*c/c++ C program

The command to compile a pro*c/c++ written in C program

proc lines=yes char_map=string MODE=ORACLE RELEASE_CURSOR=NO HOLD_CURSOR=YES PARSE=NONE practice.pc

With PARSE=FULL
This option means that the C parser is used to parse the code.
You need not use EXEC SQL DECLARE BEGIN SECTION; and EXEC SQL DECLARE END SECTION;
Since the file is getting parsed you need to include the path of the header files.

proc MODE=ORACLE SQLCHECK=SEMANTICS CHAR_MAP=VARCHAR2 CLOSE_ON_COMMIT=NO AUTO_CONNECT=NO DEFINE=UNIX INCLUDE=/usr/lib/gcc/x86_64-redhat-linux/4.1.2/include INCLUDE=/app/vector/include INCLUDE=/app/oracle/product/10.2.0/db_1/rdbms/public INCLUDE=/app/oracle/product/10.2.0/db_1/lib INCLUDE=/usr/include/linux RELEASE_CURSOR=NO HOLD_CURSOR=YES THREADS=NO example_sqlgls.pc PARSE=FULL practice.pc

To compile on VC++ 8

proc PARSE=FULL DEFINE=_WSTDIO_DEFINED CHAR_MAP=VARCHAR2 CLOSE_ON_COMMIT=NO AUTO_CONNECT=NO DEFINE=WIN32 MODE=ORACLE SQLCHECK=SEMANTIC include=("C:\Program Files (x86)\Microsoft Visual Studio 9.0\VC\include",C:\vector_dms\vector_dms) C:\vector_dms\vector_dms\vector_dms_sql.pc

To  compile on VC++ 6

proc PARSE=FULL CHAR_MAP=VARCHAR2 CLOSE_ON_COMMIT=NO AUTO_CONNECT=NO DEFINE=WIN32 MODE=ORACLE SQLCHECK=SEMANTIC include=("C:\Program Files\Microsoft Visual Studio\VC98\Include",C:\vector_dms) C:\vector_dms\vector_dms_sql.pc

gcc -g -I/app/oracle/product/10.2.0/db_1/precomp/public -L/app/oracle/product/10.2.0/db_1/lib -lclntsh practice.c -o practice


Text of practic.pc
#include
#include
#include
#include
void sql_warning();
void sql_notfound();
void sql_error();

/*exec sql begin declare section;*/
char username[20];
char password[20];
char o_value[3];
char server_name[20] ="oltpdev";
char buff[300];
int buff_len,msg_len;
long SQLCODE;
exec sql declare DB_NAME DATABASE;

/*exec sql end declare section;*/

EXEC SQL WHENEVER SQLWARNING DO sql_warning();
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL WHENEVER NOT FOUND DO sql_notfound();


exec sql whenever sqlerror do sql_error();
exec sql whenever sqlwarning do sql_warning();
exec sql whenever not found do sql_notfound();


int main()
{
strcpy(username,"pbatch_md_int");
strcpy(password,"pbatch_md_int");
o_value[0]=o_value[1]=' ';
o_value[2]='\0';


exec sql connect :username identified by :password at DB_NAME using :server_name;

printf("sqlocde %ld \n",SQLCODE);
exec sql at DB_NAME select 'XE' into :o_value from t_dmv_file;
printf("sqlocde %ld \n",SQLCODE);
printf("connected and %s \n",o_value);
return 0;
}

void sql_warning()
{
printf("sqlocde %ld \n",SQLCODE);
buff_len=sizeof(buff);
sqlglm(buff, &buff_len, &msg_len);
printf( "SQL ERROR %s\n",buff);
printf("SQL_WARNING\n");
EXEC SQL WHENEVER SQLWARNING DO sql_warning();
return;
}

void sql_notfound()
{
printf("sqlocde %ld \n",SQLCODE);
buff_len=sizeof(buff);
sqlglm(buff, &buff_len, &msg_len);
printf( "SQL ERROR %s\n",buff);
printf("NOT FOUND\n");
EXEC SQL WHENEVER NOT FOUND DO sql_notfound();
return;

}

void sql_error()
{
printf("sqlocde %ld \n",SQLCODE);
buff_len=sizeof(buff);
sqlglm(buff, &buff_len, &msg_len);
printf( "SQL ERROR %s\n",buff);
EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}