Monday, November 19, 2007

Update for Sedna Upsizing Wizard

Someone recently emailed me about a couple of changes they wanted in the new Upsizing Wizard in Sedna. One was the ability to select Varchar(MAX) as the data type to use when upsizing a memo field. The other was the ability to use a different date in the place of blank VFP dates; the default in the Upsizing Wizard is 01/01/1900 but they wanted to use 12/31/1899 instead.

The first change simply involved adding a new record to TypeMap.DBF, the table containing the type mappings. Set LocalType to "M" (for Memo), FullLocal to "memo", RemoteType to "varchar(max)", and Server to "SQL Server". Leave the logical fields as .F., especially VarLength, which determines whether the user is prompted for a field length.

The second was a little more work. First, BulkXMLLoad.PRG needs a change to support this because it used a hard-coded value. Change the statement setting ltBlank to:

ltBlank = iif(vartype(ttBlank) $ 'TD', ttBlank, SQL_SERVER_EMPTY_DATE_Y2K)
and add the following statement after the LOCAL statement:

#include Include\AllDefs.H
Second, JimExport (interesting method name!) in WizUsz.PRG also used a hard-coded value, so change the assignment to lcEmptyDateValue in the first CASE statement to:

lcEmptyDateValue = "IIF(EMPTY(" + ALLT(lcCursorName)+'.'+ALLT(aTblFields[ii,1])+ "), " + ;
transform(This.BlankDateValue) + ","
Finally, to change the actual date, change these two constants in AllDefs.H to the desired values:
#DEFINE SQL_SERVER_EMPTY_DATE_Y2K {^1900-01-01}
#DEFINE SQL_SERVER_EMPTY_DATE_CHAR "01/01/1900"
Rebuild UpsizingWizard.APP and you're ready to go.

1 comment:

Anonymous said...

The top of the JimExport method says:

*Thanks Jim Lewallen for this code (or the important and bug-free parts of it anyway)

Google Groups turns up a few messages from him in the 92-96 time frame, and he evidently had some expertise in client/server. I couldn't find much since, but he is immortalized forever in code!