Creating Stored Procedure in Java
Hi All,
I would like to ask how to create a stored procedure in Java.
My project now is to create a program that will open a DB2 SQL File (Creating Table and Stored Procedure) and then Execute it.
Executing the CREATE TABLE command is not that difficult since I can break the execution down by statement (divide by ";" in the SQL file).
The logic I applied here are:
1. Open the SQL file and store the contents into a String object.
2. Call the executeUpdate(string_sql_fileContent) method of Statement Object.
Example: SQL File contect to be executed.
===========================================================
CREATE TABLE MDMTEST.sample
(firstname CHARACTER(40),
middlename CHARACTER(40),
lastnameCHARACTER(40),
idNoSMALLINTNOT NULL
)
DATA CAPTURE NONE;
#SYNC 10;
ALTER TABLE MDMTEST.sample
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
#SYNC 20;
ALTER TABLE MDMTEST.sample
ADD PRIMARY KEY
(idNo
);
#SYNC 30;
RUNSTATS ON TABLE MDMTEST.sample
AND INDEXES ALL
SHRLEVEL REFERENCE;
===========================================================
I can divide this by";" and execute them by statement.
But my problem is when creating Stored Procedure... Since in stored procedure there are so many stub-statements...
Example: Content of SQL File for creating stored procedure.
===========================================================
CREATE PROCEDURE MDMTEST.DROP_ALL_OBJECTS ( )
SPECIFIC MDMTEST.SQL051109115453000
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
Delete Statement;
Insert Statement;
Update Statement;
END;
===========================================================
Take note there are three statements inside the CREATE..BEGIN.. and END.. (namely Delete, Insert, and Update Statements).
How can I execute the command for creating the procedure in Java? I believe that creating stored procedures in other databases are almost the same...
So... anyboby who have tried creating stored procedure in Java?
Please help me in this... I wanna pass the subject hehe..
# 1
Addtition:I am afraid that Statement's executeUpdate() is in per statement basis and I hope that it can be used to call a SQL command for create a stored procedure [CREATE PROCEDURE () BEGIN... END]
# 2
From the Book:
The following SQL statement creates a stored procedure:
create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME
The following code puts the SQL statement into a string and assigns it to the variable createProcedure , which we will use later:
String createProcedure = "create procedure SHOW_SUPPLIERS " +
"as " +
"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"order by SUP_NAME";
The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:
Statement stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
The procedure SHOW_SUPPLIERS will be compiled and stored in the database as a database object that can be called, similar to the way you would call a method.
Question is... How about if there is sub-statements inside CREATE PROCEDURE ...
# 3
remember....no matter how many sub statements it doesnt matter...you are CREATING a PROCEDURE..in the sense you are creating an entry in table....and thats it...you are NOT executing a procedure...so
create procedure behaves a same way as of a insert statement...no matter how many statements inside the stored procedure ..it will be considered as a simple string to be populated in the appropriate column of the table ..
# 4
I have used these files as a sample:
Java Program:
import java.io.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
import javax.swing.border.*;
import my.db.components.*;
public class ProcedureUploader {
private MyDBConnection dbConn=new MyDBConnection();
private Statement statement;
public ProcedureUploader() {
try{
dbConn.connectDB ("206.206.206.206","12345","DEV","mdmtest","mdmtest");
this.statement=dbConn.getStatement ();
}catch(SQLException sqle){
sqle.printStackTrace();
}
}
public String getContents(String fileName) throws IOException{
File inputFile = new File(fileName);
FileReader in = new FileReader(inputFile);
int c; String contents="";
while ((c = in.read()) != -1) contents+=(char) c;
in.close();
return contents;
}
public void executeSQLCommand(String file) throws IOException{
String command="";
try{
command=getContents(file);
statement.executeUpdate(command);
System.out.println("Command Execution Successful.");
}catch(Exception ex){
System.out.println("There is a problem executing the command. Please check the SQL file and re-execute again.");
ex.printStackTrace();
}
}
public static void main(String args[]) throws IOException{
ProcedureUploader uploader=new ProcedureUploader();
uploader.executeSQLCommand("C:\\I2\\Development\\ProcedureUploader\\sample.sql");
}
}
SQL File:
CREATE PROCEDURE MDMTEST.sampleProc ( )
SPECIFIC MDMTEST.sampleProc
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
delete from SAMPLE;
commit;
select * from SAMPLE;;
#SYNC 10;
===========================================================
The problem is that ... there will be an error:
===========================================================
There is a problem executing the command. Please check the SQL file and re-execute again.
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: delete from SAMPLE;;PECIAL REGISTERS
;<psm_repeat>
at com.ibm.db2.jcc.c.fg.e(fg.java:1596)
at com.ibm.db2.jcc.c.fg.b(fg.java:1160)
at com.ibm.db2.jcc.b.gb.h(gb.java:217)
at com.ibm.db2.jcc.b.gb.b(gb.java:46)
at com.ibm.db2.jcc.b.w.b(w.java:40)
at com.ibm.db2.jcc.b.vb.f(vb.java:118)
at com.ibm.db2.jcc.c.fg.m(fg.java:1155)
at com.ibm.db2.jcc.c.fg.a(fg.java:1865)
at com.ibm.db2.jcc.c.fg.c(fg.java:517)
at com.ibm.db2.jcc.c.fg.executeUpdate(fg.java:501)
at ProcedureUploader.executeSQLCommand(ProcedureUploader.java:44)
at ProcedureUploader.main(ProcedureUploader.java:54)
===========================================================
But still the procedure will be created but until the first ";" only... OR.. creates a stored procedure having the following lines only.
===========================================================
CREATE PROCEDURE MDMTEST.sampleProc ( )
SPECIFIC MDMTEST.sampleProc
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
delete from SAMPLE;
===========================================================
# 5
I have tried this sample program executing the SQL Codes below:
============================================================
CREATE PROCEDURE MDMTEST.sampleProc ()
BEGIN
DELETE FROM MDMTEST.SAMPLE;
COMMIT;
END
============================================================
I works fine but if I will make it like:
============================================================
CREATE PROCEDURE MDMTEST.sampleProc ()
BEGIN
SELECT * FROM MDMTEST.SAMPLE;
END
============================================================
There will be a problem.. As what I have observed... the problem is in the SELECT statement. Do you guys know the reason behind this problemo? hehe
