Sorry for the long post, I just want to give as much information as possible:
I have a package in Oracle which locates a row based upon a unique ID and returns it as a record type. Here's some watered down code:
ORACLE PACKAGE (Customer) DEFINITIONS:
type t_rcd_customer is record (
customer_id customer.customer_id%type,
name customer.description%type,
address customer.address%type,
city customer.city%type,
state customer.state%type,
zip customer.zip%type
);
function info(p_customer_id in customer.customer_id%type) return t_rcd_customer;
pragma restrict_references(info, wnds, rnps, wnps);
function write(rcd in t_rcd_customer) return customer.customer_id%type;
PACKAGE FUNCTIONS:
function info(p_customer_id in customer.customer_id%type) return t_rcd_customer is
rcdReturn t_rcd_customer;
begin
select c.customer_id,
c.name,
c.address,
c.city,
z.state,
c.zip)
into rcdReturn
from customer c;
return (rcdReturn);
end;
function write(rcd in t_rcd_customer) return customer.customer_id%type is
nRetval customer.customer_id%type;
begin
-- adding or editing?
if rcd.customer_id is not null then
update customer
name = rcd.name,
address = rcd.address,
city = rcd.city,
state = rcd.state,
zip = rcd.zip
where customer_id = rcd.customer_id
returning customer_id
into nRetval;
else
insert into customer (name,
address,
city,
state,
zip)
values (rcd.name,
rcd.address,
rcd.city,
rcd.state,
rcd.zip)
returning customer_id
into nRetval;
end if;
return (nRetval);
end;
** Within C++ Builder, I define a TCustomerInfo ** class as follows:
** CUSTOMER.H: **
class TCustomerInfo : public TObject
{
private:
int __fastcall getCustomerID(void) { return rcd->CustomerId; };
AnsiString __fastcall getName(void) { return rcd->Name; };
AnsiString __fastcall getAddress(void) { return rcd->Address; };
AnsiString __fastcall getCity(void) { return rcd->City; };
int __fastcall getState(void) { return rcd->State; };
AnsiString __fastcall getZip(void) { return rcd->Zip; };
// variables
CustomerPakgTRcdCustomer *rcd;
TCustomerPakg *pkg;
public:
__property int CustomerID = {read = getCustomerID };
__property AnsiString Name = {read = getName };
__property AnsiString Address = {read = getAddress };
__property AnsiString City = {read = getCity };
__property int State = {read = getState };
__property AnsiString Zip = {read = getZip };
__fastcall TCustomerInfo(int iID, TOracleSession *);
__fastcall ~TCustomerInfo();
};
** CUSTOMER.CPP **
#include "customer.h"
//---------------------------------------
// TCustomerInfo
//---------------------------------------
__fastcall TCustomerInfo::TCustomerInfo(int iSearchID, TOracleSession *session) : TObject()
{
TObject *temp;
TCursor scrCur;
scrCur = Screen->Cursor;
Screen->Cursor = crSQLWait;
pkg = new TCustomerPakg(Application);
rcd = new CustomerPakgTRcdCustomer();
pkg->Session = session;
rcd = pkg->Info(iSearchID);
Screen->Cursor = scrCur;
}
//---------------------------------------
// ~TCustomerInfo
//---------------------------------------
__fastcall TCustomerInfo::~TCustomerInfo()
{
delete rcd;
delete pkg;
}
** WHEW!! OK, I've finally got all the definitions done... now here's my call in my code... **
** To view data... ***
.
.
.
TCustomerInfo *rcd = new TCustomerInfo(43254);
lblName->Caption = rcd->Name;
lblAddress->Caption = rcd->Address;
.
.
.
delete rcd;
.
.
.
** To add / update data... ***
.
.
.
CustomerPakgTRcdCustomer *rcd = new CustomerPakgTRcdCustomer();
rcd->Name = edtName->AsString;
rcd->Address = edtAddress->AsString;
rcd->City = edtCity->AsString;
rcd->State = edtState->AsString;
rcd->Zip = edtZip->AsString;
** CustomerPakg is a generated component
** from the Component Wizard.
CustomerPakg->Write(rcd);
OraSession->Commit();
delete rcd;
All of this works like a champ. It's a bit of an initial set up for the class work, but when I need to get information about a specific record in the customer table (or any other table for that matter), I just have to instantiate the class with the unique ID and then address the record member. It works very slick.
Here's the problem:
Obviously, my customer record has more information than what I've shown here. One of the fields is of type CLOB (actually several of them). All the CLOB fields are for notes and such. I would love to have my TCustomerInfo class handle the conversion between the CLOB and an AnsiString, but I have not been able to come up with a solution. Any help out there? assuming you've made it this far in my post! :-)
Thanks,
Stephen
