ElevateDB: Stored Procedures Part 1

Okay, so I finally decided to shit or get off the pot and I purchased ElevateDB last week. The GUI tool is great very user friendly. I created a couple tables and populated them. I used the reverse engineer tool to see what that does. It would be nice if the reverse engineer tool would let you pick just one table. (Submited a wish list comment on the support forum)

I'm commited to making ElevateDB work inside my Delphi applications. So, one of the first things I tried doing was creating a simple stored procedure to select all of the data from one of the tables. Easy enough right... wrong.

I've been using stored procedures inside of Microsoft SQL for the past 12 years. So I created what I thought was a pretty straight forward stored procedure:

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

Nope. This gave 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)

I sped off to make my first post on ElevateDB's support forum. This is place where users help users. Besides being told my use of a stored procedure was a trivial use of a stored procedure I was given the correct syntax for how an ElevateDB stored procedure should look.

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

The whitespace is not necessary, that's all me. I tend to use a lot of white space in my code. I know I'm anal when it comes to certain things. Anyway, it turns out that ALL stored procedures within ElevateDB act on Dynamic SQL. In order to get a dataset returned you need to

DECLARE a CURSOR;
PREPARE a statement;
OPEN the CURSOR

It's going to take me a little while to wrap my SQL Head around to the ElevateDB way of thinking. Stay tuned as I learn more about ElevateDB.

Semper Fi,
Gunny Mike

Next >