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

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

> 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

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

> 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

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

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

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
>

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

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