[Proc] A complete Joint interface Development Program

This example is mainly from "proficient in Oracle 10g pro*c/c++ programming," the example obtained from the original book has some bugs, I changed.

Database sql:create or Replace type Address_type as Object (
Street VARCHAR2 (50),
City Varchar2 (20),
State VARCHAR2 (20),
ZipCode VARCHAR2 (6)
);
CREATE TABLE Customer (
ID Number (6),
Name VARCHAR2 (10),
Address Address_type
);
Create or replace type Employee_type as Object (
ID Number (6),
Name VARCHAR2 (10),
Adress address_type
);


Create or replace type Home_type as Object (
Street VARCHAR2 (50),
City Varchar2 (20),
State VARCHAR2 (20),
ZipCode VARCHAR2 (6),

Owner VARCHAR2 (10)
);
CREATE table homes of Home_type;

In.type

----------------------------------
Case=lower
TYPE Address_type hfile=employee.h
TYPE Employee_type hfile=employee.h

Command:

Ott userid=llpaytest/llpaytest@ora*** Intype=./in.typ Outtype=./out.typ code=c

Compile command: Note the path to include
Proc Include=/opt/oracle/product/10g/lib Include=/opt/oracle/product/10g/rdbms/public Include=/opt/oracle/product /10g/network/public Include=/opt/oracle/product/10g/rdbms/demo include=./iname=demo15_1.pc Intype=./out.typ

#不要缺少 $ORACLE _home/lib-l clntsh will report sqlcxt mistakes.
Gcc-g-O demo15_1 demo15_1.c-i/opt/oracle/product/10g/lib-i/opt/oracle/product/10g/rdbms/public-i/opt/oracle/ product/10g/network/public-i/opt/oracle/product/10g/rdbms/demo-i./-i//opt/oracle/product/10g/precomp/public/-L $ORACLE _home/lib-l Clntsh


#include <stdio.h>
#include <string.h>

/* Contains SQLCA header file * *
#include <sqlca.h>

/* contains OCI header file * *
#include <oci.h>

/* contains header files generated after OTT conversion * *
#include "employee.h"

void Sql_error ();
void Query_object ();
void Add_object ();
void Update_object ();
void Delete_object ();

int main ()
{
char action;

/* Install error handling handle * *
EXEC SQL whenever SQLError do Sql_error ();

/* Connect to Database/*
EXEC SQL BEGIN DECLARE section;
int money;
Char answerbuff[200];
int flag;

Char username[10],password[10],server[10];
EXEC SQL end DECLARE section;

/*
* Define input Host variables: Receive user name, password, and network service name
*

*/

memset (username,0x00, sizeof (username));

memset (password,0x00, sizeof (password));

memset (server,0x00, sizeof (server));
strcpy (username, "llpaytest");
strcpy (password, "llpaytest");
strcpy (Server, "* * *"); * * Here is the database of the sid*/

/* Connect to Database/*
EXEC SQL Connect:username identified by:p Assword Using:server;
if (sqlca.sqlcode==0)
printf ("Connected successfully!/n");
Else
/* Display Connection error message * *
printf ("%.*s/n", SQLCA.SQLERRM.SQLERRML,
SQLCA.SQLERRM.SQLERRMC);

for (;;)
{
printf ("\ns: Retrieving Objects,");
printf ("A: Adding objects,");
printf ("U: Update object,");
printf ("D: Delete object,");
printf ("X: Exit Loops,");

printf ("Please enter operation:");
scanf ("%c", &action);
Fflush (stdin);

Switch (action)
{
Case ' s ':
Case ' S ':
/* Retrieve object information * *
Query_object ();
GetChar (); Note here that there will be a carriage return brought into the For loop, the following same
Break

Case ' a ':
Case ' A ':
/* Add Object * *
Add_object ();
GetChar ();
Break

Case ' U ':
Case ' U ':
/* Update Object * *
Update_object ();
GetChar ();
Break

Case ' d ':
Case ' D ':
/* Delete Object * *
Delete_object ();
GetChar ();
Break

Case ' x ':
Case ' X ':
/* COMMIT TRANSACTION, disconnect * *
EXEC SQL commit work release;
GetChar ();
return 0;

Default
GetChar ();
Continue
}
}

return 0;
}

void Sql_error ()
{
/* Display SQL Error * *
printf ("%.*s\n", SQLCA.SQLERRM.SQLERRML, SQLCA.SQLERRM.SQLERRMC);
}

void Query_object ()
{
/* Define HOST variable * *
Address_type *address;
int id;
Char name[10];
Char city[20];

/* Initialize String * *
memset (name, 0x00, sizeof (name));
memset (city, 0x00, sizeof);

/* Enter data for input host variable * *
printf ("Please enter customer number:");
scanf ("%d", &id);
Fflush (stdin);

/* Allocate Object Cache * *
EXEC SQL allocate:address;

/* Object column data-> Object Cache * *
EXEC SQL select name, Address Into:name,: Address from customer where id =: ID;

/* Get Object Properties * *
EXEC SQL OBJECT get city from:address into:city;

/* Release Object Cache * *
EXEC SQL free:address;

/* Display Customer information * *
printf ("Customer Name:%s\n", name);
printf ("Town:%s\n", city);
}

void Add_object ()
{
/* Define HOST variable * *
address_type* address;
int id;
Char name[20];
Char city[20];
Char state[20];

/* Initialize String * *
memset (name, 0x00, sizeof (name));
memset (city, 0x00, sizeof);
memset (state, 0x00, sizeof);

/* Enter data for input host variable * *
printf ("Please enter customer number:");
scanf ("%d", &id);
Fflush (stdin);

printf ("Please enter customer Name:");
scanf ("%s", name);
Fflush (stdin);

printf ("Please enter the customer's province:");
scanf ("%s", state);
Fflush (stdin);

printf ("Please enter the customer's city:");
scanf ("%s", city);
Fflush (stdin);

/* Allocate Object Cache * *
EXEC SQL allocate:address;

/* Set Object Properties * *
EXEC SQL OBJECT set state of:address to:state;
EXEC SQL OBJECT set City of:address to:city;

/* Insert Data * *
EXEC SQL INSERT into the customer values (: ID,: Name, address);

/* Release Object Cache * *
EXEC SQL free:address;

* * Submit Transaction * *
EXEC SQL commit;
}

void Update_object ()
{
/* Define HOST variable * *
address_type* address;
int id;
Char street[50];
Char zipcode[10];

/* Initialize String * *
memset (street, 0x00, sizeof);
memset (ZipCode, 0x00, sizeof (ZipCode));

/* Enter data for input host variable * *
printf ("Please enter customer number:");
scanf ("%d", &id);
Fflush (stdin);

printf ("Please enter the customer's street:");
scanf ("%s", street);
Fflush (stdin);

printf ("Please enter customer ZIP code:");
scanf ("%s", ZipCode);
Fflush (stdin);

/* Allocate Object Cache * *
EXEC SQL allocate:address;

/* Object data column-> Object Cache * *
EXEC SQL Select Address Into:address from customer where id =: ID;

/* Set Object Properties * *
EXEC SQL OBJECT set Street of:address To:street;
EXEC SQL OBJECT set zipcode of:address to:zipcode;

/* Update Data * *
EXEC SQL Update Customer Set address =: address where id =: ID;

/* Release Object Cache * *
EXEC SQL free:address;

* * Submit Transaction * *
EXEC SQL commit;
}

void Delete_object ()
{
/* Define HOST variable * *
Char city[20];

/* Initialize String * *
memset (city, 0x00, sizeof);

/* Enter data for input host variable * *
printf ("Please enter the customer's city:");
scanf ("%s", city);
Fflush (stdin);

/* Delete Data * *
EXEC SQL Delete from the customer p where p.address.city =: City;

* * Submit Transaction * *
EXEC SQL commit;
}