Stored procedure, function, very dynamic SQL and casting

[IuliaS]

Hello everyone!

I want to create a stored procedure, so I can more easily, and transparent retrieve data from db2. Long story short: when a user wants to put some data in the DB, he also creates the tables and their links. When getting the data from the DB... well suffice to say it's ugly. I want to use one stored procedure that will return a result set as (name, value) pairs so I can display it nice and easy in the UI. So far I've managed to write the stored procedure, but I am stuck at inserting the value part in the declared temporary table. Why? Because I have to convert decimal to varchar and I can't figure out how to do that nice and easy. Decimal->char->varchar will add 0's to the left until the size of the decimal.
What I need help with? Making this query work:
SET STMT1 = 'INSERT INTO SESSION.TAX_VALUES VALUES
('''||COL_NAME||''',
(SELECT '||COL_NAME||' FROM '||TABLE_FOR_COL||' WHERE ID_TAX='
||CHAR(ID_TAX)||'),
'''||COL_MU||''')';

This part is giving me the headaches:
(SELECT '||COL_NAME||' FROM '||TABLE_FOR_COL||' WHERE ID_TAX='
||CHAR(ID_TAX)||'),

How can I put that in a function? How can I format it nicely? In the function. I've seen the parametised way of doing this, with the ?, but I've never seen it done for the table name itself. Will that work? And how could I actually put the returned value from the select in a local variable? There is the SQL select into statements and then there is this. Will it work with the ? part?

Any help will be greatly appreciated. Thank you very much for your time.

Kind regards,
Iulia

the code snippet:

CREATE PROCEDURE SP_TAXE_DESCRIERE (IN TYPE_TAX_ID INTEGER,
                    IN ID_TAX INTEGER,
                     OUT TAX_NAME VARCHAR(200),
                     OUT SQLSTATE_OUT CHAR(5),
                                     OUT SQLCODE_OUT INTEGER )
    DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure 
    -- SQLSTATE_OUT  
    -- SQLCODE_OUT  
------------------------------------------------------------------------
MAIN: BEGIN
        -- Declare variables
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
        DECLARE SQLCODE INT DEFAULT 0;
        DECLARE STMT1 VARCHAR(800);
        DECLARE STMT2 VARCHAR(800);
        DECLARE CURRENT_POSITION INT DEFAULT 0;
        DECLARE NO_OF_ROWS INT DEFAULT 0;
        DECLARE TABLE_FOR_COL VARCHAR(50);
        DECLARE COL_NAME VARCHAR(50);
        DECLARE COL_TYPEULUI VARCHAR(50);
        DECLARE COL_MU VARCHAR(20);
        DECLARE COL_VALUE VARCHAR(20);
 
TEMP_INSERT: 
    BEGIN
        DECLARE GLOBAL TEMPORARY TABLE TAX_VALUES (COL_NAME VARCHAR(50) NOT NULL,
                                COL_VALUE VARCHAR(50) NOT NULL,
                                COL_MU VARCHAR(20) NOT NULL) WITH REPLACE;
    CURSOR_DETALII:
        BEGIN
 
            DECLARE WHICH_DETAILS CURSOR FOR 
                SELECT DENUMIREA, CAMP, TIP, UM FROM DETAILS WHERE ID_TAXA=TYPE_TAX_ID;
            SELECT OBS, NR_CAMPURI INTO TAX_NAME, NO_OF_ROWS 
                FROM DEFINITIONS WHERE ID_TAXA=TYPE_TAX_ID;
            OPEN WHICH_DETAILS;
            SET NO_OF_ROWS = NO_OF_ROWS+1;
            WHILE CURRENT_POSITION < NO_OF_ROWS DO
                FETCH WHICH_DETAILS INTO TABLE_FOR_COL, COL_NAME, COL_TYPE, COL_MU;
                SET TABLE_FOR_COL = TABLE_FOR_COL||'_1';
                SET STMT1 = 'INSERT INTO SESSION.TAX_VALUES VALUES('''||COL_NAME||''',(SELECT '||COL_NAME||' FROM '||TABLE_FOR_COL||' WHERE ID_PERSOANA='||CHAR(ID_TAX)||'), '''||COL_MU||''')';
                INSERT INTO poi values(1,STMT1);
                PREPARE PREPARED_STMT1 FROM STMT1;
                EXECUTE PREPARED_STMT1;
                SET CURRENT_POSITION = CURRENT_POSITION+1;
            END WHILE;
 
            CLOSE WHICH_DETAILS;
    END CURSOR_DETALII;
END TEMP_INSERT;
 
CURSOR_DECLARATION_FOR_TEMP: 
    BEGIN
        DECLARE REZULTAT CURSOR FOR
            SELECT * FROM SESSION.TAX_VALUES;
 
        -- Declare handler
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
                SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
                    FROM SYSIBM.SYSDUMMY1;
        -- Cursor left open for client application
        OPEN REZULTAT;
END CURSOR_DECLARATION_FOR_TEMP;
 
SET SQLSTATE_OUT = SQLSTATE;
SET SQLCODE_OUT = SQLCODE;
return;
END MAIN
 

[cburnett]

Perhaps the easiest way is to turn the problem around:

1. WHERE INT(ID_PERSOANA)=?
2. EXECUTE PREPARED_STMT1 USING ID_TAX

You can't use parameter markers in select lists or as table names but there's nothing stopping you building up the statement in the way you have.

For the variable data type translation, look at the DIGITS function (integer to char with leading zeroes) or the CAST function. Note that with the proposed solution an index on ID_PERSOANA cannot be used.

[IuliaS]

Hello everyone!
Thank you for the prompt response. I have finally managed to write everything I needed, including the java code to call the procedures and all seems to be working just fine. BUT... since I am still pretty much exploring the unknown I am worried about the use of the DECLARE GLOBAL TEMPORARY TABLE. I've read the IBM explanation for it from here, and I'm not sure what a session means. The code will run within a servlet and I intend to use a database connection pool. Does that mean that if I reuse a connection previously owned by user A for user B, user B will get the information retrieved for user A? If so, how can I fix it? Maybe my scenario is wrong, so in other words: is there anyway one user can get the data that was retrieved for another user?
I've uploaded the code, maybe others will find it usefull. It is working and if anyone has any ideas of improving it, please post them. Just don't be too harsh, it is my first try.
Passing some knowledge for others:
Biggest problem was trying to obtain a value from a very dynamic SQL (found in subroutine.txt) without using a cursor. I came to the conclusion it's not possible. If anyone can prove me wrong, please post an example. I tried making a function for it, turns out functions can't be this complicated (case statements, prepare statement or execute and quite a few others are NOT allowed in the function body. read this for more information). So instead I just made another stored procedure that returns a varchar. Using a cursor to return just ONE value out of a select it is a bit of an overkill, but I can't find another way to do it. First I forgot the WITH RETURN part for the cursor, so be carefull, don't make the same mistake.
Thank you very much for all your help. Hope some of this will help others as well.
Kind regards,
Iulia

Java code for calling the stored procedure

Connection con = null;
CallableStatement cs = null;
int contor = 0;
try
{
    con = Page.getDS().getConnection();
    cs = con.prepareCall("{CALL SP_TAXE_DESCRIERE (?, ?, ?, ?, ? )}");
    cs.setInt(1, 100);
    cs.setInt(2, 100);
    cs.registerOutParameter(3, Types.VARCHAR);
    cs.registerOutParameter(4, Types.CHAR);
    cs.registerOutParameter(5, Types.INTEGER);
    cs.execute();
    ResultSet rs = cs.getResultSet();
    while(rs.next())
    {
             String name = rs.getString("COL_NAME");
             String value = rs.getString("COL_VALUE");
             String    mu = rs.getString("COL_MU");
    }
}
catch (SQLException e)
{
    e.printStackTrace(System.out);
    try
    {
        System.out.println("status "+cs.getString(4)+" error code"+cs.getInt(5));
    }
    catch(SQLException s)
    {
        s.printStackTrace(System.out);
    }
}
finally
{
    try
    {
        con.close();
    }
    catch(Exception ignored)
       {
       }
}