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)