By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

SQL0518N

P: n/a
SQL0518N The statement named in the EXECUTE statement is not in a
prepared
state or is a SELECT or VALUES statement. SQLSTATE=07003

Does this mean DB2 prohibits using Dynamic SQL for a Statement that has
a SELECT in it?

How can I dynamically issue SELECTS on the fly based on a set of
flexible parameters like tablename, where clause etc,.

Zri Man

Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"Zri Man" <Da**********************@gmail.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
SQL0518N The statement named in the EXECUTE statement is not in a
prepared
state or is a SELECT or VALUES statement. SQLSTATE=07003

Does this mean DB2 prohibits using Dynamic SQL for a Statement that has
a SELECT in it?

How can I dynamically issue SELECTS on the fly based on a set of
flexible parameters like tablename, where clause etc,.

Zri Man

You have to prepare it, or do execute immediate.
Nov 12 '05 #2

P: n/a
I did prepare and then execute it inside a SQL Stored Procedure. This
error is from that prepare and execute process.

Nov 12 '05 #3

P: n/a
Hi,
What is your environment?
DB2 UDB v7 (I cannot recall which release - 7.1 or 7.2) did not support
a dynamic SELECT statement.
The workaround then was to use dynamic Cursor to achieve select
functionality.

......anurag

Zri Man wrote:
I did prepare and then execute it inside a SQL Stored Procedure. This
error is from that prepare and execute process.


Nov 12 '05 #4

P: n/a
Thank you for you reply, I'm on 8.2 UDB on Linux.

I guess it still does not work.

By the way, is there any one document that lists each feature/language
element of DB2 and says whether its available in each environment of
DB2 ?

This one obviously compiles and the Help Online even lists this SYNTAX
as allowed, but however the compiled procedure does not work.

Nov 12 '05 #5

P: n/a
DYNAMIC SCROLL CURSOR does not work in a SQL Procedure

Bloody hell, this is a frustrating exercise. I'm about ready to give up
on DB2 stored procedures.

Nov 12 '05 #6

P: n/a
Zri Man wrote:
DYNAMIC SCROLL CURSOR does not work in a SQL Procedure

Bloody hell, this is a frustrating exercise. I'm about ready to give up
on DB2 stored procedures.

Slowly, slowly....
The DPF limitation is one of FUNCTIONS and not procedures.
SQL Procedures (and C and Java) work just fine in DPF.
Just make sure you are _connected_to_the_catalog_node_ when you do your
CREATE PROCEDURE script. (e.g. by using the DB2NODE enviornment variable)

Scrollable cursors are only supported through te hclinet interfacse
(CLI, JDBC, ...) this has little to do with stored procedures.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7

P: n/a
"Zri Man" <Da**********************@gmail.com> wrote in message

By the way, is there any one document that lists each feature/language
element of DB2 and says whether its available in each environment of
DB2 ?

Yes, it is called the SQL Reference for Cross-Platform Development
http://www-106.ibm.com/developerwork...206sqlref.html
Nov 12 '05 #8

P: n/a
This is the place where security comes into play.
Consider a SP x(with no dynamic stuff) which refers to the tables
t1,t2 and t3. SP can be executed if user have only execute privilege
over that SP(Even though the user is having no direct privilege over
t1, t2 and t3).

But if a SP has a dynamic statement which refers to table t1, then
the user should be given the explicit privilege over that table.

Make sure you have proper privileges over the tables which is
involved in dynamic statement and retry.

Correct me if i am wrong.
Cheers,
Thiru.
WantedToBeDBA
WantedToBeDBA {at} gmail {dot} com

Nov 12 '05 #9

P: n/a
Thanks for your enthusiastic rebuttal or shall I say reassurance that
my impressions are wholly unfounded.

But let me clarify , DPF I presume is partitioning, my database is not
partitioned. There is only one instance.

I'm trying to execute a SELECT with COUNT and other aggregates at
Runtime and store the returned values into a Table for tracking,
analysis etc,.

The process requires to be dynamic as there will be an increasing
number of tables in this workflow and I do not want to hard code the
table names or the Conditions or the GROUP BY's for their aggregates
into huge stored procedures.

I did not anticipate anything to do with nodes or instances as these
are all occuring within the same database.

From your reply its quite clear SCROLLABLE cursors are out favor for

SQL Stored Procedures.

However What do I do about the error that appears say Stored Procedures
(in SQL) are not allowed to have Dynamic SELECT or VALUES clause.

your expertise is appreciated.

Thanks

Nov 12 '05 #10

