Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread DBISAM table updates for new client versions
Thu, Feb 2 2006 7:44 PMPermanent Link

"Kerry Neighbour"
I am developing a client application in DBISAM 4.22. No problems there. I
was wondering about how to support an existing client base when I bring out
new versions of my software down the track. I can update my software ok, but
what happens when/if I alter the DBISAM table structures?

My first thought was to write a small Delphi app that simply ran a series of
SQL ALTER TABLE queries....so that all a customer has to do is run my
UpdateDelphiApp with the latest script that I provide. This will include
every single change to the database since day 1.

If a particular customer already has some of the alterations (from a
previous upgrade), then the early ALTER TABLE commands will simply not do
anything. So no harm done.

This method will not take into account serious table alterations or serious
data manipulation, but it should take care of newly added fields and the
like. I can even do some basic data manipulation with UPDATE and INSERT INTO
queries if need be. This is all I envisage it will have to do.

Does this sound like a reasonable way to go? Have I missed a fatal flaw in
my thinking?

Thu, Feb 2 2006 8:23 PMPermanent Link

Steve Forbes

Team Elevate Team Elevate

Hi Kerry,

> Does this sound like a reasonable way to go? Have I missed a fatal flaw in
> my thinking?

Absolutely, .. my applications check the DB major/minor version numbers when
opening the DB, and if changes are needed, backs-up the existing DB (IMO
this is important), and then applies the changes (via SQL DDL). In my case
it is not necessary, but you could embed this functionality in a DLL or
package to make distributing updated DB version stuff easier.

BTW, been using this method for years and have never had a single glitch.

Best regards

Steve

"Kerry Neighbour" <kneighbour@securedoc.com.au> wrote in message
news:06088772-4359-44BB-BB1C-370C3B77D910@news.elevatesoft.com...

Thu, Feb 2 2006 8:25 PMPermanent Link

Steve Forbes

Team Elevate Team Elevate

Hi Kerry,

Reread my post after sending .. my absolutely was aimed at your question
"Does this sound like a reasonable way to go?", not the other part I quoted
<bg>

Best regards

Steve

"Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message
news:7AF5ACD5-4F00-4714-993B-BC4536E59403@news.elevatesoft.com...

Thu, Feb 2 2006 10:08 PMPermanent Link

"Kerry Neighbour"
Good - this will not be a very common occurance (I hope), so I do not plan
to build the update into the main application. I thought of having a
separate application that would run a supplied SQL script...something like
this

ALTER TABLE if EXISTS "usersrequests" ADD COLUMN if not EXISTS UserID
integer
ALTER TABLE if EXISTS "usersrequests" REDEFINE COLUMN if EXISTS UserID
integer
ALTER TABLE if EXISTS "usersrequests" ADD COLUMN if not EXISTS OfficeID
integer
ALTER TABLE if EXISTS "usersrequests" REDEFINE COLUMN if EXISTS OfficeID
integer
ALTER TABLE if EXISTS "usersrequests" ADD COLUMN if not EXISTS Active
integer
ALTER TABLE if EXISTS "usersrequests" REDEFINE COLUMN if EXISTS Active
integer

This small app would read in the script line by line, then run it through a
DBISAMQuery - line after line.

I have written a small application that scans all the tables in my current,
working database, and generates all these SQL lines (as above). This is the
version that I want users to have.

I simply ADD any columns that do not exist - and then I REDEFINE any columns
that do exist. I think that this should work, although it is not elegant. ie
I always redefine a column - even if the column has just been added.

I could probably get around this by using the Version numbers, but this also
seems to be a maintenance nightmare. I would have to update my table
versions every time I did something - and what if I forgot? It seems very
prone to error, so I think that this shotgun approach seems the safest. I
only have 30 or 40 tables, all fairly small (10 or so fields in each), so I
do not think that it will take long to perform the update in any case.

It is a shame you cannot compound the ALTER statements - but it seems that
you need to do an ALTER statement for each and every field line by line. ie
if you have 30 fields in a table, then you need 30 ALTER statements (or in
my case 60, as I do an ADD, then a REDEFINE).

Does this all seem ok?

Fri, Feb 3 2006 1:14 AMPermanent Link

"Adam H."
Kerry,

