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 15:46:52 GMT
Tnanks Dag. I am now calling the procedure, but it is not executing properly. I can execute
exactly the same code successfully in SQL. Code follows this explanation.

The app uses Derby as an embedded database, as a temp database for the app. In starting the
app, a base temp schema containing the tables is created in Derby. Multiple empty schemas
are then created. The tables from the base are then created in each schema, using "create
table ... as". The idea is each user will get a copy of the temp schema when a session begins.
We need as many copies of the schema as we expect simultaneous users. 

Creating 10 temp schemas and tables works in SQL, but not via the procedure, which creates
the tables for a schema. The proc creates all the schemas, but creates tables only in 2 schemas.
Here is the abstracted SQL/Java for the proc. Abstracted SQL (which works) follows. The Derby
log is not helpful. 

Before I prepare a stand-alone test (apart from Spring, Atomikos etc) and submit a bug if
I reproduce it, is there anything obvious I'm missing? There are 1240 tables in each temp
schema; but 10*1240 should not be an issue (or 100*!240 for that matter). Thanks

SQL/Java, for procedure

/* SQL */
create procedure createTempSchema (in schemaName char(254))
  language java parameter style java  modifies sql data external name
  'com.kve.vanguard.model.orm.dao.TempSchemaGenerator.createTempSchema';

create schema vanguardtemp;
set schema=vanguardtemp;
create table DBK2CPP ...
create table D2GWREP ...
...
create schema temp00;
call app.createTempSchema('temp00');
...
create schema temp00;
call app.createTempSchema('temp09');

/* Java */
public static void createTempSchema(String schemaName) {
  /* get data source bean, get connection on ds */
  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();
  ...
}
========
SQL 

/*
  temp ORM schema written June 23, 2010 9:55:09 AM EDT
*/
create schema vanguardtemp;
set schema = vanguardtemp;
create table DBK2CPP (
 ...
);
create table D2GWREP (
 ...
);
...
create schema temp00;
set schema=temp00;
create table DBK2CPP as select * from vanguardtemp.DBK2CPP with no data;
create table D2GWREP as select * from vanguardtemp.D2GWREP with no data;
...
create schema temp01;
set schema=temp01;
create table DBK2CPP as select * from vanguardtemp.DBK2CPP with no data;
create table D2GWREP as select * from vanguardtemp.D2GWREP with no data;
...
create schema temp09;
...






-----Original Message-----
From:	Dag H. Wanvik [mailto:Dag.Wanvik@Sun.COM]
Sent:	Mon 6/21/2010 5:18 PM
To:	Derby Discussion
Cc:	
Subject:	Re: Procedure issue

Hi,

Did you look in derby.log for any error messages relating to the
calls?

Some suggestions:




Mime
View raw message