Phone800.366.3472 SupportGet Support DocumentationDocumentation Resource CenterResource Center
search
close
Open Menu

Synergex Blog


It’s a good time to clean up your old Synergy DBMS data

By William Hawkins, Posted on November 11, 2009 at 4:27 pm

Avatar

Synergy/DE is a pretty forgiving environment when it comes to dealing with fields containing unexpected data. For example, you can have a d field that is either all spaces, all numbers, or some combination, and Synergy/DE will treat the spaces as zeros. (For those of you old enough to remember DBL version 2, back then you could even increment a decimal field that was all spaces, resulting in a field that was all spaces except for the last character which was a !, and DBL would still see the decimal field as a numeric 1. When that occurred, it made for some interesting debugging!)

Now that the world of Synergy/DE for .NET is imminent (a beta version is available for those who are interested), the issue of data interoperability and ensuring that C#/ VB.NET/Framework-language-of-choice can work with Synergy data is also moving to the forefront. For those of you who have been using xfODBC and/or SQL Connection, some of these issues may be familiar, but a lot of developers will foray into this arena for the first time with Synergy/DE for .NET. This is especially true for those of you using xfNetLink .NET edition and looking to bypass the xfNetLink layer and use your code directly from Synergy/DE for .NET. (xfNetLink shelters you from these issues, as it can coerce inappropriate data into a usable native form.)

Synergy/DE basically has three “native” data types: integer, alpha, and decimal. Here are some of the issues that you’ll need to think about for each:

· Synergy integer fields (not to be confused with System.Integer fields) – No issue here; you really cannot store inappropriate data in a Synergy integer field. All you really need to watch for is storing an integer field that is all spaces. Of course, this is still a valid integer; it’s just not the zero value that you may have expected.

· Synergy alpha fields – Non-printable characters (outside the 7-bit characters space thru tilde) really need to be processed as a byte array and not thought of as a string (or alpha) field. But for most situations, alpha fields are relatively problem-free.

· Synergy decimal fields – Here’s your main problem area. I think of Synergy decimals as a special form of Synergy alphas that only contain numeric data and that you can perform math functions on. So, you need to ensure that decimal fields only contain the characters 0 thru 9, with the last character optionally being p thru y (to deal with trailing over-punch data). It’s not uncommon for decimal fields to be all spaces or to have spaces inside the field instead of zeros. It really depends on your code and the last time that you wrote the data to the file. It’s not all that uncommon to have data that’s over 10 years old and hasn’t ever been updated.

Another common issue is that Synergy/DE allows a d8 date to have a value of 00000000. In most other languages, this is an invalid date. If you don’t have a date, other languages use null, not zero, to indicate this. When you move your d8 date field to a datetime field in .NET, you’ll need some conditional logic to cope with this. Your code may also deal with a date field that contains a value such as 20090230, and it will just ignore the fact that this a bad date and display 02/30/2009 (U.S. format) or 30/02/2009 (rest of the world). Try to do this in other languages and you could be in for an unpleasant surprise.

Hopefully, most of the reasons for having inappropriate data in your Synergy DBMS files have been addressed over time. However, you may still have residual data out there that is technically “bad” but is hidden due to the forgiving nature of Synergy/DE. In our experience, adding logic to the intermediate layer to check and fix the data as it’s being pushed to non-Synergy/DE systems can add significant overhead to the application. It’s much more efficient to fix the data (hopefully as a one-off exercise) and modify your application to stop adding bad data to the Synergy DBMS database. For example, commands such as the INIT statement can help ensure that decimals and integers contain correctly initialized data. There are probably a few of you out there who have cleared a Synergy record and then realized that an ISAM key contained a number that got stored as a space and not a zero, so that the key contained records that were “out of order.”

To help, we have added a utility to CodeExchange (fixdata.dbl) that will allow you to apply a Synergy Repository structure definition to a Synergy DBMS file, forcing fields to contain valid characters and (for example) date fields to contain valid dates. We will be extending the utility to include other features, such as validation logic based on Synergy Repository definitions, so that it will be able to detect when a field contains an “out of range” value. If you already expose or plan on exposing your data to .NET, xfODBC, or SQL Connection, I encourage you to download and run this utility. If you have any comments or suggestions on this utility, please email me at William.Hawkins@Synergex.com.


Don't miss a post!

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Recent Posts Categories Tag Cloud Archives