Re: Dynamically creating procedures

Hi Matt,

we are doing similar things. the issue for us was, that executeUpdate 
executes just one command and the DELIMTER statement, DROP PROCEDURE and 
CREATE PROCEDURE are multiple ones.
So first you should try executing the DROP statement alone.

Regading DELIMITER + CREATE ... I know, that setting the delimiter did 
not work, as expected but I'm not sure, if that was a mysql or sql 
server issue. Anyway - if you have an issue there, here is how we solved 
it: By playing around with the statement delimiters in the stored 
procedure. It worked even with complex scripts, that every sql 
instruction was enclosed by if, begin+end or similar controll 
statements, so we could remove all ; and have the database server still 
understand the code correctly.

I'm not sure, if the jdbc batch execution would also solve the issue, 
but that could be another thing to look at.

Regards,
Paul

Giedt, Matt schrieb:
> 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)
>
>   


-- 
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/
_/   BDM Business Data Management GmbH
_/
_/   Firmenbuch: FN 242059w
_/   Gerichtsstand: Landesgericht Wiener Neustadt  
_/   UID: ATU57685758
_/
_/   Grenzgasse 111, Objekt 9/4
_/   2340 Mödling
_/   Tel,Fax: +43 2236 46 393
_/
_/   Ing. Paul Palaszewski, (Hons) B.Sc.
_/   Geschäftsführer
_/   Mobil: +43(650) 37 36 030
_/   E-Mail: [email protected]
_/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 


-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/[email protected]