XZip: Free Zipping Utility

In a message on the Universal Thread, Frank Cazabon pointed out a free zipping utility named XZip. I downloaded and installed it today and already like it. It's a 138K DLL COM object, so it must be registered when you deploy it.

It's really easy to use: simply instantiate XStandard.Zip and call the Pack method to add a file or folder to a ZIP file (the file is created if it doesn't exist) or Unpack to extract a file or folder. There are also methods to delete or move files or folders, and one that returns a collection of items in a ZIP file.

Here's some sample code:

#define tFolder 1
#define tFile 2

loZIP = createobject('XStandard.Zip')
loZIP.Pack('SomeFile.txt', 'MyZip.zip')
loItems = loZIP.Contents('MyZip.zip')
for each loItem in loItems
  ? 'Name:', loItem.Name
  ? 'Date:', loItem.Date
  ? 'Path:', loItem.Path
  ? 'Size:', loItem.Size
  ? 'Type: ' + iif(loItem.Type = tFolder, 'Folder', 'File')
next

Vacation Time

Today's my last day at work for three weeks. I haven't taken a three-week vacation in four years; it's always been a week here or ten days there. I'm really looking forward to this; I'm mentally pretty tired right now and need a break, plus we have tons of fun planned.

We (my wife Peggy, son Nick, and I) fly to New York City and stay with friends in Connecticut for a couple of days. We then all drive to a beach house in NC, with an overnight stay in Washington, DC, which we've never visited before. After a week swimming, golfing, and eating and drinking too much, we drive back to CT, visiting with Tamar Granor and her husband on the way. We plan to do some sightseeing in NYC for a few days, then head home and take a few days to decompress from our vacation.

Another interesting aspect of this vacation is it's the first one since the last three-weeker that I'm not taking my laptop. I made a deal with my friend Tracey from Dallas, who will also be at the beach house with her family (three families altogether) that I wouldn't bring mine if she doesn't bring hers. She even more rabidly addicted to email than I am, so it'll be harder on her than it will on me. We'll see if she actually arrives sans computer or not, given the temptation of high-speed Internet access in the house.

What started as three friends (Betsy, Tracey, and I) who met in graduate school in Dallas in 1980 has expanded to three families who vacation together almost every year. The great thing is that you could take any two people out of the group, including kids, and they'd have a great time together. That's pretty rare in my experience. Like the saying goes, old friends are the best friends.

SQLXMLBulkLoad Rocks!

