Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: quote problem with string/varchar in Dynamic SQL

    Hi there.

    Im new using DB2 and Im facing some problems and have decided to ask for help.


    Ive been reading about using dynamic SQL to develop stored procedures so that I could give it table/column/etc in its call. I managed to get some working, but in a little more complex query Ive got stucked and Im thinking it is due to the quotes.

    Please take a look:


    CREATE OR REPLACE PROCEDURE TEST_DYNAMIC_SELECT(
    IN PROC_LANGUAGE CHAR(5),
    IN PROC_CHANNEL DECIMAL(3),
    IN PROC_PROJECT VARCHAR(50),
    IN PROC_CONDICTION VARCHAR(150))
    LANGUAGE SQL
    RESULT SETS 1
    BEGIN
    DECLARE SEL_STMT VARCHAR(500);
    DECLARE C1 CURSOR WITH RETURN TO CALLER FOR S1;
    SET SEL_STMT = 'SELECT A. TEXT ,A. RTEXTOID ,A. HALT ,A. CPROJ
    FROM TABLE1 A, TABLE2 B WHERE A. CDSEN = B. CDSEN
    AND A. CHANNEL = B. CHANNEL ' || PROC_PROJECT || ' AND B. LANGUAGE = ' || PROC_LANGUAGE || '
    AND B. CHANNEL = ' || PROC_CHANNEL || ' ' || PROC_CONDICTION || ' ORDER BY A. TEXT';
    PREPARE S1 FROM SEL_STMT;
    OPEN C1;
    END


    I have 4 parameters:
    PROC_LANGUAGE = static (ex pt_PT)
    PROC_CHANNEL = static (ex 2)
    PROC_PROJECT = dynamic (could be any project the users is trying to get data)
    PROC_CONDICTION = dynamic (if a field from the screen is empty it appends one more AND in the end)

    When I try to call it:

    CALL TEST_DYNAMIC_SELECT('pt_PT',1,'AND A. CPROJ = 22','') (note that I wrote the first dynamic param)

    I got:
    [CALL - 0 row(s), 0.000 secs] [Error Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=PT_PT, DRIVER=3.50.152

    I noticed that the same error comes up when I execute de original query (all hardcoded) but without the single quotes where would be my first param:

    Select .... from.... AND B. LANGUAGE = 'pt_PT' works....but

    Select .... from.... AND B. LANGUAGE = pt_PT give me the same error message!

    I have no clue how to solve this problem...it is clear that my param pt_PT was evaluated but it seems that the quotes was missing and messed it up!

    What can I do guys???

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Always post your DB2 version + fixpack and operating-system on each new thread.

    From where do you invoke the CALL statement?

    IF From a command-line, or from some shell or shell-script (because then you need to escape the quotes because the shell can eat the quotes).

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dgo08 View Post
    the quotes was missing and messed it up!

    What can I do guys???
    Well, how'bout introducing those missing quotes?

    Code:
    CALL TEST_DYNAMIC_SELECT('''pt_PT''',1,'AND A. CPROJ = 22','')
    or

    Code:
    ... || ' AND B. LANGUAGE = ''' || PROC_LANGUAGE || ''' AND B. CHANNEL = '...

  4. #4
    Join Date
    May 2012
    Posts
    2
    Thank you both.

    The operating-system is a Windows XP + SP3, DB2 I almost sure it is an Express 9.7.5, but Im acessing it remotely using DBVisualizer.

    For now Icalling it from DBVisualizer console but when I get it working properly I will have to call it inside a Java program.


    Triple single quotes is the way to go? Im a system analyst, who worked with Java not long ago but Ive never had experience with databases other than CRUD inside applications, or calling stored procedures someone else had done.

    Well lets keep thins going please, I still need help...I tried what was said but still get the error:

    CALL TEST_DYNAMIC_SELECT('''pt_PT''',1,'AND A. CPROJ = 22','')
    [Error Code: -433, SQL State: 22001] DB2 SQL Error: SQLCODE=-433, SQLSTATE=22001, SQLERRMC='pt_PT', DRIVER=3.50.152
    Now the quotes can be seen in the log and I noticed the error code now is -433 (Error: VALUE IS TOO LONG), besides (pt_PT) should fit a char(5) lenght I changed it to VARCHAR(10) just to check it.

    As it started to send error -206 in my 3rd param I tried to put in the same way I did with the first and it turned into

    DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=AND A. CHANNEL = B. CHANNEL 'AND ;CDSEN
    AND;<name>, DRIVER=3.50.152


    What could be wrong now please???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •