Hi Tim,

Is EDB's version of PSQL proprietory?

The reason I ask is that I am trying to implement/test some stored
procedures in EDB which I already have working in 3 other DB engines (the
PSQL syntax is fairly similar in all those DBs), however, apart from the
top/tail/params I am having difficulty replicating them in EDB. I guess a
PSQL syntax guide would be very, very handy <bg>

--
Best regards

Steve

Steve


Why not post the procedures to give Tim a chance to suggest how to word them?

Roy Lambert

Hi Roy,

That's definitely a sage piece of advice Roy :-P .. but I'm a hands on
learner, and will get much more out of the exercise if I figure it out for
myself. Also, I don't want to bog Tim down with wads of procedures that I
should be able to do on my own.

Hopefully there's a little PSQL syntax guide in the pipeline at Elevatesoft

--
Best regards

Steve

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:9CF51B31-02B2-4E33-B921-A2A8A5046C98@news.elevatesoft.com...
> Steve
>
>
> Why not post the procedures to give Tim a chance to suggest how to word
> them?
>
> Roy Lambert
>

But here's one of the procedures anyway

CREATE PROCEDURE PROC_INSERT_BOOK(NewName VarChar(60), NewId Integer, OUT
Success Boolean)
 MODIFIES SQL DATA
BEGIN
 DECLARE NewLeft INTEGER DEFAULT 0;
 SET Success = FALSE;

 START TRANSACTION;

 TRY
   SET NewLeft = (SELECT TreeRight FROM Category WHERE Id = 1);

   IF NewLeft > 0 THEN
     UPDATE Category SET TreeRight = TreeRight + 2 WHERE Id = 1;
     INSERT INTO Category (Id, RecordGUID, TreeLeft, TreeRight, Name)
VALUES (NewId, TOSTRING(NEWGUID), NewLeft, NewLeft + 1, NewName);
     COMMIT;
     SET Success = TRUE;
   END IF;
 CATCH (TRUE)
   ROLLBACK;
   SIGNAL ERROR_MESSAGE;
 END;
END;

--
Best regards

Steve

"Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message
news:C1792F55-E8AD-42AA-B4E0-499A5194BB94@news.elevatesoft.com...
> Hi Roy,
>
> That's definitely a sage piece of advice Roy :-P .. but I'm a hands on
> learner, and will get much more out of the exercise if I figure it out for
> myself. Also, I don't want to bog Tim down with wads of procedures that I
> should be able to do on my own.
>
> Hopefully there's a little PSQL syntax guide in the pipeline at
> Elevatesoft
> --
> Best regards
>
> Steve
>
> "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
> news:9CF51B31-02B2-4E33-B921-A2A8A5046C98@news.elevatesoft.com...
>> Steve
>>
>>
>> Why not post the procedures to give Tim a chance to suggest how to word
>> them?
>>
>> Roy Lambert
>>
>
>

Steve


That's good cos it also lets other people learn from your mistakes

Roy Lambert

Steve,

<< Is EDB's version of PSQL proprietory? >>

Partially.  We had to make a choice to go completely-early-bound like some
DBs do and bind DML statements in the procedures/functions to the database,
or go completely-late-bound and make all non-control DML and DDL statements
require dynamic preparation/execution.  We chose a the latter.  Initially we
had it designed to all be early-bound, but that can cause some serious
issues such as preventing DDL statements in procedures/functions or having
any DDL statement force a recompile of the procedure or function during
execution.  It also causes issues where you have to prevent objects from
being dropped from the database if they are referenced in a procedure or
function, which can cause major headaches when doing simple things like
altering a table and adding or dropping columns.  Plus, with late-bound
procedures and functions, you can still prepare a statement once and execute
it many times, thus negating the major benefit of early-bound procedures and
functions - pre-compilation.

The bottom line is that any DML or DDL statement that you may execute in
another database is at least going to require modification to the form of:

EXECUTE IMMEDIATE <StatementText>

and <StatementText> must be enclosed in single quotes.

<< I guess a PSQL syntax guide would be very, very handy <bg> >>

We have one in the works and it should be done pretty soon:

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

A lot of the stuff in there is not correct (SQL 2003 deviations, etc.), but
the syntax is all correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Steve,

<< But here's one of the procedures anyway >>

Okay, here's the translation (off the top of my head, not tested):

CREATE PROCEDURE PROC_INSERT_BOOK(NewName VarChar(60), NewId Integer, OUT
Success Boolean)
BEGIN
 DECLARE NewLeft INTEGER DEFAULT 0;
 DECLARE CategoryCursor SENSITIVE CURSOR FOR CategoryStatement;

 SET Success = FALSE;

 START TRANSACTION;

 BEGIN

   PREPARE STATEMENT CategoryStatement FROM 'SELECT TreeRight FROM Category
WHERE Id = 1';
   OPEN CategoryCursor;

   IF ROWCOUNT(CategoryCursor) > 0 THEN

     FETCH FIRST FROM CategoryCursor (TreeRight) INTO NewLeft;

     UPDATE CategoryCursor SET TreeRight = TreeRight + 2;

     INSERT INTO CategoryCursor (Id, RecordGUID, TreeLeft, TreeRight, Name)
             VALUES (NewId, CURRENT_GUID, NewLeft, NewLeft + 1, NewName);

     COMMIT;

     SET Success = TRUE;

   END IF;

 EXCEPTION

   ROLLBACK;

   RAISE;

 END;

END;

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks!
--
Best regards

Steve

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:8F567FF9-A721-4BD8-9FD4-818FA66AD9E7@news.elevatesoft.com...
>

Hi Tim,

Thanks, that's a big help!

--
Best regards

Steve

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:210CF137-98EF-4E71-9FC1-97149259F870@news.elevatesoft.com...

Tim,

<< Partially.  We had to make a choice to go completely-early-bound like
some  DBs do and bind DML statements in the procedures/functions to the
database, or go completely-late-bound and make all non-control DML and DDL
statements require dynamic preparation/execution.  We chose a the latter.
>>

I understand what you're saying, but IMO this design makes ElevateDB
somewhat awkward to use compared to many RDBMS, and especially so since
regular multi-statement blocks (scripts) are not supported either.

I fear that Steve (Forbes) and other SQL users might find it hard to port
their SQL routines to dynamic SQL.

Ole Willy Tuv