Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Use of params in Script ... |
Tue, Aug 20 2013 5:51 AM | Permanent Link |
Adam Brett Orixa Systems | In my innocence, I would expect the following to work:
SCRIPT BEGIN DECLARE Crsr Cursor FOR Stmt; DECLARE ExecCrsr Cursor FOR ExecStmt; DECLARE TableName VARCHAR(120); PREPARE Stmt FROM ' SELECT Name FROM Information.Tables '; OPEN Crsr; FETCH FIRST FROM Crsr('Name') INTO TableName; PREPARE ExecStmt FROM ' REPAIR TABLE ? STATISTICS '; WHILE NOT EOF(Crsr) DO OPEN ExecCrsr USING (TableName); CLOSE ExecCrsr; FETCH NEXT FROM Crsr('Name') INTO TableName; END WHILE; CLOSE Crsr; -- But it doesn't. I can't prepare the statement with a param "?" in place of the TableName, EDB tells me it expects a tablename not a param. An alternative syntax: WHILE NOT EOF(Crsr) DO EXECUTE IMMEDIATE ' REPAIR TABLE ' + TableName + ' STATISTICS ' ; FETCH NEXT FROM Crsr('Name') INTO TableName; Works fine, but I understand that statements are quicker if they are prepared & there are other instances where it is useful to parameterize the tablename ... so I wonder whether it is possible somehow? |
Tue, Aug 20 2013 5:54 AM | Permanent Link |
Adam Brett Orixa Systems | As an aside, it would also be useful to have SQL:
REPAIR DATABASE [Tables] which repaired all the tables in a database. |
Tue, Aug 20 2013 6:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I'm pretty sure you can't use parameters to substitute for any of the system objects >Works fine, but I understand that statements are quicker if they are prepared & there are other instances where it is useful to parameterize the tablename ... so I wonder whether it is possible somehow? Well yes but in this case I doubt you'll see any difference. Doing a lot of activity like updating a table or lots of selects one after another you can save a lot of time, but those activities are short themselves. REPAIR takes a bit of time. Preparing a query basically acquires the table handle and checks that column names are valid etc and that can take a significant percentage of the time taken to run a query like "SELECT * FROM Companies WHERE _ID = :companyID' Roy Lambert [Team Elevate] |
Mon, Aug 26 2013 10:59 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< Works fine, but I understand that statements are quicker if they are prepared & there are other instances where it is useful to parameterize the tablename >> This is only the case when you're executing thousands of DML statements or more, ala INSERT. DDL statements have to open tables in a special manner and do special processing anyways, and would not be able to benefit from preparation at all. Preparation isn't really about saving time from parsing/compilation of the statements (although there are a small amount of such savings), rather it's about saving the time it takes to open/close the tables involved in the statement. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |