Home » Developer & Programmer » Precompilers, OCI & OCCI » Using OCCI and REF CURSOR
Using OCCI and REF CURSOR [message #93967] Thu, 05 June 2003 07:30 Go to next message
ashish chandra
Messages: 3
Registered: January 2002
Junior Member
Hi All

I am very new to OCCI (3 days now) and I am trying to do the following:

I have the following package:

CREATE OR REPLACE PACKAGE BODY UNICORN.SAL2UNI
AS
PROCEDURE SAL2UNI_GETPLANS(PLAN_ID IN OUT PK_PLAN_CUR)
AS
BEGIN
IF NOT PLAN_ID%ISOPEN
THEN
OPEN PLAN_ID FOR SELECT PLANID from PK_PLAN where STOPDATE >= SYSDATE;
END IF;
END;

PROCEDURE SAL2UNI_GETSERVICEINSTANCES(SERVINST IN VARCHAR2,SVCMIG OUT CUR_GET_SI)
IS
PICCODE VARCHAR2(1);
COMPANYID VARCHAR(5);
OCPPRODUCT VARCHAR(5);
BEGIN
PICCODE := 'P';
COMPANYID := '11';
OCPPRODUCT := 'OCP';

IF NOT SVCMIG%ISOPEN
THEN
OPEN SVCMIG FOR
select distinct
accountno,
serviceno,
status,
startdate,
stopdate,
reasonid,
PICCODE,
startdate,
COMPANYID,
OCPPRODUCT,
cic,
null,
null,
null,
null
from CC_AccountService
where serviceno=SERVINST;
END IF;
END;

END SAL2UNI;

In my C++ program, I am doing the following:
.
.
.
Statement *stmt = con->createStatement
("BEGIN SAL2UNI.SAL2UNI_GETSERVICEINSTANCES(:v1, :v2); END;");

stmt->setString(1,"9721234567");
stmt->registerOutParam (2, OCCICURSOR);
int updateCount = stmt->executeUpdate ();

// updateCount returns 1

ResultSet *rs = stmt->getCursor(2);
???
.
.
I don't know what to do from this point on. I have tried while(rs->next()) but I get a Microsoft C++ exception (using VC++ editor). Same thing with rs->getString(...).

Does anyone have any idea whether what I am doing is correct and how to proceed.

Thanks a lot in advance.

ashish
Re: Using OCCI and REF CURSOR [message #93968 is a reply to message #93967] Thu, 05 June 2003 15:08 Go to previous messageGo to next message
ashish chandra
Messages: 3
Registered: January 2002
Junior Member
Never mind, I was able to make it work. However, I have a new problem:

stmt->registerOutParam (1, OCCICURSOR);
//stmt->registerOutParam (3, OCCISTRING, 30, "");
int updateCount = stmt->executeUpdate ();
// cout << "Update Count:" << updateCount << endl;

ResultSet *rs = stmt->getCursor(1);
ResultSet::Status status = rs->status();
string s;int cnt = 0;
unsigned u = rs->getMaxColumnSize(1);
//rs->next();
if(status == ResultSet::DATA_AVAILABLE)
status = rs->next();

while(status == ResultSet::DATA_AVAILABLE)
{
s = rs->getString(1); // *** Problem ***
status = rs->next();
cnt++;
}

For the very first fetch, it gets the data. However, for the subsequent fetches, it fetches garbage. Also, the cnt variable holds the number or rows I am expecting. What am I doing wrong?

Thanks
ashish
Re: Using OCCI and REF CURSOR [message #94360 is a reply to message #93967] Thu, 02 September 2004 03:47 Go to previous message
Plugg Preagar
Messages: 1
Registered: September 2004
Junior Member
Hi,
it's very late, but may be the problem is still around.

So i tried fetching this way - and it works, but only with a OUT-Declared Ref-Cursor! You aren't able to register a Cursor in OCCI-Statment for Input. So you have to use an Only-Out-Ref-Cursor. And then it works.

pStmt->registerOutParam(1, OCCICURSOR);
if (pStmt->executeUpdate()>0)
{
cout << pStmt->getSQL()<< " .. reading "<< endl;
pResultSet = pStmt->getCursor(1);
while( pResultSet->next())
{
cout << pStmt->getSQL()<< " --> " << pResultSet->getInt(1) << endl;
}
}

plugg
Previous Topic: 10g client with red hat 9cc3.3.2 run time error
Next Topic: OCCI link problem with gcc2.96 in Redhat7.2, Oracle9.0.1
Goto Forum:
  


Current Time: Thu Mar 28 16:45:20 CDT 2024