JRun Administrator's Guide

http://livedocs.adobe.com/jrun/4/JRun_Administrators_Guide/contents.htm

JRun Overview of filters

http://livedocs.adobe.com/jrun/4/Programmers_Guide/filters2.htm
Filters process request objects before they get to the server or process response objects after they leave the server and before they are returned to the client. You can use filters to do the following tasks:

Take flow-control logic out of web application components.
Examine and modify the request object before the server receives it from the client.
Examine and modify the response object before the client receives it from the server.
Change the content of the response.
...

Specifically, filters are often implemented as:
Request dispatchers
User authenticators and authorizers
Request and response loggers and auditors
Form validators
Image converters
Data compressors and decompressors
Data encryptors and decryptors
Response output tokenizers
Triggers for resource access
XML transformers
Content localizers

Using the getResource method

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet19.htm
You can use the ServletContext object's getResource method to include content in your servlet. The getResource method returns a URL object. Then you can use the URL object to access the content. One advantage of using the URL object is that you can parse the content before returning it to the browser. You can also use this technique to include content that is otherwise not accessible to the users directly, such as files in the /WEB-INF directory.

JRun Passing control

Using the RequestDispatcher
Using the sendRedirect method
http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet17.htm

Using databases - Understanding JDBC

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet16.htm

JRun connection pooling mechanism

...

import javax.naming.*;
import javax.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
...
Connection dbConnection = null;
ResultSet dbResultSet = null;
ResultSetMetaData rsmd = null;
try {
��InitialContext ctx = new InitialContext();
��DataSource ds = (DataSource) ctx.lookup("compass");
��dbConnection = ds.getConnection();
��Statement stmt = dbConnection.createStatement();
��dbResultSet = stmt.executeQuery("SELECT * FROM user");
��rsmd = dbResultSet.getMetaData();
} catch (Exception e) {
}
...
//process your result set and result set metadata here

JRun Handling exceptions

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet12.htm
Exceptions are errors detected within your servlet. Exceptions can occur at run-time, such as when a servlet processes form data, or at compile-time, such as when you pre-compile JSPs. You should catch compile-time exceptions before putting your web application into production. This section describes how to handle run-time exceptions in servlets and JSPs.
In a Java servlet, an exception is represented by an instance of the class javax.servlet.ServletException.

You can define how a web application handles errors using the error-page element in the WEB-INF/web.xml file. You can also define error handling for all web applications on the JRun server by adding error-page elements to the SERVER-INF/default-web.xml file.
The error-page element defines exceptions by exception type or by error code, as the following sections describe. The order of these elements in the web.xml file determines the error handler. JRun redirects the error processing to the location specified by the first error-page element that matches the error-code or exception-type.



��java.io.FileNotFoundException
��/error-pages/404.jsp





��500
��/error-pages/servererror.jsp

JRun Writing results back to the client

Working with special characters
Setting headers
Using the PrintWriter
Using the ServletOutputStream
http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet10.htm

Writing out to files

Writing to the web application's root directory

Writing to the web application's temporary directory

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet11.htm

JRun Working with sessions URL Rewriting

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet13.htm#1180255

Processing requests

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet9.htm
Servlet requests can come in a variety of ways. Your servlets are responsible for accepting those requests, parsing client input, and then generating a response. The methods of passing data to a servlet include:

Query string parameters
Form input
Request headers

JRun HTTP requests and responses

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet8.htm

JRun Working with servlets - Using initialization parameters

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet7.htm

Understanding servlet mappings

http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet6.htm
To access any servlet on a JRun server, you can use the implicit /servlet mapping as long as you store the servlet in the /WEB-INF/classes directory. For example, if you store the MyServlet.class file in /WEB-INF/classes, you can request http://yourhost/servlet/MyServlet to request that servlet.
This mapping is predefined in the default-web.xml file, as the following example shows:

��ServletInvoker
��/servlet/

Understanding URLs

JRun security architecture

Using a customized security implementation
http://livedocs.adobe.com/jrun/4/JRun_Administrators_Guide/authentic5.htm
http://livedocs.adobe.com/jrun/4/JRun_Administrators_Guide/authentic.htm

Using HttpServlet
http://livedocs.adobe.com/jrun/4/Programmers_Guide/techniques_servlet4.htm

J-Run Custom Load Balancing

http://livedocs.adobe.com/jrun/4/JRun_Service_Pack_1_Guide/customloadbalancing.htm

Differences Between JRun 3.1 and JRun 4

http://livedocs.adobe.com/jrun/4/JRun_Service_Pack_1_Guide/migration.htm

The JAAS-based JRun 4 security architecture is incompatible with the proprietary JRun 3.x security architecture

