Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Flummoxed by this script |
Thu, Dec 5 2013 10:38 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Hi
I have written a script that neither my program nor EDB Manager like. I wrote it initially the old DBISAM3 way with a CREATE TABLE and a CREATE INDEX separated with ";" and used ConvertSQL to make a script. I did a showmessage(scrCreateTable.SQL.Text); to capture the script with Ctrl+C and pasted it into EDB Manager. On Executing, I get the message:- --------------------------- Error --------------------------- There are no statements to execute. --------------------------- OK --------------------------- I must have done something silly, but can't spot it - sometimes being a sole developer sucks! I've tried it with only the CREATE TABLE and only the CREATE INDEX, same result. The script is below. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz SCRIPT () BEGIN EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "TEMP00001099" ( "TenantCode" VARCHAR(4), "SortKey" VARCHAR(20), "Disburse" BOOLEAN DEFAULT False, "AmountHeld" DECIMAL(19,2), "LastReceiptDate" DATE, "AmountOutstanding" DECIMAL(19,2), "AddressTo" VARCHAR(20), "PropertyName" VARCHAR(60), "VacateDate" DATE, CONSTRAINT "TenantCode_PrimaryKey" PRIMARY KEY ("TenantCode") )'; EXECUTE IMMEDIATE 'CREATE INDEX "AlphaIndex" ON "TEMP00001099" (SortKey, TenantCode COLLATE UNI_CI)'; END |
Thu, Dec 5 2013 11:36 PM | Permanent Link |
Barry | Jeff,
I copied the script to EDBMgr 2.13 B2 and pasted it into a new script window and it executed fine. (I assume you selected New > Dropdown Arrow > Script to open the script window. I also assume you have selected a database in the tree before you ran the script.). The TEMP00001099 table was created along with the index. The status bar shows "The script was executed successfully in 0.047 seconds". To see temporary tables you need to execute: select * from information.temporarytables; because temporary tables don't show up in the EDBMgr tree. I did notice you had "SCRIPT ()" and I don't know why you have the "()". I just use "SCRIPT". I tried it with "SCRIPT ()" and it still worked. Barry |
Sun, Dec 8 2013 3:05 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | <Barry> wrote in message
news:9ECE7ACB-8B81-4473-A6B5-8671F57D0264@news.elevatesoft.com... > (I assume you selected New > Dropdown Arrow > Script to open the script > window. I also assume you have selected a database in the tree before you > ran the script.). > Yes, that is what I did - at least that is what I thought that I had done. I pasted the script created for me using the ConvertSQL. > The TEMP00001099 table was created along with the index. The status bar > shows "The script was executed successfully in 0.047 seconds". That is where I got the error message about no statements to execute. I can understand that I have done something wrong but can't see the relevance of the error message. > > To see temporary tables you need to execute: > select * from information.temporarytables; > > because temporary tables don't show up in the EDBMgr tree. > Aha! didn't know that, so I may have been confused at some stage - I might have successfully created the table and not known it. I was using a Memory database so there was no folder to look into to see if the tables had been created. No sure how you know the index is there if it isn't in the dtabase tree. Similarly how would I find the fields created? > I did notice you had "SCRIPT ()" and I don't know why you have the "()". Just because that's how the ConvertSQL made it with the "()". However, after resting my brain for the weekend, I have been through it all again and all works as expected. I think that a 3 day week with one work day and two day weekend would work for me! Thanks Barry Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Sun, Dec 8 2013 5:49 PM | Permanent Link |
Barry | Jeff,
>> because temporary tables don't show up in the EDBMgr tree.<< >Aha! didn't know that, so I may have been confused at some stage - I might have successfully created the table and not known it. I was using a Memory database so there was no folder to look into to see if the tables had been created. No sure how you know the index is there if it isn't in the dtabase tree. Similarly how would I find the fields created?< Unfortunately there isn't an Information.TemporaryTableColumns so you have to write a Delphi rtn to execute "Select * from temptable range 0 to 0' and then use FieldDefs to get access to the columns returned. Also if you want to know if a table has an index on col1,col2,col3, just do a 'Select * from temptable order by col1,col2,col3' and if it creates a Sensitive query you know it has an index on those columns (assuming the role you are using doesn't have the table unupdateable). >However, after resting my brain for the weekend, I have been through it all again and all works as expected. I think that a 3 day week with one work day and two day weekend would work for me!< Are you thinking of running for Congress? Barry |
Mon, Dec 9 2013 6:11 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Barry
> > Are you thinking of running for Congress? > We don't have a Congress, but I get the idea -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |