We're planning to create a Dynamic SQL stored procedure, and we usually declare the Prepare stmt SQL as varchar field, as we know there is a limitation on the varchar length(which is 32K).
But, SQL that we are going to create might cross that 32K limit, so we can’t define Prepare stmt SQL variable as Varchar field now.
So, we tried to define it as CLOB, but was getting the following error
The string constant beginning with "'
" is too long.. SQLCODE=-102, SQLSTATE=54002, DRIVER=4.11.69
Can you pls advise how to resolve the issue?
Pinned topic Dynamic SQL - Data Length limitation on the Prepare SQL stmt
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2013-01-03T23:46:25Z at 2013-01-03T23:46:25Z by SystemAdmin
B.Hauser 1000007U1D272 Posts
Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt2012-12-30T09:19:42ZThis is the accepted answer. This is the accepted answer.According to the documentation of the PREPARE statement a character or graphic variable must be used. Character/Graphic variables are restricted to 32 K.
Do you rally need strings that exeed this length?
Isn't it possible to create and use SQL view that include business logic, i.e. joins or specific where conditions or group by clauses? In your program you only select the columns (dynamically) if necessary and add some where conditions.
I'm working on a web application that builds all lists based on SQL statements that are built dynamically, i.e. the programmer/user only selects columns of existing tables/physical files or views, fiter fields can be defined and the appropriate where conditions are dynamically built and added. It is also possbile to add where conditions manually. ... until now we hadn't any trouble with SQL statements larger then 32 K.
If you need the prepare statement to built store the source code for stored procedures, triggers or user defined functions, you may consider to store the source code as SQL script in a stream file or source physical file member. For executing this source code, i.e. for creating the SQL routine, the RUNSQLSTM CL command can be used.
kmilligan 20000043PN14 Posts
Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt2013-01-02T21:44:15ZThis is the accepted answer. This is the accepted answer.What is the size of the CLOB variable that you're declaring? This code works fine on my 7.1 system.
DECLARE stmt CLOB(100k);
SET stmt = 'CREATE TABLE ct1(c1 INT)';
PREPARE s1 FROM stmt;
B.Hauser 1000007U1D272 Posts
Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt2013-01-03T08:03:54ZThis is the accepted answer. This is the accepted answer.
- kmilligan 20000043PN
it's working fine if the CLOB variable is defined and used in SQL programming language, but it does not work within embedded SQL. (... I assumed the poster wanted to use it in embedded SQL)
When defining a CLOB variable within RPG (independent of the size to be used) and using this CLOB variable in an SQL PREPARE Statement, the program is compiled without problems but when executing the program SQLCODE -311 (Length in varying-length, LOB, or XML host variable not valid) is returned.
When defining a character (fixed or varying length) variable within RPG. It can be used in an SQL PREPARE statement, but the maximum size is restricted to 32K (even though RPG currently supports a maximum length of 16 MB for all character fields or data structures).
SystemAdmin 110000D4XK3129 Posts
Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt2013-01-03T23:46:25ZThis is the accepted answer. This is the accepted answer.Can you give some more details about the statement when you get the SQL0102. Are you trying to set the CLOB variable with a literal string > 32K? The maximum length of a literal string is 32K. In order to set the CLOB variable you may need to break the literal up into multiple smaller literals.