473,326 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL0518N

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
14 6980
"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
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
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
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
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
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
"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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Nabil | last post by:
I am new to DB2 and here is my situation. I have 2 temp tables where I am trying to insert data (from 2 select statements) DECLARE v_t1 VARCHAR(50); DECLARE v_t1 VARCHAR(50); DECLARE stmt1...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.