Thanks. There are no tx stmts in Derby SQL. In order to commit the SQL create schema so the tables can be created in the proc, I have to create the schemas in Java, should have done that anyway, just got lazy.
-----Original Message----- From: Dag H. Wanvik [mailto:[email protected]] Sent: Wed 6/23/2010 3:58 PM To: Derby Discussion Cc: Subject: Re: Procedure issue Hi, The following example works using a nested connection. Your procedure comment says: get data source bean, get connection on ds. This probably means the procedure SQL, would run in another transaction, so before you make the calls, you should make sure that the caller has committed the corresponding create schema statement. Hope this helps, Dag import java.sql.*; public class Foo { static public void main(String[] args) throws SQLException, ClassNotFoundException { Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); Connection conn = DriverManager.getConnection("jdbc:derby:wombat;create=true"); Statement stm = conn.createStatement(); /* SQL */ stm.executeUpdate("create procedure app.createTempSchema (in schemaName char(254))" + " language java parameter style java modifies sql data external name " + "'Foo.createTempSchema'"); stm.executeUpdate("create schema vanguardtemp"); stm.executeUpdate("set schema=vanguardtemp"); stm.executeUpdate("create table DBK2CPP (i int, j int)"); stm.executeUpdate("create table D2GWREP (i int, j int)"); stm.executeUpdate("create schema temp00"); stm.executeUpdate("call app.createTempSchema('temp00')"); stm.executeUpdate("create schema temp01");; stm.executeUpdate("call app.createTempSchema('temp01')"); conn.close(); } public static void createTempSchema (String schemaName) throws SQLException { /* get data source bean, get connection on ds */ // Using nested connection here (same trans as caller) Connection conn = DriverManager.getConnection("jdbc:default:connection"); PreparedStatement ps = conn.prepareStatement( "set schema = " + schemaName ); ps.execute(); ps = conn.prepareStatement( "create table DBK2CPP as select * from vanguardtemp.DBK2CPP with no data"); ps.execute(); ps = conn.prepareStatement( "create table D2GWREP as select * from vanguardtemp.D2GWREP with no data"); ps.execute(); } } $ rm -rf wombat; java -cp .:$CLASSPATH Foo $ java org.apache.derby.tools.ij ij version 10.7 ij> connect 'jdbc:derby:wombat'; ij> show tables; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ SYS |SYSALIASES | : : TEMP00 |D2GWREP | TEMP00 |DBK2CPP | TEMP01 |D2GWREP | TEMP01 |DBK2CPP | VANGUARDTEMP |D2GWREP | VANGUARDTEMP |DBK2CPP | 28 rows selected
<<winmail.dat>>
