Surely you’ve heard the question before. In fact, maybe it is why you are reading this book. If not, the question is inevitable. “What will it take to go client/server?” Long ago one of the authors was asked this question so many times that finally he sat down with VFP3, upsized a VFP database to SQL Server 6.5 and started to get his hands dirty doing client/server work. This is a great way to learn client/server development. So roll up your sleeves, make a copy of a project you are familiar with and get ready to move
from file-server development to client/server. In this chapter you will learn how to upsize a VFP database to SQL Server, how to use the upsized data, and some tips on more easily transitioning from file-server development to client/server.
We didn’t invent the term “upsizing” and are not really sure we like it. There is no rule that says a SQL Server database or application is “bigger” than a FoxPro one. But the term has become so widely used—and is even used in the names of the “Upsizing Wizards”—that we’ll stick with it here. When we discuss upsizing, we’re referring to converting a Visual FoxPro database to a client/server database.
Visual FoxPro ships with two Upsizing Wizards: one for SQL Server and one for Oracle. As elsewhere in this book, all the examples in this chapter will use Microsoft SQL Server.
Why upsize?
If you have an existing file-server application and database that you wish to convert to client/server, then upsizing the database may be a good way to start the process. If you have designed your existing application to use local views, then it is possible that the Upsizing Wizardwill do most of the work necessary to make the conversion. If your application accesses the tables directly rather than using views, then you have a lot more work to do. Even so, upsizing is still a good place to start, as it gets you an instant copy of the database in SQL
Server so that you can begin working with it quickly.
On the other hand, if you are developing a new client/server application, it is better to use the tools designed for SQL Server (such as Enterprise Manager, Visual InterDev or Access
2000) to create a new database directly in SQL Server, rather than develop the database first in VFP and then upsize. This two-step process, called “prototyping locally and deploying remotely,” was a more reasonable approach with SQL Server 6.x because the 6.x versions were very difficult to deploy on laptops or small installations for demos or prototypes. By comparison, the newer versions of SQL Server (7.x and 2000) can easily be installed and run
on laptops and other small machines. Additionally, with MSDE, a prototype can be deployed royalty-free without the need for a complete SQL Server installation. (For more on MSDE, see Chapter 7, “Downsizing”).
The best reason for upsizing a VFP database is to learn to use SQL Server with a database
with which you are already familiar. After upsizing a database, you will have a VFP version and a SQL Server version containing the same data. You can easily work with both in order to get the feel for SQL Server. Look at the data types in the two databases, learn about SQL Server indexes, see how referential integrity is handled, and compare database tools. It is often much easier to work with data you know rather than with simplified, sample databases, such as Pubs or Northwind, which are included with SQL Server.
Despite the preference for working with familiar data rather than sample databases, we will use the VFP Tastrade database for the examples in this chapter.
‡
The Upsizing Wizard updates your local database container. Therefore, before upsizing your database, be sure to make a backup of the entire local database container (x.DBC, x.DCT, x.DCX) and the tables as well. This will
ensure that you can upsize as many times as you like without affecting any current applications that need to access the database or its tables.
Using the SQL Server Upsizing Wizard
The SQL Server Upsizing Wizard requires a DSN or a named connection to a SQL Server. We suggest creating a new DSN before even opening the wizard. For the examples in this chapter, we have created a System DSN named Tastrade, as shown in Figure 1. For more on creating DSNs, see Chapter 4, “Remote Views.”
Figure 1. The ODBC Data Source Administrator dialog showing a System DSN for upsizing the Tastrade database.
The Upsizing Wizard must be able to open the database and all of its tables exclusively, so before running the wizard, close them and ensure they are not in use by anyone else.
To run the SQL Server Upsizing Wizard, select Tools | Wizards | Upsizing on the Visual
FoxPro menu and then, in the resulting dialog, choose the SQL Server Upsizing Wizard. The
first step of the wizard will ask you which database to upsize. Figure 2 shows the first step with the Tastrade database selected.
Figure 2. Step 1 of the SQL Server Upsizing Wizard showing the Tastrade database selected for upsizing.
Note here that if the database is already open, you will be warned that the Upsizing Wizard requires exclusive access to the database. Also note that the VFP database is referred to throughout the wizard as the “local” database. Clicking the Next button here will take you to Step2, shown in Figure 3.
Figure 3. Step 2 of the SQL Server Upsizing Wizard—selecting an ODBC data source to use for upsizing and for remote views created by the Upsizing Wizard.
Step 3 of the Upsizing Wizard allows you to select which tables to upsize. By default, none are selected, but you may choose any or all of the tables. In Figure 4, you can see that we have selected to upsize all tables.
Figure 4. Step 3 of the SQL Server Upsizing Wizard. All the tables in the Tastrade database have been selected for upsizing by moving them to the right- hand list box.
Simple stuff so far, right? Well, here’s where it starts to get more complicated. Step 4, illustrated in Figure 5, allows you to map the data type of each column in each VFP table to a SQL Server data type, to set up timestamp columns, and to use SQL Server’s identity featurefor columns. You select the table in the Table drop-down list, and all the columns
will be shown in the grid below. Note that any table that has a memo field will be marked for
a timestamp column. The timestamp column will not appear in the grid but will be named timestamp_column when upsized. You have the option at this time to add timestamp columns to other tables, or to remove the timestamp columns from those that already have them. However, if you plan to replicate the database, you must remove the timestamp columns, as they are not supported in replicated databases.
You can choose to use an identity column for the table so that SQL Server can automatically create unique integer values suitable for use as primary keys by checking the Identity column check box. The IDENTITY property is described in greater detail in Chapter 3,“Introduction to SQL Server 7.0.” Identity columns will be named
identity_column, and the seed value and increment will both be set to one. Before using identity columns, read Chapter 9, “Some Design Issues for C/S Systems,” which describes some “gotchas.”
Figure 5. Step 4 of the SQL Server Upsizing Wizard showing the data type mapping from the VFP table to the SQL Server table. The Timestamp column check box is checked, specifying that a timestamp column will be created for this table, though it does not appear in the list of column names.
The final task in Step 4 is to set the data types for each column in each table. In most cases, the default data types will be adequate, but there may be times when you wish to change them. For example, the VFP numeric data type is mapped by the Upsizing Wizard to the SQLServer float data type, rather than numeric, and the VFP character type is always mapped to the SQL Server char, while there may be times when it is preferable to use varchar. Table 1 displays the default data type conversions used by the Upsizing Wizard.
Table 1. How the Upsizing Wizard maps Visual FoxPro data types to SQL Server data types.
VFP data type
SQL data type
character
char
currency
money
date
datetime
datetime
datetime
double
float
float
float
general
image
integer
int
logical
bit
memo
text
memo binary
image
character binary
binary
numeric
float
You should be careful when using varchar data types because they can cause performance problems that can compromise the scalability of your database. Visual FoxPro uses fixed-length columns, which allow the database engine to make assumptions about where the columnsare stored, thus permitting fast access. In a table that has variable length columns, the database engine must store additional bytes to describe the length of each variable length column, which forces the database engine to work harder to retrieve and write data.
Retrieving data does not cause a major performance problem unless there are many variable length columns in each row and there are a significant number of rows.
However, performance problems are more likely when writing data. When you insert a row, SQL Server places it in an existing page that has room for the data. (If there is no such page, SQL Server creates a new page and inserts the row there.) If you update an existing row and add data to a variable length column, the row is now longer. If the row no longer fits in the same page, SQL Server moves the row to a page that has enough space, or creates a new page if necessary. This activity creates a considerable amount of disk I/O. In ahigh-transaction environment, this overhead can cause performance problems.
One particular data type to pay attention to is VFP’s date type. Although there are multiple datetime types, there is no date type in SQL Server! If you use the FoxPro date type, then whether you like it or not, the SQL Server type will always be a datetime. If you use VFP remote views, then the best way to deal with this without changing your code is to use DBSETPROP() to change the data type of the field view to date, as in the following line of
code that changes the order_date field’s data type to date:
DBSETPROP('SALES DETAIL.order_date', 'Field', 'DataType', "D")
In Step 5 of the Upsizing Wizard, you select the database in which the upsized tables will reside. For some reason, the default is to dump everything into the master database. Be very careful not to click past this step without changing this. You definitely do not want to putyour data in the master database. In Figure 6, the New option has been selected, and the new database will be named Tastrade.
Steps 6 and 7 are skipped when you run the wizard against SQL Server 7 databases, as
they only apply to version 6.5. Step 6 enables you to select the database device for a new database, but devices have disappeared in SQL Server 7. Similarly, Step 7 is used to specify the device for the transaction log of the new database.
Step 8 is a big one, with lots of important decisions to make. Here you will specify which table attributes to upsize, whether to upsize data or just structure, how to deal with referential integrity, and what changes to make to the local DBC. The Upsizing Wizard upsizes indexes,defaults, relationships, relational integrity and validation rules.
Note that the Upsizing Wizard cannot upsize triggers and stored procedures, which contain procedural code, because SQL Server does not support VFP procedural code.
Also, because of differences in the way expressions are handled in VFP and SQL Server, some of the features of your VFP database will not be upsized. The effects of these differences are described in the following sections.
Figure 6. Step 5 of the SQL Server Upsizing Wizard. A new database named Tastrade will be created rather than the default, which is to dump all the new tables into the master database. Don’t overlook this step!
Indexes
Although there are many similarities between indexes in VFP and SQL Server (in both products, indexes can be used for primary keys, to enforce non-primary-key uniqueness and to optimize queries), there are numerous differences between indexes in the two products. Table2 shows how the Upsizing Wizard maps VFP index types to SQL Server index types.
Table 2. Visual FoxPro index types and how the Upsizing Wizard maps them to SQL Server index types.
VFP index
SQL Server index
Primary
Clustered
Candidate
Unique
Unique
Non-clustered
Regular
Non-clustered
‡
In SQL Server, it is not common to build a clustered index on your primary key. However, the Upsizing Wizard moves all Visual FoxPro primary key indexes to SQL Server as clustered indexes! Be aware of this
situation, and be ready to modify your indexes before going into production with this upsized database.
SQL Server indexes are covered in greater detail in Chapter 3, “Introduction to SQL Server 7.0,” but here are a few things about them to keep in mind regarding upsizing.
First, SQL Server indexes are on columns only, never on expressions or UDFs, and indexes are always ascending. Therefore, any indexes that contain expressions such as NOT or UDFs or are descending will not be correctly upsized. Only the column names of the indexes willbe upsized, not the expressions, which is probably not what you expected.
Second, the physical order of a table is determined by the clustered index, only one of which, for obvious reasons, is allowed per table. This is different from a VFP primary index in that the physical order of a VFP table is not changed by the value of the column in the key for that index, but it is changed in a SQL Server table. If a clustered index exists on a table, then a SELECT with no ORDER BY clause will return records in the clustered index order; if no clustered index exists, then results are returned in an unpredictable order.
Finally, there are no SQL Server indexes similar to VFP’s so-called UNIQUE indexes. Though these will be upsized to non-clustered indexes in SQL Server, there is no uniqueness to them.
By default, the VFP tag names will be retained for the index names when upsized. However, if a tag name is a reserved word in SQL Server, then an underscore will be appended to the end of the name. For example, a tag named “level” would become an index named “level_”after upsizing.
Defaults
Defaults aren’t handled quite the same way in SQL Server and Visual FoxPro. In a VFP database, a default expression is assigned individually to a field. In SQL Server, defaults are handled either with constraints or with expressions that are created and then bound to a field. In this way, fewer expressions need to be created, as it is likely that multiple fields will share a default expression.
The Upsizing Wizard will create a SQL Server default for every field with a default expression unless the default expression is zero. If one or more fields have a zero default, then the Upsizing Wizard will create a default called UW_ZeroDefault and will bind it to each field thatneeds it. This default is also used for all VFP logical fields, which are upsized to SQL Server’s bit data type and bound to the UW_ZeroDefault default unless the logical field in the local database has a default setting the value to .T., in which case a default is created that sets thevalue to 1.
The Upsizing Wizard names defaults by using the prefix Dflt_ plus the table name
and field name separated by an underscore. Therefore, a default for detail.order_date would be named Dflt_detail_order_date. Names longer than SQL Server’s limit of 30 characters are truncated.
Expression mapping between VFP and SQL Server is illustrated in Table 3. The following expressions are the same in both VFP and SQL Server and require no conversion by the Upsizing Wizard:
• CEILING( )
• LOG( )
• LOWER( )
• LTRIM( )
• RIGHT( )
• RTRIM( )
• SOUNDEX( )
• SPACE( )
• STR( )
• STUFF( )
• UPPER( )
Table 3. Mapping of Visual FoxPro expressions to SQL Server expressions by the
Upsizing Wizard.
VFP expression
SQL Server expression
.T.
1
.F.
0
#
<>
.AND.
AND
.NOT.
NOT
.NULL.
NULL
.OR.
OR
=<
<=
=>
>=
ASC( )
ASCII( )
AT( )
CHARINDEX( )
CDOW( )
DATENAME(dw, ...)
CHR( )
CHAR( )
CMONTH( )
DATENAME(mm, ...)
CTOD( )
CONVERT(datetime, ...)
CTOT( )
CONVERT(datetime, ...)
DATE( )
GETDATE( )
DATETIME( )
GETDATE( )
DAY( )
DATEPART(dd, ...)
DOW( )
DATEPART(dw, ...)
DTOC( )
CONVERT(varchar, ...)
DTOR( )
RADIANS( )
DTOT( )
CONVERT(datetime, ...)
HOUR( )
DATEPART(hh, ...)
LIKE( )
PATINDEX( )
MINUTE( )
DATEPART(mi, ...)
MONTH( )
DATEPART(mm, ...)
MTON( )
CONVERT(money, ...)
NTOM( )
CONVERT(float, ...)
RTOD( )
DEGREES( )
SUBSTR( )
SUBSTRING( )
TTOC( )
CONVERT(char, ...)
TTOD( )
CONVERT(datetime, ...)
YEAR( )
DATEPART(yy, ...)
Relationships
SQL Server 7 has two different ways of handling relationships and referential integrity: triggers and declarative referential integrity constraints. The Upsizing Wizard can upsize the referential integrity constraints from a VFP database using either triggers or declarative referential integrity.
Figure 7 shows the default settings for upsizing, which is to not use declarative referential integrity. If you choose this option, then the Upsizing Wizard will write triggers that duplicate the functionality of referential integrity in Visual FoxPro. Table 4 shows how VFPreferential integrity is upsized when you choose this option.
Figure 7. Step 8 of the SQL Server Upsizing Wizard.
Table 4. Mapping by the SQL Server Upsizing Wizard of Visual FoxPro referential integrity to SQL Server triggers.
Integrity
Constraint
SQL Server trigger
DELETE
Cascade
Cascade DELETE trigger
DELETE
Restrict
Restrict DELETE trigger
INSERT
Restrict
Restrict INSERT trigger
UPDATE
Cascade
Cascade UPDATE trigger
UPDATE
Restrict
Restrict UPDATE trigger
When the Upsizing Wizard creates triggers for referential integrity, it names them by using the prefix Trig, followed by the letter D for DELETE triggers, I for INSERT triggers or U for
UPDATE triggers, followed by an underscore. The table name follows the underscore. So a
DELETE trigger on the employee table would be named TrigD_Employee.
If you check the Use declarative RI check box in Step 8, then no triggers will be created. Instead, the Upsizing Wizard will use declarative referential integrity. Declarative referential integrity, discussed in Chapters 1 and 3, prevents any changes from occurring that would break the reference and is equivalent to Restrict constraints in VFP. Declarative referential integrity is a part of the schema rather than a trigger. Without the option of declarative referential integrity, most SQL Server 7 DBAs would prefer creating a stored procedure for
deleting child records rather than relying on triggers for cascading deletes because the triggers can create performance issues.
Validation rules
The Upsizing Wizard treats rules much like defaults—a rule object is created and then bound to a column or data type. This reduces the number of rules if the same rule is required for multiple columns or types. An example might be the following rule, which prevents entry of valuesless than 1,000 or greater than 100,000:
CREATE RULE myrange
AS
@range >= 1000 AND @range <= 100000
Then the rule can be bound to a column by using a system stored procedure called
sp_bindrule:
EXEC sp_bindrule 'myrange', 'mytable.myfield'
The SQL Server Upsizing Wizard does not upsize VFP rules into SQL Server rules, though. Instead, it writes a trigger for the column with the rule, and the trigger calls a stored procedure that enforces the rule. For example, in the Tastrade database, the rule for the order.deliver_by column is converted to the following stored procedure:
CREATE PROCEDURE vrf_orders_deliver_by @status char(10) output AS IF @status='Failed'
RETURN
IF (SELECT Count(*) FROM orders
WHERE NOT (deliver_by>=order_date)) > 0
BEGIN
RAISERROR 44444 'Cannot be earlier than Order Date'
SELECT @status='Failed'
END ELSE
BEGIN
SELECT @status='Succeeded'
END
The Upsizing Wizard also creates update and insert triggers TrigI_Orders and
TrugU_orders for the orders table that, in turn, call the vrf_orders_deliver_by stored procedure
and pass the appropriate parameter. Although this is a rather unusual way to implement rules, it certainly works.
The naming convention for the triggers is the same as that defined previously for triggers created for referential integrity. For the stored procedures for field rules, the prefix vrf_ (validation rule field) is concatenated with the table name and column name, separated by underscores. Table validation rules begin with vrt_ (validation rule table), followed by the table name.
Changes made locally
Step 8 also allows you to specify what will happen locally—that is, on the VFP side. The options (Create upsizing report, Redirect views to remote data, Create remote views on tables, and Save password with views) are covered in the “Finished at last...” section later in this chapter.
Step 9 of the Upsizing Wizard allows you to finish the job of upsizing (see Figure 8). You can simply perform the upsize, which will execute all the choices you have selected in previous steps, by checking Upsize. After clicking Finish, the changes will actually be made on the server. If you check Save generated SQL, then the changes will not be made on the server, but all the SQL code required to create the changes will be written into one of the report tables described in the next section. The Upsize and save generated SQL option does both.
Figure 8. Step 9 of the SQL Server Upsizing Wizard.
‡
We recommend that you not use the Upsize-only option, because this option does not provide any opportunity to “tweak” the upsizing process. Further, the upsize can take a considerable amount of time to execute if
you have not chosen the “structure only, no data” option.
Finished at last? Modifying the results of the Upsizing Wizard
Well, the Upsizing Wizard may be finished at last, but you are not! Now you need to find out what upsized and what didn’t, what happened in your local DBC and what to do about both.
The Upsizing Wizard creates a project called Report.pjx that contains several tables and reports for each of those tables. Figure 9 shows the project and all the files contained in it after upsizing the Tastrade database. Do not ignore this project, which the Upsizing Wizard automatically creates and opens for you. Table 5 shows each of these reports.
Figure 9. The VFP Project Manager showing the project created by the SQL Server
Upsizing Wizard and the tables and reports associated with it.
Table 5. The reports created by the SQL Server Upsizing Wizard.
Report
Contents
rpterrs1.frx
Errors
rptfiel1.frx
Fields
rptinde1.frx
Indexes
rptrels1.frx
Relations
rpttabl1.frx
Tables
rptview1.frx
Views
Open the errors report and print it immediately—we guarantee that you will need it. The fields report can be huge, as it details every field in the database, along with pre- and post- conversion data types, defaults, rules and so forth. Even with the miniature Tastrade database, this report runs 24 pages. If some field-level object did not correctly upsize, then the report will note the errors associated with it. We believe that it is better to start with the errors report in the first place. If something in the errors report needs further explanation, then open the fields report for preview and work your way down to the field in question.
It is interesting to read the errors report, as it gives you a good feel for what kinds of things don’t upsize well. That, in turn, will help you learn more about SQL Server. A good example of this in the Tastrade database is the failure of many field validation rules to upsize. Themost common reason in this case is that many rules use the VFP EMPTY() function, which cannot
be upsized to SQL Server. Another good example is the orders.order_number default, which calls a UDF called newid(). The Upsizing Wizard is unaware that the newid() function already exists in SQL Server 7, and it attempts to upsize an illegal call to that function.
You also may find many errors in views. Typically, these errors are caused by differences in SQL syntax between SQL Server and VFP and are relatively easy to fix.
The tables in the project are used for creating the reports, with the exception of sql_uw.dbf. This table contains one row with one column, a memo field containing the T-SQL script generated by the Upsizing Wizard. This table will exist only if you chose to save the generatedSQL in the last page of the wizard. This script can be quite useful in helping you learn SQL Server. The script can even be used for deploying a system. See Chapter 10, “Application Distribution and Managing Updates,” for more information on using scripts to deploy databases.
The local database
Hopefully you made a copy of the local database, because after upsizing it may have been changed dramatically. All the VFP tables are still part of the database, as are the original views, the upsized views and single-table views of each upsized table. However, if you selected the appropriate options, your local views now point to remote data, new remote views were created for the upsized tables, and a new connection exists (called “Upsize”). Therefore, if you are planning to deploy any of this, you have some cleaning up to do first. If you want, you canopen the DBC and clean up the DBC manually. Our preference is to use code, because this approach is easier and more reliable.
If you have never used GenDBC, then this is a good time to become familiar with it. GenDBC is a program that is distributed with VFP and can be found in the HOME() + “tools\gendbc” directory. It creates a PRG that can recreate the structure of a VFP database. To run it,simply execute the following code in the Command Window:
DO (HOME() + "tools\gendbc\gendbc.prg")
We use GenDBC a lot, and not just because we would rather work with code for views than the visual tools. Many times, you will have to maintain your views through code, as the VFP View Designer simply will not allow you to edit many types of complex remote views. You can visually create the views that you want (and we certainly recommend doing so where possible), but it is possible that when you try to edit it, you will receive an error.
GenDBC to the rescue! When you run GenDBC, every database object is recreated in
code. It will create a function for each view, table and relation, and another function to generate the local referential integrity code. Table 6 lists the functions created and their use. If, for example, your DBC has a view named Category, then a function will be generated named MakeView_Category.
Table 6. The functions generated by GenDBC.
Function
Purpose
MakeView_
Recreate local and remote view.
MakeTable_
Recreate VFP table.
MakeRelation_
Recreate a VFP relation.
MakeRI_
Recreate the relational-integrity code.
At the top of the generated program is a set of calls to each of the functions generated. Figure 10 shows the VFP Procedures and Functions dialog for the PRG generated by GenDBC.prg for the VFP database after upsizing Tastrade. Take a close look at the first three. Table 7presents descriptions of each of these three functions.
Table 7. The GenDBC-generated functions for Category.
Function
Purpose
MakeView_CATEGORY
Remote view of the SQL Server table, created by
the Create remote views on tables option in the
Upsizing Wizard.
MakeView_CATEGORY_LISTING
Existing local view, redirected to the SQL Server tables,
rather than the VFP tables.
MakeView_CATEGORY_LISTING_LOCAL
Existing local view, renamed by appending _LOCAL to
the end.
Figure 10. The Procedures and Functions dialog for the VFP editor showing some of the procedures created by GenDBC for the upsized Tastrade.DBC. Note the grouping of views in threes: category was created by the Upsizing Wizard from the category table; category_listing was created by the Upsizing Wizard by converting the local view; and category_listing_local is the renamed local view.
What to do with these views? Look at the last view in Table 7—though it is helpful to have the “old” local view available to compare the results to the “new” remote view, you won’t be deploying the local database. Therefore, you’ll be deleting this one eventually. Regardless,here is the code for it:
CREATE SQL VIEW "CATEGORY LISTING_LOCAL" ;
AS SELECT category.category_name, category.description, category.picture
FROM tastrade!category
The first view in the list certainly isn’t needed either. However, if you will be reworking the system, you might want to modify this view a bit and keep it around. You will need to modify this view because, in its current form, it is not parameterized and will return all records in thetable. Here is the code for the non-parameterized view created by the Upsizing Wizard:
CREATE SQL VIEW "CATEGORY" ; REMOTE CONNECT "Upsize" ; AS SELECT * FROM category
This view can be quite useful if it is parameterized to return only a single record:
CREATE SQL VIEW "CATEGORY" ; REMOTE CONNECT "Upsize" ;
AS SELECT * FROM category WHERE category_id LIKE ?cCategory_id
The second view, CATEGORY LISTING, is the one that most closely matches the original local view. Here is the original local view:
CREATE SQL VIEW "CATEGORY LISTING" ;
AS SELECT category.category_name, category.description, category.picture ;
FROM tastrade!category
Except for the name, you can see that it is identical to the original local view. Here is the new view that has been redirected to the remote tables:
CREATE SQL VIEW "CATEGORY LISTING" ; REMOTE CONNECT "Upsize" ;
AS select category.category_name, category.description, category.picture ; FROM category
In Figure 10, you can see this pattern of three views per table repeated over and over again. In Figure 11 you can see that all the local tables are still there, too, but that they’ve been renamed by appending _LOCAL to the table name.
Figure 11. The Procedures and Functions dialog for the VFP editor showing local table generation procedures created by GenDBC for the upsized Tastrade.DBC.
So, what is the best way to deal with all this? We suggest opening the PRG and commenting out the function calls you don’t need. First get rid of all MakeTable, MakeRelation and MakeRI calls. You aren’t going to be using local data, so why bother keeping those
around? Also, comment out all the MakeView_…_LOCAL calls because, again, there is no local data.
That leaves the MakeView calls that create the remote views. Here we recommend being selective. Go through each one to decide whether you can use the view. If you’re not sure, keep the view because it is easier to delete a view later than to recreate it.
Once you’ve commented out all the unnecessary calls, move the PRG to a new, clean directory, set that directory as your default directory with SET DEFAULT and execute the modified PRG. Why? Because now it will create a new DBC with only those objects you didn’t comment out. Then run GenDBC again. Now your generated PRG will be much smaller, as it
no longer contains any of the functions you didn’t call.
For the remainder of your development on the project, this generated file will be your master for the DBC. You’ll check it, and not the DBC, into your source control program.
You’ll modify it, not the DBC, when you make changes. If you need to modify a view, work on the code in the PRG and then simply call the function you worked on. Sometimes, when wholesale changes have been made, you might want to simply delete the DBC from the disk
and run the entire generated PRG to recreate the DBC from scratch.
When you create new views, you should also do so in code in the originally generated PRG. You might look at the PRG and think that this could be a daunting task. After all, there are four DBSETPROP() calls for every field in every view! However, not all of those calls are necessary. Table 8 shows the calls to DBSETPROP() for each view and a brief description of when it is required.
Table 8. Calls to DBSETPROP() made for each field in a view.
Property
When required
KeyField
When the field is used as a key for updating.
Updatable
When the field must be updated.
UpdateName
When the default is incorrect—typically in views with joins where the same
column name exists in more than one source table.
DataType
When the default data type is incorrect, such as when you wish to use a
date rather than datetime.
The only property that typically needs to be changed for most fields is Updatable, which, by default, is set to .F. Instead of setting this property for each field, you can simply let a procedure set all of them to .T. for you, and then you can set individual fields to .F. ifnecessary. Here’s some code that will do that for you:
*-- Open the view to get all the fields USE (lcView) NODATA ALIAS propgetter lnCount = AFIELDS(laFields, "propgetter")
*-- Loop through all fields and set properties
FOR i = 1 TO lnCount
lcField = ALLTRIM(lcView) + "." + laFields[i, 1]
DBSETPROP(lcField, "Field", "Updatable", .T.)
ENDFOR
*-- Close view
USE IN propgetter
You will need to set the KeyField property to .T. for at least one column per table involved in the join. Typically there will be one or two fields in a view that need to be set to .T. As the default is .F., it is simple to write the one or two lines necessary to do this.
You may find it worthwhile to replace all the property calls that were generated by GenDBC. Why? If your company’s developers access a source-control database via the Internet and must sometimes use slow dial-up connections, you’ll find that reducing the size of the file really helps speed up this process. Also, by using a PRG instead of a DBC, you’ll dramatically reduce the amount that must be transferred over a slow connection.
Summary
In this chapter you learned about the SQL Server Upsizing Wizard in Visual FoxPro, how it works, what it does and how to deal with its results. Hopefully, even if you don’t use the Upsizing Wizard, you’ve picked up some tips that will help you get going in client/server development. In Chapter 7, you will learn about downsizing.
Posts
We need to move foxpro to sql server. It can improve the application performance.
ReplyDeleteVisual FoxPro to .Net
FoxPro to .Net
Visual FoxPro Migration
FoxPro Migration