This article is intended to help dBASE developers who are considering using a new or existing MS SQL Server database, or for developers that could use a few tips for MS SQL Server based on our experiences over the years.  Our company still has dBASE 5.7 apps using Level 4 databases, dB2K apps using Level 7 databases, dB2K apps using SQL Server databases and Java apps using those same SQL Server databases.  We have been using MS SQL Server since the year 2000.

There are several tips towards the end of this article about setting up and designing MS SQL Server databases, but there is a crucial setup decision to made after that stage as well.  There are two main ways to setup a BDE Alias to access MS SQL Server.  One is to use a native BDE-SQLServer driver, and the other is to use an ODBC-SQLServer driver.  This article will detail how to set up both kinds of BDE Aliases, and will then discuss some of the differences between the two.

Short Story: Use the BDE’s native SQLServer Driver.

Long Story: Pull up a chair and have a sit, won’t you?
 

BDE Setup The SQL Server Name MS SQL Server Ports BDE Alias with Native SQL Server Driver: Setup BDE Alias with ODBC Driver: Setup BDE BLOB Settings: Setup Native Driver vs. ODBC Driver ODBC SQL Server Driver: Lost Errors ODBC SQL Server Driver: Stored Procedure Output Parameters and Return Values Short Story Recap General MS SQL Server Tips Should the MS SQL Server be case-sensitive? How should database Owners, Users and Login IDs be used? Recommendations for Field Data Types Potential Problems with TEXT Fields Other Recommendations Conclusion

BDE Setup

SQL Server Name

The SQL Server ID that either kind of BDE Alias uses will be either a local network name or an IP Address.

If the database server is on the internal network, it may be something like SQLSERVER or SERVER01.  There may be the option of using a VPN (Virtual Private Network) connection to a remote network.  Once the VPN connection is open the local network name can be used in the BDE or ODBC settings.

If the SQL Server is being accessed over the internet, a static IP address may be needed that can directly access that server.  Firewalls may be set up to block external access, in which case access through the firewall may be required for the source IP Address.

MS SQL Server Ports

MS SQL Server defaults to permit access via Port 1433 or 1450, but it may be set to another port.  This is the port that MS Query Analyzer uses, and this will be the port that the BDE uses.  This port must be open to get a database connection.  This applies to MS SQL Servers on both intranets and the internet.  MS Enterprise Manager may be set to use another port, like 1035, by default.

SQL Server Driver: Setup

A BDE-ODBC connection may have trouble finding the correct port number automatically in some cases.  An option may be to use the SQL Server Client/Network Utility provided with the SQL Server tools.  Create an Alias that uses TCP/IP (not Named Pipes) and exact port can be specified that way.  Use the new SQL Server Alias as the Server Name when the ODBC DSN is created.

BDE Alias with Native SQL Server Driver: Setup

1. Create a new BDE Alias in the BDE Administrator.  Set the Database Driver Name to MSSQL.
2. Set the Server Name of the alias as described above.
3. Set the Database Name.  The database name can be the same as it would appear in MS Query Analyzer.
4. The User Name isn’t necessary, but I recommend filling it in as a rule of thumb.

   

BDE Alias with ODBC Driver: Setup

1. Open the Data Sources or ODBC Administrator option from the Control Panel.  It’s located under Administrative Tools in the Control Panel for Windows XP.  If a BDE Alias that uses the ODBC SQL Server driver already exists, there is an “ODBC Administrator” option in the right-click menu for that Alias in the BDE Administrator.


2. Create a System DSN.  As with the native BDE Alias, the local network name or an IP Address for the Server Name can be used.




3. The MS SQL Server administrator should have created a Login User on the SQL Server.  For my database I have a login user named “sql” that has full administrator/ownership privileges.  It’s actually the login ID for the default “dbo” user on the MS SQL Server.




4. The rest of the DSN options can be left at their default.

Once the DSN Alias has been created and tested, open the BDE Administrator.  Set the Database Driver Name to “SQL Server.”  If it is not available in the list of drivers, then some of the MS SQL Server tools like Enterprise Manager or Query Analyzer may need to be installed.  The latest version of MDAC would also contain the latest version of this driver.  As of August 20, 2003 this was MDAC Version 2.8, I believe.

One caution is to make sure that the “SQL Server” ODBC driver that is used is not too old.  There are a few bugs that Microsoft has confirmed with older versions of this driver from around the year 2000.  To check the version of the ODBC SQL Server driver, open the ODBC Administrator and go to the Drivers tab.  There should be a driver named “SQL Server” in that list, and the date in the far right of that section should be from the year 2001 or later.


