none
Numeric field width in DBF file inconsistent when created with oleDB (.NET) RRS feed

  • Question

  • OleDB always add one more digit to DBF numeric fields when we create them. A command like this:CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(1,0) NULL) will create a field that can contains 2 digits (or one digit and a minus sign). What's funny is that I can ask for a length of zero like this NUMERIC(0,0) and it's going to create a field with a length of 1. 

    I couldn't find any documentation regarding this behavior.

    Is it specific to the use of oleDB to create DBF or the same can happen with some other DB?

    Is the extra digit added by oleDB only to handle the minus sign?

    Is this behavior consistent? I mean, can I just subtract one to the width when I create the table?

    Someone confirmed this issue on stackoverflow:

    http://stackoverflow.com/questions/3551918/numeric-field-width-in-dbf-file-inconsistent-when-created-with-oledb-net

    Tuesday, August 24, 2010 7:28 PM

Answers

  • Hi,
    First, you are using a older version of the VFP OLE DB provider.   You are using version 9.0.0.3504. The most recent build of the provider is 9.0.00.5815.  You can download it from here:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

    However, updating the provider is probably not going to change this behavior.

    So, I assume you are trying to make a numeric field  one digit in length?  This does not seem possible. If I run this command in the VFP development environment, 
    CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(0,0) NULL)

    I get the error:
    "Field width or number of decimal places is invalid."

    So, creating such a field, even in VFP, is not possible.

    This article, which talks about the VFP ODBC driver mentions behavior very similar to what you see:
    150434 PRB: Field Width Increases When Use Visual FoxPro ODBC Driver
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;150434

    Both the VFP ODBC driver and the VFP OLE DB Provider are compact minimal versions of the VFP database engine.  Neither has the full functionality of VFP Development environment.  (Please note that the VFP ODBC driver is a deprecated technology.  We no longer recommend anyone use it.  You should use the VFP OLD DB provider.)

    Basically, what you are seeing seems to be design behavior.


    Thanks!


    150434 PRB: Field Width Increases When Use Visual FoxPro ODBC Driver
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;150434

    This seems to explain it.  While the article talks about the VFP ODBC driver, the same thing applies to the OLE DB Provider.  (Please note that the VFP ODBC driver is a deprecated technology.  We no longer recommend anyone use it.  You should use the VFP OLD DB provider.)


    Need to update the VFP OLE DB provider to build .5815. 

    the OLE dB Provider is simply a small portion of Visual FoxPro.  So, I ran his command in VFP :

    CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(1,0) NULL)

    it works. 
    however, this command
    CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(0,0) NULL)
    throws this error:
    Field width or number of decimal places is invalid.

     

    • Marked as answer by Simon Tanguay Friday, September 3, 2010 8:05 PM
    Wednesday, September 1, 2010 9:07 PM

All replies

  • Which provider are you using? If it's the Jet Provider then it should create a Decimal data type with precision of 1 (number of total digits) and a scale of 0 (total digits to the right of the decimal point).

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, August 25, 2010 7:44 PM
  •  

    We actually use Visual FoxPro OLE DB Provider (vfpoledb.dll, version 9.0.0.3504). Connection string:

    "Provider=vfpoledb.1;Locale Identifier=1252;Collating Sequence=machine;Data Source="

    We want those DBF to be in the FoxPro format and as far as I know, the Jet provider only support dBASE IV format. We use the Jet provider to export to dBASE IV format too with that connection string:

    "Provider=Microsoft.Jet.OLEDB.4.0;Locale Identifier=1252;Extended Properties=dBASE IV;User ID=Admin;Password=;Data Source="

    With that provider, we always end up with field of size 20 and precision 5 no matter how we defined the field when we create the table.  I assumed that it was normal in the dBASE IV format but I'm not longer sure.

    The way I understand it is that the foxpro driver add one to the size to make space to the minus sign. The thing is that this extra size can be used to put another digit too and we end up with a bigger number than what should be allowed. Also, if the precision is not zero (ex: NUMERIC(3,2)) I will end up with a size of 5 in the file. Basically, the driver add one for the minus sign and one for the comma.

    • Proposed as answer by billb08 - MSFT Wednesday, September 1, 2010 9:02 PM
    Thursday, August 26, 2010 8:25 PM
  • Hi,
    First, you are using a older version of the VFP OLE DB provider.   You are using version 9.0.0.3504. The most recent build of the provider is 9.0.00.5815.  You can download it from here:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

    However, updating the provider is probably not going to change this behavior.

    So, I assume you are trying to make a numeric field  one digit in length?  This does not seem possible. If I run this command in the VFP development environment, 
    CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(0,0) NULL)

    I get the error:
    "Field width or number of decimal places is invalid."

    So, creating such a field, even in VFP, is not possible.

    This article, which talks about the VFP ODBC driver mentions behavior very similar to what you see:
    150434 PRB: Field Width Increases When Use Visual FoxPro ODBC Driver
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;150434

    Both the VFP ODBC driver and the VFP OLE DB Provider are compact minimal versions of the VFP database engine.  Neither has the full functionality of VFP Development environment.  (Please note that the VFP ODBC driver is a deprecated technology.  We no longer recommend anyone use it.  You should use the VFP OLD DB provider.)

    Basically, what you are seeing seems to be design behavior.


    Thanks!


    150434 PRB: Field Width Increases When Use Visual FoxPro ODBC Driver
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;150434

    This seems to explain it.  While the article talks about the VFP ODBC driver, the same thing applies to the OLE DB Provider.  (Please note that the VFP ODBC driver is a deprecated technology.  We no longer recommend anyone use it.  You should use the VFP OLD DB provider.)


    Need to update the VFP OLE DB provider to build .5815. 

    the OLE dB Provider is simply a small portion of Visual FoxPro.  So, I ran his command in VFP :

    CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(1,0) NULL)

    it works. 
    however, this command
    CREATE TABLE [file1.DBF] ( [MY_FIELD] NUMERIC(0,0) NULL)
    throws this error:
    Field width or number of decimal places is invalid.

     

    • Marked as answer by Simon Tanguay Friday, September 3, 2010 8:05 PM
    Wednesday, September 1, 2010 9:07 PM
  •  

    The behavior described for the knowledge base 150434 about  VFP ODBC driver is exactly the same as I encountered with the OleDB driver so that explain it.

    Friday, September 3, 2010 8:12 PM
  • So Bill (billb08), it appears (if I've read things right) that if I create a numeric field (using "CREATE TABLE"), then VFPOLEDB adds 1 to the length (to allow for the sign).

    I never had Foxpro, but did use Clipper years ago.  I'm under the impression that VFP doesn't add 1 to the length portion of the field header.  This infers that "CREATE TABLE" works differently in VFP and VFPOLEDB.  Why would this be??  IMHO, we should be able to create identical databases (database headers) using the same CREATE whether in real VFP or using VFPOLEDB.

    Tuesday, September 28, 2010 8:29 PM