Data Flask

Microsoft FoxPro and dBase.

Specifying Column Type

When creating or modifying a table using OleDb Admin, you can specify a specific FoxPro column type from a list.

foxpro column types
FoxPro provider types

This will be the same text (up to the -- comment) that will be provided to the CREATE TABLE command. Additional qualifiers can be added. For example,

D DEFAULT (DATE( ))

will specify that a field defaults to the date the record was appended.

If ISAUTOINCREMENT flag was set, the type

I AUTOINC

will be used. If KEYCOLUMN flag is also set, the type

I AUTOINC PRIMARY KEY

will be used. When Natural Order is selected as the Update Key in the Data Link dialog,

WHERE RECNO( )=nRow

will be used for Write Data Preview (SQL). Most providers produce this script when using Natural Order for UPDATE records. However with FoxPro, the script actually works.

PRIMARY key and UNIQUE (CANDIDATE) indexes can be created using DDL. Regular indexes need to be created using the INDEX ON command, which does not work using DDL. To create regular indexes, unload Flask.exe, set PreferOledbInterfaces to 1, create indexes with Write Data.

Indexes and columns with names longer than 10 characters can be truncated automatically. Subsequent updates will require that the .dfg also contain shortened column names, otherwise the columns will not match. To automatically recover the truncated names, use Reload All.

Sample Migration SQL

Load Customers from Nwind.mdb, as in the Crash Course Tutorial . Replace the connection string with a link to a FoxPro database, for example Provider=VFPOLEDB.1;Data Source=C:\DATA\FLASK.DBC . A Write Data command would create a table using:

CREATE TABLE [Customers] (
   CustomerID C(5) NOT NULL,
   CompanyName C(40) NOT NULL,
   ContactName C(30),
   ContactTitle C(30),
   Address C(60),
   City C(15), Region
   C(15), PostalCode
   C(10), Country
   C(15), Phone
   C(24),
   Fax C(24)
)

and create keys and indexes using

ALTER TABLE [Customers] ADD PRIMARY KEY CustomerID
USE [Customers]; INDEX ON CompanyName TAG [CompanyNam]; USE
USE [Customers]; INDEX ON City TAG [City]; USE
USE [Customers]; INDEX ON Region TAG [Region]; USE
USE [Customers]; INDEX ON PostalCode TAG [PostalCode]; USE

to create indexes and a primary key. After creating the table, Write Data Preview (SQL) would produce

-- updating Customers (customerid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax)
UPDATE [Customers] SET address='87 Duval St.'+CHR(13)+CHR(10)+'Suite 5' WHERE customerid='LETSS'
-- update complete (1 of 91 rows had changes)

after Polk were changed to Duval in cell 45,5 (LETSS, address). Note that the cell contains a newline, CHR(13)+CHR(10).

DML (INSERT, UPDATE) commands have a binary literal (i.e. 0hBEEF) length restriction of 255.

Sample Test Migration: CategoriesFoxpro.sql (text).