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 Procedure issue
Date Mon, 21 Jun 2010 17:27:12 GMT
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');





Mime
View raw message