Stephen Mull's Guide To Converting To MS-SQL

by Stephen Mull

Published 1999-11-02    Printer-friendly version

This document relates my experiences and findings while performing a conversion of a large C5b Legacy app from TPS to Microsoft SQL Server. This document is based on Clarion 5b Enterprise Edition and MS-SQL Version 7.0. Using different versions of Clarion or MS-SQL Server may require different approaches, but information relating to file structures should still apply. I do not cover the details associated with MS-SQL 7.0 setup in this document, except where it relates to my Clarion application.

I have been using Clarion for Windows since the first release, and feel it is the finest RAD tool available anywhere! This was my first application involving the usage of a SQL based backend. While the initial learning curve has taken some time, I would certainly recommend the usage of SQL whenever possible.

My initial learning experiences began with reading the Clarion documentation, searching the newsgroups, and reading two really good articles relating to using Clarion with SQL. One is Rick Hoffmann's "MS-SQL Tips and Tricks and C5", and the other is a summary of the presentation by Scott Ferrett at Euro Devcon '99 in Amsterdam. While all of the available information was indeed helpful, there was no single source covering the details of my specific needs. I felt my experiences might be of some assistance to other developers, thus this article.

It is important to understand that you, the developer, must use your best judgment regarding the usage of this information. While I feel I am relating accurate information based on my experiences, your situation may be different, thus requiring different approaches. On we go!

MS-SQL Server 7.0, MS-SQL Server - Desktop Edition 7.0, MSDE, ODBC, MS-SQL Accelerator
What Works And What To Use

What works and what to use? There are a variety of choices. After investigating the options available, I chose to use the Clarion MS-SQL Accelerator instead of ODBC. I also decided that MS-SQL Server 7.0 was the best choice. I would not recommend you use prior versions as Version 7.0 is far superior and free from headaches for the most part. The finished application will also work fine with MS-SQL Server 7.0 Desktop Edition.

Other approaches including using the MSDE or ODBC probably work fine, but my choices seemed to be the most reliable and straightforward approach for my project. Whatever choices you make, if you use Windows 95 as your platform you will need to install the DCOM updates first, and then the new MS-SQL ODBC driver (3.7x). The new ODBC driver is recommended with MS-SQL 7.0. These are all included with the MS-SQL 7.0 CD.

Changes To The Dictionary

The first thing to do is create a backup of everything! I suggest you start with a new directory for the SQL app, and copy your existing app, dct, etc. to the new directory. You do not need to move the existing TPS tables into the new directory. You may use them later to copy the data to the SQL server, should you wish to do so. This will be discussed later in this article.

The first thing I had to do was make changes to the existing field definitions. Refer to the following table, which worked perfectly for me, excluding dates (to be discussed shortly).

The following table of field type equates is excerpted from Rick Hoffman's paper. Some changes and additions have been made to the original content.

SQL Field Type

Clarion Field Type

CHAR(20)

STRING(20)

VARCHAR(20)

CSTRING(21)

INT

LONG

BIT

BYTE

DATETIME

STRING(8) 
GROUP(Over String(8))
  DATE ! you modify these fields
  TIME ! you modify these fields
END

OR Date OR Time

SMALLDATETIME

STRING(8)
GROUP(Over String(8))
  DATE ! you modify these fields
  TIME ! you modify these fields
END

DECIMAL(18,4)

DECIMAL(18,4)

FLOAT

REAL

IMAGE

STRING(2048)

MONEY

DECIMAL(19,4)

NUMERIC

DECIMAL(18,4)

REAL

SREAL

SMALLINT

SHORT

SMALLMONEY

DECIMAL(10,4)

SYSNAME

CSTRING(31)

TEXT

STRING(2048)

TIMESTAMP

STRING(8)

TINYINT

BYTE

VARBINARY

STRING(255)

STRING

MEMO *see below

Global And Local Data Definitions

Don't forget to update all of your global and local definitions as well - they are easily overlooked! You will also need to add something along the lines of GLO:Owner for the owner name of each table (discussed below).

STRING To CSTRING

Look at the following string comparison: 'Dog' ~= 'Dog '. With MS-SQL 7 tables, the trailing spaces become an issue when comparing field values. In most cases, this is only an issue of importance with key components as they are used with relational links and filters. I still recommend you change all of them accordingly. If you use CSTRINGs instead of STRINGs (highly recommended), then Clarion treats the trailing spaces in the same manner as the SQL 7 system. I encountered no problems doing this. Just remember that STRING(20) = CSTRING(21) in Clarion, so add the extra length in your field definitions or you may end up with truncated data!

MEMO To STRING

The MS-SQL driver does not support MEMO. Instead, you may create your memo fields as a very large string. As your SQL app is 32bit, you do not have a 64K record limit to be concerned with. After you make this change, you will find that you will no longer be able to display and update the "memo" contents in the form you used with MEMO. What you need to do to correct this is go to the DCT, go to that fields display properties tab and change the control type from Entry to Text. Then go to the form, repopulate the field onto the form, and it will work properly once again.

DATE - TIME To DATETIME

My DCT contains fields which store both date and time as Date and Time. I left these "as is", and upon sync with SQL 7.0 server, they were automatically converted to DATETIME and continued to function perfectly. Do not confuse DATETIME with DATETIMESTAMP on the SQL 7 server; they are different. I store my date values from within the app. This was appropriate for my application, but leaves the possibility of the data being incorrect if the workstation's date and time are not correct. Having SQL 7 insert date values is also possible, and will insure the correct date and time are used. If you do this, the SQL 7 Server will try to populate a DATETIME field with the date and time. It is possible to get around this, but I avoided it completely.

As I mentioned above, I store my dates and times separately. Clarion supports the DATETIME via a Group, as illustrated in the chart above, but reporting and filtering with it is a hassle I chose to avoid. Avoid storing various date and time data as a LONG. If you store as a DATETIME, your data will be usable with 3^rd party products such as Crystal Reports, Access, Excel, etc.

Field Names And Definitions

I left my field names and key names the same, and all worked perfectly. There is a maximum field name length on MS-SQL tables, so be aware if you have excessively long field or key names. Regarding the use of external names, try to maintain identical field names. If for any reason the Clarion dictionary and the MS-SQL field names differ, you should set the External Name in the Clarion dictionary to the MS-SQL field name. Since you will sync from the DCT to the SQL 7 server, you should not encounter any issues in this area. Remember to update all field definitions to match the above list. One note about initial values and case: initial values will be set correctly using legacy code, even using recursive entries, but will not be set correctly using ABC, except on an initial insertAs far as case goes, it works fine, but keep in mind how this will relate to keys and NOCASE support, as mentioned below.

Of Keys And Indexes

Make sure all tables have a unique key. This is very important! Also realize that unlike Topspeed files, there is not a hidden record number in SQL 7 tables. You must inform the driver how to uniquely identify a record. To accomplish this, at least one unique key must be defined for each and every MS-SQL table. Do not use indexes with the MS-SQL driver, as they will not work properly.

Be sure to set all keys to either case sensitive or case insensitive. You must not attempt to use a mixture of both with your keys. The MS-SQL server's performance will suffer greatly if you do so. I learned this first hand! You must also not use GROUP fields as part of your keys. To restate this, fields within a group may be used as part of a key, but do not use any GROUP field in your key. I did not have any keys of this type, so I encountered no problems.

For files that required a unique record number or ID, I created a field called RecordID (@s18) for the file, and supplied its value from the app rather than the server. You may use the server to auto-populate this with an incremented number. To do this with MS-SQL, use an appropriate data type, most likely integer, and make it part of a unique key. Then mark the key as auto incrementing, exactly like you would in a Topspeed file. NOTE: Topspeed and others claim this works, but I was never successful in getting this to work properly. My method was to use Date() & Clock() on INSERT. Please don't call me crazy; it works fine with over 100 users adding records every moment of the day, there has never been a duplication error, and it requires no interaction from the server!

Referential Integrity

MS-SQL 7 does not have a cascade delete declarative referential integrity feature. I understand this is planned for the next version of MS-SQL Server. Thus, SQL 7 will not enforce RI except when you ADD a record. To use the server to update and delete child records, you will have to create triggers and/or stored procedures to handle the process. I found this to be quite a pain with SQL 7. With all of this given, I would recommend you do not change RI to Server based! I left the dictionary "as-is" here, and all works perfectly! Additionally, this allows easier data manipulation via 3^rd party tools on the SQL 7 Server, but remember you can just as easily mess up your data using 3^rd party tools to manipulate data, so be careful!

File Relationships

None of the documentation has properly addressed file relationships, in my opinion. I initially used the sync tool in Clarion, created a SQL script, executed on my new database on the SQL 7 server, and voila, everything was created properly including relationships. I encountered all types of erratic problems with my app with the relationships defined both in my DCT and on the SQL 7 Server. Even with newsgroup and Topspeed tech support, all of these issues could not be resolved. Others may argue my final solution, but it is working perfectly. What I did was to leave the relationships intact in my Clarion DCT, and did not create the relationships on the MS-SQL server. Everything works perfectly, combined with leaving the referential integrity as stated above. Additionally, this allows easier data manipulation via 3^rd party tools on the SQL 7 Server, with the same caveat mentioned above.

Here is a helpful hint: before you sync you DCT with the SQL Server, save your DCT, then Save As a new DCT name, then Remove file relationships from the new DCT, save, then use the Synchronizer to create the SQL script (covered below). Be sure to use your original DCT with your app, and not this new one without the relationships!

MS-SQL Driver Properties and Settings

Here are a few tips I learned from Rick Hoffmann's paper and the newsgroups. See the Clarion docs for more information.

/SAVESTOREDPROC = FALSE

NOTE: sometimes the driver setting works backwards, so try both. You will notice the performance difference! What you want to do is not have the server save the temporary stored procedures that your app will create on the SQL 7 server.

1. /TRUSTEDCONNECTION = your choice, based on your situation. I did not use NT security, instead opting for SQL Server security.
2. /LOGONSCREEN = your choice, based on your situation. See OWNER Attribute.
3. /GATHERATOPEN - Not used with MS-SQL Accelerator. This is for ODBC use only.

Driver Options

Change "Topspeed" to "MS-SQL Accelerator". Do this after all other changes are complete.

RECLAIM attribute

The MS-SQL Accelerator driver does not support this attribute.

Enable Field Binding Option

I enabled field binding in my DCT, and would recommend this to be enabled in most cases.

Enable File Creation Option

This does work properly with MS-SQL 7. I would still recommend the use of a SQL script instead.

OWNER Attribute

SQL 7 tables require an OWNER attribute. This indicates how to connect to the database where the tables are located. Use a variable whenever possible. If you have tables which already have an OWNER attribute, change them to something resembling !GLO:Owner. What do you do with this you might ask? See the Changes to the Application section below for information on how I handled this one!

Create a SQL Script Using The Synchronizer

To create a SQL Script you will need to first save your dictionary. Make sure you have completed all of the above actions before creating the SQL script, or you will have to do it again. You will probably not get it 100% right the first time, so don't worry! You will need your DCT file and an MS-SQL database. You get an MS-SQL database by either creating a new MS-SQL database or using an existing one. Make sure you have your MS-SQL server setup and running, and make sure you have installed the appropriate client software on your workstation. You may also use MS-SQL Server Desktop Edition if you do not have a separate server; it works fine.

I recommend you create a new database, using all defaults in Enterprise Manager. Also, save yourself some trouble and change the default sa database on the MS-SQL server login to your new database for the time being, or create a new login and give full rights to the new database to the new login.

Should you decide to follow my advice on not creating the file relationships on the SQL Server be sure to use a temporary DCT without the relationships before creating the tables, as mentioned above.

You run the Dictionary Synchronizer to create a SQL script, so first open your DCT, save, then run the Synchronizer. Select your other dictionary to be MS-SQL, and then select the database you want to create the SQL tables in. You will have to log in. Once you get to the Synchronizer screen you need to copy all the files to your SQL database. The easiest way to do this is to highlight the top line. Press the right mouse button and select add. Click OK, then click Finish when offered. You will be prompted for a script name and location, so answer accordingly. That's it! Run the script from Enterprise Manager's Query Analyzer, found on the tools menu of Enterprise Managers menu bar. Either load the script, or copy and paste from your script, select the database to run the script against in Query Manager and run it. You should be notified that the command(s) completed successfully in short time, and you're done! Now migrate your data if you wish.

How To Migrate Existing Data

I decided not to create a conversion program for my data dictionary. I used MS-Access and the Topspeed ODBC driver, along with the MS-SQL ODBC driver from Microsoft to migrate all data, using append queries. It is quick and easy! Your existing data might require some massaging before appending to your new SQL 7 tables, so you could import the data into new Access tables, perform your data manipulation, then append the data to the SQL 7 tables.

Views

