Dynamically creating procedures

[Giedt, Matt]

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)

[Paul Palaszewski]

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: [phone]
_/
_/   Ing. Paul Palaszewski, (Hons) B.Sc.
_/   Geschäftsführer
_/   Mobil: [phone]
_/   E-Mail: paul.palaszewski@...
_/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 


--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/java?unsub=lists@...

[Giedt, Matt]

Thanks Paul.

Good tip on executing the drop command outside of the 'delimiter +
create'. You have to do this w/ SQL Server as well -- I should have
caught that. (As opposed to Oracle which lets you do 'create or
replace')

I can't, however, get past the DELIMITER statement.

      stmt.execute( "DELIMITER |" ); // <-- blows up here
      stmt.execute( "CREATE PROCEDURE FOO() BEGIN SELECT 1 AS FOO; END|"
);
      stmt.execute( "DELIMITER ;" );

OR:

      stmt.executeUpdate( "DELIMITER |" ); // <-- blows up here
      stmt.execute( "CREATE PROCEDURE FOO() BEGIN SELECT 1 AS FOO; END|"
);
      stmt.executeUpdate( "DELIMITER ;" );

OR:

      stmt.addBatch( "DELIMITER |" );
      stmt.addBatch( "CREATE PROCEDURE FOO() BEGIN SELECT 1 AS FOO;
END|" );
      stmt.addBatch( "DELIMITER ;" );
      stmt.executeBatch(); // <-- blows up here

Hopefully I'll be able to limit my code paths to a single semi-colon!

With thanks,
-Matt

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/java?unsub=lists@...

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Giedt, Matt wrote:
> Thanks Paul.
>
> Good tip on executing the drop command outside of the 'delimiter +
> create'. You have to do this w/ SQL Server as well -- I should have
> caught that. (As opposed to Oracle which lets you do 'create or
> replace')

Hi Paul,

> I can't, however, get past the DELIMITER statement.
>
>       stmt.execute( "DELIMITER |" ); // <-- blows up here

That's because "DELIMITER" isn't a SQL statement, it's for the mysql
command-line client. There's no need for the construct in client
libraries, as the string you pass to Statement.execute() has a length
(and thus doesn't have to be parsed for a token to find the "end").

Drop your "delimiter" statements, and you should be good to go.

        -Mark
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHrQB1tvXNTca6JD8RArsJAJsEUc6rrTE0ZT2RqH04So1qEYJB/wCfVwvx
TF7ngjrr76qaw63vlg/aWDQ=
=YEHl
-----END PGP SIGNATURE-----

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/java?unsub=lists@...