5. In the BDE Administrator for the new “SQL Server” Alias, set the ODBC DSN.  Use the DSN Alias that was just created.


6. Set the Database Name.  Use the database name as it would appear in MS Query Analyzer.


7. The User Name isn’t necessary, but I recommend filling it in as a rule of thumb.

There are potential problems with the default BLOB-related properties in either kind of BDE Alias.  In particular, Blob Size and Blobs to Cache may need to be changed.  There are several possible solutions, one of which is to increase these values past their default settings.  For more information, see the General SQL Server Tips below concerning the Text Field Datatype.

Native Driver vs. ODBC Driver

In order to use dBASE as a front-end for MS SQL Server, we have the choice between the BDE’s native driver or an ODBC’s driver. The BDE’s native driver has two advantages.  The first is that the ODBC is actually a second layer or tier in the database access architecture.  Information and SQL statements must pass through both the BDE and the ODBC, which may decrease the efficiency a bit (although this difference wasn’t measurable in my personal tests).  It may also increase the complexity of the database access and the chance of errors.

Which brings us to the second reason, which is that there are two known bugs in dBASE if the ODBC driver is used.  Whether these two bugs are part of dBASE, the BDE or the ODBC Driver is a question best left to the ages and the inner workings of dBASE Inc.  I will at least specify the two potential problems to watch out for if the ODBC SQL Server driver is used.

ODBC SQL Server Driver: Lost Errors

The dBASE Query object ignores SQL Server errors under the following circumstances.

• During a beginAppend or beginEdit.
• If the rowset has a TEXT field in it.
• If the TEXT field has something other than null assigned to it.
• If any kind of SQL Server error occurs when rowset.save() is called.

There is a work-around, which is to leave the TEXT field out of the rowset and save it separately after the initial rowset.save() has been completed.  This isn’t possible if the TEXT field needs to be a required field, but it usually works.  It allows the initial rowset.save() to properly return the SQL Server errors it needs to.

This problem exists only if the ODBC SQL Server driver is being used.  The native MS SQL driver properly throws the errors in this situation.

ODBC SQL Server Driver: Stored Procedure Output Parameters and Return Values

