Techniques for using Visual DataFlex with SQL Databases

Techniques for using Visual DataFlex with SQL Databases Stephen W. Meeley

The evolution of connectivity • dBase? You gotta be kidding me. First we’ll export the data to an ASCII file and we’ll read it into a REAL multi-user database…

The evolution of connectivity • Btrieve is client/server with a transactional interface that fits like a glove. Let’s use it when we need to…

The evolution of connectivity • Looks like Microsoft, IBM and Oracle aren’t just going to go away and leave us alone. We can still get the sale if we convert our “DataFlex” data and…

The evolution of connectivity • Hmmm. “Our” data is working out pretty darn well in an SQL database, maybe now we could…

The evolution of connectivity • We can get more return out of our investment in that data if we use Visual DataFlex to…

Project Goals • Make it possible to connect to more tables without needing to change those tables. • You can have a “native” connection to any table that has a unique, single-column, numeric identifier (mapped to recnum) • You can connect to any other table, but not as easily (using Embedded SQL)

The why’s of improving connectivity • Native connections mean that tables are fully supported by the Application Framework • Developers are better served by “native” connections • Both the product and the data are much easier to work with • Increasing opportunities for existing developers • More natural integration with the product • We can attract new users and train them more easily if they can feel comfortable with their “native” data

The how’s of improving connectivity • Examined SQL data and found issues with… • Terminology • Specification mismatches • Data type support • Primary keys (unique identifiers) • Relationships (data structures) • Indexes (non-unique) • Some issues could be lived with or dealt with in code, others could not • Incremental development model says to focus on those “others” first…

Living in an SQL world • When we connect to the SQL world we no longer control the database • As the database is more central to the business, changes to the structure are harder to get and must be very tightly coordinated • Thank God that DBAs are rarely armed…

Visual DataFlex 11 and connectivity • Use more standard terminology (at least for the easy stuff) • Make the tools more intuitive to use • More wizards • Less DataFlex-centric • Support non-numeric, multi-column relationships • Support columns larger than 16k

What’s left for you to deal with… • Specification mismatches • Database structures • Data types • Unique indexes

Specification mismatches • Table names limited to 8 characters and uppercased • It’ll all work automatically, but you may want to change the default names for some tables to better suite use • Column names • Reserved words • Spaces replaced with underscores • Named indexes • Use index numbers assigned by connection wizard • Nulls • Allowing nulls in columns that participate in indexes will negatively impact performance • See if DBA will consider dummy zero date

Database structures • None of these are “new” because of SQL, you just run into these a lot… • Recursive relationships • One-to-one relationships • Conditional relationships

How to deal with them… • Use alias tables • Described in Data Dictionary Book • Define the relationships in DDO structures, not table definition • Augment relate_main_file and attach_main_file

Data types • Datetime mapped to date • Use DF_FIELD_TIME attribute to get and set time portion • Numeric limited to 14.8 positions • No good solution for this • Use ESQL and manipulate as reals • Most other unsupported types are mapped to strings • CLOB and BLOBs • If “reasonable” to assign a static maximum size, set in .int with DF_FIELD_LENGTH

Indexes in an SQL world • Indexes more for tuning than part of the data structure • Non-unique indexes are quite common • In an Order table you’ll see them indexed by Customer, but that’s it • The Application Framework is really built around unique indexes

What happens with non-unique indexes • It may look like it’s working, but it isn’t really… • In reporting (unidirectional access) the sequence may change from instance to instance • In data entry and lookup (bi-directional access) the behavior is undefined (i.e. sometimes it’s correct and sometimes it’s “goofy”) • The whole system really requires that the result of all find next and find previous operatons be consistent

What are your options • Where possible, use unique indexes • Ask if non-unique indexes can be changed • This is not as big a deal as asking for a structure change • Add “phantom” columns to indexes in the .int file • Forces the server to give correct sequence • Will be less efficient, but only sometimes • Gives you the consistency you need

How to deal with database modifications • Use native tools to create and modify tables • Database Builder is used to acquire tables into filelist, create alias tables and create data dictionaries • Make “structure” adjustments in .int files • Use Database Builder in “safe” mode (read-only)

Validations • Many validations (and other business rules) will be defined at the server • Don’t be afraid to “duplicate” validations in Data Dictionaries • Usually means that the DD with catch the failure of the validation first • There could be advantages to this • Just as the server rules protect against unplanned changes driven by the application, DDO rules protect against unplanned changes driven by the database

Where are we going next? • Essentially, the list of things you are left to deal with is our list of continued improvements

Techniques for using Visual DataFlex with SQL Databases