http://livedocs.adobe.com/jrun/4/JRun_Service_Pack_1_Guide/4relnotes9.htm
http://livedocs.adobe.com/jrun/4/JRun_Service_Pack_1_Guide/4relnotes9.htm#1177288
To use a custom JRun 3.x security implementation class with JRun 4:
Confirm that your customized security implementation can be used in JRun 4 and modify as necessary, as described in "Usage notes".
Open the JRun server's SERVER-INF/jrun.xml file.
Comment out the JRunUserManager service.
Add the following JRunUserManager service, specifying the fully qualified class name of your customized JRun 3.x security implementation in the authenticationManager and authenticationInterface attributes:

true
authentication.ClassName

authentication.ClassName


Save the jrun.xml file.
Copy your customized security implementation to the jrun_root/servers/lib directory.

Using multiple JVM combinations

http://livedocs.adobe.com/jrun/4/JRun_Service_Pack_1_Guide/4relnotes7.htm
You can use more than one set of JVM configuration properties for the JRun servers in a JRun installation. This feature is documented in Installing JRun, and is available from the command line and when running JRun as a Windows service.

For the following procedures, you can put the custom configuration file in the jrun_root/bin directory, or you can specify a complete path to the file.

To set up and use a custom JVM configuration from the command line:
Copy the jrun_root/bin/jvm.config file to a new filename.
Modify the new .config file to meet your needs.
At a command prompt or shell, start a JRun server using the following command:
jrun -config custom_jvm.config -start jrun_server

where custom_jvm.config is the name of your JVM configuration file, and jrun_server is the name of your JRun server. You can specify one or more server names separated by commas.

To use a custom JVM configuration when starting JRun as a Windows service:
Install the service using the following command:
jrunsvc -install jrun_server service-name service-display service-description
-config custom_jvm.config

To use a custom JNDI security class

http://livedocs.adobe.com/jrun/4/JRun_Service_Pack_1_Guide/4relnotes7.htm

DBMS_JAVA Package

Java Stored Procedures Application Example

http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/appendixa.htm

http://www.stanford.edu/dept/itss/docs/oracle/10g/java.101/b12021/dbms_java.htm

Check These Out!

http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/cheight.htm#sthref484

Frequently asked questions: Oracle JVM and Java Stored Procedures

http://www.oracle.com/technology/tech/java/jsp/faq_jvm_java_stored_procedures.html

Check This Out!

Using the SAVEPOINT Statement

http://www.csee.umbc.edu/help/oracle8/server.815/a68022/trans.htm#388

http://www.oracle.com.cn/other/9ionlinedoc/appdev.920/a97269/toc.htm

EXEC SQL SAVEPOINT start_delete1;

EXEC SQL DELETE FROM mail_list
WHERE stat = 'INACTIVES';
if (sqlca.sqlerrd[2] < 25) /* check number of rows deleted */
printf("Number of rows deleted is %d\n", sqlca.sqlerrd[2]);
else
{
printf("Undoing deletion %d rows\n", sqlca.sqlerrd[2]);
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
EXEC SQL ROLLBACK TO SAVEPOINT start_delete1;
}

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL COMMIT WORK RELEASE;

C Programming Tips!

int a,b,c,d;
a=b=c=d=10;

(a>b?a:b)
while(i++>10)a=a+10;
for(i=0;i++<10;)a=a+10;
for(i=0,j=0;i++<10;)a=a+10;
for(;;) infinate loop
for(;a=="";)
break;
continue;

