Error handling and debugging in traditional Visual FoxPro file server applications is relatively straightforward because traditional Visual FoxPro applications use a single technology that controls the user interface, data access, and the actual storage of data. Client/server applications use three separate technologies (the Visual FoxPro user interface, ODBC and a SQL Server database), which must communicate with each other. Because this architecture is more complex, the process of handling errors in the applicationand debugging is also more complex. In this chapter, you will learn some of the secrets of handling client/server errors. You are also introduced to some debugging tools that will make debugging easier for you.
Handling errors
After reading this far into the book, you may have gathered that handling data errors in a client/server application is not very different from a traditional Visual FoxPro application. This is simply because most data updates are handled through a TABLEUPDATE() or SQLExec() call,and you can use the AERROR() function to determine the cause of any failures. Any other type of failure (such as an application error) is trapped with either the Error event of the object, or through your global ON ERROR handler.
Unfortunately, it is not that simple: Handling client/server errors from Visual FoxPro can get tricky, particularly when SQL Server is used in a way that is not friendly to the client application.
Trapping errors
The first lesson to learn is how to trap the errors you receive during a TABLEUPDATE() call. You probably know that this function does not report errors through the ON ERROR handler. Instead, you must use the AERROR() function to capture the reasons for TABLEUPDATE() failures. In file-server applications, this array has a single row containing the details of the failure. For any Visual FoxPro error, the array contains the data shown in Table 1.
Table 1. The elements returned by AERROR() for Visual FoxPro errors.
Element
Data type
Contains
Description
1
Numeric
Number
Visual FoxPro error number.
2
Character
String
Visual FoxPro error message.
3
Character
NULL or string
Optional error parameter (same as SYS(2018)).
4
Numeric
NULL or number
Work area where error occurred.
5
Numeric
NULL or number
Trigger that caused the failure (1=insert,
2=update, 3=delete).
6
NULL
Always null.
7
NULL
Always null.
However, for errors that occur through an ODBC update, such as when updating data on a SQL Server with a remote view, the array will always have the same value, 1526, in the first column. This is because all ODBC errors trigger the same Visual FoxPro error (1526). The remaining elements of the array contain data that differs from a traditional Visual FoxPro error, since ODBC errors are reported differently. The contents of the array from an ODBC error are shown in Table 2.
Table 2. The elements returned by AERROR() for an ODBC error.
Element
Data type
Contains
Description
1
Numeric
1526
Error number, always 1526.
2
Character
Error message text.
Visual FoxPro error message.
3
Character
ODBC error
message text
The error parameter—same info as column 2,
but less of it
4
Character
ODBC SQL state
If the error is directly related to ODBC, this is an
error number that describes the error. These codes can be found in the ODBC Programmer’s Reference.
5
Numeric
ODBC data source
error number
If the error is a SQL Server error, this element
contains the SQL Server error number, which can be found in the SQL Server Books Online.
6
Numeric
ODBC connection
handle
Shows the connection handle across which the
error occurred.
7
NULL
Always null.
‡
ODBC errors can create multiple rows in the array created by AERROR(). Normally, you are primarily interested in the data of the first row, but the other rows could contain important information that is related to the error
reported in the first row.
Reporting errors
By analyzing the array, you can quickly determine the cause of any update errors by reading the fifth column and comparing the value there with error numbers from SQL Server. For example, imagine that you are working with the sample pubs database on SQL Server. In this database, there is an authors table, which contains information about the authors of the books in the database. The sample CREATE TABLE statement shown here defines the first column of the authors table, named au_id:
CREATE TABLE authors
(au_id varchar(11) NOT NULL
CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
--other columns defined here…
From this statement, you can see that the au_id column has a CHECK constraint
(equivalent to a field validation rule in Visual FoxPro), restricting the data to the format
999-99-9999, where 9 represents any valid digit. Therefore, if you attempt to place any data into this column that does not meet this CHECK constraint, the operation fails.
‡
The CHECK constraint created here does not specify a name for the constraint. This causes SQL Server to generate a name for the constraint in the format CK tablename fieldname xxxxxxxx, where xxxxxxxx is a
unique character string. To avoid this, you should always provide a name for all constraints, for reasons that should become clear in the next few paragraphs.
Now imagine that you have created a view that retrieves data from the authors table and allows the user to update that data. Suppose that after your user has pulled down a record to edit, he or she decides to change the au_id column of an author. In doing so, the CHECK constraint on the column is violated, which causes TABLEUPDATE() to fail. After invoking the AERROR() function, the resultant array will contain the following information:
LAERROR
Pub
A
( 1,
1)
N
1526 ( 1526.00000000)
( 1,
2)
C
"Connectivity error: [Microsoft][ODBC SQL Server
Driver][SQL Server]UPDATE statement conflicted with
COLUMN CHECK constraint 'CK authors au_id08EA5793'.
The conflict occurred in database 'pubs', table
'authors', column 'au_id'."
( 1,
3)
C
"[Microsoft][ODBC SQL Server Driver][SQLServer]UPDATE
statement conflicted with COLUMN CHECK constraint
'CK authors au_id 08EA5793'. The conflictoccurred in
database 'pubs', table 'authors', column 'au_id'."
( 1,
4)
C
"23000"
( 1,
5)
N
547 ( 547.00000000)
( 1,
6)
N
1 ( 1.00000000)
( 1,
7)
C
.NULL.
( 2,
1)
N
1526 ( 1526.00000000)
( 2,
2)
C
"Connectivity error: [Microsoft][ODBC SQL Server
Driver][SQL Server]UPDATE statement conflicted with
COLUMN CHECK constraint 'CK authors au_id08EA5793'.
The conflict occurred in database 'pubs', table
'authors', column 'au_id'."
( 2,
3)
C
"[Microsoft][ODBC SQL Server Driver][SQL Server]The
statement has been terminated."
( 2,
4)
C
"01000"
( 2,
5)
N
3621 ( 3621.00000000)
( 2,
6)
N
1 ( 1.00000000)
( 2,
7)
C
.NULL.
As you can see from this output, the array contains two rows. The first reports the violation of the CHECK constraint, and the second tells you that SQL Server has terminated the statement.
While you are developing your application, you can show the contents of the second or third columns of this array, since only you (or other developers) would ever see the message. However, if your end users are anything like the folks that we’ve encountered, they willsurely react with panic to a message like that one! Therefore, you will eventually want to capture an error like this and “translate” it into something more friendly before putting the application in front of them.
This is where the trouble begins, as there is no easy way to uniquely identify the errors you receive back from SQL Server. For example, notice that the fifth column of the first row of the array contains the SQL Server error number 547. If you check this error number in
the Help system of SQL Server, you will find that this error is reported for any kind of constraint violation, not only CHECK constraints. This means that the only other way to determine the exact cause of the error is to parse the character string in the second or third column of the array.
In deciding which column to use, notice that the first and second rows of the array have the same exact error message in the second column, but the third column reports a different error
for each row.
Regardless, using the contents of the error array to create a user-friendly message poses a small problem that can really only be solved in one of two ways. The first is to search for the
constraint name (in this case, CK
authors
au_id
08EA5793) and present a friendly
message for each. While this seems easy enough, it requires that you always have an up-to-date list of constraints that are on the server and details about their meaning. If the constraint is modified at any time, you will have to update your client-side code to match. Also, refer tothe earlier note about how this constraint name came to exist. If someone simply regenerates the schema of the database or the table, the name change could break all of your code.
A second approach to error handling is a radical change over this first approach: Do not update the data on the server through views, but use stored procedures instead. The first step to making this approach work is to define a stored procedure for handling the updates ofauthor records, as in the following T-SQL code:
/* Add the message */ EXECUTE sp_AddMessage
@Msgnum = 50001,
@Severity = 16,
@Msgtext = 'Invalid author ID specified. Use the format
999-99-9999 for author IDs.'
GO
/* Create the stored procedure */ CREATE PROCEDURE UpdateAuthors
@Key Varchar(11) = NULL,
@Au_ID Varchar(11) = NULL
AS
IF @Key IS NULL
RAISERROR ('You must provide the @Key parameter when
calling UpdateAuthors',16,1)
ELSE
IF @Au_ID IS NOT NULL
IF @Au_ID LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
UPDATE Authors
SET Au_ID = @Au_ID
WHERE Au_ID = @Key
ELSE
RAISERROR (50001,16,1)
ELSE
RAISERROR('No update occurred since @au_id was not provided',16,1)
go
This code starts by creating a system message that is stored in the master database of SQL Server. This makes the message available to all databases, and it can be easily invoked by the RAISERROR function. After the message is created, the stored procedure code canreference the message through its unique error number (in this case, 50001). Note that this code merely creates the system message and the stored procedure—it does not handle any kind of update of an author record, nor does it actually execute the stored procedure.
With this procedure in place in the pubs database, instead of posting the update directly through a Visual FoxPro view, you can now use a SQL pass through call:
*--turn off the non-trappable warnings
SQLSetProp(lhConn,"DispWarnings",.F.)
*--execute the stored proc to try the update
lnResult = SQLExec(lhConn,"EXECUTE UpdateAuthors "+ ;
"@key = 'keyval', @au_id = 'new value'")
IF lnResult < 0
*--something failed, check it out
lnRows = AERROR(laError)
*--more code normally follows, but is omitted here
The SQL pass through statement produces an error that can be captured from within Visual
FoxPro with the AERROR array. In this case, the error array looks like this:
LA
Pub
A
( 1,
1)
N
1526 ( 1526.00000000)
( 1,
2)
C
"Connectivity error: [Microsoft][ODBC SQL Server
Driver][SQL Server] 'Invalid author IDspecified. Use
the format 999-99-9999 for author IDs."
( 1,
3)
C
"[Microsoft][ODBC SQL Server Driver][SQL
Server] 'Invalid author ID specified. Use theformat
999-99-9999 for author IDs."
( 1,
4)
C
"37000"
( 1,
5)
N
50001 ( 50001.00000000)
( 1,
6)
N
1 ( 1.00000000)
( 1,
7)
C
.NULL.
Therefore, if the stored procedure is programmed with these “friendly” error messages, you can simply display these messages directly from the AERROR array. Furthermore, you can
trap for each specific error number (in this case, 50001) and translate the error message if desired. However, to get this kind of information, you will need to use stored procedures and forego the convenience of using views.
A third alternative is to not use any data validations on the server at all, and instead handle all of them from within the client application. This approach performs well, but only if you are writing the sole application that will ever touch the data on the server. Leaving thedatabase open with no validation whatsoever is typically not a good idea, as it makes it very easy for bad data to enter the system.
Decisions, decisions… The choice is up to you. One major factor in your decision may depend on how much access you have to the server. If you are the entire development department, and you take over the SQL Server and the client-side application development, then youcan choose to either design your own custom stored procedures for the updates, or monitor the naming of constraints and other rules so you can capture them when using views.
However, you may work in an environment where you do not “own” all of the pieces of the application. For example, the SQL Server may already be in use by another department. This department claims ownership of the server and the associated database that you need to access in order to write your Visual FoxPro application. Since the database belongs to the other department, you may have political problems in acquiring the necessary access to the database(s) on that server. Without proper access, you will have a tough time determining the
current status of any rules, stored procedures and so on. This can definitely complicate matters, and may force you to resort to error-handling techniques that otherwise would not be your
first choice.
Conflict resolution
No, this is not a section on how to deal with difficult employees or your “significant other.” It
is, however, meant to introduce another big difference between Visual FoxPro and SQL Server:
how they handle update conflict resolution.
When using native Visual FoxPro data, you have some choices when deciding how to deal with update conflicts. Recall that this error occurs when two users have pending changes on the same record and then attempt to commit their changes. Only one user can update at atime, so when the first user finishes his or her update, the second user is left with a problem because the data on disk is not the same as it was when the edit began. You can control Visual FoxPro’s behavior through the second parameter of TABLEUPDATE(), which allows you tomodify
how the second user experiences this problem.
If you set the second parameter to False, the second user’s update fails with error 1585, “Update conflict,” as Visual FoxPro will detect that the first user updated the disk values while the second user was waiting with pending changes. On the other hand, if you set the second parameter to True, the second user is permitted to overwrite the first user’s changes without questions or errors. Informally known as “last one in, wins,” this avoids the update conflict problem entirely. This is a great choice if it is not common for two users to edit the same recordsconcurrently, and it reduces the amount of error-handling code that you have to write.
When working with remote data, the same options are available and operate similarly. What changes is how you handle an update conflict error (i.e., use False for the second parameter). When a conflict occurs against Visual FoxPro data, you can use the OLDVAL() andCURVAL() functions to view the state of a field before it was edited and the actual value on disk, respectively. This allows you to go so far as to show the user what changes were made by the other user and let them evaluate how to proceed.
However, when dealing with remote data, CURVAL() is worthless, as it always returns the same value as OLDVAL(). Therefore, you have to use a completely different technique to resolve conflicts when working with remote data. Since CURVAL() does not work, you have tofind a different way to get at the actual data on the server. You may first think that REQUERY() is the answer, but this cannot be done on a view that has pending changes.
The only technique that seems to work is to open a separate copy of the data in its own work area, either with SQL pass through or with the view. You still have to use the REQUERY() function after opening this second copy to ensure that you’re looking at the latest values.This is due to the way that Visual FoxPro caches the view’s contents. But once you have opened the view and executed REQUERY(), you can retrieve the current values and use them as part of your conflict resolution.
Before we continue, we should point out another subtle difference that exists between updates against native data and updates against remote data. Recall that TABLEUPDATE() can detect update conflicts with its second parameter set to False. When applied to native data, an update conflict occurs even if the two users edited different fields. This is due to the way Visual FoxPro implements data buffering—a comparison of the entire row takes place, instead of only checking the modified fields. To check whether there is truly an update conflict, you must write a handful of code that employs the use of the OLDVAL() and CURVAL() functions. However, when using views (either local or remote, actually), you can choose a WhereType that ensures only the modified fields are checked against the back end for update conflicts.
For example, when your WhereType is set to the default of 3 (key and modified fields), Visual FoxPro will submit an UPDATE statement that only includes the fields that were modified by the current user. As long as the other user did not edit any of these same fields, no conflict exists. However, if you use the WhereType of 2 (key and updatable fields), you are bound to hit update conflicts more readily, as this will include any fields marked as updatable.
Note that choosing a WhereType of 4 (key and timestamp) is going to catch any update conflict in the entire record, as the timestamp will be updated regardless of which field or fields were changed. However, if you need to detect changes in any field (particularly memo fields), this proves to be the most efficient option.
Finally, if you wish to avoid update conflicts entirely, you can choose a WhereType
of 1 (key fields only), so that Visual FoxPro only cares about matching the key fields before posting the update. This has the same effect as specifying True for the second parameter of TABLEUPDATE().
View errors
There are many possible errors that ODBC can return when a view update fails. There are three main categories of errors: Something is wrong with your view, something is wrong with the data, or something is wrong with the server. You’ve already seen how data errors
happen, and with server errors, a Visual FoxPro error occurs that you can trap with traditional error handling.
During development, you are most likely to run into errors in your views. Most of these errors will be among those listed in Table 3.
Table 3. Common ODBC error messages indicating errors in views.
Error message
Action
No update table(s) specified. Use the Tables
cursor property.
Use the Tables property to specify at least one server
table, or modify the Tables property.
No key column(s) specified for the update
table table_name. Use the KeyFieldList cursor property.
Set the KeyField property to .T. for at least one field,
or use the KeyFieldList property of the view.
No valid update table specified for column
column_name. Use the UpdateNameList and
Tables cursor properties.
Set the table qualifier for the UpdateName property
for the column or for the UpdateNameList propertyfor the view.
The KeyField List cursor property doesn’t
define a unique key.
Set the KeyField property to .T. for at least one field,
or use the KeyFieldList property of the view.
One frustrating issue with some of these is the use of the ownership qualifier dbo for tables. Sometimes you’ll get an error message such as the first one in Table 3—“No update table(s) specified. Use the Tables cursor property.” So you check the value of the property, expecting it to be empty, but it says something like this:
dbo.category
This is exactly what it should be for the category table. We don’t know why, but sometimes VFP chokes when dbo is used and vice versa. So, if you get this message and you used dbo, then change the property to remove dbo. If the property doesn’t contain dbo, then changethe property to add it. This error doesn’t happen very often, but you can’t guess how many hours it will cost you to find it the first time! We have had the error occur with both SQL Server 6.5 and 7.0, but not yet with SQL Server 2000.
Debugging tools
Now that you have the general idea of how to handle and report errors in your client/server application, you need to learn how to debug errors that originate in any part of the application.
On the client side, you can continue to use your familiar Visual FoxPro debugging tools: the Debugger, Event Tracker, Coverage Profiler and, of course, your collection of past experience. But the client side is only one of three places where problems can crop up. The othertwo are in the ODBC driver and on the SQL Server. These two pieces have their own ways of debugging: ODBC logs (or trace) and some SQL Server tools.
SQL Server Profiler
One of the most useful tools for debugging tricky client/server problems is the Profiler, which is installed with SQL Server. You can use the Profiler to monitor events on a SQL Server. The Profiler will capture data about each event and allow you to either view that data in the Profiler’s graphical environment or save the data in a file or a SQL Server table for later viewing. You might be surprised by how many events take place on a SQL Server; fortunately, the Profiler also allows you to filter for certain types of events.
The Profiler can trace numerous types of events. Here is a partial list:
• Login connections, failures and disconnections
• Transact-SQL statements
• Lock acquisition and release
• Beginning or end of a stored procedure
• Beginning or end of statements in stored procedures
• Errors
• Beginning or end of SQL batches
• Beginning or end of transactions
• Remote procedure calls
By using the Profiler, you can examine the commands that VFP sends to SQL Server, evaluate the performance of individual commands or stored procedures, and even debug T-SQL stored procedures one line at a time.
The Profiler can be found in the Microsoft SQL Server file group. To create a trace, open it and select File | New | Trace. Figure 1 shows the resulting Trace Properties dialog. All you need to do here is name the trace. In the example shown in Figure 1, the trace is called MyTrace.
Figure 1. The SQL Profiler Trace Properties dialog.
Figure 2 shows a simple trace created by opening a view of the Tastrade category table created by the SQL Server Upsizing Wizard in Chapter 5, “Upsizing: Moving from File-Server to Client/Server.” This view shows some of the interesting information that can be supplied by the Profiler. The Event Class column describes the server event. When VFP sends a query, the class is SQL:BatchCompleted, and the Text column next to it will show the actual SQL statement that was sent in the batch. Note that the SQL User Name column displays the default administrative login. Since this system is using NT security, the NT User Name appears as
well. The CPU column will show which CPU in a multi-processor server handled the request. SQL Server is multi-threaded, so each thread can be sent to a different CPU. In this case, however, it is running on a single-processor system, so only CPU 0 is used. The next three
columns are particularly useful, as they can be used to troubleshoot performance problems. These show the number of reads, the number of writes, and the duration in milliseconds of the event. Partially hidden is the Connection column, and further to the right, off the screen, is thedatetime of the event. The datetime column isn’t particularly useful, as we would rather use the duration column to troubleshoot performance.
Figure 2. A simple trace in the SQL Server Profiler.
The Profiler is a great tool for tracking down all kinds of issues. Consider the following example of using the Profiler to track down a particularly nasty performance problem in the way the SQL Server 6.5 product handled certain messages from the ODBC driver. In this situation, Visual FoxPro was used as the client application for a SQL 6.5 box that was quite powerful: multiple processors, high-throughput RAID array, and all the other toys. Nevertheless, for some reason, when an insert occurred to a SQL table with a Text field, the applicationappeared to freeze.
The client tried letting it run its course to determine if the insert would eventually complete or if it was a lock-up situation. After waiting two hours with no response, they asked for help. When it had been determined that the code was implemented correctly and that thedatabase schema was valid and efficient, the Profiler provided the answer—it supplied the pertinent information and explained why performance was so awful.
The table was rather large, with more than 200,000 records, and each record had a Text field. SQL Server 6.5 was quite wasteful with Text fields, as it allocated a single 2K page for each Text field, regardless of whether the field contained data or not (SQL Server
7 fixed this problem by allowing its 8K pages to be shared among multiple Text fields). Therefore, searches through Text fields were to be avoided at all costs, since each search
would require moving and searching through 400MB of data. Even on this heavy-duty machine, searching through that much data would be slow, particularly since there are no indexes on
Text fields.
What was happening was that SQL 6.5 received the update request and converted it into three statements. The first inserted all of the data, except the contents of the Text field. Instead
of using the actual Text data, the statement provided a unique value for the Text field.
Then, the next statement was the death knell: it was performing a SELECT statement with a WHERE clause to find the unique value in the Text field! (Wouldn’t you at least think it would try to find the record with the primary key of the table?) It did this because it would use another function called UPDATETEXT to place the data into the Text field—used in order to avoid other kinds of problems with sending large amounts of Text data across an ODBC connection.
Once this performance problem was discovered, it was easy to rewrite their update routine to solve the problem. Without the Profiler, there would have been no clue as to why the server would choke on something as apparently simple as an INSERT statement.
Another great use for the Profiler is as a learning tool. You can find out all sorts of neat things by trying a function in the Enterprise Manager or from Visual FoxPro, and then looking at the Profiler to see what happened. For example, imagine that you are not sure how to build a CREATE DATABASE statement in T-SQL. You can use the Enterprise Manager to create a new database, and then switch over to the Profiler to see what command(s) the Enterprise Manager submitted to the server. Here’s the output from trying this little test:
CREATE DATABASE [test] ON PRIMARY
(NAME = N'test_Data',
FILENAME = N'C:\MSSQL7\data\test_Data.MDF' ,
SIZE = 1,
FILEGROWTH = 10%)
LOG ON
(NAME = N'test_Log',
FILENAME = N'C:\MSSQL7\data\test_Log.LDF' ,
SIZE = 1,
FILEGROWTH = 10%)
You may also uncover some undocumented features or discover how to do something programmatically that you thought could only be done through the Enterprise Manager. For example, we once used the Profiler to help troubleshoot a problem with the Upsizing Wizard before the source code for the wizard was available.
The SQL Server Performance Monitor
Another useful tool for tracking down performance problems on the server is the Performance Monitor, shown in Figure 3. This tool is used to view details about a wide variety of “counters” available through SQL Server. For example, you can see if your processors are always running at maximum throughput, if SQL Server is constantly swapping data in and out of the buffer cache, if you are using large amounts of locks or other resources, or even how many users are logged into the system at any given time.
As you can see, the Performance Monitor is best at telling you whether your SQL
Server hardware and software are configured properly for the typical load it needs to handle. You can log the activity as well, so it can be reviewed over time to help discover any possible degradation.
Figure 3. The SQL Server Performance Monitor utility.
ODBC logs
Although not our favorite tool for debugging a client/server application, ODBC trace is sometimes the only way to determine whether the problem lies between Visual FoxPro and SQL Server. For example, perhaps you believe that SQL Server is receiving an ill-formed statement that is causing an error that you just cannot seem to track down within either Visual FoxPro or SQL Server.
We don’t jump at the chance to use ODBC logs, because they are tremendously large due
to the detailed information stored therein, and it is quite tedious to wade through them. Because
C++ programmers, not Visual FoxPro developers, develop ODBC drivers, the logs they produce are full of hex values, but you can also view function calls and their success codes. This permits you to view the exact steps that ODBC takes to get data from the server and to put databack onto it. Armed with this information, you may be able to determine the cause of the problem you are having and perhaps come up with a workaround for it.
Depending on how you are accessing the server, there are two places where you can request creation of an ODBC log. First, if you are using a DSN, there is a check box where you can enable tracing (see Figure 4). Even if you are not using a DSN, you can also use the Tracingtab to enable an ODBC trace (see Figure 5).
Once you turn on ODBC logging, it will log every statement that occurs across the ODBC connection until it is disabled. Since this generates a large amount of information, you will want to turn it off as soon as you have logged the desired functions.
Figure 4. Enabling ODBC logging in an ODBC Data Source.
Figure 5. Enabling general ODBC logging.
One big problem with using an ODBC trace is the amount of time it takes to create the file. For example, what if you were to trace the opening of a view that retrieves data from the
authors table in the pubs database? This table has a “whopping” 23 records, and contains records that are no more than 151 bytes wide. The table uses mostly varchar fields, so the
record width is variable and is usually much smaller than 151 bytes. Worst-case scenario means that the table is 3473 bytes (or 3.4K) at its largest possible size.
When this view is opened under normal circumstances, it opens almost instantaneously. However, when the ODBC trace is enabled, opening the view will take 28 seconds. Clearly, the additional time is required to report the wealth of information produced by the ODBC trace to the log—the resulting log file is 178K!
This log file contains several useful bits of information. The first part of the file shows how the ODBC driver connects to the server, changes the database context, and sets a variety of options, including the timeout values set by the Visual FoxPro connection definition used by
the view. Once the connection is complete, the log shows that the SQL statement “SELECT * FROM dbo.Authors Authors” was executed. The rest of the log contains the steps used to retrieve the column data types, and their values for each record.
Therefore, you can use the ODBC log to produce a high level of detail on how Visual FoxPro is communicating through the ODBC driver to talk to SQL Server. However, in our experience, the ODBC log has been a “last resort” debugging tool. In most cases, if the problem is not something easily traced within the Visual FoxPro debugger, the next step is to use the SQL Server Profiler.
Summary
In this chapter, you have seen the details of handling errors and how to go about debugging problems. Errors are handled differently in a client/server application, and regardless of the development product you choose, you must decide where to actually handle errors. In Visual FoxPro, you can perform all error handling in the client application and only send clean data to the server, you can use views and capture TABLEUPDATE() errors, or you can discard views and use stored procedures on the server and SQLExec() statements.
Of course, you can still use the Visual FoxPro debugging tools to track down any Visual FoxPro errors. However, when the problems seem to be outside of Visual FoxPro, SQL Server and ODBC both provide some good tools for watching all of the activity generated by your Visual FoxPro application.
In the next chapter, you will see more information on how to design a client/server application in order to keep it flexible, scalable and maintainable.
Posts
No comments:
Post a Comment