Monday, 27 January 2014

Connecting to database in Pro *C/C++

 



The sample code to connect the database…

#include<stdio.h>
#include<string.h>
//#include"sqlca.h"

EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
 char *username="vinoth_db";
 char *pwd="vinoth_db123";
 char *str="vino";
EXEC SQL END DECLARE SECTION;

int main()
{
   EXEC SQL WHENEVER SQLERROR GOTO sqlerr;
   EXEC SQL COnneCT :username IDENTIFIED BY :pwd USING :str;
   printf("Success Connecting with database!");
   return 0;

sqlerr:
   printf("Error occured in connecting with database");
}

OUTPUT:
Success Connecting with database!

DESCRIBTION OF THE PROGRAM:

EXEC SQL INCLUDE SQLCA;
#INCLUDE ”/PATH/TO/SQLCA”       
The above file is to be included in the proc file to connect with the databases. The above file will contain the information to connect with the databases. The difference between the first and second line is, the first line is a SQL statement. In that we no need to specify the path of the file SQL will refer it internally. But in the second line we need to specify the path of the file.


EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;

All the variable that are used with the EXEC SQL statements are need to be declared under these two lines. If variables are not declared under this lines then SQL precompile will show error. For eg in above code, if variable username is declared outside this then the precompilation will throw error.

EXEC SQL WHENEVER SQLERROR GOTO sqlerr;
Whenever the error occurs in the database then program control will be directed to sqlerr label. For instance when  you give the username or password wrongly then sqlerr label will be executed.

EXEC SQL COnneCT :username IDENTIFIED BY :pwd USING :str;
This statement is used to connect with the database. Here I have given connect as COnneCT. there is no special meaning for that. Since the proc is not case sensitive I have written like that. Using statement defines the string that can be used to connect the oracle DB’s.
you can use the input variable for username, pwd as char pointer or VARCHAR.
other forms of connect statement:

   EXEC SQL CONNECT : “vino_db/vino_db123@vino”
This is simplest form of connect statement. You can connect like this too. Just give the connection string to connect phrase.

Here the connection string is combination of username,pwd,oracle string.


Advanced connection technique:

EXEC SQL DECLARE DB_NAME1 DATABASE:
The above statement will give a unique name to the database connection. we have to use the above declared stamen while connecting to the database. For instance

exec sql connect:”abc/abc123” at DB_NAME1;
The above code will connect to the datebase with a name of db_name1;
Don’t use semicolon for at clause; The db_name1 is not host or program variable. It is just a identifier used by the oracle. No need to declare too. As a exception we can use host variable for at clause too. If we use a host variable no need to use the “Declare db_name1 database” statement.

EXEC SQL AT DB_NAME1 INSERT IN TO …………

Execute the sql operation by preceeding with the at clause. For cursors no need to use at clause for open,fetch etc. we need to mention only at the Declare statement of the cursor.