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.