JDBC, Create Stored Procedure, delimiters

[Jim Chandler]

Hello everyone,

I've been writing an application that will be running on top of MySQL 5.0+. I have deployment all taken care of, save for one (hopefully minor) detail.

On my first attempt, JDBC threw up at the sight of semicolons in my statements (MySQLSyntaxErrorException.) I've gotten it to work by creating statements out of the main file -> to String -> .split(";"). This works great except for my stored procedures. Those have semicolon delimiters inside of them - I don't know a way around this.

Has anyone else encountered this? Am I missing something obvious? I'd prefer to deploy from strings thru JDBC, so I can encrypt the schema creation files on-disk so as to protect it.

Here's the Java code, and the stored procedure that fails insertion.

for(int i = 0; i<files.length; i++)
{
File file = new File(files[i]);
if(file.getName().toUpperCase().endsWith("SQL") && !file.isDirectory())
{
RandomAccessFile raf = new RandomAccessFile(directory+"\\"+file.getName(), "r");
System.out.println("Processing file: "+file.getName());
byte[] thisBytes = new byte[(int)raf.length()];
raf.read(thisBytes);
String thisReallyBytes = new String(thisBytes);
System.out.println("This really bites! : "+thisReallyBytes);
// Break this string into bite-size pieces.
String[] nibbles = thisReallyBytes.split(";");
for(int s = 0; s<nibbles.length; s++)
{
Statement stmt = con.createStatement();
stmt.execute(nibbles[s]);
stmt.close();
}
}
}



Stored procedure SQL:

DROP PROCEDURE IF EXISTS InsertSerialnum;
delimiter ?
CREATE PROCEDURE InsertSerialnum (IN inSerialnum VARCHAR(20),
IN inItemId LONG)
BEGIN
DECLARE snCount INTEGER;
SELECT COUNT(id) INTO snCount FROM serialNumbers
WHERE (serialnum=inSerialnum);
IF (snCount < 1) THEN
IF (inSerialnum != '') THEN
INSERT INTO serialNumbers (serialnum,itemId,dateCreated)
VALUES (inSerialnum,inItemId,Now());
END IF;
END IF;
END;
?
delimiter ;

... If I could get that stored procedure to run as a single PreparedStatement, I would be in business. But I don't know how to get around the semicolon problem.

Thanks for any replies!
-Jim

[Jeanne Boyarsky]

Jim,
A stored procedure is supposed to be defined in the database. In JDBC, you just refer to it by name (and parameters) via a CallableStatement.

If you don't want to or can't use a real stored procedure, you would need to split that into real standalone SQL statements within a transaction. For example:
1) get count query
2) logic to see whether to insert (in Java or SQL)
3) insert query

[Jim Chandler]

Thank you, Jeanne, for your reply. I apologize for not being more specific about the actual context of the problem - perhaps I could explain the context a bit further.

Calling stored procedures and general JDBC statements has never been a problem, once the database is up and running. However, the problem code is that which will run on an initial installation that detects a MySQL instance, connects, creates the initial database and all of the schema. I was hoping to put this all into one .JAR file to allow people easy copy / distribution, however, after searching for the better part of the day yesterday, perhaps I just don't have an understanding of how to properly initialize and set up a new MySQL database w/ existing schema. I thought this could all be done via JDBC, and as far as I can tell, it can be, save for this one problem - how to create a stored procedure in a single JDBC Statement.

Thanks,
-Jim

[Bob Good]

Perhaps you could exec a batch process from java to compile the stored procedure? Then you could use jdbc to execute callable statement if you needed to.

[Jim Chandler]

Bob,

Thanks, this solved it. pstmt.addBatch(); pstmt.executeBatch(); instead of pstmt.executeUpdate(); solved the issue.

-Jim