Hello Jonathan,
I had a similar problem and here is an example like it works for me:
CREATE DBPROC SP_SETROWSOURCE (IN myvar1 SMALLINT, IN myvar2 SMALLINT, IN
myvar3 VARCHAR(3), IN myvar4 SMALLINT)
RETURNS CURSOR AS
VAR
SQLSTRING VARCHAR(2000);
AUSWAHL SMALLINT;
$CURSOR = 'SETROWSOURCE_CURSOR';
BEGIN
AUSWAHL = 0;
<snip>
<here: AUSWAHL gets a value <> 0 for creating my WHERE-clauses>
</snip>
SQLSTRING = 'DECLARE ' || $CURSOR || ' Cursor for SELECT * ';
SQLSTRING = SQLSTRING || ' FROM testtable
WHERE testrow = ' || myvar1;
<snip>
<some more Where-clauses>
</snip>
EXECUTE SQLSTRING;
END;
I hope I understood your problem and that this helps.
Peter
-----Urspr�ngliche Nachricht-----
Von: Jonathan Sarabia [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 28. April 2004 15:22
An: Anhaus, Thomas
Cc: [EMAIL PROTECTED]
Betreff: RE: Prepared sentences
Thomas,
Thanks a lot for your response. First of all, I want to confirm you are
right...I need to use dynamic sentences.
Based on the example you gave me, I did something like this:
CREATE DBPROC TEST (IN tablename CHAR(32)) AS
VAR
stmt CHAR(100);
stmt = 'select * from ' || tablename || '';
execute stmt;
This dbproc doesn't retrieve any information. I am not sure if the dbproc
must be indicated as a "returns cursor", and so, I am not sure if the cursor
may be declared with the "stmt" variable. I have tried and I get the
following error:
---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
Syntax error or access violation;-5015 POS(175) Missing keyword:SELECT.
CREATE DBPROC TEST (IN tablename CHAR(32)) RETURNS CURSOR AS
VAR
stmt CHAR(100);
stmt = 'select * from ' || tablename || '';
$cursor = 'test';
declare :$cursor cursor for stmt;
Regards,
Jonathan
-----Original Message-----
From: Anhaus, Thomas [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 28, 2004 2:14 AM
To: Jonathan Sarabia
Cc: [EMAIL PROTECTED]
Subject: RE: Prepared sentences
Jonathan Sarabia wrote :
>Hi,
>I am development a report and I need a Stored Procedure for that. The
>thing is, I need that SP to be dynamic, I
>mean, I need to use prepared sentences. I looked for that kind of sentence
and I did what it is specified in the
>SAPDB documents, however, when I tried to compile mi SP the following error
was reported:
>---- Error -------------------------------
>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
>Syntax error or access violation;-5015 POS(66) Missing
>keyword:WHILE,SELECT,IF,EXECUTE,CLOSE,SET,RETURN,LANGUAGE,FET.
>create dbproc dba.test (
>in testvar varchar(5) )
>as
>EXEC SQL BEGIN DECLARE SECTION;
>I am working with SAPDB version 7.4, on Windows 2000.
>Anybody have an idea of this error?
>Thanks in advice.
>Jonathan
I'm not really sure to understand your question. What are 'prepared
sentences' ? I assume you want to use dynamic sql inside a stored procedure,
i.e. you want to create the sql statements inside the procedure at runtime.
Here is an example how this can be achieved :
CREATE DBPROC TEST (IN tablename CHAR(32)) AS
VAR
stmt CHAR(100);
stmt = 'INSERT INTO ' || tablename || ' VALUES (1,2)';
execute stmt;
Please note that compared to other embeddings the power of dynamic sql
inside
stored procedures is limited. Especially parameters are not supported.
Best Regards,
Thomas
--
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]
