none
FoxBase+/dBASE III PLUS and decimal numbers RRS feed

  • Question

  • For hist(o|e)rical reasons I need to produce some .dbf files (the old ones seem to be in "FoxBase+/dBASE III PLUSE, no memo" format, but maybe a little newer format would still work). Accorging to some Google searches I now have (shortened) :

    using (var dBaseConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE III;Data Source=" + TEMP + ";")) {
    	dBaseConnection.Open();
    	
    	string sql = "CREATE TABLE WKLST (posta char(6), some numeric(8,0), other numeric(15,2))";
    	var cmd = new OleDbCommand(sql, conn);
    	cmd.ExecuteNonQuery();
    
    	var insert = new OleDbCommand("insert into WKLST (posta, some, other) VALUES (?, ?, ?)", conn);
    	
    	var posta = new OleDbParameter();
    	posta.ParameterName = "posta";
    	posta.DbType = DbType.AnsiString;
    	posta.Direction = ParameterDirection.Input;
    	insert.Parameters.Add(posta);
    
    	{
    		var parametr = new OleDbParameter();
    		parametr.ParameterName = "some";
    		parametr.DbType = DbType.Decimal;
    		parametr.Precision = 8;
    		parametr.Scale = 0;
    		parametr.Direction = ParameterDirection.Input;
    		insert.Parameters.Add(parametr);
    	}
    	{
    		var parametr = new OleDbParameter();
    		parametr.ParameterName = "other";
    		parametr.DbType = DbType.Decimal;
    		parametr.Precision = 15;
    		parametr.Scale = 2;
    		parametr.Direction = ParameterDirection.Input;
    		insert.Parameters.Add(parametr);
    	}
    
    	insert.Parameters[0].Value = "1";
    	insert.Parameters[1].Value = 0;
    	insert.Parameters[2].Value = 2615207.7m;
    	insert.ExecuteNonQuery();
    }

    There are two problems. First according to DBF Viewer2000 v4.45 both the decimal colums are (19,5) even though the create table statement specified different scale and precission.

    Second, more important, the ExecuteNonQuery() throws OleDbException("Data type mismatch in criteria expression."). Seems the problem is the decimal point because as long as all the numbers are integers everything works fine. Even though what I pass is an integer valued decimal, not an integer. Something like:

    insert.Parameters[i-1].Value = row.Field<decimal>(i);

    I'll most probably get away with the first problem, but the second is a showstopper. 

    In case it's important ... my computer where I'm running this for now is set to the Czech locale, but I tried both to specify "Locale Identifier = 1029;" or "Locale Identifier = 1033;" in the connection string and set the current thread locale to EN-US before creating the OleDbConnection.

    Should I use a different driver? Or add something to the connection string?

    Thanks for any help, Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Tuesday, March 6, 2012 4:11 PM

Answers

  • Hello, I just did a simple create table using OleDb

    CREATE TABLE customer (Identifier int, First_Name char(50),Last_Name char(50),Address char(50),City char(50),Birth_Date date, Balance decimal(18,2))

    The schema read from a DataReader on the table

    <?xml version="1.0" standalone="yes"?>
    <DocumentElement>
      <SchemaTable>
        <ColumnName>IDENTIFIER</ColumnName>
        <ColumnOrdinal>0</ColumnOrdinal>
        <ColumnSize>8</ColumnSize>
        <NumericPrecision>15</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.Double, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>5</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>IDENTIFIER</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>FIRST_NAME</ColumnName>
        <ColumnOrdinal>1</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>FIRST_NAME</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>LAST_NAME</ColumnName>
        <ColumnOrdinal>2</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>LAST_NAME</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>ADDRESS</ColumnName>
        <ColumnOrdinal>3</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>ADDRESS</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>CITY</ColumnName>
        <ColumnOrdinal>4</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>CITY</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>BIRTH_DATE</ColumnName>
        <ColumnOrdinal>5</ColumnOrdinal>
        <ColumnSize>8</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>7</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>BIRTH_DATE</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>BALANCE</ColumnName>
        <ColumnOrdinal>6</ColumnOrdinal>
        <ColumnSize>8</ColumnSize>
        <NumericPrecision>15</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.Double, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>5</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>BALANCE</BaseColumnName>
      </SchemaTable>
    </DocumentElement>
    Note that the data types are different than when created. Next since I do not have a dBase view I looked at the newly created DBF file using a binary viewer, the numeric fields are type N while string fields are of type C. Also imported the DBF into MS-Access 2007 and noted that the schema matched that of the schema file above.

    I noticed via Google a similar issue http://www.codeguru.com/forum/showthread.php?t=508859 which does not adhere to a specified scale via OleDb. Yet another one http://p2p.wrox.com/vb-databases-basics/42921-how-create-dbf-db-set-size-field.html with the same issue. I would think it is safe to say it is not possible to change this behavior in regards to OleDb.


    KSG

    Tuesday, March 6, 2012 6:53 PM
  • Your code worked fine for me with one exception. The column name "some" is an Access reserved word and should be changed if you are going to use Jet with the dBase ISAM driver. I also used DEC instead of NUMERIC but I believe they are the same. 

    As soon as I changed the column name I had no issues. Unfortunately I was unable to check precision, but the the column values displayed properly when linking the resulting table through Microsoft Access.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 6, 2012 7:08 PM
  • As I needed the resulting .dbf to have the right column sizes I ended up dumping OleDb and ended up using 

    http://fastdbf.codeplex.com/


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    • Marked as answer by JendaPerl Monday, March 19, 2012 8:12 AM
    Monday, March 19, 2012 8:12 AM