P: n/a
Zri Man wrote:
Thanks for your enthusiastic rebuttal or shall I say reassurance that
my impressions are wholly unfounded.

But let me clarify , DPF I presume is partitioning, my database is not
partitioned. There is only one instance.

*LOL* Right you are. I somehow crossed the wired with the DPF post about
SQL in UDF in DPF. I humbly apologize.

Let's just restart:
SELECT INTO and VALUES INTO are just shortcuts for
DECLARE cur1 CURSOR FOR SELECT ... FOR READ ONLY
OPEN cur1;
FETCH cur1 INTO ..;
CLOSE cur1;

So all you nedd to do is to use a regular dynamic cursor.
You need to:
1. declare text variable where you compose the statement.
2. declare a statement variable to hold the prepared statement
3. Declare the cursor for the statement.
4. PREPARE the text into the statement.
5. Open, fetch, close as usual.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11

P: n/a
Serge,

That is exactly what I did , it compiles just fine and gives the
runtime error that is the header for this post.

It just does not work, and apparently the error explains its not
supposed to.

So I'm not sure what I'm missing it.

Again we are on UDB 8.2 on SuSE Linux.

Sridhar

Nov 12 '05 #12

P: n/a
Zri Man wrote:
Serge,

That is exactly what I did , it compiles just fine and gives the
runtime error that is the header for this post.

It just does not work, and apparently the error explains its not
supposed to.

So I'm not sure what I'm missing it.

Again we are on UDB 8.2 on SuSE Linux.

Sridhar

Can you post your source? This definitely works.
A cleansed repro (!) script will work. Make sure it returns the same
error...
I have used this technique myself and it's a mainstream "feature".
The error suggests that you either used EXECUTE instead of OPEN,
or you didn't do the PREPARE before the OPEN.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #13

P: n/a
Thank you very much for your perseverece, it works.

I was suspicious of one of the declarations I had copied from a sample
code on one of the IBM guides where the CURSOR declaration preceeded
the Statement Preparation where the relationship between the prepared
statement and the Cursor declared for its execution were dubious. That
proved to be the problem.

Below is Code that works. Thanks again. (Maybe I wil continue to use
DB2 SQL Stored Procedures after all) Never say Never.

CREATE PROCEDURE Get_RowCount( InSchemaName VARCHAR(30),
InTableName VARCHAR(30),
InWhereClause VARCHAR(1000),
InGroupBy VARCHAR(1000),
OUT RowCount INT)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN ATOMIC

DECLARE stmt VARCHAR(1000);
DECLARE l_RowCount INT;
IF InWhereClause IS NULL AND InGroupBy IS NULL THEN
SET stmt = 'SELECT count(*) from ' ||
rtrim(char(InSchemaName)) || '.'
|| rtrim(char(InTableName)) ;

ELSEIF InWhereClause IS NOT NULL AND InGroupBy IS NULL THEN
SET stmt = 'SELECT count(*) from ' ||
rtrim(char(InSchemaName)) || '.'
|| rtrim(char(InTableName)) ||
' WHERE ' || InWhereClause ;

ELSEIF InWhereClause IS NOT NULL AND InGroupBy IS NOT NULL THEN
SET stmt = 'SELECT ' || InGroupBy || ', count(*) from ' ||
rtrim(char(
InSchemaName)) || '.' || rtrim(char(InTableName)) ||
' WHERE ' || InWhereClause ||
' GROUP BY ' || InGroupBy;

ELSEIF InWhereClause IS NULL AND InGroupBy IS NOT NULL THEN
SET stmt = 'SELECT ' || InGroupBy || ', count(*) from ' ||
rtrim(char(
InSchemaName)) || '.' || rtrim(char(InTableName)) || ' GROUP BY ' ||
InGroupBy;
END IF;
PREPARE S1 FROM stmt;
BEGIN

DECLARE c1 CURSOR FOR S1;

OPEN c1;
FETCH c1 into RowCount;

CLOSE C1;

END;
return ;
END

Nov 12 '05 #14

P: n/a
Zri Man wrote:
Thank you very much for your perseverece, it works.

I was suspicious of one of the declarations I had copied from a sample
code on one of the IBM guides where the CURSOR declaration preceeded
the Statement Preparation where the relationship between the prepared
statement and the Cursor declared for its execution were dubious. That
proved to be the problem.

Below is Code that works. Thanks again. (Maybe I wil continue to use
DB2 SQL Stored Procedures after all) Never say Never.

That's OK. SQL Procedures will forgive you if you buy
"DB2 SQL PL", ISBN: 0-13-147700-5 ;-)
(no I'm not an author.. I really believe it's a must have)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.