Please note my initial app did not utilize views, but they are easy to use in your app. Assuming you know how to create a view, do so on the SQL server, give it a name like v.myfile. In your dictionary go to the Synchronizer and import the view(s). Make sure you create a primary key, because when you import views a key is not created. Make the key look identical to the primary key of the view's main table. Make sure the following attributes of the key are set:

• Require Unique Key = On
• PrimaryKey = On
• Case Sensitive = On
• Exclude Empty Keys = Off

You may now use your view in your app!

PROP:SQL And Stored Procedures

Please note my conversion did not involve using any stored procedures. It is my understanding that working with stored procedures and other cool advanced functionality available from the MS-SQL server is best utilized via the CCS Client Server SQL Template Sets, available from Andy Stapleton at Cowboy Computing Solutions. I intend to purchase and add to my app very soon!

Team Topspeed recommends that when possible you should write your own PROP:SQL statements to process data. Please note I did not do this for my initial conversion, and all works perfectly, so I may decide to leave everything as-is. The Process templates do work! The MS-SQL driver creates stored procedures in the TEMP DB for all SELECTs and for all inserts, deletes and changes. PROP:SQL eliminates the stored procs for INSERTS, DELETES and UPDATES.

The following are some performance and usage hints for working with PROP:SQLs, excerpted from Rick Hoffman's paper). Some changes and additions have been made to the original content.

Use performance hints such as NOLOCK, FASTFIRSTROW and INDEX = when using PROP:SQLs.

1. NOLOCK - Please read MS-SQL online documentation for description.
2. FASTFIRSTROW - Please read MSSQL online documentation for description.
3. INDEX - Sometimes MSSQL will not pick the correct Unique Constraint or primary key. Hinting will force MSSQL to use the specified PK or UC.
4. Don't use the RECORDS(TableName) on large tables to find the record count. Use a PROP:SQL with a SELECT Count(1) FROM TableName (NOLOCK). Since the select statement returns only one value you need to create a new file in the dictionary with a single field typed as a LONG.
5. For batch processes you may use BEGIN TRAN and COMMIT. Works like a charm, but monitor the transaction log for sizing (this is not a big issue with SQL 7).
6. NORESULTCALL - If the stored procedure is not going to return a result set then use the prefix NORESULTCALL. For example: MyTable{PROP:SQL} = 'NOTRESULTCALL SP_UpdateWhatEver (1234)'.
7. CALL - If the stored procedure is going to return a result set via a SELECT statement then use CALL. Prefix is used. For example: MyTable{PROP:SQL} = Call('SP_SecuritySelectMembers (''FL0021002'')').
8. The data returned via a SELECT within the stored procedure needs to match the structure of MyTable. C5 only supports returning information via a SELECT. There are two ways to return things from a stored procedure. One is via an embedded SELECT and the other is RETURN (or similar command). The MS-SQL driver does not support returning information via RETURN, only SELECT.

Special Notes on PROP:SQL

The following special notes are excerpted from Rick Hoffman's paper. Some changes and additions have been made to the original content.

When issuing a PROP:SQL, issue a BUFFER(TableName, 0) before the PROP:SQL. This will tell the Cursor Fetch to retrieve one record at a time. Example:

BUFFER(TableName, 0)
  TableName{PROP:SQL} = 'SELECT Field1, Field2, FROM TableName'
  NEXT(TableName)

You can also issue a BUFFER(TableName, 20) and then Push the PROP:SQL twice. This will tell the Cursor Fetch to retrieve 20 records at a time. Example:

BUFFER(TableName, 20)
  TableName{PROP:SQL} = 'SELECT Field1, Field2, FROM TableName'
  TableName{PROP:SQL} = 'SELECT Field1, Field2, FROM TableName'
  NEXT(TableName).

