Roy,

<< I think its more like we don't know what good they would be or how to use
them yet. If I'm interpreting your comments correctly it is possible to run
scripts its just that you have to create a stored procedure first. If I'm
right maybe all that's needed is a simpler way of getting from the scrip to
the SP, running it and getting the result. >>

Well, since most scripts are hand-coded, it's somewhat difficult to automate
this other than to simply bundle the SQL statements into a CREATE PROCEDURE
statatement.

--
Tim Young
Elevate Software
www.elevatesoft.com

Michael,

<< Unicode build pleeeeaaaaaseeeeeee ????? >>

Not yet.

<< CD/MP3/OGG/ACC/MP4 catalog system ?? <bg> >>

CD's only.  It's just an example application, after all.

--
Tim Young
Elevate Software
www.elevatesoft.com

Michael,

<< Hm. I went to search on google. CD I guess is a Certificate of Deposit.
And the Collector is probably somebody who makes you wanna do the deposit.
But I wouldn't know much about that business >>

Actually CD in this case is compact disc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>Well, since most scripts are hand-coded, it's somewhat difficult to automate
>this other than to simply bundle the SQL statements into a CREATE PROCEDURE
>statatement.

See I knew you'd be able to do it

Roy Lambert

Tim


I always thought the C stood for criminally expensive

Roy Lambert

Tim,

I think you misunderstood my point. I didn't really ask for any
change of implementation, just a usability thing. We all and pretty
much all our tools work with SQL scripts. Let's call them batches, so
that it wouldn't sound like something that has to be directly supported
by the server. Batch execution of SQL statements is what required
to work with SQL database. In order to support batches, which may
include declaration of stored procedures one should add pragma (to
Console applcation) to allow changing of delimiter. That was my first suggestion.

You don't even need to do anything about it in the engine. As a matter of fact,
you don't need to do anything, as far as I'm concerned, cause I have my own
universal parser used in our Database Designer console, which
allows to execute batches towards any database by specifying which
types of comments, delimiter and pragma are used. This is mostly a
suggestion to make EDBMgr more useful for those who test EDB.

> Your mixing two different things.  EXECUTE BLOCK *is* dynamic SQL, but the
I realize that perfectly. And it's fine. It's just that stored procedures
are not the means of creating tables, etc. Nobody does it this way, especially
via dynamic SQL. Execute block (yes, dynamic) is convenient if you need
to add some DML processing to your batch which requires, say, iterations
of other goodies that PSM provides. That's all. It's just a way of avoiding
creating procedures which you only be calling once (say, when updating
database structure). In this case you don't need to escape anything, just
write a batch, that creates\alters tables and executes blocks which
updates data if required. Alternatively, you can create procedure, execute
it and (probably) drop it in the same batch, but it's just less convenient.

> There's no way to do any harm with DDL in a stored procedure in EDB.  You
> can't delete any referenced procedures and any SQL statements that reference
> dropped or altered objects will simply issue the appropriate error.
Sure, but there's a way to defeat concurrency OR run into dead-lock.
Also every time you execute DDL you destroy meta-data cache.
DDL is simply unnecessary inside stored procedures. If we are (the developers)
are to build reliable fast databases, then no matter how it is implemented
we should avoid DDL in stored procedures by all means - IMO. This is misuse of
the procedures. The fact that they are dynamic is very good - it's one
of nicest features of EDB. The fact, that they are *only* dynamic... is
understandable.

> << The idea is that procedures are something frequently used, while DDL is
> something that should be avoided by all means >>
> The frequency of use doesn't matter with the way it is implemented in EDB.
Unfortunately so However, it's not the main feature we are looking for
in EDB, so I don't consider this to be a disadvantage. The only thing is -
it just makes writing DDL statements inside procedures more difficult (cannot be
validated at all), so you should be the one actually arguing for not using
stored procedures for DDL .

> You can PREPARE statements once and execute them as many times as you want
> within the context of the stored procedure.  IOW, the pre-compilation
> "benefits" of traditional stored procedures simply aren't that great when
> compared to having more flexibility and the ability to pre-compile selective
> statements as required.  
Can we precompile dynamic SQL within stored procs?

