Topic
4 replies Latest Post - ‏2013-01-03T23:46:25Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic Dynamic SQL - Data Length limitation on the Prepare SQL stmt

‏2012-12-27T12:09:53Z |
Hi Team,
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 "'

Select XXXXXXXXX
From XXXXXXXXX
Where XXXXXXXXX

" is too long.. SQLCODE=-102, SQLSTATE=54002, DRIVER=4.11.69

Can you pls advise how to resolve the issue?

Thanks
Dattu21
Updated on 2013-01-03T23:46:25Z at 2013-01-03T23:46:25Z by SystemAdmin
  • B.Hauser
    B.Hauser
    223 Posts
    ACCEPTED ANSWER

    Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt

    ‏2012-12-30T09:19:42Z  in response to SystemAdmin
    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.

    Birgitta
  • kmilligan
    kmilligan
    14 Posts
    ACCEPTED ANSWER

    Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt

    ‏2013-01-02T21:44:15Z  in response to SystemAdmin
    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;
    EXECUTE s1;
    • B.Hauser
      B.Hauser
      223 Posts
      ACCEPTED ANSWER

      Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt

      ‏2013-01-03T08:03:54Z  in response to kmilligan
      Kent,

      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).

      Birgitta
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: Dynamic SQL - Data Length limitation on the Prepare SQL stmt

    ‏2013-01-03T23:46:25Z  in response to SystemAdmin
    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.

    Paul