Issue a BUFFER(TableName, #) before the PROP:SQL.

When writing PROP:SQL statements its very easy to make syntactical errors. Team Topspeed recommended creating a debug procedure that's passed the SQL statement and displays it in a window. If you use a text field as the displayed field then you can cut and paste the SQL statement between your application and MS-SQL ISQLW or Query Analyzer. Example: SQLDebugWindow(TableName{PROP:SQL}).

Changes To The Application

In most cases very little change is required to an application for it to work with MS-SQL. Despite Topspeed claims that ABC is better than Legacy for SQL apps, I have found that Legacy offers excellent performance with MS-SQL 7.0. Additionally, due to MS-SQL's ability to self-tune, you will find performance will increase with each usage! I actually converted my app to ABC from Legacy, and have yet to eradicate all the minor bugs. I have decided to stay with Legacy for now.

As far as third party templates go, I use several third party template sets without issue. These templates include the following: CPCS Reporting Tools 5.1x, many templates from Sterling Data, some from Boxsoft Development, LSPack from Linder Software, Princen-IT Sendmail, and several other templates. I also use some custom templates that some very talented 3^rd party developers have written specifically for me, and yes, they all work fine with MS-SQL! Be sure to read all Clarion docs referring to using MS-SQL Accelerator and the SQL accelerators in general. Also read any sections on Browses, Processes, Reports, etc. where SQL is referred. Although the docs are geared towards ABC, most information still applies in Legacy as well. You should actually do this before taking any of the steps described in this document.

Moving on to the app, one thing you will need to do is add a logon procedure at the beginning of the application. You will need to collect the SQL user id and password as a minimum. You could hard code this, hiding it from the user. If you decide to do this, remember to add some sort of message window telling the user to wait while the app connects to the server, as this could take a short bit of time.

Sample logon source is available in the Clarion docs, or you may create a Wizard app against any SQL database and the Wizards will automatically create the appropriate source code . Just cut and paste into your existing app. You may feel free to use the source in my sample app if you wish. An initial connection to the server is completed when any data file is opened, so open a file with your initial start. After opening any file, feel free to close it if you wish, as your connection to the server would remain until your app closes.

As mentioned earlier, MS-SQL 7 tables require an OWNER attribute. This indicates how to connect to the database where the tables are located. I created GLO:Owner as Global, CSTRING(255), and use an INI file to save and or retrieve and supply this string at runtime. I created a Clarion procedure that offers default SQL login definition, and store in the app's .ini file (this information could be stored in the registry for security or a TPS file with encryption if you wish). You must prompt your user to define the default login info using proper syntax. Refer to the Clarion docs for the Owner string format.

Embed source to retrieve the .ini entry and initialize GLO:Owner with this info in your main procedure before opening your SQL 7 tables. This way, if the string is empty, you will get the MS-SQL login. If it is populated correctly, the MS-SQL login will be skipped and login will occur automatically!

Another thing you will need to do is make sure that on any browse or process you set the Quick-Scan Records (Buffer Reads) option on. This will result in great performance increases! Any place you are prompted for an approximate record count, enter a number much greater than you actually ever expect to see with regards to records retrieved. If you leave this blank or zero, the process will first attempt to get a record count from the MS-SQL Server, which usually means a big performance hit!

Changing all browses to fixed thumb appears to enhance performance a bit, but records displayed in a browse sometimes disappear or appear twice. Your call on this one, as the book also said to do this. I did, but finally changed back. Also, make sure for any and all browses you have selected a key for the main file in the file schematic. This also applies to reports and processes.

If you find in a report or process that fields are coming up blank where you know data exists, check that the field(s) have the Bind attribute. This one tripped me up bad a few times! Be sure to check your embedded source or hand coded procedures if they involve data access, as these may require modification.

The browse and process templates access data through a Clarion view. This is why any field needed within a browse or process must have the "Bind" attribute. The easiest way to check things after making your changes is to run the app and see how it performs. Don't forget to address any auto-increment issues (mentioned above); these may throw you off as well! Also don't forget to bind any field used as part of a filter in your procedures.

Third Party Books And Reference For MS-SQL 7.0

Clarion Magazine published by Dave Harms. Fantastic Clarion resource!

SQL Server Magazine published by Duke Press. This too is a great new resource.

Using Microsoft SQL Server 7.0 by Que. This book is very complete.

Summation

These are the main things I had to address with my conversion. I did not have any other significant issues. I did have a bit of trial and error, and you probably will too! Hopefully this article will help you avoid most of my trial and error process! Remember that I am not an SQL expert by any means! My methods were the result of reading everything I could find, asking lots of questions, and the trial and error process. If you are unsure about anything, post your questions to the newsgroups. Everyone is very helpful, and you will probably get the answers you need. I will make available for download a sample app, dct, and SQL script, along with a copy of this article in RTF format on my Website at: http://www.mullusa.com/demo/sqldemo.zip

Stephen Mull
http://ww.mullusa.com/products.html.

Printer-friendly version

Copyright © 1999-2009 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942