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:
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?
dBforums
>
PL/SQL through JDBC
09-20-07, 05:58
PL/SQL through JDBC
Linear Mode
