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
