Most recent edit on 2008-09-09 11:22:18 by RosieColler [Spaced out]

No differences.

Edited on 2007-04-04 22:42:12 by FrankVanHerreweghe

Additions:
In Btrieve if you want to find if ANY record exists with certain criteria it is really tempting to use a link. With MSSQL that causes REALLY poor performance if the link can find lots of records that match the criteria.

Deletions:
In Btreive if you want to find if ANY record exists with certain criteria it is really tempting to use a link. With MSSQL that causes REALLY poor performance if the link can find lots of records that match the criteria.

Edited on 2007-01-02 18:29:00 by RosieColler

Additions:

Converting An Application From Btrieve to SQL

Btrieve lets you be lazy in the way you program while other databases are not so forgiving. If your programmers have been really careful to program in a way that is compatible with all databases, while only testing on Btrieve, then you'll be OK, but the chances are this is not the case.
Below are some of the known issues you may have to address. If you have done this sort of conversion yourself please add any you know about which are not listed here.

Note On Pervasive.SQL

Steps Required During Conversion

Some of the steps that must be undertaken to convert an application

Establishing what data types are needed in SQL

The database storage types for SQL need to be thought about. Logicals are of particular interest. Integer Logicals can only be included in a key if they are 2 bites rather than 1. Memo's do not exist in a database such as MSSQL, if you make the mistake of replacing them with a CHAR datatype you will drastically increase the size of your database, so something like a VARCHAR is a better choice.
The choice you make for each depends on the application but it needs more thinking about that with a Btrieve database.

Structure of File/Field and Key Names

'?' in them and other symbols are not allowed. Some names are restricted and it is best to avoid them as the database may decide the syntax is wrong if use them.

Unique Keys On Files

In some cases this may not be a simple case of altering the file as it may be that new program logic is required to ensure the file is always written with the correct unique values.

Converting Existing Data

If your existing customers are going to be moved to the new database then their existing data will need to be transferred. There are many methods of achieving this but that does not make it a minor consideration, especially as the file structure in the new files differs from the old ones (which from the points above can easily be the case).
One method is to write magic programs to do it, and it is possible to automate the writing of these programs.

Issues To Be Careful Of

Duplicate Index Messages

You cannot use Check Index with SQL as you can with Btrieve. This means that a duplicate is only detected as the record is committed AFTER the record suffix. When Magic Rolls Back it does not roll back anything done by a called task or program in the record suffix unless your transactions
are carefully set (which in an old Btrieve application is often not the case).
One solution is to do a link in each online program with a main file to check the unique key. This has the advantage of displaying a nice message worded by YOU rather than Magic AND it does the check immediately after the last field in the unique key (like Btrieve) rather than when writing the record (like SQL).

Creating Records

You need to check that ALL places that create a record have ALL fields in ALL unique keys selected. This is a requirement of SQL that if you have only programmed with Btrieve you have probably not done.

Creation of Files in the Database

It is generally best NOT to allow magic to create the files automatically if they don't exist.
In SQL there is an overhead each time a file is opened if "Check Existence" is set to yes.
In MSSQL there is also an issue that if you want to specify a default value for a field then MSSQL will create a constraint. If you do not specify a name for this constraint that it will name it for you and you cannot predict the name it will use. In the future if you want to make any changes to the file then you need to know the names of the constraints, therefore you need to name them your self.
So you need to write creation scripts for the files in the database and either run these manually at new sites or automate the process using either magic or some other installation/upgrade routine.

Locking And Transactions

"Record Has Been Updated Restart" Problems

Probably linked to transaction settings this error can start cropping up and causing the roll back of a transaction in the most incontinent places.
It seems that if you have a task (usually online) which has a link to a record then if that record is deleted in a sub task or updated in such a way as to no longer be returned by the link (i.e. the link no longer returns any record) then you may get a "Record Has Been Updated Restart" message and a roll back of the changes.
e.g. A parent child task is set up to delete all associated children when you delete the parent. The delete action invokes a handler which calls a sub-task to delete the child records then propagates to delete the parent. The parent task has a link though to find out if any child records exist (see "Links" above for why this is not a good idea anyway). When the child records are deleted by the subtask the presence of the link in the parent causes the "Record Has Been Update Restart" error and the deletion of both records is cancelled and the customer gets annoyed because it is impossible to delete the record.

General performance

An initial performance drop may be seen in some programs. In most cases the programs can be re-written to use the SQL features now available to you to get the performance back to what it used to be or even better. But except in a few cases programs written for Btrieve will not perform well in SQL.
With Btrieve if you have a screen with no range at all, then Btrieve will only return enough to fill a screen. With SQL you will get the entire file and you therefore should not do this as it takes longer for Magic to draw the screen. For this you need to re-think the design of many of the programs so that they always have quite small ranges.

Deletions:

Converting An Application From Btrieve to SQL

Btrieve lets you be lazy in the way you program while other databases are not so forgiving. If your programmers have been really careful to program in a way that is compatible with all databases, while only testing on Btrieve, then I would be really impressed.
Below are some of the known issues you will have to address. This is a 'work in progress', please add any you know about which are not listed here.

Note On Pervasive.SQL

Locking And Transactions

General performance

Unique Keys On Files

Edited on 2007-01-02 17:08:08 by RosieColler

Additions:

Note On Pervasive.SQL

Locking And Transactions

Unique Keys On Files

Deletions:

Note on Pervasive.SQL

Locking and Transactions

Unique keys on files

Oldest known version of this page was edited on 2007-01-02 17:07:23 by RosieColler []

Page view:

Converting An Application From Btrieve to SQL

The very first thing you should know if you are considering converting an application which uses the Btrieve gateway from magic to an SQL based gateway is that it is not a process that should be undertaken lightly.

Having said that, in my experience it can be worth the headaches in the end.

The amount of work involved depends greatly on how compliant the Btrieve application is with SQL but in most cases it can require a significant structure change within the application.

Btrieve lets you be lazy in the way you program while other databases are not so forgiving. If your programmers have been really careful to program in a way that is compatible with all databases, while only testing on Btrieve, then I would be really impressed.

Below are some of the known issues you will have to address. This is a 'work in progress', please add any you know about which are not listed here.

Note on Pervasive.SQL

An important note is that you do not have to convert your application at all to use the Pervasive database.

All versions of Pervasive can be accessed by magic in two ways, through the Btrieve Gateway and through the Pervasive Gateway. An existing Btrieve application can seamlessly and invisibly access the same data if you upgrade the database to Pervasive.SQL so long as you use the Btrieve Gateway to access it.

If on the other hand you want to use the Pervasive Gateway and get the advantages inherent with an SQL database then you may need to take note of some of the issues below. The big advantage of Pervasive here over other SQL databases is that it is easier to do these steps a bit at a time, you can even have one file accessed though both gateways (BOBW or 'Best of Both Worlds' to use Steve Blank's phrase).

Locking and Transactions

In Btrieve it really does not matter what transactions settings you have used, you can just ignored them. A straight replace of the files without checking transaction strategy through the whole application can lead to record locks throughout. Some are obvious as soon as the application is run in a multi-user environment. Others are less obvious and can take time to come out of the wood-work.
Will your users bear with you while you find and fix them, because the only true multi-user environment to identify them in is the live one.

Links

In Btreive if you want to find if ANY record exists with certain criteria it is really tempting to use a link. With MSSQL that causes REALLY poor performance if the link can find lots of records that match the criteria.

General performance

Unique keys on files

Btrieve does not object if there is no unique key on a file. SQL does. All files which do not have one will need converting, either by identifying an existing key which can be made unique, by adding extra fields to make the records unique in some way or, (possible in MSSQL) adding an IDENTITY column to the file.

Magic Wiki-Wakka : BtrievetoMSSQL