locked
Convert ADO recordset to a Foxpro DBF free table RRS feed

  • Question

  • Hi,

        I currently have a need to convert an ADO recordset to a DBF. I've seen some posts here on how to create and write to a DBF using C# in conjunction with the VFPOLEDB provider. I mocked some code up in VB6 similar to the example and I am able to open the dbf in VFP6 no problem. However, I'd like to find out what ADO to Foxpro field type conversions would work. The ADO recordset comes as a result of a query from SQL Server 2000, so there are bound to be some datatypes that will not convert natively. I've come up with this matrix so far based on some MSDN Library materials.

        SQL DataType              ADO DataType           Foxpro DataType
        ================================================================
        BigInt                    adBigInt                 F
        UniqueIdentifier          adGUID                   C (40)
        Char(n)                   adChar                   C (n)
        Varchar(n)                adVarChar                C (n)
        NChar(n)                  adWChar                  C (n)
        NVarchar(n)               adVarWChar               C (n)
        Money, SmallMoney         adCurrency               Y
        Decimal, Numeric          adNumeric                F
        Real                      adSingle                 F
        DateTime, SmallDateTime   adDBTimeStamp            T
        Float                     adDouble                 B
        Integer                   adInteger                I
        Smallint                  adSmallInt               I
        TinyInt                   adUnsignedTinyInt        I
        Bit                       adBoolean                L

    Is there anything that would not work from this matrix? I purposely ommitted SQL text and ntext fields because I do not intend to support MEMO fields. I also noticed in the example that date values had to be prefixed with curly braces. Would that still be needed if I were to declare parameters in the command object and just set the parameter type to addbtimestamp and set the value equivalent to the one in the recordset (I'm thinking of using the parameters collection as a way to simplify my insert statements since each recordset could have 100 rows of 20 fields each). In addition, I am working on conversion code that would produce a CREATE TABLE DDL given the Fields collection of a Recordset object. Is there a special syntax I need to observe when trying to create F and B field types? I appear to be able to create an F(18,4) type field, however, I can't seem to figure out what to do with the Double field type. I've switched from specifying precision and numeric scale to not specifying them at all and I receive syntax errors both ways. Any help or example would be appreciated.

    Regards,
    Tristan

    Wednesday, November 1, 2006 4:52 PM

Answers

  • I wouldn't use F at all. Instead use double ( syntax b(4) where 4 denotes decimal places).  For datetime you could use D or T ( D if you're only concerned about date part and not time). varchar is supported but I would still prefer c(n). For GUID size is from 16 (NOCPTRANS - binary) to 38 whichever you might prefer.

    It would be easier if you instead used VFP or VB/VC#.Net. Check MS VFP site downloads. There is VFPCOM.DLLwhich might help (but no idea if it really can and how from VB).

    Just my curiosity why would someone want to create a free table from SQL2000 db.

    PS: On this machine I don't have any developer tools (but there is MSSQL2000 dev. edition and/2005 express) and I just downloaded VFPOLEDB from MS VFP site, and in 5-10 mins I could create

    c:\temp\vfpcustomers.dbf

    which is almost a copy of Northwind.dbo.customers. I verified it was good VFP data (no VFP here remember) using notepad to code in a quick C# code, connected via VFPOLEDB and listed data to console. Voila it worked:)

    Here is how I did it and gotchas encountered along the way:

    1) Used SQL2000 DTS to export data. However directly selecting offered steps in DTS dialogs failed for various reasons (it doesn't write correct VFP syntax, doesn't take into account free tables can't have longfieldnames etc). So I took an easierstep and instead of  selecting "copy tables" I specified query:

    select customerID, companyName as company, contactTitle as Title,Address,Phone,Fax,City,State,Zip,Country from customers

    In next step I made sure to specify C for varchar fields (on my first try varchars were simply not exported - all empty).

    I also chose to save the script to a .bas file so now I know how you could do it totally programmatically from within VB but code is rather long to post here. Just export one table as a sample and you'd see its code.

     

     

     

    Wednesday, November 1, 2006 9:54 PM

All replies

  • I wouldn't use F at all. Instead use double ( syntax b(4) where 4 denotes decimal places).  For datetime you could use D or T ( D if you're only concerned about date part and not time). varchar is supported but I would still prefer c(n). For GUID size is from 16 (NOCPTRANS - binary) to 38 whichever you might prefer.

    It would be easier if you instead used VFP or VB/VC#.Net. Check MS VFP site downloads. There is VFPCOM.DLLwhich might help (but no idea if it really can and how from VB).

    Just my curiosity why would someone want to create a free table from SQL2000 db.

    PS: On this machine I don't have any developer tools (but there is MSSQL2000 dev. edition and/2005 express) and I just downloaded VFPOLEDB from MS VFP site, and in 5-10 mins I could create

    c:\temp\vfpcustomers.dbf

    which is almost a copy of Northwind.dbo.customers. I verified it was good VFP data (no VFP here remember) using notepad to code in a quick C# code, connected via VFPOLEDB and listed data to console. Voila it worked:)

    Here is how I did it and gotchas encountered along the way:

    1) Used SQL2000 DTS to export data. However directly selecting offered steps in DTS dialogs failed for various reasons (it doesn't write correct VFP syntax, doesn't take into account free tables can't have longfieldnames etc). So I took an easierstep and instead of  selecting "copy tables" I specified query:

    select customerID, companyName as company, contactTitle as Title,Address,Phone,Fax,City,State,Zip,Country from customers

    In next step I made sure to specify C for varchar fields (on my first try varchars were simply not exported - all empty).

    I also chose to save the script to a .bas file so now I know how you could do it totally programmatically from within VB but code is rather long to post here. Just export one table as a sample and you'd see its code.

     

     

     

    Wednesday, November 1, 2006 9:54 PM
  • Hi,
        Thanks for your advise regarding Double datatype. I'm actually going through this exercise not out of want but more out of necessity. We still have some third party Windows barcode printing/labeling programs that are executed through command line with some parameters. The label definitions derive their data from DBF. Users do not want to change anything with the label definitions because it is time consuming and would require end-customer qualifications/validations. These labels are printed in-line with an application we wrote, so I have to be able to generate these dbfs at will, based on screen values that the users have before them (for example, if they are at an order screen and they want to print labels for the order at hand, they click print and I would have to generate this dbf).

    Regards

    Wednesday, November 1, 2006 10:21 PM
  • I see. Then be carefull. Saying they need free tables it might be possible they actually meant foxpro 2.x style tables (and actually creating them just with plain lowlevel I/O is easier than all this ADO stuff (for me of course:).

    I forgot to tell you with free tables the real problem is to find fieldnames which are at most 10 in length.

    Wednesday, November 1, 2006 10:55 PM
  • Funny you'd mention that route of creating foxpro 2.x style tables using low-level I/O. I actually found it rather tedious, error-prone, and hard to debug. I spent 4 days using this method only to get nowhere with invalid table errors. The specification for a dbf file is out there but it seems to have been appended to by several parties that have extended the format over time, making it very difficult to comprehend. I also could not find a handy map for translating the ADO type value to workable foxpro datatypes without losing precision. I did attempt to use the Precision, Size, and NumericScale property of ADO to no avail as there doesn't seem to be any reference on how to translate them into foxpro-compatible values. I tried to use a third party tool called RSConvert http://www.motobit.com/help/RSConv/database.asp which supposedly does this for me in a painless manner but it appears to be using ODBC DSNs behind the scenes and croaks with an unintelligible error. Quite useless for being labeled as commercial product. In any case, I would be open to any suggestions. My test case involves an 18 field table with 2 rows where each field represents one sql datatype. I explicitly did not include support for text, ntext, varbinary, binary, sql_variant, and image. Each row contains the min/max values supported by each datatype where applicable. My test harness just does a select * FROM this table, looping through each column and field (normalizing the field to 10 chars) and using VFPOLEDB to create the dbf. I then manually inspect the values in the dbf to see if I truncated any data. Granted, any character data is limited only to 254 characters.
    Thursday, November 2, 2006 3:16 PM
  • I intentionally included "for me" note:) I really find it easy and fast to create a fox2x table lowlevel (and used in a project where I needed to create and fill a dbf from within C).

    If you're not a purist you can follow this guide to find compatible types (not for fox2x - VFP free table structure):

    c(n) - for any character data up to 254 bytes.

    *Saying character data it doesn't really need to be all printable chars (ASCII 0-255 acceptable). However if you use it for binary data then be sure
    * to include NOCPTRANS clause for that field to prevent code page translation

    d  - for all datetime when you're only concerned about date part

    t - for datetime

    i - 32bits integer. All SQL integral types including bit datatype

    b(n) - any numeric which wouldn't fit to 32bits range or need decimal precision. n precision

    y - money type. But I'd choose this one for any numeric up to 4 decimal places precision instead of b(n)

    L - bit datatype if a cast to T/F is done (or if explicitly ado field is set to .t./.f. for 1/0, true/false respectively). Otherwise use I to be safe

    m - character data over 254 (namely 0 bytes to 2Gb - 2Gb is total file limit). text,ntext,image,xml,varbinary ... virtually anything fits here

    ***********************************

    You can actually store anything to C(n) and M types but naturally would need conversion functions when used from VFP code or some DML (ie: if you store "2000/01/31" style dates to a c(10) field it takes only 2 lines of code to make that field into a date field). Advantage is that it's available whenever you're unsure temporarily what type to use or things like varchar which foxpro doesn't support - (there is a varchar support but that's not a true varchar support, more like a synonym).

    PS: I think you're trying to stay away from .Net. If not possibilities there are a lot including SQL2005 CLR-hmm sounds like a good candidate for a CLR procedure reason:). Also probably using excel in between is not an option. Otherwise you could put into an excel sheet and saveas dBaseIII (fox2x compatible).

    Thursday, November 2, 2006 5:41 PM
  • Thanks for the guide btw. I will keep this post handy. I'm definitely not adept with C so I stay away from that whenever I can. The code I have works beautifully now without having to resort to low-level I/O. I fudged a little and converted any non-integer numbers to C(40) such as money, smallmoney, numeric, decimal, float, real, and double. The resulting dbf is definitely not fox 2.x format (which is what I need), but rather, one of the newer vfp formats. Is there a way to specify in the VFPOLEDB connection string that I want the dbf in FOX2X format? If there isn't, is there an alternative way using a different ODBC provider? I had to convert the resulting dbf using VFP6 and exporting as FOX2X, to which it echoed COPY TO <destination fox2x dbf> TYPE FOX2X command. I suppose when there aren't anymore options, I may have to write a Foxpro program that takes in a command line parameter and simply converts the dbf into fox2x type and call that from VB6 code. I actually am writing this code both ways (VB6 and VB.Net) and the only major difference there is the actual object used to issue DDL and DML statements (ADODB.Command vs. OLEDBCommand). However, the client that will be consuming this component I'm writing is still VB6 and I found that it is very difficult for me and for others to debug Interop assemblies as you cannot step through them in VB6 and find the offending lines of code. This is a big problem when analyzing and reproducing production issues.
    Thursday, November 2, 2006 6:56 PM
  • Then you might try an old ODBC driver like:

    "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;dbq=c:\temp;"

     AFAIK that's a fully compatible with fox2x dbf.

    "You may have to write" ??? You mean you had VFP to write it in the first place? Then do it. VFP can take SQL server connection string and query string, create the table or you. It is as little as 5 lines of code (no error checking, fieldnames are already processed for length in sql etc). ie:

    lparameters tcSqlConStr, tcSQL, tcOutputFileName
    lnHandle = SQLStringConnect(m.tcSQLConstr)
    SQLExec(m.lnHandle,m.tcSQL,"_result")
    SQLDisconnect(m.lnHandle)
    copy to (m.tcOutputFileName) type fox2x

    Just compile an exe from it and call from VB. No need to mess with COM and interoperability.

    Thursday, November 2, 2006 11:17 PM
  • I tried this method too, except I think the DriverID might have been different. As a result, my existing CREATE TABLE syntax ended up with an error but it is not telling me which part is causing the error. There seems to be a dearth of useful SQL DDL and DML reference guide for Fox 2.x or Dbase 3 or Dbase 4 on the internet when using the ADO to ODBC method. I do have VFP6, but we're not distributing applications written with it anymore. Come to think of it, i also had my label user test various versions of DBF files I had originally created using VFPOLEDB. I had exported them to FOX2X format and FOX2PLUS format (supposedly equivalent to DBASE IV when exported via VFP6). However, they told me that it's not working. I'm going to install their antiquated Label Matrix software (www.strandware.com) to see for myself why it's not working. From the versions that we currently have installed, it appears that only DBase III (from the DBF family of databases) is supported. That is quite a trip in itself and I'm so close to just pulling the plug on this effort (because of so many variations to the DBF format itself) were it not for the thousands of labels that they would have to recertify with the customers as a result of switching over to a better client/server product called Loftware.

    Now.... I actually would use VFP6 if the problem were it really feasible. However, the ActiveX component(s) we are developing are all in VB6 and they use a combination of COM+, MSMQ, etc. to pull everything together. The clients that consume them are also in VB6. Hence, VFP6 is out of the picture unless I'm only shell'ing a VFP6 compiled executable to convert a VFP-style dbf from to another FOX2X or DBASE IV.

    Friday, November 3, 2006 2:31 AM
  • I see. Seeing it yourself you should be able to solve that easily. I feel that you'd end up lowlevel creation:)
    Friday, November 3, 2006 10:17 AM