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_CHA R,SQL_C_CHAR,
sizeof(company),0,company.c_str(),0,&compId);
ret = SQLBindParameter(hstmt,1,SQL_PARAM_OUTPUT,SQL_C_CH AR,SQL_C_CHAR,
sizeof(firstname),0,firstname,0,&firstnameId);
ret = SQLBindParameter(hstmt,2,SQL_PARAM_OUTPUT,SQL_C_CH AR,SQL_C_CHAR,
sizeof(familyname),0,familyname,0,&familynameId);
ret = SQLBindParameter(hstmt,3,SQL_PARAM_OUTPUT,SQL_C_CH AR,SQL_C_CHAR,
sizeof(address),0,address,0,&addressId);
ret = SQLBindParameter(hstmt,4,SQL_PARAM_OUTPUT,SQL_C_CH AR,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

I have made the following changes:
I have delete these and recompiled successfully
cnt NUMBER DEFAULT 1;
cnt = cnt + 1;

Also deleted this:
ret = SQLBindParameter(hstmt,0,SQL_PARAM_INPUT,SQL_C_CHA R,SQL_C_CH*AR,

sizeof(company),0,company.c_str(),0,&compId);
ret = SQLBindParameter
And inside this statement
ret = SQLFetch(hstmt); // ret gives -1
while (ret != 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);
result = result + "\r\n " + (AnsiString)firstname
+ "\t" + (AnsiString)familyname + "\t" +
(AnsiString)address + "\t" + (AnsiString)telno;
ShowMessage(result);
ret = SQLFetch(hstmt);
}
The ret in the while-statement delivers -1, it still enters the
statement and gives out only the last row endless.
I will appreciate your comments.
Harp