Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 17 of 17 total |
Simple Stored Procedure in ElevateDB |
Sun, Sep 4 2011 3:36 PM | Permanent Link |
Michael Riley ZilchWorks | 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 |
Sun, Sep 4 2011 4:01 PM | Permanent Link |
David Cornelius Cornelius Concepts | 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 |
Sun, Sep 4 2011 4:02 PM | Permanent Link |
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 |
Mon, Sep 5 2011 2:20 AM | Permanent Link |
Michael Riley ZilchWorks | >>
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 |
Mon, Sep 5 2011 11:27 AM | Permanent Link |
Raul Team Elevate | > 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 |
Tue, Sep 6 2011 1:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Sep 6 2011 1:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |