I can't get a procedure to execute. It's in the database, and the code is in
the classpath. But it doesn't get called. I'm probably overlooking something
simple. I'm using Spring/Hibernate/Atomikos in a Java console app; Derby is an
embedded database used in file mode as a temp database for the app. I want to
create a single base temp schema with the tables. Then I want to create
multiple, empty, temp schemas. Then I call the procedure, createTempSchema,
passing a schema name as argument. The proc then creates the tables in the temp
schema using "as select * from ... with no data". There are 1200 tables in the
base temp schema, and as many temp schemas as we decide we need for
simultaneous connections. For now I'm testing with a single table and 2 temp
schemas, as well as the base temp schema.
The following SQL is successfully executed by Spring as part of creating the
embedded database. The method for the procedure implements the Spring interface
ApplicationContextAware so I can get the data source bean from Spring and get a
connection on it. The setter for the AppCtx is duly called by Spring. The
"createTempSchema" method underlying the procedure is not called. I assume I
can issue create table stmts in a procedure.
Anyway here is the SQL. The database is populated as I expect, with
vanguardtemp and temp00 and 01, and vgtemp has the table. Thanks for any advice.
/*
create temp ORM schemas
*/
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 DBAPREP (
APAZCD char(5) primary key,
...
);
create schema temp00;
create schema temp01;
call app.createTempSchema('temp00');
call app.createTempSchema('temp01');
