![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 12 total |
![]() |
Wed, May 21 2008 1:13 AM | Permanent Link |
"David Cornelius" | In my installation/upgrade script, I want to detect the user's table
version and if old, update the table with an alter statement. I've got a stored procedure that declares a cursor to get the version from the information database into a local variable, then based on their current version, executes the alter the table statement. Declaring a cursor seems like a lot of work for a statement that returns exactly one value. Is there a simpler method? I'm sure someone has asked this before (for EDB), but I didn't find it. What I've done (which works) is below: BEGIN DECLARE VerCursor CURSOR FOR VerStmt; DECLARE Ver FLOAT; DECLARE UpgradeStmt STATEMENT; PREPARE VerStmt FROM 'SELECT Version FROM Information.Tables WHERE Name = ''MyTable'''; OPEN VerCursor; FETCH FIRST FROM VerCursor (Version) INTO Ver; CLOSE VerCursor; UNPREPARE VerStmt; IF Ver = 1.0 THEN PREPARE UpgradeStmt FROM 'ALTER TABLE MyTable ADD COLUMN "abc" NUMERIC(10,2) DEFAULT 0.0 AT 10, VERSION 1.1'; --finally! the actual alter table statement!! EXECUTE UpgradeStmt; UNPREPARE UpgradeStmt; END IF; END -- David Cornelius CorneliusConcepts.com custom designed software |
Wed, May 21 2008 2:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | David,
<< Declaring a cursor seems like a lot of work for a statement that returns exactly one value. Is there a simpler method? I'm sure someone has asked this before (for EDB), but I didn't find it. >> No, that's the correct approach. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, May 23 2008 1:03 AM | Permanent Link |
"David Cornelius" | > No, that's the correct approach.
Thanks. I was hoping there was a short-cut way to get a single value from one record in one table into a local variable. Like DELCARE Ver INTEGER; SELECT Ver=Version FROM Information.Tables WHERE ... Oops--there's my SQL Server usage showing up again! -- David Cornelius CorneliusConcepts.com custom designed software |
Fri, May 23 2008 1:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | David,
<< I was hoping there was a short-cut way to get a single value from one record in one table into a local variable. Like >> What you want is a USING clause for EXECUTE IMMEDIATE coupled with an INTO clause that allows for selection into the USING output variables. Both of these things are on my list for EDB. It would look like this: CREATE FUNCTION GetTableVersion(IN TableName VARCHAR) RETURNS DECIMAL(19,2) BEGIN DECLARE Version DECIMAL(19,2) DEFAULT 0; EXECUTE IMMEDIATE 'SELECT Version INTO ? FROM Information.Tables WHERE Name=?' USING Version,TableName; RETURN Version; END; -- Tim Young Elevate Software www.elevatesoft.com |
Sat, May 24 2008 1:02 AM | Permanent Link |
"David Cornelius" | > What you want is a USING clause for EXECUTE IMMEDIATE coupled with an
> INTO clause that allows for selection into the USING output > variables. Both of these things are on my list for EDB. Very cool! Looking forward to it. Thanks for the note. -- David Cornelius CorneliusConcepts.com custom designed software |
Sat, May 24 2008 2:04 AM | Permanent Link |
"David Cornelius" | Ah! But you've given me an idea: hide the details of getting the
table version in a function. Thus, my table update procedure is reduced to: CREATE PROCEDURE "TableUpgradeCostSheet" () BEGIN IF TableVersion('MyTable') = 1.0 THEN EXECUTE IMMEDIATE 'ALTER TABLE MyTable ADD COLUMN "abc" NUMERIC(10,2) DEFAULT 0.0 AT 10, VERSION 1.1'; END IF; END And for those of you who want to see it, my new TableVersion function in the current version of EDB is: CREATE FUNCTION "TableVersion" (IN "TableName" VARCHAR COLLATE ANSI) RETURNS FLOAT BEGIN DECLARE VerCursor CURSOR FOR VerStmt; DECLARE Ver FLOAT; DECLARE CostSheetUpgradeStmt STATEMENT; PREPARE VerStmt FROM 'SELECT Version FROM Information.Tables WHERE Name = '''' + TableName + '''''; OPEN VerCursor; FETCH FIRST FROM VerCursor (Version) INTO Ver; CLOSE VerCursor; UNPREPARE VerStmt; RETURN Ver; END Still a little long, but more elegant and modular. Thanks for the idea! I hadn't gotten into functions yet. (But I am slapping myself on the forehead--this is nice!) -- David Cornelius CorneliusConcepts.com custom designed software |
Sat, May 24 2008 3:07 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | David,
<< Ah! But you've given me an idea: hide the details of getting the table version in a function. Thus, my table update procedure is reduced to: >> Very nice. Very nice. ![]() -- Tim Young Elevate Software www.elevatesoft.com |
Sat, May 24 2008 4:11 PM | Permanent Link |
"Iztok Lajovic" | Tim,
isn't David's method something that we have already seen - i.n DBISAM we can refer to version number via table attrributes and his function is something in that direction. I am wondering if making such function as part of ElevateDB SQL is a deviation from SQL standard? In a present situation everybody has to find his/hers own path to making programming more pleasant by making such functions which could be done once for all. Maybe is now time for opening a thread for contributions of developers like a deverlopers' library of useful solutions. Yes, I know that it would be better that this kind solutions to be an official part of ElevateDB but somewhere one has to start. Iztok Lajovic KreS, Kreativni sistemi "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> je napisal v sporočilo news:E1616D7C-0620-4381-99BC-A420E45A43D5@news.elevatesoft.com ... > David, > > << Ah! But you've given me an idea: hide the details of getting the > table version in a function. Thus, my table update procedure is reduced > to: >> > > Very nice. Very nice. ![]() > > -- > Tim Young > Elevate Software > www.elevatesoft.com > |
Sat, May 24 2008 4:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Iztok,
<< isn't David's method something that we have already seen - i.n DBISAM we can refer to version number via table attrributes and his function is something in that direction. I am wondering if making such function as part of ElevateDB SQL is a deviation from SQL standard? In a present situation everybody has to find his/hers own path to making programming more pleasant by making such functions which could be done once for all. >> Well, the issue is to avoid dealing with a "kitchen sink" result in ElevateDB. It's kind of like Delphi itself, there's only so much you can provide before you're starting to provide solutions that are above and beyond your main mission. This is especially true with meta data in ElevateDB - we will be extending the meta data for tables and other objects quite a bit as the product progresses, and to start down the road of trying to provide a built-in function for every column that you'd like to query in the Information.Tables schema table really defeats the purpose of having system information tables in the first place. We ran into this problem with DBISAM, which required that every little bit of meta data be surfaced with a specific property and remote call to the server, and it was a documentation and testing nightmare. << Maybe is now time for opening a thread for contributions of developers like a deverlopers' library of useful solutions. Yes, I know that it would be better that this kind solutions to be an official part of ElevateDB but somewhere one has to start. >> We have an ElevateDB Extensions newsgroup just for that purpose: http://www.elevatesoft.com/newsgrp?action=viewthreads&group=18&page=1 -- Tim Young Elevate Software www.elevatesoft.com |
Sun, May 25 2008 2:08 AM | Permanent Link |
"David Cornelius" | I am subscribed to that newsgroup, but forgot it was there. This is a
perfect post for that group--so I did! Thanks for the suggestion, Iztok and Tim. -- David Cornelius CorneliusConcepts.com custom designed software |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |