Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 20 of 20 total |
Append data to a query |
Sat, Sep 7 2013 3:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
> WITH DataModule.qryBrowseMem DO BEGIN > Close; > SQL.Clear; > SQL.Add('CREATE TABLE "ReposTemp" AS' + > ' SELECT * FROM "' + DatabaseStrRepos + '"."REPOS"' + > ' WHERE Standard = True' + > ' WITH DATA'); > Open; > END; You do know that using "with" is selling your soul to Satan >I get the error message :- > > 401 The table or view ReposTemp does not exist in the schema Default. > >On the data module I have the component TEDBQuery (Name = qryBrowseMem) >which points to the >data source TDataSource which in turn points to the data set >TEDBTable which is ReposTemp. I think this is where your problem is. You're trying to create a table but the query you're using to do it is trying to point to the table you're creating which doesn't exist yet. >To my mind, yes, the table ReposTemp does not exist because the SQL is >trying to create it. To me the error message is meaningless. Its quite correct because you've told the query to use a datasource >I know the error message is telling me something, but at this stage I >just don't see it. See above >I have tried running similar CREATE TABLE AS in the ElevateDB Manager >Unicode and that code can create a table on disc. But in EDBManager you can't tell the query to use a datasource. >Does the CREATE TABLE AS only work to create a disc table and not an >in-memory table? Nope. I do both. >I think I do need to look at the Script you offered as it might be what >I need. All you need to do is remove the datasource reference from qryBrowseMem, make sure the database qryBrowseMem is pointing at is the in-memory one and you're in business. You'll need to hook up TEDBTable to ReposTemp afterwards The script is below my sig Roy Lambert [Team Elevate] SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR) BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"'; END IF; CLOSE InfoCursor; PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA'; EXECUTE ResultStmt; IF IdxSet IS NOT NULL THEN PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')'; EXECUTE ResultStmt; END IF; END |
Sat, Sep 7 2013 4:24 AM | Permanent Link |
Peter Evans | On 7/09/2013 5:57 PM, Roy Lambert wrote:
> > You do know that using "with" is selling your soul to Satan Possibly, must be a lost cause. > > Its quite correct because you've told the query to use a datasource Yes, this lead looks promising. > > But in EDBManager you can't tell the query to use a datasource. Yep, but at least I could one CREATE TABLE AS to work! > > SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR) Thanks for script. Will try your suggestions and reply. Regards, Peter Evans |
Sun, Sep 8 2013 9:29 PM | Permanent Link |
Peter Evans | On 7/09/2013 6:24 PM, Peter Evans wrote:
> > Will try your suggestions and reply. Success at last. What I did was to place a TEDBScript component on my data module. I call it edbscriptCreateTableAs. I also set the database name. Then I wrote a procedure which has the following in it :- DataModule.edbscriptCreateTableAs.SQL.Clear; DataModule.edbscriptCreateTableAs.SQL.Add( 'SCRIPT (IN SQLStatement VARCHAR,' + ' IN TableName VARCHAR,' + ' IN IdxSet VARCHAR)' + ' BEGIN' + ' DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;' + ' DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;' + ' PREPARE InfoStmt FROM ''SELECT * FROM Information.Tables WHERE Name=?'';' + ' OPEN InfoCursor USING TableName;' + ' IF (ROWCOUNT(InfoCursor) > 0) THEN' + ' EXECUTE IMMEDIATE ''DROP TABLE "'' + TableName + ''"'';' + ' END IF;' + ' CLOSE InfoCursor;' + ' PREPARE ResultStmt FROM ''CREATE TABLE "'' + TableName + ''" AS '' + SQLStatement + '' WITH DATA'';' + ' EXECUTE ResultStmt;' + ' IF IdxSet IS NOT NULL THEN' + ' PREPARE ResultStmt FROM ''CREATE INDEX Idx ON "'' + TableName + ''"('' + IdxSet + '')'';' + ' EXECUTE ResultStmt;' + ' END IF;' + ' END' ); DataModule.edbscriptCreateTableAs.Prepare; DataModule.edbscriptCreateTableAs.ParamByName('SQLStatement').AsString := 'SELECT * FROM "' + DatabaseStr + '"."REPOS"' + ' WHERE Standard = True'; DataModule.edbscriptCreateTableAs.ParamByName('TableName').AsString := 'ReposTemp'; DataModule.edbscriptCreateTableAs.ExecScript; So thanks again Roy for the script. I have included the script to help others wanting to know how to set parameters and call a script. There is one fly in the ointment though. I will get to that in a moment. After calling the procedure, and then appending the data I want to append to table ReposTemp, and then running a query to get the data from ReposTemp to the query, I want to drop the table. Then I display stuff on the screen. What appears on the screen is correct. So what is the fly in the ointment? It is dropping the table ReposTemp. I get the error 'ElevateDB Error #300 Cannot lock the table ReposTemp in the schema Default for exclusive access'. I even put in two lines to and make sure the table was closed or whatever. TargetDataMod.edbscriptCreateTableAs.UnPrepare; {free resources} TargetDataMod.ReposTemp.Close; if TableLowUtil.TableExists(TargetDataMod.DBTempMem, 'ReposTemp') then TableLowUtil.TableDrop(TargetDataMod.DBTempMem, 'ReposTemp'); But even putting in the two line to UnPrepare and Close did not help. When the TableDrop routine is called it gets the error. What do I have to do to the table so it can be Dropped? Regards, Peter Evans |
Mon, Sep 9 2013 3:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
>After calling the procedure, and then appending the data I want to >append to table ReposTemp, and then running a query to get the data from >ReposTemp to the query, I want to drop the table. Then I display stuff >on the screen. What appears on the screen is correct. This could be your problem. You're using ReposTemp in whatever this query is called and until that query is closed (and probably unprepared) you can't drop the table. Roy Lambert [Team Elevate] |
Mon, Sep 9 2013 8:17 AM | Permanent Link |
Peter Evans | On 9/09/2013 5:16 PM, Roy Lambert wrote:
> > This could be your problem. You're using ReposTemp in whatever this query is called and until that query is closed I have now coded :- TargetDataMod.qryBrowse.UnPrepare; TargetDataMod.ReposTemp.Close; On my limited tests these lines enable the dropping of the table without an exception. Can I dare to hope these routines are now working? Thanks Roy for your assistance. Regards, Peter Evans |
Mon, Sep 9 2013 10:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
>Can I dare to hope these routines are now working? No! What you can hope is that the Great Electron will deign to listen to your supplications and grant that your code will work THIS TIME. The Great Electron may decide at any time for ITs own inscrutable reasons to prevent your code from working at all, or to insert any bug IT so chooses to do. Well that's what seems to happen to me anyway. Roy |
Mon, Sep 9 2013 5:47 PM | Permanent Link |
Michael Riley ZilchWorks | Roy Lambert wrote:
> What you can hope is that the Great Electron will deign to listen to > your supplications and grant that your code will work THIS TIME. +1 "The Great Electron" -- Michael Riley GySgt USMC (Ret) www.zilchworks.com |
Mon, Sep 9 2013 10:17 PM | Permanent Link |
Peter Evans | On 10/09/2013 12:10 AM, Roy Lambert wrote:
> > What you can hope is that the Great Electron will deign to listen to your supplications Oh, you gave me a fright as I first read what you wrote at "What you can hope is that the Great Election will deign to listen to your supplications". You see that on the weekend we had a Great Election here... Regards, Peter Evans |
Tue, Sep 10 2013 3:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Peter
My sympathies. I'm still waiting for an election where an option on the ballot paper is "take them out, stand against a wall and shoot them" I know there are some good MPs (or whatever the Oz term is for them) but I'd be willing to risk the collateral damage to get rid of the "normal" ones. Roy Lambert |
Wed, Sep 11 2013 1:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Peter,
<< Well, I was following a suggestion that Roy made... >> First mistake....just kidding, Roy. Sorry I got to this thread late, but Roy handled things just fine, as always. Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |