Another detail on this issue. I generated the redundant SQL I was trying to avoid for 100 copies of my temp schema, 124,000 tables total. It took a while but completed. My test code then selected one of the temp schemas on the temp data source and inserted a record, successfully. I shut down the app and used ij to confirm that the test table in the selected temp schema had the record I'd inserted.
When I have time I will see if I can reproduce the procedure failure outside
my Spring/Atomikos/Hibernate setup with just the raw tables.
-----Original Message-----
From: Clark, Harry
Sent: Wed 6/23/2010 10:46 AM
To: Derby Discussion
Cc:
Subject: RE: Procedure issue
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:[email protected]]
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:
<<winmail.dat>>
