Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Question Unanswered: PL/SQL through JDBC

    I'm trying to run the following over JDBC:

    Code:
    	
    DECLARE counter INT DEFAULT 0;
    	DECLARE alter_statement VARCHAR(100);
    	SELECT COUNT(*) INTO counter FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='USERS' AND NAME='EXTRACOLUMN';
    
    	SET alter_statement = 'ALTER TABLE USERS ADD COLUMN EXTRACOLUMN VARCHAR(50)';
    	IF counter = 0 THEN 
    		EXECUTE IMMEDIATE alter_statement;
    	END IF;
    As you can see, I need to check whether a column exists, and if not, create it.

    Attempting to execute the above as a PreparedStatement throws the following error:

    Code:
    java.sql.SQLException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: DECLARE counter INT DEFAULT 0;;
    	;<labeled_begin_atomic>: 
    	DECLARE counter INT DEFAULT 0;
    	DECLARE alter_statement VARCHAR(100);
    	SELECT COUNT(*) INTO counter FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='USERS' AND NAME='EXTRACOLUMN';
    
    	SET alter_statement = 'ALTER TABLE USERS ADD COLUMN EXTRACOLUMN VARCHAR(50)';
    	IF counter = 0 THEN 
    		EXECUTE IMMEDIATE alter_statement;
    	END IF; :
    The only way to achieve this seems to be to create a procedure with the above content, then call the procedure, then drop it. So I have to run three JDBC statements, which have the following content:

    Statement 1:
    Code:
    CREATE PROCEDURE TESTPL () LANGUAGE SQL
    BEGIN
    	DECLARE counter INT DEFAULT 0;
    	DECLARE alter_statement VARCHAR(100);
    	SELECT COUNT(*) INTO counter FROM SYSIBM.SYSCOLUMNS WHERE TBNAME='USERS' AND NAME='EXTRACOLUMN';
    
    	SET alter_statement = 'ALTER TABLE USERS ADD COLUMN EXTRACOLUMN VARCHAR(50)';
    	IF counter = 0 THEN 
    		EXECUTE IMMEDIATE alter_statement;
    	END IF;
    END
    Statement 2:
    Code:
    CALL TESTPL
    Statement 3:
    Code:
    DROP PROCEDURE TESTPL
    Is there a better way to do this? (preferably without having to create a procedure). Is it completely impossible to execute anonymous blocks of PL/SQL over JDBC?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am not sure if this will work, but the error indicates that maybe you need to put the statements in a "BEGIN ATOMIC -- END" block. If that does not work, why not do the processing with java code instead of PL/SQL?

    Andy

  3. #3
    Join Date
    Sep 2007
    Posts
    2
    BEGIN ATOMIC
    ...
    END

    Doesn't seem to help. The reason I'm trying to get this working as PL/SQL is that I'm trying to put together a java based tool that can execute a set of PL/SQL scripts through JDBC. When developers need to write a new script, it's best if they can do this in SQL (not Java), and have the power of PL/SQL if required.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The only other thing I would suggest, is to have the java program parse the PL/SQL pulling each statement from it and executing each statement iteratively.

    Andy

Posting Permissions

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