All replies

  • Hello, I just did a simple create table using OleDb

    CREATE TABLE customer (Identifier int, First_Name char(50),Last_Name char(50),Address char(50),City char(50),Birth_Date date, Balance decimal(18,2))

    The schema read from a DataReader on the table

    <?xml version="1.0" standalone="yes"?>
    <DocumentElement>
      <SchemaTable>
        <ColumnName>IDENTIFIER</ColumnName>
        <ColumnOrdinal>0</ColumnOrdinal>
        <ColumnSize>8</ColumnSize>
        <NumericPrecision>15</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.Double, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>5</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>IDENTIFIER</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>FIRST_NAME</ColumnName>
        <ColumnOrdinal>1</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>FIRST_NAME</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>LAST_NAME</ColumnName>
        <ColumnOrdinal>2</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>LAST_NAME</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>ADDRESS</ColumnName>
        <ColumnOrdinal>3</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>ADDRESS</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>CITY</ColumnName>
        <ColumnOrdinal>4</ColumnOrdinal>
        <ColumnSize>50</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>202</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>CITY</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>BIRTH_DATE</ColumnName>
        <ColumnOrdinal>5</ColumnOrdinal>
        <ColumnSize>8</ColumnSize>
        <NumericPrecision>0</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>7</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>BIRTH_DATE</BaseColumnName>
      </SchemaTable>
      <SchemaTable>
        <ColumnName>BALANCE</ColumnName>
        <ColumnOrdinal>6</ColumnOrdinal>
        <ColumnSize>8</ColumnSize>
        <NumericPrecision>15</NumericPrecision>
        <NumericScale>0</NumericScale>
        <DataType>System.Double, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
        <ProviderType>5</ProviderType>
        <IsLong>false</IsLong>
        <AllowDBNull>true</AllowDBNull>
        <IsReadOnly>false</IsReadOnly>
        <IsRowVersion>false</IsRowVersion>
        <IsUnique>false</IsUnique>
        <IsKey>false</IsKey>
        <IsAutoIncrement>false</IsAutoIncrement>
        <BaseTableName>Customer</BaseTableName>
        <BaseColumnName>BALANCE</BaseColumnName>
      </SchemaTable>
    </DocumentElement>
    Note that the data types are different than when created. Next since I do not have a dBase view I looked at the newly created DBF file using a binary viewer, the numeric fields are type N while string fields are of type C. Also imported the DBF into MS-Access 2007 and noted that the schema matched that of the schema file above.

    I noticed via Google a similar issue http://www.codeguru.com/forum/showthread.php?t=508859 which does not adhere to a specified scale via OleDb. Yet another one http://p2p.wrox.com/vb-databases-basics/42921-how-create-dbf-db-set-size-field.html with the same issue. I would think it is safe to say it is not possible to change this behavior in regards to OleDb.


    KSG

    Tuesday, March 6, 2012 6:53 PM
  • Your code worked fine for me with one exception. The column name "some" is an Access reserved word and should be changed if you are going to use Jet with the dBase ISAM driver. I also used DEC instead of NUMERIC but I believe they are the same. 

    As soon as I changed the column name I had no issues. Unfortunately I was unable to check precision, but the the column values displayed properly when linking the resulting table through Microsoft Access.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, March 6, 2012 7:08 PM
  • As I needed the resulting .dbf to have the right column sizes I ended up dumping OleDb and ended up using 

    http://fastdbf.codeplex.com/


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    • Marked as answer by JendaPerl Monday, March 19, 2012 8:12 AM
    Monday, March 19, 2012 8:12 AM