Good Morning Tim,

A while back, I believe I saw a post saying that EDB currently does not
support multiple queries within one SQL. (ie, seperating queries with a
semicolon).

I was wondering if you were planning on implementing this support at some
stage in EDB and if so had any indication on when. (ie, in a minor upgrade
at somestage, or whether it would be the next major ver, etc, etc...)

Cheers

Adam

Adam


Yes he is - I think the timescale is August-ish

Roy Lambert

Adam,

<< I was wondering if you were planning on implementing this support at some
stage in EDB and if so had any indication on when. (ie, in a minor upgrade
at somestage, or whether it would be the next major ver, etc, etc...) >>

1.05 will have support for scripts, although they will be a little more
involved than just a semi-colon delimited list of statements like with
DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Roy and Tim,

Thanks for the replies...

> 1.05 will have support for scripts, although they will be a little more
> involved than just a semi-colon delimited list of statements like with
> DBISAM.

My main concern is in porting a DBISam 4 application to EDB. At the moment I
have tonnes of SQL's with semicolumns that I'd have to recode. As long as I
can just change the TDBISamQuerys to TEDBQueries and it'll run, I'll be
happy. (I'll look at the new scripting toys after

Cheers

Adam.

Adam


You could always cheat in the short term - create a function that grabs the sql, chops it into single statements and runs each one. I did something like that in my first ElevateDB test app (used ¬ rather than using a glTStringList to hold the sql rather than a query but the principal is the same.

Roy Lambert

Adam,

<< My main concern is in porting a DBISam 4 application to EDB. At the
moment I have tonnes of SQL's with semicolumns that I'd have to recode. As
long as I can just change the TDBISamQuerys to TEDBQueries and it'll run,
I'll be happy. (I'll look at the new scripting toys after >>

It won't do so - the scripting won't be supported via the TEDBQuery
component, but rather via a separate component.  There will be facilities in
the EDB Manager for converting scripts from DBISAM to EDB format, however.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Roy,

> You could always cheat in the short term - create a function that grabs
> the sql, chops it into single statements and runs each one.
> I did something like that in my first ElevateDB test app (used
> ¬ rather than using a glTStringList to hold the sql rather than
> a query but the principal is the same.

Thanks for the reply, and sorry for the delay. Had to go down to Tassie last
week for some work. That's not a bad idea, and I'll keep it in mind, however
if their's going to be another simpler (less coding solution int he
near future, I might hold back for now.

Cheers

Adam.

Good Morning Tim,

> It won't do so - the scripting won't be supported via the TEDBQuery
> component, but rather via a separate component.  There will be facilities
> in the EDB Manager for converting scripts from DBISAM to EDB format,
> however.

Thanks for your reply. I think I'm a little confused now (I have too much
info) Could you please clarify the following:

1) From what I understand, TEDBQuery will never support multiple SQL's
seperated witha semicolon?

2) However, there will be a seperate component that will support this?

3) Will this 'seperate' component be a decendant of TEDBQuery, or be able to
be used as such as a replacement to the TEDBQuery? (ie, can I simply
refactor my existing TDBISamQueries as this new component)?

4) I'm not sure what you mean by converting scripts. Are you saying that
seperate SQL's (seperated with a semicolon) are scripts, or is there more to
scripts than just this?


Basically, what I have in my existing applications are a TDBISamQuery
component with sql that pretty much does the following:

a) Run a query and save results into a memory table.

b) Run a seperate query, that may include the memory table as part of a
join, and save results into another memory table.

c) Possibly continue to have seperate queries loaded into memory and query
on existing results from memory for a number of times (depending on how
complex the query is).

d) If required, have one last SQL that does the final select from the memory
tables (or combination of memory and disk) to retrieve the data exactily how
I need it, and display as a standard query result for reporting purposes.

Is this what this new component for EDB will allow me to do?


and...

5) How different are the 'scripts' in EDB as compared to DBISam. Is there
going to be a new learning curve, or are the differences pretty simple (when
referring to multiple queries if they are scripts)?

Thanks & Regards

Adam.

Adam,

<< 1) From what I understand, TEDBQuery will never support multiple SQL's
seperated witha semicolon? >>

Never is a long time, but yes, this is the plan.

<< 2) However, there will be a seperate component that will support this? >>

Not directly, no.  However, it will support a multi-statement script that
looks like a stored procedure without being stored in the database.  Also,
there will be an easy-to-use script conversion utility (and code) for
converting one to the other.

<< 3) Will this 'seperate' component be a decendant of TEDBQuery, or be able
to be used as such as a replacement to the TEDBQuery? (ie, can I simply
refactor my existing TDBISamQueries as this new component)? >>

It will be able to be used as a replacement to the TEDBQuery and will also
be a TDataSet-descendant that can use the result set returned from a script.

<< 4) I'm not sure what you mean by converting scripts. Are you saying that
seperate SQL's (seperated with a semicolon) are scripts, or is there more to
scripts than just this? >>

There will be more to the scripts in EDB since they will be more like stored
procedures.  They will allow for variables, branching, special cursor
handling, statement prepartion and execution with parameters, etc.

<< Is this what this new component for EDB will allow me to do? >>

Yes, and then some.

<< 5) How different are the 'scripts' in EDB as compared to DBISam. Is there
going to be a new learning curve, or are the differences pretty simple (when
referring to multiple queries if they are scripts)? >>

There will be a new learning curve, yes.  For doing the same type of script
as DBISAM, the main difference will be the inclusion of a begin..end block
and re-formatting of the statements so that they use this format:

EXECUTE IMMEDIATE '<StatementText>';

Also, any result sets returned from a script will need to be changed to a
result set cursor declaration (see below).

So, effectively, a script in DBISAM that looks like this:

SELECT *
INTO "\Memory\Temp"
FROM MyTable;

SELECT * FROM "\Memory\Temp";

would need to be changed to this:

BEGIN
  DECLARE TempCursor CURSOR WITH RETURN FOR TempStmt;

  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE Temp AS '+
                                          'SELECT * FROM MyTable WITH
DATA';

  PREPARE TempStmt FROM 'SELECT * FROM Temp';

  OPEN Temp;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Good Morning Tim,

Thank you for your detailed reply! I believe I have a good handle of what to
expect when changing over to EDB with some of my applications and can see
that the script converter will be essential to my plans.

Have a great day!

Adam.