Procedure issue

["Clark, Harry" <Ha...@knovalent.com>]

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');

Re: Procedure issue

["Dag H. Wanvik" <Da...@Sun.COM>]

"Clark, Harry" <Ha...@knovalent.com> writes:

> 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';
:
> call app.createTempSchema('temp00');
> call app.createTempSchema('temp01');

Hi,

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

Some suggestions:

I see you created the procedure without explicit schema, but call it
with explicit schema 'app'. Does it make a difference to use an
explicit app.createTempSchema when you create it? (should not be
necessary if app is the default schema already)

Have you verified that the classpath contains TempSchemaGenerator?

Dag