MS SQL Server and BDE Setup
by Jamie A. Grant, AV-Base Systems, Inc.

RELATIONAL databases are fast, powerful, reliable…and can seem like a lot of work the first time in. The good news is that dBASE works very well with relational databases of all kinds, and there are increasing numbers of dBASE developers that use MS SQL Server in particular. The software I help develop at my company has successfully progressed from dBASE-IV through dBASE 5.x, Visual dBASE, dB2K and dBASE Plus. Our database has progressed from DBF Level 4 through to DBF Level 7, MS SQL Server 7 and MS SQL Server 2000.

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.

BDE BLOB Settings: Setup

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.

Primary, Foreign and Unique Key violation errors do not show up in dBASE. The error that should appear is lost somewhere along the route back from the SQL Server. The record is not saved, but dBASE is permitted to continue as if the `save()` succeeded. This problem also applies to any other kind of error that could be triggered by the SQL Server, such as required fields (that do not accept nulls) being left empty.

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.

Other Recommendations

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.