For reasons that will become obvious in September (sly grin), I've been working a lot lately on uploading data to SQL Server 2005. There are a variety of mechanisms you can use to do this, but all of them have their shortcomings under certain conditions. For example, using a series of INSERT statements is slow while bulk insert doesn't work with memo fields. While looking for something else in SQL Server Books Online, I happened across a topic on bulk XML loading. After playing with it for a while, it seemed pretty easy to do, so I did some testing on a relatively large file (365,741 records) containing memo fields (which means I couldn't use bulk insert). Using other mechanisms, it took more than two hours to load the data into a SQL Server table. Using bulk XML load, it took 11 minutes. That's a 90% improvement in speed. I love making things faster!

Using bulk XML load is easy: you instantiate SQLXMLBulkLoad.SQLXMLBulkload.4.0, set its ConnectionString property to an OLE DB connection string, set some other properties appropriately (for example, KeepNulls to .T. to insert null rather than default values for missing column values), and call Execute, passing it the name of a schema file and the name of the XML data file. Execute throws an error if there's something wrong with either file, and import errors are logged to a file whose name is in the ErrorLogFile property.

There's one slight wrinkle in working with VFP data: while the CURSORTOXML() function can create a schema file, it needs to be tweaked slightly to work with bulk XML load. Also, DateTime fields have to be flagged with the sql:datatype="dateTime" attribute (the latter was documented, the former took some trial and error to resolve).

Here's a generic program that will load the data from an open VFP cursor into an existing SQL Server table. You can download it from http://www.stonefield.com/articles/other/bulkxmlload.zip.

*==============================================================================
* Function:        BulkXMLLoad
* Purpose:         Performs a SQL Server bulk XML load
* Author:          Doug Hennig
* Last revision:   07/06/2006
* Parameters:      tcAlias    - the alias of the cursor to export
*                  tcTable    - the name of the table to import into
*                  tcDatabase - the database the table belongs to
*                  tcServer   - the SQL Server name
*                  tcUserName - the user name for the connection (optional:
*                    if it isn't specified, Windows Integrated Security is
*                    used)
*                  tcPassword - the password for the connection (optional:
*                    if it isn't specified, Windows Integrated Security is
*                    used)
* Returns:         an empty string if the bulk load succeeded or the text of
*                    an error message if it failed
* Environment in:  the alias specified in tcAlias must be open
*                  the specified table and database must exist
*                  the specified server must be accessible
*                  there must be enough disk space for the XML files
* Environment out: if an empty string is returned, the data was imported into
*                    the specified table
*==============================================================================


lparameters tcAlias, ;
  tcTable, ;
  tcDatabase, ;
  tcServer, ;
  tcUserName, ;
  tcPassword
local lnSelect, ;
  lcSchema, ;
  lcData, ;
  lcReturn, ;
  loException as Exception, ;
  lcXSD, ;
  loBulkLoad


* Create the XML data and schema files.


lnSelect = select()
select (tcAlias)
lcSchema = forceext(tcTable, 'xsd')
lcData   = forceext(tcTable, 'xml')
try
  cursortoxml(alias(), lcData, 1, 512 + 8, 0, lcSchema)
  lcReturn = ''
catch to loException
  lcReturn = loException.Message
endtry


* Convert the XSD into a format acceptable by SQL Server. Add the SQL
* namespace, convert the  start and end tags to ,
* use the sql:datatype attribute for DateTime fields, and specify the table
* imported into with the sql:relation attribute.


if empty(lcReturn)
  lcXSD = filetostr(lcSchema)
  lcXSD = strtran(lcXSD, ':xml-msdata">', ;
    ':xml-msdata" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">')
  lcXSD = strtran(lcXSD, 'IsDataSet="true">', ;
    'IsDataSet="true" sql:is-constant="1">')
  lcXSD = strtran(lcXSD, '<xsd:choice maxOccurs="unbounded">', ;
    '<xsd:sequence>')
  lcXSD = strtran(lcXSD, '</xsd:choice>', ;
    '</xsd:sequence>')
  lcXSD = strtran(lcXSD, 'type="xsd:dateTime"', ;
    'type="xsd:dateTime" sql:datatype="dateTime"')
  lcXSD = strtran(lcXSD, 'minOccurs="0"', ;
    'sql:relation="' + lower(tcTable) + '" minOccurs="0"')
  strtofile(lcXSD, lcSchema)


* Instantiate the SQLXMLBulkLoad object, set its ConnectionString and other
* properties, and call Execute to perform the bulk import.


  try
    loBulkLoad = createobject('SQLXMLBulkLoad.SQLXMLBulkload.4.0')
    lcConnString = 'Provider=SQLOLEDB.1;Initial Catalog=' + tcDatabase + ;
      ';Data Source=' + tcServer + ';Persist Security Info=False;'
    if empty(tcUserName)
      lcConnString = lcConnString + 'Integrated Security=SSPI'
    else
      lcConnString = lcConnString + 'User ID=' + tcUserName + ;
        ';Password=' + tcPassword
    endif empty(tcUserName)
    loBulkLoad.ConnectionString = lcConnString
*** Can set the ErrorLogFile property to the name of a file to write import
*** errors to
    loBulkLoad.KeepNulls        = .T.
    loBulkLoad.Execute(lcSchema, lcData)
    lcReturn = ''
  catch to loException
    lcReturn = loException.Message
  endtry


* Clean up.


  erase (lcSchema)
  erase (lcData)
endif empty(lcReturn)
select (lnSelect)
return lcReturn