Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Dynamic parameter references |
Wed, Jun 17 2009 8:35 AM | Permanent Link |
Peter Thorne | 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 |
Wed, Jun 17 2009 8:43 AM | Permanent Link |
"John Hay" | 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 |
Wed, Jun 17 2009 8:50 AM | Permanent Link |
Peter Thorne | "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 |
Wed, Jun 17 2009 9:10 AM | Permanent Link |
"John Hay" | 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 |
Wed, Jun 17 2009 9:14 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Jun 17 2009 9:24 AM | Permanent Link |
Peter Thorne | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |