Connectivity in DataFlex 18.1

Support for Primary Keys and Clustered Indexes

The drivers now allow you to set a table’s primary key (PK) and define a MSSQL or DB2 clustered index. While it has always been possible to do this using the SQL server’s native table maintenance tools, you can now do this directly. In addition, our conversion wizards now let you assign PKs and clustered indexes, and our connection wizards do a better job of connecting to tables with these features enabled.

Normally, an SQL table will have a primary key with a primary index, and in MSSQL or DB2, that index will be clustered. To create such a table in the Studio, you would:

Create a New Table (e.g., Customer).
Create a column (or columns) for your PK (e.g., Customer.Number).
Create an Index for your PK (e.g., Create one segment Customer.Number Index).
For that index, set the attribute DF_INDEX_SQL_PRIMARY_KEY to True. This is the only new step.

This will define the PK and automatically make this a clustered index. This technique will work with both standard and legacy style recnum tables.

A clustered index is used to improve performance and is usually assigned to the PK index. There may be rare cases where you would not want your PK index to be clustered (either wanting no clustered index or a different clustered index). In such a case, you can use the DF_INDEX_CLUSTERED attribute to assign a different or no clustered index. You can only have one PK and one clustered index. Therefore, DF_INDEX_SQL_PRIMARY_KEY and DF_INDEX_CLUSTERED can only be set to true for one or no indexes.

If you are using recnum tables, your recnum column cannot be designated as the PK or the clustered index. This should not be an issue as it is not recommended to use recnum as your only unique field (i.e., you don’t use recnum in relationships).

Eventually, the Studio will be changed to make the selection of PKs and clustered indexes even easier.

New Attributes: DF_INDEX_SQL_PRIMARY_KEY, DF_INDEX_CLUSTERED

SQL Identity Columns

MSSQL and DB2 allow you to define a single column as an identity column. An identity column is a numeric column (often int or bigint) whose value is auto-assigned when a new record is created. Using this instead of the System Table/DD auto-increment has the advantage that there is no locking contention on a system table. While identity columns may be assigned to any column, it is most often used with a numeric PK column.

To create an identity column for a standard table within the Studio:

Create a numeric column (int or bigint). This column will probably be your PK column as well.
Set its DF_FIELD_IS_IDENTITY attribute to true.
Make sure that your DD class does not mark this column as an auto-increment column. For example, if you converted a standard (non-recnum) Customer table’s Customer.Customer_Number to identity, you would also need to clear the DD’s auto-increment setting.

After a new record is saved, the new identity value will be set in your file buffer.

Recnum tables always assign recnum as the identity column, and this cannot be changed.

New Attributes: DF_FIELD_IS_IDENTITY

GUID Columns

A GUID or UUID value is a randomly assigned value that is (almost) guaranteed to be universally unique. Typically, it looks something like this:

60FCEA2E-CFFA-4788-AB32-6B81F37D3FFA

In SQL, this type is called a uniqueidentifier. Normally, this value is assigned automatically, either by the client (i.e., your code) or on the server. You can create as many GUID columns in a table as you wish. When used, this is often used as your PK column value.

To create a GUID PK whose value is client-assigned, do the following:

Create a GUID column (uniqueidentifier). This column will probably be your PK column as well.
Add the following code to your DD Class to assign the GUID during creation:

Use WinUUID.pkg // add to top of dd Class
:
// augment Creating as follows
Procedure Creating
    Forward Send Creating
    Move (RandomHexUUID()) to myTable.myUUID // whatever your table column name is
End_procedure

You can also have the server do this assignment for you. You do this by setting the field’s DF_FIELD_DEFAULT_VALUE to newid() or newsequentialid() as explained below:

Create a GUID column (uniqueidentifier). This column will probably be your PK column as well.
Set the DF_FIELD_DEFAULT_VALUE to [newid()] or [newsequentialid()].

Note that these expressions are input with surrounding brackets [ ]. You must do this. It tells the driver that the value enclosed is not a string literal. After entering the brackets, they will not appear in the Properties Panel, but the server will be properly updated.

After a new record is saved, the new GUID value will be set in your file buffer.

The use of newid() vs. newsequentialid() in MSSQL is technical. When GUIDs are used as the primary clustered index, the random assignment of GUIDs with newid() does not result in a well-balanced tree. Using newsequentialid() creates GUIDs that are better-balanced.

Conversion Wizard

The conversion wizard in Database Builder now has a new option that allows it to detect and assign primary keys. The "Recnum or Standard table" page now has two checkboxes (Define PKs and Convert to Standard). When using the PK option, the back-end should end up with a PK defined and a clustered index. This should be the same with standard and recnum conversions. When you do standard conversions, the recnum should be dropped, and the file primary index attribute should be set as well.

This should allow one to convert tables directly to Azure format with or without recnum. If you've got system tables, you have one extra step. You must add an index to your system table. You can actually add that to the embedded table before the conversion. Just pick a column that rarely or never changes. This is pretty much what anyone needs to do when connecting to Azure with no indexes (i.e., create a dummy index).

In addition, the following minor changes were made to the conversion wizard:

Fixed problem on the same page where the combo items in the “Primary Index” grid column could not be selected and dropped down.
Fixed bug where not being able to recreate indexes after a conversion/copy would result in the source table getting deleted instead of the destination.
Added ability to determine if a converted table that encountered errors copying records to it should be removed or not.

Previously, copy errors were reported, but the new table would be created (with the missing records). While this may be desirable in some cases (e.g., big table with just a few missing records you can add manually), in many other cases this was not desirable – particularly for first-time use. If a developer wants to attempt to make the conversion again, they need to drop the newly created table and reconnect to the prior source table using a filelist editor. This creates confusion. We feel that most of the time, any type of conversion failure should result in no changes (old table still in place). This is now the new default.

We've added a checkbox option to the “Define Conversion Options” tab page titled “Keep new table even if some records cannot be copied.” When false (the default), converted tables with copy errors will not create a new table (actually, it will drop that new table) and it will leave the old unconverted table as is. In addition, the last tab page and the error log will clearly state if a table was created or not.

Connection Wizard

The connection wizard in the Studio and in Database Builder will properly connect to tables using Primary Keys, clustered indexes, identity columns, and GUID columns.

We’ve changed the connect to table logic so it doesn't create a different logical name each time you reconnect to the same table. For example, if you connect to a table and then remove the connection (without deleting the .int or .fd file), you will get a new name when you connect again. For testing purposes, this gets pretty annoying and it was mentioned in the forums as being our random table connect naming scheme. Now we just check to see if the logical name we are trying to use already exists in the filelist. This means that .int and .fd files may now get overridden, but we consider that a good thing.

SQL Azure

The drivers now support SQL Azure databases. SQL Azure requires each table to have a clustered index. Therefore, you must convert all of your tables so they have a clustered index. If you are converting from an embedded database, the conversion wizard will assist you with this.

In SQL Azure, even system tables must have a clustered index. To use system tables on SQL Azure, a clustered index should be defined on a column in the system table. The column can be any column already in the table, or a new column can be added. The only purpose of adding an index is to meet the SQL Azure requirement of having a clustered index on any table. It is not necessary to make program changes. If you are converting from the embedded database, you may wish to add the system table index to your embedded database before running the conversion.

Preserve Triggers and Foreign Keys on Restructure

The drivers have been changed so that triggers and foreign keys are maintained during a restructure. Foreign Keys cannot currently be set directly within the Studio, but definitions made using a native tool are now maintained.

Change in Client Cache Timeout Default

The client cache timeout default has always been set to a small value (10 milliseconds). Depending on the client operation and the speed of the network, this timeout may be too short. While this can be changed, most developers never do this. We have increased the default to 100 milliseconds, which should improve performance in some cases.

Improved Large Data Handling

This is actually a runtime change, but you will mostly see the impact when working with large SQL columns. You can now use UChar arrays to get and set buffer values (Get_Field_Value and Set_Field_Value), resulting in simpler code and more efficient memory usage. This is described elsewhere.