I am pretty sure this would have worked around version 1.05 but, on revisiting my app with 2.02 build 14, it seems to have stopped:

CREATE PROCEDURE "CreateExportTables" (mid INTEGER)
BEGIN                                              
 DECLARE Query STATEMENT;   
 PREPARE Query FROM 'CREATE TABLE ieModels AS (SELECT * FROM Models WHERE Models.mid_local = ?)';
 EXECUTE Query USING mid;
END

How can I acheive the desired effect?

Thanks

Peter

Peter
> I am pretty sure this would have worked around version 1.05 but, on
revisiting my app with 2.02 build 14, it seems to have stopped:
>
> CREATE PROCEDURE "CreateExportTables" (mid INTEGER)
> BEGIN
>   DECLARE Query STATEMENT;
>   PREPARE Query FROM 'CREATE TABLE ieModels AS (SELECT * FROM Models WHERE
Models.mid_local = ?)';
>   EXECUTE Query USING mid;
> END
>
> How can I acheive the desired effect?

You need to add WITH DATA at the end of the statement.

Cheers

John

"John Hay" wrote:

> You need to add WITH DATA at the end of the statement.

John,

That was quick! Unfortunately, the problem is with the parameter throwing the error "dynamic parameter not allowed" when I execute the
procedure. This pops up even when "WITH DATA" is included.

Thanks

Peter

Peter

> That was quick! Unfortunately, the problem is with the parameter throwing
the error "dynamic parameter not allowed" when I execute the
> procedure. This pops up even when "WITH DATA" is included.

Sorry - didn't read the question properly.

Dynamic parameters are not allowed in sub-queries/derived tables currently.

You could chage the procedure to

CREATE PROCEDURE "CreateExportTables" (mid INTEGER)
BEGIN
 DECLARE Query STATEMENT;
 PREPARE Query FROM 'CREATE TABLE ieModels AS (SELECT * FROM Models WHERE
Models.mid_local ='+CAST(mid AS VARCHAR)'+') WITH DATA';
 EXECUTE Query;
END


John

Peter


You just can't use them in derived tables. Do a search of this newsgroup for 'dynamic' and you'll find a post about it. Tim said <<No, you didn't do anything wrong.  Currently you cannot use parameters with derived tables.  It's something that will be addressed after 2.03 is released.>>

What you need to do is build the statement as a VARCHAR using your input parameter eg

CREATE PROCEDURE "CreateExportTables" (mid INTEGER)
BEGIN                                             
 DECLARE Query STATEMENT;  
 PREPARE Query FROM 'CREATE TABLE ieModels AS (SELECT * FROM Models WHERE Models.mid_local = ' + CAST(mid AS VARCHAR) + ')';
 EXECUTE Query USING mid;
END


Roy Lambert

OK. I think I must have split it into two procs, one to create the table without data and then one (parameterised) to populate it with the subset of
records. Thanks anyway.

Peter