Saturday, 22 February 2014

Context in Pro *C/C++

 
Context is used to connect two or more databases and it allows to switch between them in run time.


CREATING CONTEXT:

Context is created using the pseudo column sql_cursor.


sql_cursor mycursor;

The above declaration should be done inside the declare section of pro c/c++.


ALLOCATE THE CONTEXT:

The context should be allocated before they were used in the program.


EXEC SQL CONTEXT ALLOCATE :mycursor;

Note that for cursor allocation the same above statement is used but the clause context is removed.


USE THE CONTEXT:

We can switch between the context by using the following declaration.
The use statement should come before connecting to the database. Allocate is much similar to declaration.  


EXEC SQL CONTEXT USE :my_cusor;


DEFAULT IN CONTEXT:

You can swith to the default cursor by the following statement.
EXEC SQL CONTEXT USE DEFAULT;
The default is the keyword no semicolon comes before that.


SAMPLE PROGRAM



 #include"stdio.h"
Exec sql include sqlca;
int  main()
{
   exec sql begin declare section;
   char *usr1="usr1/usr1pwd@servicename1";
   char *usr2="usr2/usr2pwd@servicename2";
   char *usr3="usr2/usr2pwd@servicename3";
   sql_context usrcontext1;
   sql_context usrcontext2;
   int avg_mark;
   exec sql end declare section;
   exec sql whenever sqlerror go to sqlerr;

   // Connecting to default context

   EXEC SQL CONNECT :usr1;

   exec sql select AVG_MAR into :avg_mark from MARK_LIST where STUD_ID LIKE '121';
   printf("\n Avg Mark at connected database: %d",avg_mark);

   //allocating and connecting to usrcontext1

   EXEC SQL CONTEXT ALLOCATE  :usrcontext1;
   EXEC SQL CONTEXT USE :usrcontext1;
   EXEC SQL CONNECT :usr2;


   exec sql select AVG_MAR into :avg_mark from MARK_LIST where STUD_ID LIKE '121';
   printf("\n Avg Mark at connected database: %d",avg_mark);


   //allocating and connecting to usrcontext2


   EXEC SQL CONTEXT ALLOCATE  :usrcontext2;
   EXEC SQL CONTEXT USE :usrcontext2;
   EXEC SQL CONNECT :usr2;


   exec sql select AVG_MAR into :avg_mark from MARK_LIST where STUD_ID LIKE '121';
   printf("\n Avg Mark at connected database: %d",avg_mark);

   // Now i am connecting to default context database

   EXEC SQL CONTEXT USE DEFAULT;
   exec sql select AVG_MAR into :avg_mark from MARK_LIST where STUD_ID LIKE '121';
   printf("\n Avg Mark at connected database: %d",avg_mark);

   // Now i am connecting to usrcontext2  database

   EXEC SQL CONTEXT USE :usrcontext2;
   exec sql select AVG_MAR into :avg_mark from MARK_LIST where STUD_ID LIKE '121';
   printf("\n Avg Mark at connected database: %d",avg_mark);

   //By this way you can connect mutiple database and you can swith between them

   return 0;

sqlerr:
   printf("\nError in DB \n");

   return 0;
}


OUTPUT:

Avg Mark at connected database: 85.55
Avg Mark at connected database: 51.22
Avg Mark at connected database: 90.15
Avg Mark at connected database: 85.55
Avg Mark at connected database: 90.15


In above output note that 1st and 4th  line avg was same. This indicates that are connected to same database schema.