> -----Original Message-----
> From: Steve Brett [mailto:[EMAIL PROTECTED]]
> Sent: Montag, 29. April 2002 14:01
> To: Sapdb. General@Listserv. Sap. Com (E-mail)
> Subject: DB PROC Help ...
>
>
> I have the following db proc:
>
> CREATE DBPROC NAMESEARCH (IN PNAME VARCHAR(20), OUT patid INTEGER)
> AS
> TRY
> SELECT PATIENT.PATIENT_ID FROM "DBA".PATIENT
> INNER JOIN "DBA".FAMILY_NAME ON
> "DBA".PATIENT.FAM_NAME_ID=FAMILY_NAME.FAM_NAME_ID
> WHERE "DBA".FAMILY_NAME.FAM_NAME=:PNAME;
> WHILE $RC =0 DO BEGIN
> FETCH INTO :patid;
> END;
> CATCH
> IF $RC <> 100 THEN STOP ($RC, 'Unexpected error');
>
> what i'm trying to do is get a list of all the patient_ids
> that have 'Smith'
> as their surname.
>
> The SQL is correct but i fear there are mistakes in the db
> proc as a whole.
>
> In Postgres i would return a set of integers but in sapdb i
> get a resultset
> composed of 'Smith' and nothing else.
>
> Could anyone point out my error or point me in the direction
> of useful docs
> (i've been through the ref manual already and not found it much help).
Mhm, what you are doing with this dbproc is, you create a resultset,
fetch ALL resultrows (patient_id) one after the other into the same variable
(of course overwriting the ones written before) and returns the last
(because in case of error 100, the value written in this variable will not
be destroyed).
What you want to do is to return a whole list of patient_ids, do you?
Then you have to use cursors. As Thomas wrote some weeks ago:
The newest SAPDB version (7.3.0.21) provides the possibility to return
a resultset from a stored procedure. Simple Example :
CREATE DBPROC EXAMPLE RETURNS CURSOR
AS
DECLARE :$CURSOR CURSOR FOR SELECT * FROM SYSDBA:DUAL;
The new system variable $CURSOR represents the cursor name, which may be
overwritten
in your stored procedure.
However, since this feature is brand-new, you can only use it from JDBC and
maybe from
Perl and Python.
ODBC and the Precompiler are not able to deal with this feature yet. This
will come with
one of the next versions.
and there can be found this mail:
Naresh Chandak wrote :
>I am using sap db version 7.3.0.21
>java driver version : sapdb-jdbc-bin-7.3.0.23a.jar
>sql studio version 7.3.1.0
>I have a Table
>CREATE TABLE TP ( id INTEGER , name VARCHAR(50))
>It has got following values
>INSERT INTO TP( id , name ) values (1 , 'a')
>INSERT INTO TP( id , name ) values (2 , 'b')
>INSERT INTO TP( id , name ) values (3 , 'c')
>INSERT INTO TP( id , name ) values (4 , 'd')
>INSERT INTO TP( id , name ) values (5 , 'e')
>I have a procedure..
>CREATE DBPROC USP_TEST (IN ID3 INTEGER ) AS
>BEGIN
>SELECT * FROM TP WHERE ID > :ID3 ;
>END ;
>when I execute this procedure through sql studio ( CALL USP_TEST (1) )
>It shows 'Statement successfully executed , No result found ' . But it
should get 4 rows.
>same thing I tried with java. I got an exception 'SQL Statement generates a
row count '
>What should be the problem ?
This is not a procedure that returns a cursor. If you want to create a
procedure
returning a cursor you have to use the following syntax :
CREATE DBPROC USP_TEST (IN ID3 INTEGER ) RETURNS CURSOR AS
BEGIN
SET $CURSOR = 'MYCURSOR';
DECLARE :$CURSOR FOR SELECT * FROM TP WHERE ID > :ID3 ;
END ;
But as Marco already wrote this feature is still under construction. If you
use
an old JDBC driver you may have luck, but a call from SQL studio will
definitive return no result.
and the mail from Marco mentioned above is like this:
Hello,
returning resultsets from procedures is a brand new, hidden feature of the
latest released version of sapdb and still in testing. In this version it
doesn't work very well together with our interfaces (odbc, jdbc). Maybe you
can use this feature with an old version of the JDBC-driver (< 7.3.0.21).
But at the moment I cannot recommend to use it in a production environment.
You should wait for the next version.
Why don't you handle the patient_ids returned in the dbproc itself?
Or why do you use a dbproc for something what can be done with a normal
select?
Dbprocs are nice, but do not use them for everything.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
