Retrieving resultsets from oracle stored procedures via odbc

From: harp <hmetu_at_gmx.net>
Date: 8 Feb 2005 06:21:49 -0800
Message-ID: <1107872509.212902.266950@c13g2000cwb.googlegroups.com>

I created a stored procedure in oracle with the help of sql +plus:

Set serveroutput on
 create or replace procedure spComp(company IN CHAR,oFname OUT CHAR, oFamname OUT CHAR, oAdr OUT CHAR, oTelno OUT CHAR)

 AS

    Cursor SelWorkers IS
    SELECT
        firstname, familyname, address, telno     FROM customers
    WHERE company = 'Compname';

 cnt NUMBER DEFAULT 1;

 BEGIN
   DBMS_OUTPUT.PUT_LINE('Employees of Companyname');    DBMS_OUTPUT.PUT_LINE('firstname familyname address telno');    FOR customers IN SelWorkers LOOP

     oFname := customers.firstname;
     oFamname := customers.familyname;
     oAdr := customers.address;
     oTelno := customers.telno;
     cnt := cnt + 1;

   DBMS_OUTPUT.PUT_LINE(customers.firstname||' : '||customers.familyname||' : '||customers.address||' : '||customers.telno);

   END LOOP;
 END spComp;
 /

DECLARE
   oFname CHAR(15);
   oFamname CHAR(15);
   oAdr CHAR(20);
   oTelno CHAR(7);
BEGIN
 spComp('Companyname',oFname,oFamname,oAdr,oTelno); END;
/
The results were okay.
Now I am trying to call it via odbc.
All about connections were okay.

AnsiString stmt = "CALL
spComp('Companyname',?,?,?,?)";
ret = SQLAllocStmt(hDBconn, &hstmt);//locate the statement ok
//bind the parameters

ret = SQLBindParameter(hstmt,0,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_C_CHAR, sizeof(company),0,company.c_str(),0,&compId); ret = SQLBindParameter(hstmt,1,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_C_CHAR, sizeof(firstname),0,firstname,0,&firstnameId); ret = SQLBindParameter(hstmt,2,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_C_CHAR, sizeof(familyname),0,familyname,0,&familynameId); ret = SQLBindParameter(hstmt,3,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_C_CHAR, sizeof(address),0,address,0,&addressId); ret = SQLBindParameter(hstmt,4,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_C_CHAR, sizeof(telno),0,telno,0,&telnoId);

//prepare and execute

 ret = SQLPrepare(hstmt, stmt.c_str(), stmt.Length()); if(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO){

   ret = SQLExecute(hstmt);
   while ((ret = SQLFetch(hstmt)) != SQL_NO_DATA){

       SQLGetData(hstmt, 1, SQL_C_CHAR, firstname, sizeof
           (firstname), &firstnameId);
       SQLGetData(hstmt, 2, SQL_C_CHAR, familyname, sizeof
           (familyname), &familynameId);
       SQLGetData(hstmt, 3, SQL_C_CHAR, address, sizeof(address),
           &addressId);
       SQLGetData(hstmt, 4, SQL_C_CHAR, telno, sizeof(telno),
          &telnoId);
       AnsiString result = result + "\r\n " + (AnsiString)
          firstname + "\t" + (AnsiString)familyname + "\t" +
          (AnsiString)address + "\t" + (AnsiString)telno;
            ShowMessage(result);

  }
}

It compiles without errors, but delivers only the last row of the result.
Secondly, it runs endless (I can guess why - the cnt = cnt + 1 in loop)

How can I improve the my program so that all the right results are delivered?
If I just use normal sql-stament, it works fine. Is there any other way to call the procedure in the program?

I am using C++Builder6 as my developing environment, Oracle9i.

Thanks in advance,
Harp Received on Tue Feb 08 2005 - 08:21:49 CST