OleDb and MS Access

[abonic]

The OleDb protocol does not support writing of BIT(Boolean) and DATETIME fields to the MS Access MDB from dataset controls. The problem with the DATETIME field can be solved by adding the following line to the code:

Code: Select all

ZConnection1.Properties.Add('DatetimeWriteFormat=YYYY-MM-DD HH:NN:SS');

Is there something similar for a BIT data type or any other way to allow that field to be written to the database?

Tested with trunk svn 8005, as it is the last version that can be compiled with Delphi 2009. By the way, apart from the above, I don't see any other problems in working with the MS Access database.

[abonic]

Correction: BIT fields work correctly. I mistakenly used one of the reserved words as a field name in the test.

On the other hand, I don't know why this is a problem given that the 'IdentifierQuoteString' value is correctly set to '[]'.

EDIT: Of all the reserved words, I hit exactly one that is not in the list returned by GetIdentifierQuoteKeywordsSorted - 'Logical'.

[abonic]

After additional testing, a new problem: it is not possible to write negative values in SHORT and LONG integer fields.

For example, if I try to write -5 in the SHORT field, the parameter that ZEOS will pass is '-'. The last character seems to have been cut off.

Code: Select all

ZTable1.AppendRecord([2, -5]);

ZSQLMonitor1LogTrace:
... cat: Prepare, proto: OleDB, msg: Statement 4 : INSERT INTO T1 (ID,fShort) VALUES (?,?)
... cat: Bind prepared, proto: OleDB, msg: Statement 4 : '2','-'
... cat: Execute prepared, proto: oledb, msg: Statement 4 : INSERT INTO T1 (ID,fShort) VALUES (?,?), errcode: -198708185, error: OLEDB Error
SQLState: 3060 Native Error: -198708185
Error message: Wrong data type for parameter ''.
Source: Microsoft JET Database Engine

oledb mdb.rar

[miab3]

Yes indeed. This error occurs for OleDB. Auto_increment doesn't work either.
In the analogous connection via ADO everything works.

Michal

[abonic]

With ADO everything works, but it would be nice to have the performance improvement offered by OleDB.

	
            OleDB    ado
table open  0.025  0.648 
table scan  0.889  4.012 
sql select  0.866  1.183
sql insert  1.523  2.321

[miab3]

Yes, I wrote some time ago that ADO is the slowest option.

It is possible that in the case of Access OleDB is defective in detecting field types

Michał

[abonic]

I think I found a solution to the bug with negative integer values.

Code: Select all

unit ZDbcOleDBStatement;
...
procedure TZOleDBPreparedStatement.InternalBindSInt(Index: Integer;
  SQLType: TZSQLType; Value: NativeInt);
...
  // PDBLENGTH(PAnsiChar(fDBParams.pData)+Bind.obLength)^ := L shl 1 + Byte(Ord(Negative));  // length of '-5' is 3 here
  PDBLENGTH(PAnsiChar(fDBParams.pData)+Bind.obLength)^ := (L + Byte(Ord(Negative))) shl 1;   // should be 4

I'm not sure if this change breaks something in other databases, but it works without problems with access mdb, at least judging by the attached test application.

... cat: Prepare, proto: OleDB, msg: Statement 4 : INSERT INTO T1 (ID,fShort) VALUES (?,?)
... cat: Bind prepared, proto: OleDB, msg: Statement 4 : '2','-5'
... cat: Execute prepared, proto: OleDB, msg: Statement 4, elapsed time: '00:00:00.000'

[miab3]

The error of negative values disappeared in my test too.
The same sequence is present in 'ZDbcOleDBStatement.pas' in one more place.
Unfortunately, autonumber for Access still doesn't work.

Michał

[marsupilami]

Hello abonic,

I applied the patch. Thank you for contributing

Best regards,

Jan

[abonic]

The same sequence is present in 'ZDbcOleDBStatement.pas' in one more place.

I guess that line should be corrected in the same way. The problem is that I cannot create a situation where that part of the code would be executed.

Unfortunately, autonumber for Access still doesn't work.

The problem with the AutoIncrement field is here. Flags does not contain information about AutoIncrement for the mdb database.

Code: Select all

function TOleDBDatabaseMetadata.UncachedGetColumns
...
  Result.UpdateBoolean(TableColColumnAutoIncIndex, Flags and DBCOLUMNFLAGS_ISROWID = DBCOLUMNFLAGS_ISROWID);