Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Solutions  |  Paradox Case Studies  


Using Paradox with MS SQL Server
Introduction
© 2002 Mike Hinds

"Easy Client-Server"- NOT!

"Delphi is going to solve all your problems." That's what I was told. Delphi would mind-meld my Paradox experience with my years of Turbo Pascal. It would let me painlessly move my Pascal apps to Windows. I could access Paradox database tables from these Pascal Windows programs. Best of all, the Holy Grail of programming, "Easy Client-Server", would be mine at last.

At a roll-out demo near Redmond, Washington, Borland and Novell staff teamed up to show about a hundred developers how seamlessly WordPerfect Office 3.0 would work with Delphi. After several glitzy applications held my attention, the moment I had waited for finally arrived. An address book program was prototyped and run in all of ten minutes. Then, with a few adjustments to the BDE Configuration, and the change of a field name, the program was running against an InterBase table. It was neither magic nor rocket science, but within my grasp, as Delphi 1.0 would be available in less than a month.

Delphi 1.0 was indeed a great product, but "Acme Client-Server - just add water" was not to be. My vocal enthusiasm of both Delphi and client-server databases soon landed me a spot on a team project which heavily used Microsoft SQL Server 6.5. Today I still use Delphi, but I also still develop with Paradox. Both provide robust front-ends to a Microsoft SQL Server 2000 installation with over 270 users in 65 branch offices. Whatever else we may think about the giant from Redmond, I wouldn't want to give up any of the features I enjoy with these three productivity tools.


Don't Do It

Let's get the bad news out of the way. Maybe you've heard that your full-featured, finely tuned Paradox killer app can be promoted to a client-server system just by data-pumping the tables to the server, and changing the alias in the BDE Configuration. Please don't allow anyone to set you up for that kind of disappointment.

It's different. It requires a different mindset. Occasionally it will be forgiving of innocent ignorance, but it will never behave like good old Paradox tables. If you can avoid going into your learning period planning to cling to your preconceptions, the strength of both platforms can be used to your best advantage.


Prepare

A failure to plan ahead on your part does not constitute an emergency on mine. I've always hated hearing that. I'll try not to sound like a dripping faucet, but the best favor you can do yourself (and your successors, if any) is to plan ahead before writing any code. Specifically, normalize your data!

It doesn't matter which you attempt first: writing a new system or migrating a legacy local- or file-server-based system to client-server. You will be much worse off, and have far more conversion housekeeping to redo, if you need to change your client-server tables after they've been deployed. SQL Server 2000 is more forgiving than 6.5 was in this, allowing you to modify and delete fields after the table is created. Version 6.5 allowed only adding fields, and this only at the end of the field list (which, by the way, is still a very good suggestion).

Experiment with local Paradox tables first, if this is helpful, to test proposed linkages and relationships. Your favorite search engine will turn up a plethora of results for "third normal form", a commonly accepted standard for relational database design. Be sure to have a good feel for good normalization of your typical data before you begin building any tables on the server. Try to consider them as "set in stone" once they are created on the server. While not precisely accurate, the mandate to plan before you create will save you time, effort, and perhaps even lost data.

Please use consistent, portable field names! I inherited a very large application. It had at least a dozen different ways of spelling "Account Number" (Acct#, AcctNo, AccountNo, AcctNumber, TheAccount, and even "Acct No.". It had field names with spaces, pound signs, dollar signs, ampersands AND DOTS! These gimmicks don't move to SQL Servers well. Yes, Paradox does allow it, but other databases likely will not. Even if you're sure a workaround can be found, resist the temptation to make your job (and that of your successor) more difficult.


The Environment

Fortunately, all of what you have learned in Paradox can still be put to very good use as you build your client-server system. Forms, reports, libraries and scripts, with all the built-in methods and the rich ObjectPAL language, are yours to keep as your data moves to a more robust, industrial-strength platform.

You will still need a local working directory. Forms, reports, libraries and scripts may not be saved on the SQL Server. These design executables will need to live in either a shared fileserver or on the local machine.

Paradox's working directory and Project Viewer may not be set to an alias that is a SQL Server database. However, you do have the ability to open a table by either {Menu} File | Open | Table or {Tables} Open, and then changing the search alias to your SQL database.


Gotchas

There are some typical design habits that will just be wrong for SQL Server. Here are a few that you're sure to discover the hard way if you commit to building or moving your app to client-server. One such familiar habit has broad consequences. In short, you should never connect any data-aware UIObject (edit field, grid, graph, etc.) directly to a SQL Server table. You might even be better off without using the familiar Paradox TableFrame, or grid (and by association the MRO, or Multi-Record-Object). Browsing data row-by-row is a clear no-no in client-server platforms.

Why should this be? Tables on the local PC, or on a LAN (Novell, Microsoft Windows Networking, etc) can be considered as connected directly to the record object in the grid, and browsing simply moves the file's current record pointer forward or backward, exposing the new current record.

However, when a BDE app connects a grid to a SQL Server table, every navigation command (whether from keystroke, scroll-bar, VCR control or ObjectPAL statement) causes the BDE to write, parse, and execute another SQL query. This gives your users lots of time to admire their new "SQL hourglass" while waiting for the results of each down-arrow-keypress. We sure don't need frustrated users, and there's no need to frustrate them when we can wow them with real speed.

The secret to blazingly fast client-server applications is this: Always move the minimum number of bytes over the wire that is required to produce the desired result (especially on interactive user screens). Give that some thought. If you're searching for a customer, must you display every field in the table, for every record in the table? I think not. A customer table may have from 20 to 50 fields, but only two or three will be used for an interactive search. Once the correct customer is found, query again, and display those results in a detail screen.


Use SQL Queries

Should grid browsing be a valid design requirement, it's best to submit a query for a minimum subset of both rows and columns, dump the query results to a temporary local table, and then launch a form that will browse the local grid. My rule for my apps (and currently I have NO exception to that rule) is to do all access of SQL tables with SQL statements. Any table in a data model is a local table, a subset that is a minimum of both rows and columns.

As you'll soon discover, different dialects of SQL exist. If you've used Paradox for any amount of time, you've spent some time learning QBE. These will work! You can learn a lot about SQL by creating a new QBE query, or loading a familiar one, and pressing the "SQL" toolbar button. The "Add Tables" button will allow you to change the alias, write a QBE against your SQL Server tables, and press the SQL button to see an equivalent query in SQL. It's not always the best SQL, and some QBEs may not translate, but it will help you get started learning SQL.


endMethod

These are some broad introductory thoughts to help you as you explore the possibility of using Microsoft SQL Server for Paradox Applications. In another installment we will use:
  • SQL Enterprise Manager to build tables and indexes
  • Query Analyzer to build and test SQL queries
  • Paradox to display and interact with our data.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.