Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Red face Unanswered: Problem Converting Comma Seperated List to Table

    Hai all,
    I have a problem with a software I am developing that is supposed to support oracle, db2 and sql server.
    Before i begin, my knowledge is zero about DB2 - this is basically the first time that ive really worked on it and it has been a slow and painful learning process.

    Anyway, my problem:
    I use a function, which is available for the web, which takes a comma separated string and converts it to a table - which i then can use to find rows that match and return the matched rows.
    It works as follows:

    CREATE FUNCTION [dbo].[iter_charlist_to_table]
    (@list ntext,
    @delimiter nchar(1) = N',')
    RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
    str varchar(4000),
    nstr nvarchar(2000)) AS

    BEGIN
    DECLARE @pos int,
    @textpos int,
    @chunklen smallint,
    @tmpstr nvarchar(4000),
    @leftover nvarchar(4000),
    @tmpval nvarchar(4000)

    SET @textpos = 1
    SET @leftover = ''
    WHILE @textpos <= datalength(@list) / 2
    BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
    SET @textpos = @textpos + @chunklen

    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0
    BEGIN
    SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
    INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
    SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
    SET @pos = charindex(@delimiter, @tmpstr)
    END

    SET @leftover = @tmpstr
    END

    INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
    RETURN
    END

    Ive rewritten this in Oracle, with no problems - but I've had huge problems in DB2. Anyways I eventually came to the conclusion to write in a stored procedure in java which looks as follows:

    import java.sql.*;
    import java.util.StringTokenizer;// JDBC classes

    public class java_sp {
    public static void iter_charlist_to_table(String data, String seperator )
    throws SQLException {
    int errorCode = 0;
    boolean bFlag;
    String sql = "";

    StringTokenizer st = new StringTokenizer(data,seperator);
    while (st.hasMoreTokens()) {
    sql = sql + "select " + st.nextToken() + " as nstr from sysibm.sysdummy1 " + " union\n";
    }
    sql = sql.substring(0,sql.length() -8);
    sql = sql + ";";
    PreparedStatement stmt = null;

    try {
    Connection con = DriverManager.getConnection("jdbc:default:connecti on");

    stmt = con.prepareStatement( sql );
    bFlag = stmt.execute();
    } catch ( SQLException sqle) {
    errorCode = sqle.getErrorCode();
    throw new SQLException( errorCode + " : FAILED" );
    }
    }
    }

    Which is nice and compact and simple -- but Ive hit a snag:

    while call ITER_CHARLIST_TO_TABLE('1,2,3,4,5,6,7,8,9',',') works as expect and it returns a table list, it won't work under my desired plan which is:

    select id,product_name,product_type from admin.product where id in (call ITER_CHARLIST_TO_TABLE('1,2,6,9,12,18,22,41,42',', ') WITH RETURN TO CLIENT )

    Eventually the hard coded stuff would be replaced by a ? of course and it would by dynamic - but trying to do a call seems wrong (and is) and i can't think of how else to get this work.

    Any help would be greatfully received.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I can't understand why you are doing what you are doing, but in the context of a query you should think about using a table function, not a stored procedure. DB2 SQL reference contains syntax diagrams that clearly explain which constructs are possible and which are not.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with n_i.
    I found many doubtful tokens compared with syntax by just looking only first 6 lines.
    CREATE FUNCTION [dbo].[iter_charlist_to_table]
    (@list ntext,
    @delimiter nchar(1) = N',')
    RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
    str varchar(4000),
    nstr nvarchar(2000)) AS
    - "[" and "]"
    - "ntext"
    - "nchar"
    - "= N','"
    - "@tbl" between "RETURNS" and "TABLE"
    - "IDENTITY(1, 1)"
    - "NOT NULL"
    - "nvarchar"
    - "AS"

  4. #4
    Join Date
    Jun 2008
    Posts
    3
    Quote Originally Posted by tonkuma
    I agree with n_i.
    I found many doubtful tokens compared with syntax by just looking only first 6 lines.

    - "[" and "]"
    - "ntext"
    - "nchar"
    - "= N','"
    - "@tbl" between "RETURNS" and "TABLE"
    - "IDENTITY(1, 1)"
    - "NOT NULL"
    - "nvarchar"
    - "AS"
    you didn't read properly what I wrote. This is a T-SQL statement that i was trying to convert to DB2.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    OK, I see that the function is T-SQL.

    But, you didn't write that it was written in T-SQL or worked on SQL Server, in your first post which include the function.
    I couldn't find the word "T-SQL".
    I found only one sentence including the word "sql server".
    I have a problem with a software I am developing that is supposed to support oracle, db2 and sql server.
    Last edited by tonkuma; 06-20-08 at 01:35.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe this article is of some help for you: http://www.ibm.com/developerworks/db...03stolze1.html

    p.s: With respect to functions and procedures, DB2 sticks very closely to the SQL standard, which is typically taught in classes that are not product-specific.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jun 2008
    Posts
    3

    Thumbs up

    Quote Originally Posted by stolze
    Maybe this article is of some help for you: http://www.ibm.com/developerworks/db...03stolze1.html

    p.s: With respect to functions and procedures, DB2 sticks very closely to the SQL standard, which is typically taught in classes that are not product-specific.

    Thanks

    Now thats a great article.... and it matches with what i want....

    I find IBM DB2 documentation very good, i just wish it was easier to search through

    Thanks again...

Posting Permissions

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