Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread TEDBQuery & TEDBScript Substitutions
Thu, Jan 17 2008 10:32 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


You do ask some daft questions - yes please.

Roy Lambert
Thu, Jan 17 2008 1:56 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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
Image