Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: Stored procedure, function, very dynamic SQL and casting

    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

    PS:
    DB2 Version, fixpack and Edition:
    "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack "3".
    db2 type:
    Product name: "DB2 Workgroup Server Edition"
    License type: "License not registered"
    Expiry date: "License not registered"
    Product identifier: "db2wse"
    Version information: "9.1"
    Max number of CPUs: "4"
    Max amount of memory (GB): "16"
    Annotation: "1;(_b)"

    OS: unix.
    Attached Files Attached Files
    Last edited by IuliaS; 03-20-09 at 08:37.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My recommendation is to see what strings were assigned to STMT1, before making your procedure completely.

    If the data type of the column <COL_NAME> of the table <TABLE_FOR_COL> is Decimal, execution of the STMT1 will fail.
    Because, it will insert Decimal value to Varchar column.

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Quote Originally Posted by tonkuma
    My recommendation is to see what strings were assigned to STMT1, before making your procedure completely.

    If the data type of the column <COL_NAME> of the table <TABLE_FOR_COL> is Decimal, execution of the STMT1 will fail.
    Because, it will insert Decimal value to Varchar column.
    Thank you for your reply. I realised that problem and that is what I am trying to correct, but I don't know how.
    I am trying to figure out a way to execute this select:
    SELECT '||COL_NAME||' FROM '||TABLE_FOR_COL||' WHERE ID_TAX='
    ||CHAR(ID_TAX)
    and process the returned value as a varchar. How can I put the returned value in a variable? How can I cast it to varchar without having to worry about adding 0's to the left of my decimal? Or how can I format a timestamp type? I know the COL_NAME's type. I figured I could do a case statement and put the returned value from within the select in a variable accordingly, and put that case statement in function (that way I could call it directly from within the prepared statement, right?).
    BUT I don't know how to put THAT returned value in ANY kind of variable. How do I do that? I tryed with ?, select into, nothing works. Or I am missing something really important. My guess, the latter.

    Any enlightment on this matter will be very helpfull. Thank you very much again!

    Kind regards,
    Iulia

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The way to convert a selected column value to varchar would be something like following CASE statement.
    Where {} are your way to know the data type of <COL_NAME>.
    Code:
    CASE {data type of <COL_NAME>}
    WHEN {char} THEN
         <COL_NAME>
    WHEN {varchar} THEN
         <COL_NAME>
    WHEN {integer} THEN
         CHAR(<COL_NAME>)
    WHEN {decimal} THEN
         STRIP(CHAR(<COL_NAME>),L,'0')
    WHEN {timestamp} THEN
         VARCHAR(<COL_NAME>)
    ...
    END

  5. #5
    Join Date
    Mar 2009
    Posts
    5

    Post final draft

    Hello everyone!

    Again 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
    Code:
    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)
           {
           }
    }
    Attached Files Attached Files
    Last edited by IuliaS; 03-30-09 at 09:04.

Posting Permissions

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