David,

<< The two reasons for this are: 1) for design-time syntax checking, and 2)
simplicity.  (OK, a third comes to mind: similarity to the SQL Server work I
do.)

I guess this is just a limitation (or feature depending on your perspective)
of EDB. >>

Yes, that is correct.  EDB only uses dynamic SQL in SQL/PSM, which has the
downsides that you point out.  However, on the flip side you have the
upsides of:

1) Being able to mix DDL with DML statements without causing issues or
forcing a stored procedure, etc. to be recompiled.

2) *You* control when and where an SQL statement is compiled, and how it is
cached for multiple executions.

3) There are no static dependencies between the stored procedures and the
database catalog.  If the stored procedures used static, instead of dynamic,
SQL statement execution, we would have to prevent the altering and dropping
of referenced columns, tables, etc. from the catalog if such operations
interfered with the proper execution of the stored procedures.  With dynamic
execution, you don't have this issue and are free to do what you want with
the catalog.  If changing the catalog causes an SQL statement in a stored
procedure, the breakage occurs during the execution of the stored procedure
in a very exact and specific place that is easily traceable and fixable.

4) With dynamic SQL statements, you can build SQL statements on-the-fly and
execute them.  You simply cannot do this with static SQL statements.

5) Static SQL execution would be a nightmare for DBISAM customers coming
from the 4.x server-side procedures, which allowed you to do pretty much
anything that you want inside of a server-side procedure, including DDL.  It
would have been a major functionality gap for moving over applications, and
we didn't need to make it any harder than it already was.

6) SQL Server, Oracle, etc. all provide add-on layers for dynamic SQL
execution that are both clunky and hard to use and involve all sorts of
caveats regarding how the dynamic execution is used with the static
statements.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< The main thing I have against this is the lack of feedback for users but
that should be coming (hint hint Tim) >>

2.0 has it already.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Yes, that is correct.  EDB only uses dynamic SQL in SQL/PSM, which
> has the downsides that you point out.  However, on the flip side you
> have the upsides of:

There's a lot underneath that I, as an application developer, don't
see.  And when I work a lot with one database vendor in one
environment, it's sometimes hard to switch to a different one when
things don't work the same.  But that doesn't mean it's inferior!  Just
gotta change my perspective.

Thanks for pointing these out.

--
David Cornelius
corneliusconcepts.com
custom designed software

Tim Young [Elevate Software] wrote:
> There are two types of INSERT, UPDATE, and DELETE statements.  There
> are the kind that work as dynamic SQL statements (like Uli
> described), and there are the SQL/PSM versions that work with SQL/PSM
> cursors only:
>
> http://www.elevatesoft.com/manual?action=mancat&id=edb1sql&category=13
>
> The two are not interchangeable.  Think of the SQL/PSM INSERT,
> UPDATE, and DELETE statements as being the same as the Insert, Edit,
> and Delete methods of the TDataSet component (and descendants).  They
> operate directly on a cursor at the current row position.

This is the realization that dawned on me yesterday. It's getting
clearer--really, it is!

--
David Cornelius
corneliusconcepts.com
custom designed software

David,

<< This is the realization that dawned on me yesterday. It's getting
clearer--really, it is! >>

It takes a while to get all of the metaphors straight, but once they're
there, they're usually the same as they have been for the last 20-30 years.
We just keep changing the names, etc., but the concepts stay the same.

The SQL/PSM is basically a bastardized version of the standard that has
dynamic client-side SQL statements thrown in along with a replacement of the
UPDATE/DELETE WHERE CURRENT OF positioned cursor statements with the ones
that we allow that operate directly on cursors.  The reason for this is that
the positioned cursor statements were always just a way for the SQL
standards folks to work around the fact that initially there weren't any
SQL/PSM standards at all, let alone standards with declared cursor types,
etc.

The way that I looked at it during development of EDB was this: if MS and
Oracle, etc. aren't going to follow the SQL/PSM standards to the letter,
then I can get away with not using the more egregious aspects of the
standard, such as the cursor handling and the error handling.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> The way that I looked at it during development of EDB was this: if MS
> and Oracle, etc. aren't going to follow the SQL/PSM standards to the
> letter, then I can get away with not using the more egregious aspects
> of the standard, such as the cursor handling and the error handling.
>



I fell into the trap that what I am most familiar with, MS SQL Server,
has me in some ways spoiled you might say.  AFAIK, any SQL statement I
can run by itself in SQL Server, I can put in a stored procedure or in
a CURSOR loop exactly the same way.  I just took it for granted that
this was the way it should be.

I suppose that's somewhat akin to learning to program in BASIC, then
moving to Pascal and learning that you have to actually declare your
variables before you use them!

(that's probably a really poor analogy)

--
David Cornelius
CorneliusConcepts.com
custom designed software

David,

<< I fell into the trap that what I am most familiar with, MS SQL Server,
has me in some ways spoiled you might say.  AFAIK, any SQL statement I can
run by itself in SQL Server, I can put in a stored procedure or in a CURSOR
loop exactly the same way.  I just took it for granted that this was the way
it should be. >>

Yes, this is true.  However, overall it is a lot less flexible than the
dynamic SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com