db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clark, Harry" <Harry.Cl...@knovalent.com>
Subject RE: Procedure issue
Date Wed, 23 Jun 2010 21:43:38 GMT
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:Dag.Wanvik@Sun.COM]
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




Mime
View raw message