> More and more vendors are *adding* dynamic SQL
> capabilities to their SP languages, not removing them.
That's 100% true. Dynamic SQL is a must have feature and is very cool,
no arguing here.

Regards,
Michael

Michael,

<< You don't even need to do anything about it in the engine. As a matter of
fact, you don't need to do anything, as far as I'm concerned, cause I have
my own universal parser used in our Database Designer console, which> allows
to execute batches towards any database by specifying which types of
comments, delimiter and pragma are used. This is mostly a suggestion to make
EDBMgr more useful for those who test EDB. >>

Ahh, yes.  I misunderstood what you were saying.  We will, at the very
least, give such an ability pretty soon.

<< I realize that perfectly. And it's fine. It's just that stored procedures
are not the means of creating tables, etc. Nobody does it this way,
especially via dynamic SQL. >>

That's simply not correct.  MS specifically allows for DDL and SP
recompilation in their SPs.  They sure aren't doing it just for the heck of
it.  Oracle also allows for it via dynamic SQL, and they specifically state
how to do so in their docs.

<< Execute block (yes, dynamic) is convenient if you need to add some DML
processing to your batch which requires, say, iterations of other goodies
that PSM provides. That's all. It's just a way of avoiding creating
procedures which you only be calling once (say, when updating database
structure). In this case you don't need to escape anything, just write a
batch, that creates\alters tables and executes blocks which updates data if
required. Alternatively, you can create procedure, execute it and (probably)
drop it in the same batch, but it's just less convenient. >>

I understand, but I'm not arguing that dynamic SQL or EXECUTE BLOCK isn't
necessary or nice.  I'm saying that there are legitimate reasons outside of
these situations that you cite where dynamic SQL is necessary in stored
procedures.  Take a table that has a somewhat dynamic structure with known
variations.  SPs without dynamic SQL simply cannot adapt to such a situation
and require you to either hard-code multiple variations of the same SP or
some other type of tap-dancing around the problem.

<< Sure, but there's a way to defeat concurrency OR run into dead-lock. >>

Not in EDB.

<< Also every time you execute DDL you destroy meta-data cache. >>

Sure, but that's not a super big performance problem either.

<< DDL is simply unnecessary inside stored procedures. If we are (the
developers) are to build reliable fast databases, then no matter how it is
implemented we should avoid DDL in stored procedures by all means - IMO. >>

I just simply disagree.  There's just no good reason not to allow DDL along
with DML in an SP.

<< This is misuse of the procedures. The fact that they are dynamic is very
good - it's one of nicest features of EDB. The fact, that they are *only*
dynamic... is understandable. >>

They aren't only dynamic.  You can prepare statements in SPs in EDB so that
they are compiled once and executed many times, just like prepared
statements from a client application.

<< The only thing is - it just makes writing DDL statements inside
procedures more difficult (cannot be validated at all), so you should be the
one actually arguing for not using stored procedures for DDL . >>

You could say the same for all client-side SQL.  It all has to be run at
least once to know that there aren't any compilation errors.  That's hardly
something that we aren't all perfectly capable of dealing with.

<< Can we precompile dynamic SQL within stored procs? >>

Sure, just use the PREPARE statement:

http://www.elevatesoft.com/edb1sql_prepare.htm

The documentation isn't complete in above link, but the syntax is correct.
I should have that all fixed up by the end of this week.  There's also an
UNPREPARE statement, but that's missing from the documentation currently.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

> << Unicode build pleeeeaaaaaseeeeeee ????? >>
>
> Not yet.

You'll not release any unicode BETA build at all ?

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr

Michael,

<< You'll not release any unicode BETA build at all ? >>

Probably next week we'll have a Unicode build for BDS 2005 and  BDS 2006.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

> << You'll not release any unicode BETA build at all ? >>
>
> Probably next week we'll have a Unicode build for BDS 2005 and  BDS
> 2006.

Thank you! Thank you! Thank you!

--
Charalabos Michael - [Creation Power] - http://www.creationpower.com -
http://www.creationpower.gr