Dynamically creating procedures

"Giedt, Matt" <[email protected]> Thu, 7 Feb 2008 17:08:01 -0500
Newsgroups gmane.comp.db.mysql.java
Message-ID <17C8BF87FD5238498A83034200664FB44E0B3D@MSGMROCLN2WIN.DMN1.FMR.COM>
I'm trying to create a stored procedure in MySQL using a Java Statement.
The procedures are kept in an external XML file, and when the
application starts, parses the file for the SP's to load.

So:

final DataSource ds = ((DataSource)ctx.lookup( "java:/MySQL" ) );
final Connection conn = ds.getConnection();
final Statement stmt = conn.createStatement( );
log.info( wrapper.getContent() );
stmt.executeUpdate( wrapper.getContent() );
stmt.close();
conn.close();

The procedure is:

DELIMITER |
DROP PROCEDURE IF EXISTS LOGIN|
CREATE PROCEDURE LOGIN(
IN username varchar(32),
IN password varchar(32) )
BEGIN
SELECT  user_id
FROM    user
WHERE   username = username
AND     password = password;
END|
DELIMITER ;

And I get the error:

java.sql.SQLException: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'DELIMITER |
DROP PROCEDURE IF EXISTS QMS_LOGIN|
CREATE PROCEDURE `QMS_LOGIN`(
IN' at line 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1074)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1008)
        at
org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedSt
atement.java:186)

Is there any way to programatically create a SP in MySQL?

With thanks.

(Note, this works fine via mysql.exe)