Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Public Beta Tests » View Thread |
Messages 1 to 10 of 19 total |
PSQL Help |
Tue, Jan 2 2007 11:06 PM | Permanent Link |
Steve Forbes Team Elevate | 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 |
Wed, Jan 3 2007 5:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Steve
Why not post the procedures to give Tim a chance to suggest how to word them? Roy Lambert |
Wed, Jan 3 2007 5:39 AM | Permanent Link |
Steve Forbes Team Elevate | 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 > |
Wed, Jan 3 2007 5:44 AM | Permanent Link |
Steve Forbes Team Elevate | 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 >> > > |
Wed, Jan 3 2007 6:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Steve
That's good cos it also lets other people learn from your mistakes Roy Lambert |
Wed, Jan 3 2007 2:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jan 3 2007 3:11 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Wed, Jan 3 2007 4:20 PM | Permanent Link |
Steve Forbes Team Elevate | 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... > |
Wed, Jan 3 2007 4:21 PM | Permanent Link |
Steve Forbes Team Elevate | 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... |
Wed, Jan 3 2007 5:34 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |