Hello,
I've tested both versions (Mr. Anhaus' version (even the corrected version)
and Mr. Oltra's version) and everytime I get the same error (-4024). What
else can I do to find the "real" problem, because I think it can't be the
dbproc anymore?
Here is my original program stripped off to 1 parameter (the real program
has 4) and no if's (usually there are some if-Statements). I use the
variable 'Auswahl' for an analysis value, which is stripped of. Even with
this "small" dbproc I get the -4024 error.
Create dbproc mytest(In Kunde SmallInt)
Returns Cursor AS
Var
SQLSTRING Varchar(2000);
Auswahl Smallint;
$Cursor = 'Mytest_Cursor';
Begin
SQLSTRING = 'DECLARE ' || $Cursor || ' Cursor for SELECT
av_tables.tbl_Anfrage.Voit_Anfragenummer, av_tables.tblKunden.Kundenname
FROM av_tables.tblKunden, av_tables.tbl_Anfrage,
av_tables.tbl_Teile
WHERE av_tables.tblKunden.Kunden_ID (+) =
av_tables.tbl_Anfrage.Kunden_ID
AND av_tables.tbl_Anfrage.lfd_Anfragenummer
= av_tables.tbl_Teile.lfd_Anfragenummer (+)
AND
av_tables.tblKunden.Kunden_ID = ' || Kunde;
Execute SQLSTRING;
if $rc <> 0 then stop(-31001, 'unexpected error ' || chr($rc));
End;
And I start it with 'call mytest (1)'
Maybe I'm too blind to see my error in it.
Peter
-----Urspr�ngliche Nachricht-----
Von: H�bschen, Peter
Gesendet: Donnerstag, 11. Dezember 2003 13:50
An: Mailingliste MaxDB (E-Mail)
Betreff: AW: Return cursor in dbproc with concatenated SQL-statement??
Hello,
thanks to your help, my dbproc gets 'compiled' at least, but when I call it
with 'call foo(1,'X') I get
---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
General error;-4024 .
call foo(1,'X')
where 'all' from call is marked red.
So I changed the dbproc in that way, that I get back the whole sqlstring (I
had to delete 'Declare ' || $Cursor || and all other cursor-related things)
to exclude this as a cause for this error. So I sorted out 2 errors within
the creation of the SQLString. After that with my changed program I get back
the correct sqlstring which shows me the correct results, if I copy&paste
the string into a SQL-dialog.
So I don't know why I get the error above, when I try to call my dbproc
'foo' and I can call all other dbprocs without any problems.
Best Regards
Peter
-----Urspr�ngliche Nachricht-----
Von: Anhaus, Thomas [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 11. Dezember 2003 11:38
An: '[EMAIL PROTECTED]'
Cc: H�bschen, Peter
Betreff: RE:Return cursor in dbproc with concatenated SQL-statement??
Peter H�bschen wrote :
>Hello,
>encouraged through
>http://listserv.sap.com/pipermail/sapdb.general/2003-April/014359.html
>I tried to create a dbproc with a concatenated SQL-statement to get back a
>result-table. But somehow I didn't managed to get it work. I tried
something
>like this:
>Create dbproc foo(In myvar1 smallint, in myvar2 varchar(3))
>Returns Cursor as
>Var
> SQLString Varchar(100);
>$Cursor = 'foo_Cursor':
>Begin
> SQLString = 'Select * from mytable where '
> If myvar1 > 0 Then SQLString = SQLString || ' compvar1 = ' || myvar1;
> If myvar2 <> '' Then SQLString = SQLString || 'compvar2 = ''' || myvar2;
>
>Declare :$Cursor Cursor for
>Execute SQLString;
>End;
>In this case I get a 'Missing keyword Select' - Error at the
>execute-command. It's obvious why, but I want to know if it's possible to
>get a result-table back through a dbproc or something else, where the
>SQL-Statement is dynamically build.
>My DB-version is 7.4.3.30 on a Linux-Box
>Thanks in advance
>Peter
This syntax is slightly different but the following should work :
Create dbproc foo(In myvar1 smallint, in myvar2 varchar(3))
Returns Cursor as
Var
SQLString Varchar(120);
$Cursor = 'foo_Cursor';
SQLString = 'DECLARE ' || $Cursor || ' Select * from mytable where '
if myvar1 > 0 Then SQLString = SQLString || ' compvar1 = ' || myvar1;
If myvar2 <> '' Then SQLString = SQLString || 'compvar2 = ''' || myvar2;
Execute SQLString;
Best Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
