Roy,

<<The other thing I forgot to mention is never be afraid to post here. We don't mock people (well at least not very often) and we do try and help. If, however, you have an urgent problem and you're a fully paid up EDB'er email, phone or Skype Tim directly - at the end of the day he's the font of all wisdom about things ElevateDB. If he can't help you - panic!>>

Don't worry, I'm sure I post often enough. <img src="http://www.elevatesoft.com/emoticon_grin.png" border=0 alt="Smiley" align="top" height="16" width="16">

Michael Riley
GySgt USMC Retired
ZilchWorks

You're welcome!  I've used Firebird quite a bit in the past and have several
business friends that use it currently in their applications.  The reasons I
chose to go with EDB instead are because it works so well inside of Delphi
(don't have to use dbExpress), has encryption built in for accessing remote
databases, has seamless switching between local and remote connections, and
comes with excellent, responsive tech support from the author!

AFAIK, there are no reference materials other than Tim's manual. But the
newsgroups here are excellent, and like Roy said, feel free to post any
questions here.

David Cornelius
Cornelius Concepts

Another nice thing about a view is that it's pure SQL--no inline strings to
be parsed later!

David Cornelius
Cornelius Concepts

>>
Another nice thing about a view is that it's pure SQL--no inline
strings to be parsed later!
<<


Views have always been my friend. Do views in EDB work the same as in
MS SQL?


Michael Riley
GySgt USMC Retired
www.zilchworks.com

> Views have always been my friend. Do views in EDB work the same as in MS SQL?

In my opinion it's pretty much the same behavior - virtual table based on a query, you can enforce security (give user access to view but not table) etc.  EDB is based on SQL standard so most of the internals and constructs are more similar to MS-SQL type databases than to older Delphi/embedded databases.

Raul

Michael,

<< When does this cursor get CLOSED?  >>

It will get closed when the front-end TEDBStoredProc (or EDBCommand in .NET)
is closed, or when the TEDBDatabase closes and automatically closes it.

<< What happens if this stored procedure is executed three times on the same
Delphi form?  >>

It depends upon whether you manually prepare the procedure by calling the
TEDBStoredProc.Prepare method:

1) If you do manually prepare the procedure, then the result will be a
single PREPARE and OPEN on the first execution.  All subsequent executions
will simply refresh the cursor in the procedure - the PREPARE will do
nothing since the statement is already prepared, and the OPEN will do the
refresh since the cursor is already open.

2) If you don't manually prepare the procedure, then the result will be a
new PREPARE and OPEN on each execution, as if the procedure was being
executed from scratch each time.

<< My thinking along these lines was to put all the query code inside Stored
Procedures and not use inline sql within my Delphi applications. If I use
procs like this am I creating a ton of memory leaks by not closing the
cursors? >>

No.  EDB always cleans up any returned cursors, either when you close the
TEDBStoredProc that you used to call the procedure, or when the TEDBDatabase
is closed.  If using the EDB Server and a session is terminated unexpectedly
on the client side, then the EDB Server will clean up the cursor when it
closes the session and all contained databases.

--
Tim Young
Elevate Software
www.elevatesoft.com

Michael,

<< I'm new to ElevateDB. I have spent the past 15 years developing database
applications using Microsoft SQL Server. I'm haveing a very difficult time
wrapping my head around Stored Procedures in ElevateDB. >>

The major change that you have to get used to is that all SQL execution in
EDB's SQL/PSM implementation is dynamic in nature, just like with a
client-side application.  IOW, there is a PREPARE/EXECUTE or
OPEN/CLOSE/UNPREPARE cycle for every SQL execution that requires a result
set or needs to be executed more than once with same/different parameters.
One-off statements can be executed in one shot using the EXECUTE IMMEDIATE
statement, even with parameters (USING clause).

The reason for this type of design is to avoid the problems that SQL Server
and Oracle encountered when trying to mix statically-compiled DML with DDL
statements that may change the catalog information used to compile the DML
within the context of the same SQL/PSM routine.  With the dynamic execution
design, none of this is a problem, yet you retain the ability to
"pre-compile" routines.  In addition, it gives you the unique ability to be
able to construct dynamic SQL on the fly, which is infinitely useful with
database maintenance scripts and jobs.

Expanding upon my other message in this thread, suppose that you have that
same procedure:

CREATE PROCEDURE "spSelectStrategies" ()
BEGIN
 DECLARE procCur CURSOR WITH RETURN FOR procStmt;
 PREPARE procStmt FROM 'SELECT StrategyId,Strategy,Hint FROM tblStrategies
ORDER BY Sort';
 OPEN procCur;
END

As I indicated, if you manually prepare the TEDBStoredProc component that is
being used to execute this stored procedure, and leave it prepared, then the
procedure will remain pre-compiled as long as you don't destroy or manually
unprepare the TEDBStoredProc component.  This way, you can precisely control
how the procedure is compiled and executed, and how long it will stay
compiled.  If you needed to alter the tblStrategies table, you could simply
call the TEDBStoredProc.UnPrepare method (or just execute the UNPREPARE
statement in the procedure body itself), and that would close the table so
that it could be altered.

The basic rule is this - any statement within a routine that is prepared via
the PREPARE statement will *stay prepared* until the hosting TEDBStoredProc
is unprepared, either manually or automatically.   This means that all
referenced tables in the statement will stay open, and the statement will
stay compiled.

--
Tim Young
Elevate Software
www.elevatesoft.com