By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,584 Members | 1,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,584 IT Pros & Developers. It's quick & easy.

Stored procedure, function, very dynamic SQL and casting

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE SP_TAXE_DESCRIERE (IN TYPE_TAX_ID INTEGER,
  2.                     IN ID_TAX INTEGER,
  3.                      OUT TAX_NAME VARCHAR(200),
  4.                      OUT SQLSTATE_OUT CHAR(5),
  5.                                      OUT SQLCODE_OUT INTEGER )
  6.     DYNAMIC RESULT SETS 1
  7. ------------------------------------------------------------------------
  8. -- SQL Stored Procedure 
  9.     -- SQLSTATE_OUT  
  10.     -- SQLCODE_OUT  
  11. ------------------------------------------------------------------------
  12. MAIN: BEGIN
  13.         -- Declare variables
  14.         DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  15.         DECLARE SQLCODE INT DEFAULT 0;
  16.         DECLARE STMT1 VARCHAR(800);
  17.         DECLARE STMT2 VARCHAR(800);
  18.         DECLARE CURRENT_POSITION INT DEFAULT 0;
  19.         DECLARE NO_OF_ROWS INT DEFAULT 0;
  20.         DECLARE TABLE_FOR_COL VARCHAR(50);
  21.         DECLARE COL_NAME VARCHAR(50);
  22.         DECLARE COL_TYPEULUI VARCHAR(50);
  23.         DECLARE COL_MU VARCHAR(20);
  24.         DECLARE COL_VALUE VARCHAR(20);
  25.  
  26. TEMP_INSERT: 
  27.     BEGIN
  28.         DECLARE GLOBAL TEMPORARY TABLE TAX_VALUES (COL_NAME VARCHAR(50) NOT NULL,
  29.                                 COL_VALUE VARCHAR(50) NOT NULL,
  30.                                 COL_MU VARCHAR(20) NOT NULL) WITH REPLACE;
  31.     CURSOR_DETALII:
  32.         BEGIN
  33.  
  34.             DECLARE WHICH_DETAILS CURSOR FOR 
  35.                 SELECT DENUMIREA, CAMP, TIP, UM FROM DETAILS WHERE ID_TAXA=TYPE_TAX_ID;
  36.             SELECT OBS, NR_CAMPURI INTO TAX_NAME, NO_OF_ROWS 
  37.                 FROM DEFINITIONS WHERE ID_TAXA=TYPE_TAX_ID;
  38.             OPEN WHICH_DETAILS;
  39.             SET NO_OF_ROWS = NO_OF_ROWS+1;
  40.             WHILE CURRENT_POSITION < NO_OF_ROWS DO
  41.                 FETCH WHICH_DETAILS INTO TABLE_FOR_COL, COL_NAME, COL_TYPE, COL_MU;
  42.                 SET TABLE_FOR_COL = TABLE_FOR_COL||'_1';
  43.                 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||''')';
  44.                 INSERT INTO poi values(1,STMT1);
  45.                 PREPARE PREPARED_STMT1 FROM STMT1;
  46.                 EXECUTE PREPARED_STMT1;
  47.                 SET CURRENT_POSITION = CURRENT_POSITION+1;
  48.             END WHILE;
  49.  
  50.             CLOSE WHICH_DETAILS;
  51.     END CURSOR_DETALII;
  52. END TEMP_INSERT;
  53.  
  54. CURSOR_DECLARATION_FOR_TEMP: 
  55.     BEGIN
  56.         DECLARE REZULTAT CURSOR FOR
  57.             SELECT * FROM SESSION.TAX_VALUES;
  58.  
  59.         -- Declare handler
  60.             DECLARE EXIT HANDLER FOR SQLEXCEPTION
  61.                 SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
  62.                     FROM SYSIBM.SYSDUMMY1;
  63.         -- Cursor left open for client application
  64.         OPEN REZULTAT;
  65. END CURSOR_DECLARATION_FOR_TEMP;
  66.  
  67. SET SQLSTATE_OUT = SQLSTATE;
  68. SET SQLCODE_OUT = SQLCODE;
  69. return;
  70. END MAIN
  71.  
Mar 20 '09 #1
Share this Question
Share on Google+
2 Replies


P: 57
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.
Mar 27 '09 #2

P: 2
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
Expand|Select|Wrap|Line Numbers
  1. Connection con = null;
  2. CallableStatement cs = null;
  3. int contor = 0;
  4. try
  5. {
  6.     con = Page.getDS().getConnection();
  7.     cs = con.prepareCall("{CALL SP_TAXE_DESCRIERE (?, ?, ?, ?, ? )}");
  8.     cs.setInt(1, 100);
  9.     cs.setInt(2, 100);
  10.     cs.registerOutParameter(3, Types.VARCHAR);
  11.     cs.registerOutParameter(4, Types.CHAR);
  12.     cs.registerOutParameter(5, Types.INTEGER);
  13.     cs.execute();
  14.     ResultSet rs = cs.getResultSet();
  15.     while(rs.next())
  16.     {
  17.              String name = rs.getString("COL_NAME");
  18.              String value = rs.getString("COL_VALUE");
  19.              String    mu = rs.getString("COL_MU");
  20.     }
  21. }
  22. catch (SQLException e)
  23. {
  24.     e.printStackTrace(System.out);
  25.     try
  26.     {
  27.         System.out.println("status "+cs.getString(4)+" error code"+cs.getInt(5));
  28.     }
  29.     catch(SQLException s)
  30.     {
  31.         s.printStackTrace(System.out);
  32.     }
  33. }
  34. finally
  35. {
  36.     try
  37.     {
  38.         con.close();
  39.     }
  40.     catch(Exception ignored)
  41.        {
  42.        }
  43. }
  44.  
Attached Files
File Type: txt exampleV1.txt (2.5 KB, 400 views)
File Type: txt subroutine.txt (2.2 KB, 372 views)
Mar 31 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.