From: Hübschen, Peter (peter.huebschenvoit.de)
Date: Wed Apr 28 2004 - 23:36:08 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:jsarabiagitusa.com]
Gesendet: Mittwoch, 28. April 2004 15:22
An: Anhaus, Thomas
Cc: maxdblists.mysql.com
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:thomas.anhaussap.com]
Sent: Wednesday, April 28, 2004 2:14 AM
To: Jonathan Sarabia
Cc: maxdblists.mysql.com
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/maxdb?unsub=peter.huebschenvoit.de

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]