Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Support for SQL scripts in EDB
Tue, Apr 3 2007 7:33 AMPermanent Link

adam
Am I right in saying that EDB doesn't support the use of ";" to separate multiple SQL
statements in a query?

I seem to remember reading this around the time EDB was launched & Tim said something like
"woops I'll fix that" ...

Its a bit of a critical issue for me, if I'm going to upgrade.


Adam
Tue, Apr 3 2007 8:35 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

adam


Near but not quite. As of 1.00b1 ElevateDB doesn't support multiple statements in a query. You can put them in a stored procedure apparently (not tried it).

For my table creation scripts I'm parsing them myself (using as a statement separator) and supplying each statement individually to a TEDBQuery.

Roy Lambert
Tue, Apr 3 2007 8:44 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< Am I right in saying that EDB doesn't support the use of ";" to separate
multiple SQL statements in a query? >>

For now, yes, that is the case.  We intend to provide a much richer
scripting environment for EDB with conditional branching and looping,
variables, etc. and we simply didn't have enough time to do so before the
final release.

<< I seem to remember reading this around the time EDB was launched & Tim
said something like "woops I'll fix that" ...  >>

No, it's always been intentional to leave off the scripting initially.  I
did try to see if I could squeeze something in before the release, but time
didn't permit it.

Basically, all the scripting in DBISAM's TDBISAMQuery did was parse each SQL
statement out and throw it at the engine individually.  We want EDB's
scripting to be executed on the server in its entirety for performance
reasons.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Apr 3 2007 3:00 PMPermanent Link

adam
That sounds brilliant Tim, are we talking EDB v2 or EDB v1.2??
Tue, Apr 3 2007 8:40 PMPermanent Link

Oliver Bock
For what it's worth, I also vote for this in order to save myself the
work of emulating the old system.  I don't care for all the control flow
stuff; I just want to execute multiple statements and have the output
dataset contain the results of the last statement.  (I can also see the
benefit of parsing the script on the server.)


  Oliver
Wed, Apr 4 2007 5:26 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< That sounds brilliant Tim, are we talking EDB v2 or EDB v1.2?? >>

Most likely before summer.

--
Tim Young
Elevate Software
www.elevatesoft.com

Wed, Apr 4 2007 5:27 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Oliver,

<< For what it's worth, I also vote for this in order to save myself the
work of emulating the old system.  I don't care for all the control flow
stuff; I just want to execute multiple statements and have the output
dataset contain the results of the last statement.  (I can also see the
benefit of parsing the script on the server.) >>

I understand, but we want to make it more useful to those that do need/want
branching, etc. in the process.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Apr 5 2007 4:23 AMPermanent Link

Chris Holland

SEC Solutions Ltd.

Avatar

Team Elevate Team Elevate

Hi Tim,

Does this mean that there will be no scripting if using a direct local connection instead of via a server?

Chris Holland

> Basically, all the scripting in DBISAM's TDBISAMQuery did was parse each SQL
> statement out and throw it at the engine individually.  We want EDB's
> scripting to be executed on the server in its entirety for performance
> reasons.
>
Thu, Apr 5 2007 12:21 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Chris,

<< Does this mean that there will be no scripting if using a direct local
connection instead of via a server? >>

No, not at all.  For local connections, the script will simply be sent to
the local engine (via a direct method call) and executed locally.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Apr 6 2007 2:02 PMPermanent Link

Dave Harrison
adam wrote:
> Am I right in saying that EDB doesn't support the use of ";" to separate multiple SQL
> statements in a query?
>
> I seem to remember reading this around the time EDB was launched & Tim said something like
> "woops I'll fix that" ...
>
> Its a bit of a critical issue for me, if I'm going to upgrade.
>
>
> Adam
>
Adam,
     I open all my SQL statements in a function called OpenQuery() and
pass it the query component, SQL etc. as parameters. If the query
component (TDBISAMQuery) is nil, the function creates it and points it
to the correct database. I then parse out the SQL statements separated
by the ";" (because not all database engines support scripting) and
execute the sql statements individually. This allows me to trap all the
errors in one place and also allows me to do connection pooling. It
works great. It also reduces code in the calling procedures because I
don't have to handle error checking. My calling procedure when finished
with the query, calls "FreeQuery(TDBISAMQuery)" in a Finally clause and
it frees the component and returns it to the pool. All my large apps
have this set of routines and I'd never go back to opening queries (or
tables) individually.

Dave
Image