Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Enhancement Requests and Suggestions » View Thread |
Messages 1 to 8 of 8 total |
TEDBQuery & TEDBScript Substitutions |
Thu, Jan 17 2008 10:32 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | NOTE: I am not talking about parameters here. They are an entirely separate issue.
It's becoming rapidly apparent to me that in a number of cases I will have to store the sql string in a container (I use a TglStringContainer) with suitable placemarkers which I pass to the SQL.Text property of a TEDBQuery or TEDBScript and then use StringReplace to alter before passing the placemarkers before running the script. Example In mruSQL.Items I have SELECT _fkProjects, _LastAccess, (IF(_LastAccess IS NOT NULL THEN CAST((CURRENT_TIMESTAMP - _LastAccess)DAY AS INTEGER) ELSE 99999)) AS _LongAgo, (SELECT _Name FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _Name, (SELECT _Type FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _Type, (SELECT _fkOrderBook FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _fkOrderBook, (SELECT IF(_Status = 'L' THEN 'Live' ELSE IF(_Status = 'D' THEN 'Dead' ELSE IF(_Status = 'H' THEN 'Hold' ELSE '')))FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _Status, (SELECT _fkUsers FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _fkUsers FROM mruProjects WHERE _fkProjects <> :Schedule ORDER BY _LongAgo and I do this to the TEDBQuery RecentlyOpened RecentlyOpened.SQL.Text := StringReplace(mruSQL.Items.Text, '$Disk', dm.DB.Database, [rfReplaceAll]); RecentlyOpened.ParamByName('Schedule').AsInteger := HHCommons.Schedule; RecentlyOpened.ExecSQL; ie I'm using both substitutions and paremeters. My suggestion (at long last) is that the TEDBQuery and TEDBScript have a Substitutions property, and a MakeSubstitutions method. The method can only be called when the query or script is closed and its the user's responsibility to make sure the string (or strings) being substituted are unique. The sql property would be the one edited, but it would be the subsituted sql that is executed. I know I can do all this myself but it would be a lot neater in the core components, and (big guess here) be useful to a lot of us. Roy Lambert |
Thu, Jan 17 2008 11:27 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< It's becoming rapidly apparent to me that in a number of cases I will have to store the sql string in a container (I use a TglStringContainer) with suitable placemarkers which I pass to the SQL.Text property of a TEDBQuery or TEDBScript and then use StringReplace to alter before passing the placemarkers before running the script. >> Noted. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jan 17 2008 11:28 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
BTW, you can do this with a single script very easily. Do you want the code ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jan 17 2008 1:25 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
You do ask some daft questions - yes please. Roy Lambert |
Thu, Jan 17 2008 1:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< You do ask some daft questions - yes please. >> Well, you certainly could have answered "not interested", and that would have saved me working up a specific example just for you. Here's the code for SELECT statements that you want to return a result set from: SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR) BEGIN DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR Stmt; PREPARE Stmt FROM REPLACE('$TableName' WITH TableName IN SQLStatement); OPEN ResultCursor; END As you can see, $TableName is the place marker. Just pass in any SQL statement and the tablename that you want to use, and it will return the result set. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jan 18 2008 6:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Well, you certainly could have answered "not interested", and that would >have saved me working up a specific example just for you. I am still learning by tripping over my own feet so any examples and any aids to learning are going to be of interest. I have to alter it a bit since you'd missed out the parameter I was supplying to the TEDBQuery. I don't know if this is the best way or not SCRIPT (IN SQLCode VARCHAR, IN DBPath VARCHAR, IN Schedule INTEGER) BEGIN DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR Stmt; PREPARE Stmt FROM REPLACE(':Schedule' WITH CAST(Schedule AS VARCHAR) IN REPLACE('$Disk' WITH DBPath IN SQLCode)); OPEN ResultCursor; END Now the real question is: Is this any better than my altering the sql text and slinging the result at a query as I was doing? Any thoughts? Roy Lambert |
Fri, Jan 18 2008 11:10 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I am still learning by tripping over my own feet so any examples and any aids to learning are going to be of interest. >> Noted. << I have to alter it a bit since you'd missed out the parameter I was supplying to the TEDBQuery. I don't know if this is the best way or not >> Well, there's two ways to handle the Schedule parameter - one is with subsitution and the other is with a true parameter: SCRIPT (IN SQLCode VARCHAR, IN DBPath VARCHAR, IN Schedule INTEGER) BEGIN DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR Stmt; PREPARE Stmt FROM REPLACE(':Schedule' WITH '?' IN REPLACE('$Disk' WITH DBPath IN SQLCode)); OPEN ResultCursor USING Schedule; END However, with that approach you need to know how many times the parameter is used in order to execute the OPEN..USING properly. << Now the real question is: Is this any better than my altering the sql text and slinging the result at a query as I was doing? Any thoughts? >> The two are basically the same. My point was more to show you that the dynamic programming capabilities of the scripts and procedures can be very useful for extending EDB when it is missing something that you want and you don't wish to code it into the application. However, the script method becomes more valuable as a stored procedure, and even more valuable as multiple stored procedures where the SQL statements are hard-coded, and you only need to pass in the parameters to be replaced. Of course, this is only an option when you have a semi-limited number of SQL statements that will need this type of substitution. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jan 18 2008 11:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
And even better which is almost what I wanted from this suggestion is SCRIPT (IN DBPath VARCHAR, IN Schedule INTEGER) BEGIN DECLARE SQLCode VARCHAR; DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR Stmt; SET SQLCode = 'SELECT _fkProjects, _LastAccess, (IF(_LastAccess IS NOT NULL THEN CAST((CURRENT_TIMESTAMP - _LastAccess)DAY AS INTEGER) ELSE 99999)) AS _LongAgo, (SELECT _Name FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _Name, (SELECT _Type FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _Type, (SELECT _fkOrderBook FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _fkOrderBook, (SELECT IF(_Status = ''L'' THEN ''Live'' ELSE IF(_Status = ''D'' THEN ''Dead'' ELSE IF(_Status = ''H'' THEN ''Hold'' ELSE '''')))FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _Status, (SELECT _fkUsers FROM "$Disk".Projects P WHERE P._ID = _fkProjects) AS _fkUsers FROM mruProjects WHERE _fkProjects <> :Schedule ORDER BY _LongAgo'; PREPARE Stmt FROM REPLACE(':Schedule' WITH CAST(Schedule AS VARCHAR) IN REPLACE('$Disk' WITH DBPath IN SQLCode)); OPEN ResultCursor; END Double quoting's a bit of a pain but you can't have everything. Roy Lambert |
This web page was last updated on Friday, April 19, 2024 at 07:09 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |