Tech Rocks

Coldfusion
Java
JQuery

An online resource for latest web technologies like Coldfusion, JRun, Pro*C, JQuery, HTML5, PHP, W3C, Java, J2EE, C, C++, ORACLE, PL/SQL, MySql, Ajax, Coldbox, Fusebox, UNIX, JavaScript, NodeJS and much more...

Saturday, February 7, 2009

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;

0 comments :

Post a Comment