This driver has a second problem that does not exist for the native MS SQL driver.  The StoredProc class in dBASE can use output parameters and return values for stored procedures on the SQL Server.  While the StoredProc class works properly in most cases with an ODBC SQL Server driver, it always receives the following error if the stored procedure has output or return values defined: [Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

There is a work-around for this problem as well, which is to return a ‘fake’ rowset, with each field in the rowset corresponding to an output or return value that is needed.  The Stored Procedures article in this issue of dBulletin contains further detail and an example of this work-around solution.

As with the previous section, I describe this as a potential problem because it may be a setup issue and neither of these issues may exist for other dBASE developers.  If anyone finds a solution to either of the above problems or cannot duplicate these problems, I would certainly like to hear about that in the dBASE newsgroups.

Short Story Recap

The native MS SQL driver and the ODBC driver both work properly in every other respect that I know.  I have used the ODBC driver for several years now with only the above two problems, but I would advise dBASE developers to choose the native driver if possible.  There is, of course, a bit more advise that I would like to pass on.  (Why do I feel like an old man sitting on a mountain peak?)  The BDE connection is an important decision, but there are several other setup and design decisions on a MS SQL Server that should be considered carefully.

General MS SQL Server Tips

Should the MS SQL Server be case-sensitive?

When installing MS SQL Server for the first time, it defaults to be case-insensitive.  I recommend making the MS SQL Server case-sensitive, because a case-sensitive database can be used on a case-insensitive server but the opposite may present compatibility difficulties for the dBASE software.  This may prove crucial if the database is intended to be used at other companies, many of which will have their own unique SQL Server settings.

With that in mind, I recommend that mixed-case names not be used for tables, field, views and stored procedures.  Choose a standard of upper, proper or lower case so that the related dBASE code can be written in a standard fashion and new names do not cause conflicts because of different SQL Server settings.

How should database Owners, Users and Login IDs be used?

Make sure that all tables, views and stored procedures are owned by one user.  This will make it easier when it comes to performing structure updates or all-purpose table scans later on.  I tend to use the default dbo user as owner for everything.  I make sure that the dbo user for a particular database is associated to the sql Login ID for the SQL Server, instead of the default sa Login ID.

With that in mind, pay close attention to how the initial SQL Server users/logins are setup for a database.  This user/login will be the way dBASE applications open a database connection.  If other customers of the dBASE application have different SQL Server installations, importing or exporting data can be very tricky if they have Login IDs that are unique to their own network and do not exist on the target SQL Server already.

Recommendations for Field Data Types

I recommend not using the FLOAT data type for MS SQL Server.  It actually stores an approximate number, so it may be surprising to find that a simple 2.0 is instead stored as 1.99999999.  Try to standardize all numeric fields to be either INTEGER or NUMERIC(19,4).

Use VARCHAR fields instead of CHAR fields whenever possible.  CHAR fields will append spaces to the end, whereas VARCHAR fields will only store the exact text that was saved.  However, CHAR fields are more efficient if the text will always be of a certain length, like a CHAR(1) column that cannot be null.

I recommend that BIT fields not allow nulls for MS SQL Server.  The purpose of a BIT field is retain two possible states, 0 or 1.  If nulls are permitted, it can actually have three states, 0 or 1 or null.  If a BIT field does not allow nulls, be sure to create a default constraint that sets the field to zero if no value is specified.

SQL Server uses a DATETIME data type for most dates, even if the time is not needed.  This data type stores the date up to the milliseconds, but milliseconds tend to be very difficult to use in dBASE.  Be aware that this field will create an error if it is used with a date earlier than Jan. 1, 1753.  To display the date portion only for grids or fields within dBASE, set the field.beforeGetValue() function like this: {||DTtoD(this.value)}

Potential Problems with TEXT Fields

Be cautious with TEXT fields, which are the equivalent of BLOB/Memo fields in dbf tables and contain long notes.  These should be used instead of a field data type like varchar(5000) but dBASE and the BDE may have difficulty with the default settings.  Specifically, if a query.rowset in dBASE includes a text field and has too many records, there may be a corrupt blob error or an invalid blob handle error.  This error is related to dBASE only and does not mean that the SQL Server has a corrupt field problem.

There are three potential solutions:

• The newsgroups have various discussions about changing the default BDE settings for a SQL Server Alias to permit a larger memo size in memory.  Search for the keywords “Blob Size” and “Blobs to Cache.”  This would be the preferred option for dBASE applications, although I have not found find it to be perfectly dependable.  I mostly advise caution and research when using the default BDE settings and there is a rowset with more than a few thousand records.
  

• An SQL statement can be used that converts a TEXT field into a VARCHAR field on the fly, avoiding these potential errors.  The SQL syntax can be like this:

	SELECT CONVERT(VARCHAR(50), DESCR) AS DESCRIPTION FROM MYTABLE

This works well for most cases because it permits rowsets of any size and requires only a small change the SQL statement.  In my experience, however, I have found that a rowset with many records like this may start to create Process Blocks on the SQL Server, freezing or creating errors in any applications that try and use the blocked table.

• That leaves us with a third option, which is to create a SHORTDESCR field in the table.  It can be similar to a VARCHAR(50) field, and it will store the first 50 characters of the actual TEXT field every time the note is changed and saved.  (This can be done with dBase code or with a trigger on the SQL Server, but that's an article for another time.)  It may not seem ideal, but it does provide a fix for the Blob error and a performance boost since VARCHARS can be extracted and used more quickly than TEXT fields.

For new users of SQL Server and fully relational databases, I recommend that database structures be created to conform to at least Third Normal Form (Normalization concept).  I recommend that Primary Keys, Foreign Keys, Unique Keys and Constraints be used wherever possible.  This has been the single biggest advantage of using MS SQL Server that I have found, helping ensure excellent data integrity.  These two suggestions are entire articles unto themselves, so I’ll leave you to investigate them.

Conclusion

Ah, the joys of SQL Server and relational databases.  Any dBASE developers that have created and used dBASE databases of any table level should be able to work through using relational databases.  And based on my experience and my company’s history, once you’ve had a few tastes of the advantages of a SQL Server database you’ll soon want to try the entire main course.  It’s a satisfying meal, I assure you.

Jamie A. Grant has been a dBASE programmer with AV-Base Systems, Inc. since 1999.  AV-Base Systems has been providing Aviation Maintenance Management Software to the global aviation industry for the past twenty years.  Thanks to AV-Base Systems for their continued support of the dBASE community and their key contribution to this article.