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.

I like to put all my code inside stored procedures and for the life of me I cannot create a simple stored procedure in ElevateDB:

CREATE PROCEDURE "spSelectStrategies" ()
BEGIN
SELECT
StrategyId,
Strategy,
Hint
FROM tblStrategies
ORDER BY
Sort
END
VERSION 1.00

This gives me the following error:
ElevateDB Error #700 An error was found in the statement at line 4 and column 1 (Expected : but instead found StrategyId)

How do I create a stored procedure that basically does a simple select * from tableXXX ?

Michael,

Try this:

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

Raul,

I knew there had to be an answer. That was it. Thank you very much.

Now I need to go back and digest the statements one at a time to fully know what is going on.

How would you recommend I go about leveraging what I already know about Microsoft SQL Server into ElevateDB knowledge? Do you think the learning curve is steep?

Now I have a few questions about the OPEN command.

Given the following 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
VERSION 1.00
---------------------------------------------
When does this cursor get CLOSED?
What happens if this stored procedure is executed three times on the same Delphi form?

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?

Michael


For something of that nature (ie a static SQL statement) I wouldn't use a stored procedure but a view.

Roy Lambert [Team Elevate]

<<
For something of that nature (ie a static SQL statement) I wouldn't use a stored procedure but a view.
>>
You'll have to forgive my ignorance here. I haven't programmed in Delphi for about 8 years. I just now picked Delphi back up told commited myself to using ElevateDB as my database of choice. My previous Turbo Pascal and Delphi programming has been non-database applications. I need to redo my current software using a database.

With that said, I have spent the last 8 years developing stateless web applications using MS Sql Server. I did <b>ALL</b> of my database interaction inside stored procedures, even simple little querries like the one I mentioned earlier in this post. I cringe every time I see inline sql being used.

Perhaps I need to rethink the use of stored procedures. Perhaps I need to learn how to unthink in Microsoft and rethink in SQL/PSM. Perhaps I need an attitude adjustment.

Regardless of wether my example is trivial it still declares and opens a cursor. I was always taught if you create or open something you should destroy or close that something when you're done. I'm simply trying to find out if open cursors cause memory leaks.

Do you have any best practice resources I can turn to for using ElevateDB besides studying the hell out of Tim's CDCollection application?

I use SQL Server quite a bit in my day job and I must say that it has a very
rich feature set.  But it has a huge company behind it and a massive user
base, so you've got to expect at least some paradigm shift when moving to a
nimble embeddable database like EDB.

However, the biggest "programmability difference" for me when moving to EDB
is that all DML SQL statements in stored procedures are either prepared
cursors as Raul pointed out, or called by using EXECUTE IMMEDIATE--they're
ALL inline strings.  This means there is no way to get them syntactically
checked when you create or update the procedure.  (So for the simple ones
like your example, a View might be the better route.)

What I do to get around this is execute the statements manually using EDB
Manager, then write the stored procedures copying from the query window.  I
still get caught once in a while because I replaced a constant with a
parameter and didn't type it correctly, but everything needs to get tested
thoroughly anyway, right?

It took me quite a while to get used to cursors--and I still think they're
quite long and wordy just to do something simple.  I used Firebird for
several years and I still think it has the greatest SQL syntax of any
database!

As for the memory usage of the cursor, I believe that as soon as the cursor
is no longer needed, it is automatically freed.  I couldn't find the
reference, but I remember reading a previous post about it and feeling
assured it's not a problem.

Yeah, it'll probably take an attitude adjustment.  Considering the other
benefits of this database (ease of usability in Delphi, ease of deployment,
encryption, replication, etc.), this becomes just one of those things about
ElevateDB to be aware of.

One last thing: to get up to speed with  EDB's SQL, use EDB Manager a LOT!
There's SQL History feature that has become invaluable to me--I'm constantly
building scripts out of that and the "reverse engineer" capability.

Hope that helps,

David Cornelius
Cornelius Concepts

Michael


I'll go along with David - use EDBManager as your start point. You get fast feedback, and some (the vast majority of the time) helpful error messages. Also, as the expression goes, RTFM. Tim's put a tremendous amount of work into it, and its worth at least one read through.

In terms of best practice I don't think there is any apart from the general sort of rules that have to do with any database development.

The reason I suggested a View was that its basically a specialised stored procedure that executes the sql at system start up (or it might be first opening of the view, I can't remember), returns a cursor and lets you just get on with it. Nothing wrong with using a SP just that a View is specifically designed for this.

Roy Lambert [Team Elevate]

David,

Thank you for your thoughtful response.

<<I use SQL Server quite a bit in my day job and I must say that it has a very rich feature set.  But it has a huge company behind it and a massive user base, so you've got to expect at least some paradigm shift when moving to a nimble embeddable database like EDB.>>

Me too. However, most of my SQL Server experience came from creating stateless web applications. I developed a certain mindset because of this and now I need to rethink a little, hence the attitude adjustment.

<<However, the biggest "programmability difference" for me when moving to EDB is that all DML SQL statements in stored procedures are either prepared cursors as Raul pointed out, or called by using EXECUTE IMMEDIATE--they're ALL inline strings.  This means there is no way to get them syntactically checked when you create or update the procedure.>>

Thank you for pointing that out, that helps a lot. I know, expect and understand that there is a learning curve when it comes to EDB... this definitely helps me get my head wrapped around stored procedures a lot more. My goal is to get so familiar with EDB that I start getting SQL server erros because I've used EDB code at work. ;-}

<<What I do to get around this is execute the statements manually using EDB Manager, then write the stored procedures copying from the query window.  I still get caught once in a while because I replaced a constant with a
parameter and didn't type it correctly, but everything needs to get tested thoroughly anyway, right?>>

That's a great idea and yes test, test, test and test again.

<<As for the memory usage of the cursor, I believe that as soon as the cursor is no longer needed, it is automatically freed.  I couldn't find the reference, but I remember reading a previous post about it and feeling
assured it's not a problem.>>

I couldn't find that either. Are there any references besides the manuals that Tim has put together?

<<Yeah, it'll probably take an attitude adjustment.  Considering the other benefits of this database (ease of usability in Delphi, ease of deployment, encryption, replication, etc.), this becomes just one of those things about
ElevateDB to be aware of.>>

Agreed. Being a retired USMC Gunnery Sergeant I know all about attitude adjustments. I spent way too much time hemming and hawing about what DB to use with Delphi. I decided it was time to fish or cut bait. I'm fully commited to making EDB work.

<<One last thing: to get up to speed with  EDB's SQL, use EDB Manager a LOT! There's SQL History feature that has become invaluable to me--I'm constantly building scripts out of that and the "reverse engineer" capability.>>

I have. I've already used the "reverse engineer" several times it's great. I didn't know about the history thingy... I'll have to check that out.

Thanks again for your help

Michael Riley
GySgt USMC Retired
ZilchWorks

Michael


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!


Roy Lambert