switch(i)
{
case 1:
printf(...
break;

case 2:
....

default:
...
}


goto sos;

sos:
....;

Pointers
========
int i=3,*j,**k;
j=&i;
k=&j;

value 3 of obtained from
i, *(&i), *j, **k

**k = 3+1;
printf(i) gives 4

header file
===========
trigger init and exit functions.
#pragma startup func1
#pragma exit func2


passing an array to a function
==============================
int num[]={1,2,3,4,5};
disp(&num[0],5);//or disp(num,5);//*num or *(num+0) gives zeroth element.


disp(int *j, int n){
for(i=0;i<=n-1;i++)
{printf("%d", *j);j++;}
}

array pointers
==============

s[2][1] equals to
*(s[2] + 1)
*(*(s + 2) + 1)

VARCHAR Usage in Pro*C

VARCHAR Index[UNAME_LEN];
VARCHAR Path[PATH_LEN];
VARCHAR Name[NAME_LEN];

strncpy((char *) Index.arr, Index, INDEX_LEN);
Index.len = (unsigned short) strlen((char *)Index.arr);

strncpy((char *) Path.arr, pPath, PATH_LEN);
Path.len = (unsigned short) strlen((char *) Path.arr);

strncpy((char *) Name.arr, pName, NAME_LEN);
Name.len = (unsigned short) strlen((char *) Name.arr);

EXEC SQL INSERT INTO TABLE (Index,File,Name)
VALUES(:Index, :File, :Name);

#Define problem in compiling pro*c code

http://www.orafaq.com/forum/t/35866/0/

VARCHAR pointers in Pro*C

http://www.orafaq.com/usenet/comp.databases.oracle/1992/01/26/0107.htm
Check This Out

Date in Pro*C

char another_dt[21];
EXEC SQL VAR another_dt IS STRING(21);


EXEC SQL BEGIN DECLARE SECTION;
varchar dt[21];
EXEC SQL END DECLARE SECTION;


strcpy(user,"XXXXX@xxx.xxx");
strcpy(passwd,"XXXXX");

EXEC SQL WHENEVER SQLERROR DO print_error_msg();
EXEC SQL WHENEVER SQLWARNING DO print_warning_msg();

EXEC SQL CONNECT :user IDENTIFIED BY :passwd;

memset(dt.arr,'',21);
EXEC SQL SELECT TO_CHAR(sysdate,'DD/MON/YYYY HH24:MI:SS') into :dt from dual;
EXEC SQL SELECT TO_CHAR(sysdate,'DD/MON/YYYY HH24:MI:SS') into :another_dt from dual;
dt.len = strlen(dt.arr);
printf("Date is :%sn",dt.arr);
printf("Another Date is :%sn",another_dt);

Host Arrays and Structs in Stored Procedures

#include
#include
#include
#include
#include
#include

#DEFINE SQL_LEN 300
#DEFINE REC_LEN 30
#DEFINE MAX(A,B) ((A) > (B) ? (A) : (B))

EXEC SQL BEGIN DECLARE SECTION;
struct record {
VARCHAR npa[3];
VARCHAR npa[3];
...
}

typedef struct record recorddata;
VARCHAR host_string[SQL_LEN];
EXEC SQL END DECLARE SECTION;

strcpy((char*)host_string.arr, "BEGIN ICVC.SP_LOAD_TTC (:npa, :npx, ...); END;");
host_string.len = strlen((char*)host_string.arr);

void main()
{
insertdata();
}
void insertdata()
{
int i, cnt;
char *str;
recorddata rec_in[REC_LEN];

str = (char *)malloc (25 * sizeof(char));
for (i = 0; i < REC_LEN; i++)
{
sprintf(str, "001");
strcpy (rec_in[i].npx.arr, str);
rec_in[i].npx.len = strlen (rec_in[i].npx.arr);
rec_in[i].npx.arr[rec_in[i].npx.len] = '\0';

sprintf(str, "001");
strcpy (rec_in[i].npa.arr, str);
rec_in[i].npa.len = strlen (rec_in[i].npa.arr);
rec_in[i].npa.arr[rec_in[i].npa.len] = '\0';

...

}
free (str);
/*
EXEC SQL EXECUTE
BEGIN
ICVC.SP_LOAD_TTC (:rec_in,, :ret_code);
END;
END-EXEC;

OR

EXEC SQL ARRAYLEN int_tab (REC_LEN) EXECUTE;
char *stmt3 = "begin pkg.proc1(:v1, :v2, :v3); end; ";
EXEC SQL PREPARE s3 FROM :host_string;
EXEC SQL EXECUTE s3 USING :rec_in, :REC_LEN, :rec_in;

OR
*/




EXEC SQL CALL ICVC.SP_LOAD_TTC (:rec_in, :status :ret_code);
if(status==0){

}



==============================================================
Default Value
char v_location[12] = {'\0'};

CREATE OR REPLACE PACKAGE pkg AS
TYPE tab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;
PROCEDURE proc1 (parm1 tab, parm2 NUMBER, parm3 tab);
END;

The following Pro*C/C++ function demonstrates how host arrays can be used to determine how many times a given PL/SQL block is executed. In this case, the PL/SQL block will be executed 3 times resulting in 3 new rows in the emp table.

func1()
{
int empno_arr[5] = {1111, 2222, 3333, 4444, 5555};
char *ename_arr[3] = {"MICKEY", "MINNIE", "GOOFY"};
char *stmt1 = "BEGIN INSERT INTO emp(empno, ename) VALUES :b1, :b2; END;";

EXEC SQL PREPARE s1 FROM :stmt1;
EXEC SQL EXECUTE s1 USING :empno_arr, :ename_arr;
}

The following Pro*C/C++ function demonstrates how to bind a host array to a PL/SQL index table through dynamic method 2. Note the presence of the ARRAYLEN...EXECUTE statement for all host arrays specified in the EXEC SQL EXECUTE statement.

func2()
{
int ii = 2;
int int_tab[3] = {1,2,3};
int dim = 3;
EXEC SQL ARRAYLEN int_tab (dim) EXECUTE;

char *stmt2 = "begin pkg.proc1(:v1, :v2, :v3); end; ";

EXEC SQL PREPARE s2 FROM :stmt2;
EXEC SQL EXECUTE s2 USING :int_tab, :ii, :int_tab;
}

However the following Pro*C/C++ function will result in a precompile-time warning because there is no ARRAYLEN...EXECUTE statement for int_arr.

func3()
{
int int_arr[3];
int int_tab[3] = {1,2,3};
int dim = 3;
EXEC SQL ARRAYLEN int_tab (dim) EXECUTE;

char *stmt3 = "begin pkg.proc1(:v1, :v2, :v3); end; ";

EXEC SQL PREPARE s3 FROM :stmt3;
EXEC SQL EXECUTE s3 USING :int_tab, :int_arr, :int_tab;
}

Pro*C Documentation

Some Good References
http://wtcis.wtamu.edu/oracle/win.102/b14321.pdf
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28427/pc_08arr.htm
http://www.ibm.com/developerworks/data/library/techarticle/0309greenstein/0309greenstein.html
http://www.sc.ehu.es/siwebso/KZCC/Oracle_10g_Documentacion/appdev.101/a97269/pc_07pls.htm

Inserting and Fetching Rows by Using the Array and loading into Stored Procedure in Pro*C

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-proc.html

http://www.lsbu.ac.uk/oracle/oracle7/api/doc/PC_22/ch03a.htm

http://forums.oracle.com/forums/thread.jspa?messageID=1661451

http://asktom.oracle.com/pls/asktom/
f?p=100:11:0::::P11_QUESTION_ID:208012348074

http://www.filibeto.org/sun/lib/nonsun/oracle/
11.1.0.6.0/B28359_01/appdev.111/b28427/pc_08arr.htm

how to compile?
&
int emp_number[50];
char name[50][11];
/* ... */
EXEC SQL INSERT INTO emp(emp_number, name)
VALUES (:emp_number, :emp_name);
which will insert all the 50 tuples in one go.
*/

#DEFINE SQL_LEN 300


EXEC SQL BEGIN DECLARE SECTION;

struct record {
VARCHAR npa[50][3];
VARCHAR npa[50][3];
...
}
typedef struct record recorddata;

VARCHAR host_string[SQL_LEN];

EXEC SQL END DECLARE SECTION;

strcpy((char*)host_string.arr, "SCHEMA.PROCEDURENAME(:records[5].npa, :records[5].npx, ...)");
host_string.len = strlen((char*)host_string.arr);

EXEC SQL PREPARE stmt FROM :host_string;


/*
EXEC SQL EXECUTE stmt USING :npa, :npx, ...;

OR
rows_to_insert = 25; /* set FOR-clause variable */
EXEC SQL FOR :rows_to_insert EXECUTE stmt USING :npa, :npx, ...;
===============================================================

void insertdata()
{
int i, cnt, file_cnt=0, temp_cnt=0;
char *str;
recorddata rec_in;

while(loopFile){
file_cnt = file_cnt + 1;

/* To store temporary strings */
str = (char *)malloc (25 * sizeof(char));

/* Fill the array elements to insert */
for (i = 0; i < 50; i++)
{
temp_cnt = tmp_cnt +1;

sprintf(str, "001");
strcpy (rec_in.npx[i].arr, str);
rec_in.npx[i].len = strlen (rec_in.npx[i].arr);
rec_in.npx[i].arr[rec_in.npx[i].len] = '\0';

sprintf(str, "001");
strcpy (rec_in.npa[i].arr, str);
rec_in.npa[i].len = strlen (rec_in.npa[i].arr);
rec_in.npa[i].arr[rec_in.npa[i].len] = '\0';

...
}
if (temp_cnt == 50){
free (str);

EXEC SQL EXECUTE SCHEMA.PROCEDURENAME(:npa, :npx, ...) USING :rec_in.npa, :rec_in.npx, ...;
temp_cnt=0;
}
}end while

Dynamic SQL/Oracle Tutorial

http://www.cs.ubc.ca/~ramesh/cpsc304/tutorial/DynamicSQL/dynamicSQL1.html

PREPARE:


VARCHAR host_string[64];
char *examCount = "SELECT driver_sin, count(exam_score) FROM exam WHERE exam_type = :typ GROUP BY driver_sin";
strcpy((char*)host_string.arr, "INSERT INTO branch VALUES (:bid, :bname, :baddr, :bcity, :bphone)");
host_string.len = strlen((char*)host_string.arr);

EXEC SQL PREPARE stmt FROM :host_string;
EXEC SQL PREPARE stmt2 FROM "INSERT INTO branch VALUES (:bid, :bname, :baddr, :bcity, :bphone)";
EXEC SQL PREPARE stmt3 FROM :examCount;



EXECUTE:


int branchID;
char branchName[20];
char branchAddr[50];
char branchCity[20];
int branchPhone;
short int branchAddr_ind;
short int branchPhone_ind;
int branchID2;
char branchName2[20];
char branchAddr2[50];
char branchCity2[20];
int branchPhone2;
short int branchAddr2_ind;
short int branchPhone2_ind;

/* get values for your host variables */

EXEC SQL EXECUTE stmt USING :branchID, :branchName, :branchAddr:branchAddr_ind, :branchCity, :branchPhone:branchPhone_ind;

EXEC SQL EXECUTE stmt USING :branchID2, :branchName2, :branchAddr2:branchAddr2_ind, :branchCity2, :branchPhone2:branchPhone2_ind;

A statement can be re-executed using different host variables without needing a re-PREPARE. A re-PREPARE is only necessary when you want to associate a statement name with another statement.

Using the Object Type Translator in Pro*C

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/ott.htm#420181


CREATE TYPE my_varray AS VARRAY(5) of integer;



CREATE TYPE object_type AS OBJECT

(object_name VARCHAR2(20));



CREATE TYPE my_table AS TABLE OF object_type;



CREATE TYPE many_types AS OBJECT

( the_varchar VARCHAR2(30),

the_char CHAR(3),

the_blob BLOB,

the_clob CLOB,

the_object object_type,

another_ref REF other_type,

the_ref REF many_types,

the_varray my_varray,

the_table my_table,

the_date DATE,

the_num NUMBER,

the_raw RAW(255));


and an intype file that includes:
CASE = LOWER

TYPE many_types

EXEC SQL FOR

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/arr.htm#390

rows_to_insert = 25; /* set FOR-clause variable */
EXEC SQL FOR :rows_to_insert /* will process only 25 rows */
INSERT INTO emp (ename, sal)
VALUES (:emp_name, :salary);

Arrays in Pro*C

#include
#include

#include

#define NAME_LENGTH 20
#define ARRAY_LENGTH 5
/* Another way to connect. */
char *username = "SCOTT";
char *password = "TIGER";

/* Declare a host structure tag. */
struct
{
int emp_number[ARRAY_LENGTH];
char emp_name[ARRAY_LENGTH][NAME_LENGTH];
float salary[ARRAY_LENGTH];
} emp_rec;

/* Declare this program's functions. */
void print_rows(); /* produces program output */
void sql_error(); /* handles unrecoverable errors */


main()
{
int num_ret; /* number of rows returned */

/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error("Connect error:");

EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n", username);


EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error:");
/* Declare a cursor for the FETCH. */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT empno, ename, sal FROM emp;

EXEC SQL OPEN c1;

/* Initialize the number of rows. */
num_ret = 0;

/* Array fetch loop - ends when NOT FOUND becomes true. */
EXEC SQL WHENEVER NOT FOUND DO break;

for (;;)
{
EXEC SQL FETCH c1 INTO :emp_rec;

/* Print however many rows were returned. */
print_rows(sqlca.sqlerrd[2] - num_ret);
num_ret = sqlca.sqlerrd[2]; /* Reset the number. */
}
/* Print remaining rows from last fetch, if any. */
if ((sqlca.sqlerrd[2] - num_ret) > 0)
print_rows(sqlca.sqlerrd[2] - num_ret);

EXEC SQL CLOSE c1;
printf("\nAu revoir.\n\n\n");

/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}


void
print_rows(n)
int n;
{
int i;

printf("\nNumber Employee Salary");
printf("\n------ -------- ------\n");

for (i = 0; i < n; i++)
printf("%-9d%-15.15s%9.2f\n", emp_rec.emp_number[i],
emp_rec.emp_name[i], emp_rec.salary[i]);

}


void
sql_error(msg)
char *msg;
{
EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\n%s", msg);
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}

Remote Access

EXEC SQL EXECUTE
BEGIN
proc_name@database_link(:emp_id, :increase);
END;
END-EXEC;

Calling a stored procedure in Pro*C

#include
#include

EXEC SQL INCLUDE sqlca.h;


typedef char asciz[20];
typedef char vc2_arr[11];

EXEC SQL BEGIN DECLARE SECTION;
/* User-defined type for null-terminated strings */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;

/* User-defined type for a VARCHAR array element. */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;

asciz username;
asciz password;
int dept_no; /* which department to query? */
vc2_arr emp_name[10]; /* array of returned names */
vc2_arr job[10];
float salary[10];
int done_flag;
int array_size;
int num_ret; /* number of rows returned */
EXEC SQL END DECLARE SECTION;

long SQLCODE;



void print_rows(); /* produces program output */
void sql_error(); /* handles unrecoverable errors */



main()
{
int i;
char temp_buf[32];

/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
strcpy(username, "scott");
strcpy(password, "tiger");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n\n", username);
printf("Enter department number: ");
gets(temp_buf);
dept_no = atoi(temp_buf);/* Print column headers. */
printf("\n\n");
printf("%-10.10s%-10.10s%s\n", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%s\n", "--------", "---", "------");

/* Set the array size. */
array_size = 10;

done_flag = 0;
num_ret = 0;

/* Array fetch loop.
* The loop continues until the OUT parameter done_flag is set.
* Pass in the department number, and the array size--
* get names, jobs, and salaries back.
*/
for (;;)
{
EXEC SQL EXECUTE
BEGIN calldemo.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;

print_rows(num_ret);

if (done_flag)
break;
}

/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
void
print_rows(n)
int n;
{
int i;

if (n == 0)
{
printf("No rows retrieved.\n");
return;
}

for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2f\n",
emp_name[i], job[i], salary[i]);
}

/* Handle errors. Exit on any error. */
void
sql_error()
{
char msg[512];
int buf_len, msg_len;


EXEC SQL WHENEVER SQLERROR CONTINUE;

buf_len = sizeof(msg);
sqlglm(msg, &buf_len, &msg_len);

printf("\nORACLE error detected:");
printf("\n%.*s \n", msg_len, msg);

EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}

Using Embedded PL/SQL in Pro*C

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/plsql.htm#1140

VARCHAR Variables and Pointers in Pro*C

VARCHAR emp_name1[10]; /* VARCHAR variable */
VARCHAR *emp_name2; /* pointer to VARCHAR */

strcpy(emp_name1.arr, "VAN HORN");
emp_name1.len = strlen(emp_name1.arr);

emp_name2 = malloc(sizeof(short) + 10) /* len + arr */
strcpy(emp_name2->arr, "MILLER");
emp_name2->len = strlen(emp_name2->arr);

Or, to make emp_name2 point to an existing VARCHAR (emp_name1 in this case), you could code the assignment

emp_name2 = &emp_name1;

Character Pointer in Pro*C

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/dev.htm#2416

...
char *p_name1;
char *p_name2;
...
p_name1 = (char *) malloc(11);
p_name2 = (char *) malloc(11);
strcpy(p_name1, " ");
strcpy(p_name2, "0123456789");

Trailing Blanks Error in Pro*C

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/dev.htm#2416

char emp_name[10];
...
strcpy(emp_name, "MILLER"); /* WRONG! Note no blank-padding */
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES
(1234, :emp_name, 20);

you will find that the string "MILLER" was inserted as "MILLER\0\0\0\0" (with four null bytes appended to it). This value does not meet the following search condition:

. . . WHERE ename = 'MILLER';

To INSERT the character array when DBMS is set to V6 or CHAR_MAP is set to VARCHAR2, you should execute the statements

strncpy(emp_name, "MILLER ", 10); /* 4 trailing blanks */
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES
(1234, :emp_name, 20);

input data in a character array must be null-terminated. So, make sure that your data ends with a null.

char emp_name[11]; /* Note: one greater than column size of 10 */
...
strcpy(emp_name, "MILLER"); /* No blank-padding required */
EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES
(1234, :emp_name, 20);

Usage of the CHAR_MAP in Pro*C

char ch_array[5];

strncpy(ch_array, "12345", 5);
/* char_map=charz is the default in Oracle7 and Oracle8 */
EXEC ORACLE OPTION (char_map=charz);
/* Select retrieves a string "AB" from the database */
SQL SELECT ... INTO :ch_array FROM ... WHERE ... ;
/* ch_array == { 'A', 'B', ' ', ' ', '\0' } */

strncpy (ch_array, "12345", 5);
EXEC ORACLE OPTION (char_map=string) ;
/* Select retrieves a string "AB" from the database */
EXEC SQL SELECT ... INTO :ch_array FROM ... WHERE ... ;
/* ch_array == { 'A', 'B', '\0', '4', '5' } */

strcpy( ch_array, "12345", 5);
EXEC ORACLE OPTION (char_map=charf);
/* Select retrieves a string "AB" from the database */
EXEC SQL SELECT ... INTO :ch_array FROM ... WHERE ... ;
/* ch_array == { 'A', 'B', ' ', ' ', ' ' } */

Passing VARCHAR Variables to a Function in Pro*C

VARCHAR emp_name[20];
...
emp_name.len = 20;
SELECT ename INTO :emp_name FROM emp
WHERE empno = 7499;
...
print_employee_name(&emp_name); /* pass by pointer */
...

print_employee_name(name)
VARCHAR *name;
{
...
printf("name is %.*s\n", name->len, name->arr);
...
}

Structure Pointers

struct EMP_REC
{
int emp_number;
float salary;
};
char *name = "HINAULT";
...
struct EMP_REC *sal_rec;
sal_rec = (struct EMP_REC *) malloc(sizeof (struct EMP_REC));
...
EXEC SQL SELECT empno, sal INTO :sal_rec
FROM emp
WHERE ename = :name;

printf("Employee number and salary for %s: ", name);
printf("%d, %g\n", sal_rec->emp_number, sal_rec->salary);

Pointer Variables in Pro*C

struct
{
int i;
char c;
} structvar;
int *i_ptr;
char *c_ptr;
...
main()
{
i_ptr = &structvar.i;
c_ptr = &structvar.c;
/* Use i_ptr and c_ptr in SQL statements. */
...

Cursor and a Structure in Pro*C

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/dev.htm#2416
/*
* sample2.pc
*
* This program connects to ORACLE, declares and opens a cursor,
* fetches the names, salaries, and commissions of all
* salespeople, displays the results, then closes the cursor.
*/

#include
#include

#define UNAME_LEN 20
#define PWD_LEN 40

/*
* Use the precompiler typedef'ing capability to create
* null-terminated strings for the authentication host
* variables. (This isn't really necessary--plain char *'s
* does work as well. This is just for illustration.)
*/
typedef char asciiz[PWD_LEN];

EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE;
asciiz username;
asciiz password;

struct emp_info
{
asciiz emp_name;
float salary;
float commission;
};


/* Declare function to handle unrecoverable errors. */
void sql_error();


main()
{
struct emp_info *emp_rec_ptr;

/* Allocate memory for emp_info struct. */
if ((emp_rec_ptr =
(struct emp_info *) malloc(sizeof(struct emp_info))) == 0)
{
fprintf(stderr, "Memory allocation error.\n");
exit(1);
}

/* Connect to ORACLE. */
strcpy(username, "SCOTT");
strcpy(password, "TIGER");

EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--");

EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n", username);

/* Declare the cursor. All static SQL explicit cursors
* contain SELECT commands. 'salespeople' is a SQL identifier,
* not a (C) host variable.
*/
EXEC SQL DECLARE salespeople CURSOR FOR
SELECT ENAME, SAL, COMM
FROM EMP
WHERE JOB LIKE 'SALES%';

/* Open the cursor. */
EXEC SQL OPEN salespeople;

/* Get ready to print results. */
printf("\n\nThe company's salespeople are--\n\n");
printf("Salesperson Salary Commission\n");
printf("----------- ------ ----------\n");

/* Loop, fetching all salesperson's statistics.
* Cause the program to break the loop when no more
* data can be retrieved on the cursor.
*/
EXEC SQL WHENEVER NOT FOUND DO break;

for (;;)
{
EXEC SQL FETCH salespeople INTO :emp_rec_ptr;
printf("%-11s%9.2f%13.2f\n", emp_rec_ptr->emp_name,
emp_rec_ptr->salary, emp_rec_ptr->commission);
}

/* Close the cursor. */
EXEC SQL CLOSE salespeople;

printf("\nArrivederci.\n\n");

EXEC SQL COMMIT WORK RELEASE;
exit(0);
}



void
sql_error(msg)
char *msg;
{
char err_msg[512];
int buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the
* error message.
*/
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(1);
}

Structures and Arrays in Pro*C

struct
{
char emp_name[3][10];
int emp_number[3];
int dept_number[3];
} emp_rec;
...
strcpy(emp_rec.emp_name[0], "ANQUETIL");
strcpy(emp_rec.emp_name[1], "MERCKX");
strcpy(emp_rec.emp_name[2], "HINAULT");
emp_rec.emp_number[0] = 1964; emp_rec.dept_number[0] = 5;
emp_rec.emp_number[1] = 1974; emp_rec.dept_number[1] = 5;
emp_rec.emp_number[2] = 1985; emp_rec.dept_number[2] = 5;

EXEC SQL INSERT INTO emp (ename, empno, deptno)
VALUES (:emp_rec);

Structures in Pro*C

typedef struct
{
char emp_name[11]; /* one greater than column length */
int emp_number;
int dept_number;
float salary;
} emp_record;

/* define a new structure of type "emp_record" */
emp_record new_employee;

strcpy(new_employee.emp_name, "CHEN");
new_employee.emp_number = 9876;
new_employee.dept_number = 20;
new_employee.salary = 4250.00;


EXEC SQL INSERT INTO emp (ename, empno, deptno, sal)
VALUES (:new_employee);

Pro*C Examples

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/dev.htm#1609

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/toc.htm

Using C Structures
=================

#include

typedef emptype myemp;

myemp *employee;


ORA_PROC Macro
=================
the precompiler never reads the file.
The following example uses the ORA_PROC macro to exclude the irrelevant.h file:

#ifndef ORA_PROC
#include
#endif


OR

EXEC ORACLE IFNDEF ORA_PROC;

EXEC ORACLE ENDIF;

#define MAX(A,B) ((A) > (B) ? (A) : (B))
VARCHAR name_loc_temp[MAX(ENAME_LEN, LOCATION_LEN)];


You can use the #include, #ifdef and #endif preprocessor directives to conditionally include a file that the precompiler requires. For example:

#ifdef ORACLE_MODE
# include
#else
long SQLCODE;
#endif


TYPE and VAR statements
========================
#define STR_LEN 40
...
typedef char asciiz[STR_LEN];
...
EXEC SQL TYPE asciiz IS STRING(STR_LEN) REFERENCE;
...
EXEC SQL VAR password IS STRING(STR_LEN) REFERENCE;



UNIX systems, you can compile the generated C source file using the command
----------------------------------------------------------------------------------------------------------------------
cc -o progname -I$ORACLE_HOME/sqllib/public ... filename.c ...

switch
---------
ch = getchar();
switch (ch)
{
case 'U': update(); break;
case 'I': insert(); break;
...

Using Numeric Constants in Pro*C/C++
===================================
In Pro*C/C++, normal C scoping rules are used to find and locate the declaration of a numeric constant declaration.

const int g = 30; /* Global declaration to both function_1()
and function_2() */
void function_1()
{
const int a = 10; /* Local declaration only to function_1() */
char x[a];
exec sql select ename into :x from emp where job = 'PRESIDENT';
}

void function_2()
{
const int a = 20; /* Local declaration only to function_2() */
VARCHAR v[a];
exec sql select ename into :v from emp where job = 'PRESIDENT';
}

void main()
{
char m[g]; /* The global g */
exec sql select ename into :m from emp where job = 'PRESIDENT';
}

Defining an object type in Pro*C

--Defining an object type...
CREATE TYPE employee_type AS OBJECT(
name VARCHAR2(20),
id NUMBER,
MEMBER FUNCTION get_id(name VARCHAR2) RETURN NUMBER);
/
--Creating an object table...
CREATE TABLE employees OF employee_type;
--Instantiating an object, using a constructor...
INSERT INTO employees VALUES (
employee_type('JONES', 10042));
--LONG, LONG RAW, NCLOB, NCHAR and NCHAR Varying are not allowed as datatypes in attributes of
objects
REFs=====CREATE TYPE employee_t AS OBJECT(
empname CHAR(20),
empno INTEGER,
manager REF employee_t);
/
CREATE TABLE employee_tab OF employee_t;
Accessing Objects Using the Associative Interface============================================person *per_p;EXEC SQL ALLOCATE :per_p;EXEC SQL SELECT INTO :per_p FROM person_tab WHERE ...EXEC SQL INSERT INTO person_tab VALUES(:per_p);EXEC SQL FREE :per_p;
Using Objects in Pro*C/C++=========================Let us examine a simple object example. You create a type person and a table person_tab, which has a column
that is also an object, address:
create type person as object (
lastname varchar2(20),
firstname char(20),
age int,
addr address
)
/
create table person_tab of person;
Associative Access================char *new_name = "Smythe";
person *person_p
...
EXEC SQL ALLOCATE :person_p;
EXEC SQL SELECT INTO :person_p FROM person_tab WHERE lastname = 'Smith';
EXEC SQL OBJECT SET lastname OF :person_p TO :new_name;
EXEC SQL INSERT INTO person_tab VALUES(:person_p);
EXEC SQL FREE :person_p;
Navigational Access===================
person *person_p;
person_ref *per_ref_p;
...
EXEC SQL ALLOCATE :per_ref_p;
EXEC SQL SELECT ... INTO :per_ref_p;
EXEC SQL OBJECT DEREF :per_ref_p INTO :person_p;
/* lname is a C variable to hold the result */
EXEC SQL OBJECT GET lastname FROM :person_p INTO :lname;
...
EXEC SQL OBJECT SET lastname OF :person_p TO :new_name;
/* Mark the changed object as changed with OBJECT UPDATE command */;
EXEC SQL OBJECT UPDATE :person_p;
EXEC SQL FREE :per_ref_p;To make the changes permanent in the database:
EXEC SQL OBJECT FLUSH :person_p;
EXEC SQL OBJECT RELEASE :person_p;

Object Support in Pro*C/C++

http://www.cs.umbc.edu/help/oracle8.bak/server803/A54661_01/obj.htm