I do something similar, but something that *may* be a bit easier than
manually coding every change. (Someone else jump in if you think this is a
bad idea Smiley

I have a seperate form called FormTables, where I place a TDBISamTable
component for any tables that I have added fields to (or if I have created
any new tables).

I then add persistent fields to those tables. When the user runs a 'check
fields' routine, it tests to make sure the persistent fields in the
TDBISamTable component exist in the users table. If not - it runs a script
to add these fields automatically. If it can't find the table - it asks the
user if they want to create the table.

All you require is persistent fields in the TDBISamTable component - the
code does the rest. So, everytime you make a change to a table, all you need
to do is go to this form, double-click on the dataset in question, and
choose ADD Fields, to add any new fields that may exist.

It may be a bit lazier than your solution, and it certainly won't REDEFINE
any existing fields, but it does create new fields, if that is your issue.

If you want, I could post the code I use.

Regards

Adam.

Fri, Feb 3 2006 3:57 AMPermanent Link

Dan Rootham
Kerry,

<< what happens when/if I alter the DBISAM table structures? >>

I wrote an application that had to do this. On opening the app, the software
checked to see which minor version the tables were at - and if necessary
issued the ALTER statements - including a change to the minor version.
It had to be a multistage process, because we never knew whether a
dealer might install months later from an old (original) CD, and therefore
might need more than one update to be applied when they upgraded.

My memory is a bit hazy, but I do remember an occasional problem on Win XP.
The restructure process didn't always end up with the same file rights on the
new table as had applied to the original. It was nothing to do with DBISAM,
but you might need to check this point if the user is running on a network?

HTH,
Dan
Fri, Feb 3 2006 6:27 AMPermanent Link

Steve Forbes

Team Elevate Team Elevate

Hi Kerry,

You don't need the separate ALTER TABLE statements .. e.g.

ALTER TABLE "Control"
ADD COLUMN "LastBackup" TIMESTAMP DESCRIPTION "Date/Time of last database
backup",
ADD COLUMN "LastOptimise" TIMESTAMP DESCRIPTION "Date/Time of last database
optimise",
ADD COLUMN "LastRepair" TIMESTAMP DESCRIPTION "Date/Time of last database
repair"
USER MAJOR VERSION 1
USER MINOR VERSION 2
NOBACKUP;

HTH
--
Best regards

Steve

"Kerry Neighbour" <kneighbour@securedoc.com.au> wrote in message
news:BC82B1C6-9C8A-432E-A53B-F43AB2E2A3D5@news.elevatesoft.com...
> Good - this will not be a very common occurance (I hope), so I do not plan
> to build the update into the main application. I thought of having a
> separate application that would run a supplied SQL script...something like
> this
>
> ALTER TABLE if EXISTS "usersrequests" ADD COLUMN if not EXISTS UserID
> integer
> ALTER TABLE if EXISTS "usersrequests" REDEFINE COLUMN if EXISTS UserID
> integer
> ALTER TABLE if EXISTS "usersrequests" ADD COLUMN if not EXISTS OfficeID
> integer
> ALTER TABLE if EXISTS "usersrequests" REDEFINE COLUMN if EXISTS OfficeID
> integer
> ALTER TABLE if EXISTS "usersrequests" ADD COLUMN if not EXISTS Active
> integer
> ALTER TABLE if EXISTS "usersrequests" REDEFINE COLUMN if EXISTS Active
> integer
>
> This small app would read in the script line by line, then run it through
> a DBISAMQuery - line after line.
>
> I have written a small application that scans all the tables in my
> current, working database, and generates all these SQL lines (as above).
> This is the version that I want users to have.
>
> I simply ADD any columns that do not exist - and then I REDEFINE any
> columns that do exist. I think that this should work, although it is not
> elegant. ie I always redefine a column - even if the column has just been
> added.
>
> I could probably get around this by using the Version numbers, but this
> also seems to be a maintenance nightmare. I would have to update my table
> versions every time I did something - and what if I forgot? It seems very
> prone to error, so I think that this shotgun approach seems the safest. I
> only have 30 or 40 tables, all fairly small (10 or so fields in each), so
> I do not think that it will take long to perform the update in any case.
>
> It is a shame you cannot compound the ALTER statements - but it seems that
> you need to do an ALTER statement for each and every field line by line.
> ie if you have 30 fields in a table, then you need 30 ALTER statements (or
> in my case 60, as I do an ADD, then a REDEFINE).
>
> Does this all seem ok?
>

Thu, Feb 16 2006 5:03 AMPermanent Link

It would be really nice to have this thread, or a nice version of it, put
in the manual somewhere. Myself, I've been using the Table functions to do
the transformation. Normally I just check if a field is there, and if not
I add it. Recently I added a field change (more length needed in strings)
and finally one alter table to complete it. I always run it as part of the
initialisation routines.

/Matthew Jones/
Image