I can't get this stored proc to work properly. It creates the table correctly but won't populate with the data for the model that is identified by the model_id parameter. Is there a problem using WITH DATA in conjunction with a parameterised
select statement? I guess I can create and populate separately but that would involve more typing!

CREATE PROCEDURE "CreateExportTables" (IN model_id integer)
BEGIN
DECLARE DropImExCursor CURSOR WITH RETURN FOR Stmnt;
PREPARE stmnt FROM 'CREATE TABLE ieModel AS SELECT * FROM Models WHERE mid_local = ? WITH DATA';
EXECUTE stmnt USING model_id;
END
DESCRIPTION 'Copies the details of the current model to the temporary tables for export'

Sorry posted the wrong statement, should read ...

CREATE PROCEDURE "CreateExportTables" (IN model_id integer)
BEGIN
DECLARE Stmnt STATEMENT;
PREPARE stmnt FROM 'CREATE TABLE ieModel AS SELECT * FROM Models WHERE mid_local = ? WITH DATA';
EXECUTE stmnt USING model_id;
END
DESCRIPTION 'Copies the details of the current model to the temporary tables for export'

Peter,

<< I can't get this stored proc to work properly. It creates the table
correctly but won't populate with the data for the model that is identified
by the model_id parameter. Is there a problem using WITH DATA in conjunction
with a parameterised select statement? >>

Yes, currently you cannot use parameters with any CREATE TABLE statement,
even with the AS clause.  I will see what I can do about improving this in
the next build or release.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

<< Yes, currently you cannot use parameters with any CREATE TABLE statement,
even with the AS clause.  I will see what I can do about improving this in
the next build or release. >>

Thanks for the quick response as usual ... Unfortunately I can't get plan B to work either. Create the table separately and then use:

CREATE PROCEDURE "PopulateExportTables" (IN mid integer)
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO ieModel SELECT * FROM Models WHERE mid_local = ?' USING mid;
END
DESCRIPTION 'Copies the details of the current model to the temporary tables for export'

This throws:

ElevateDB Error #700 An error was found in the statement at line 3 and column 84
(Expected ; but instead found USING)

Plan C creates the procedure OK:

CREATE PROCEDURE "PopulateExportTables" (IN mid integer)
BEGIN
 DECLARE PopulateCursor CURSOR FOR Stmnt;
 PREPARE Stmnt FROM 'INSERT INTO ieModel SELECT * FROM Models WHERE Models.mid_local = ?';
 OPEN PopulateCursor;
 EXECUTE Stmnt USING mid;
END
DESCRIPTION 'Copies the details of the current model to the temporary tables for export'

but this won't insert any records into ieModel when executed. If I replace the dynamic part with a static mid_local then there is no problem but this doesn't suit my puproses. Have I missed an obvous Plan D? Version is 1.04 build 5

Peter

--
Tim Young
Elevate Software
www.elevatesoft.com

Peter,

<< CREATE PROCEDURE "PopulateExportTables" (IN mid integer)
BEGIN
 EXECUTE IMMEDIATE 'INSERT INTO ieModel SELECT * FROM Models WHERE
mid_local = ?' USING mid;
END
DESCRIPTION 'Copies the details of the current model to the temporary
tables for export'

This throws:

ElevateDB Error #700 An error was found in the statement at line 3 and
column 84
(Expected ; but instead found USING) >>

This needs to be corrected, but for now just use a STATEMENT declaration
like this:

CREATE PROCEDURE "PopulateExportTables" (IN mid integer)
BEGIN
 DECLARE Query STATEMENT;
 PREPARE Query FROM 'INSERT INTO customer SELECT * FROM Customers WHERE
custNo = ?';
 EXECUTE Query USING mid;
END
DESCRIPTION 'Copies the details of the current model to the temporary
tables for export'

<< but this won't insert any records into ieModel when executed. >>

You're using the wrong type of statement - you're using one for a cursor
when what you want is a plain STATEMENT declaration (see above).

--
Tim Young
Elevate Software
www.elevatesoft.com

<< This needs to be corrected, but for now just use a STATEMENT declaration
like this:

CREATE PROCEDURE "PopulateExportTables" (IN mid integer)
BEGIN
 DECLARE Query STATEMENT;
 PREPARE Query FROM 'INSERT INTO customer SELECT * FROM Customers WHERE
custNo = ?';
 EXECUTE Query USING mid;
END
DESCRIPTION 'Copies the details of the current model to the temporary
tables for export' >>


Tim,

This seems to have stopped working in 1.06. The following no longer inserts the record when the parameter is used

CREATE PROCEDURE "PopulateExportTables" (IN mid integer)
BEGIN
DECLARE Query STATEMENT;
PREPARE Query FROM 'INSERT INTO ImpExModel SELECT * FROM Models WHERE mid_local = ?';
EXECUTE Query USING mid;
END
DESCRIPTION 'Copies the details of the current model to the temporary tables for export'

It works OK in static form with mid_local set in the SQL statement

Peter

Peter,

<< This seems to have stopped working in 1.06. The following no longer
inserts the record when the parameter is used >>

Yep, you're right.  This will be fixed in the 1.06 B2 release, which should
be any day now (it's waiting for the .NET support being completed).

--
Tim Young
Elevate Software
www.elevatesoft.com

<< Yep, you're right.  This will be fixed in the 1.06 B2 release, which should
be any day now (it's waiting for the .NET support being completed). >>

That's a relief. I though I was being an idiot!

Hi Tim,

To get round this for now I went back to 1.05 but I am getting an error #100 when I try to open a database (built with 1.06) in the manager (error in catalog metadata). Databases built originally in 1.05 open OK. Is
it possible to fix the catalog file? I am attaching it just in case. Any news on progress with 1.06 build 2?

Peter

Peter,

<< To get round this for now I went back to 1.05 but I am getting an error
#100 when I try to open a database (built with 1.06) in the manager (error
in catalog metadata). Databases built originally in 1.05 open OK. Is it
possible to fix the catalog file? I am attaching it just in case. >>

You can't mix versions accessing the same database catalog.  More
specifically, if the newer version updates the catalog and then the older
version tries to read the catalog, the older version will fail with the
error that you saw.   This is how we can slipstream in changes to the
catalog metadata without requiring anything being done manually.

IOW, I can't really fix the catalog once it has been updated since the
format has changed in several areas.

<< Any news on progress with 1.06 build 2? >>

What are you specifically waiting for ?

--
Tim Young
Elevate Software
www.elevatesoft.com