Lazarus, Zeos and Ms Access (mdb)

krisos · Post by **krisos** » 29.10.2008, 21:33

Hi, Im new.
I have problem and one question. I try to connect to Ms Access database in Lazarus (Zeos) but I can't do this:( Please help me:(
I use ZConnection. I choice my Database (Baza.mdb), but when i changed Connected to true I have information :"Requested database driver was not found.
Thank You for help:)

sandeep_c24 · Post by **sandeep_c24** » 30.10.2008, 05:55

I think you cannot connect to Access database from Lazarus as protocol ADO is not supported in Lazarus/Zeos.

Regards

Sandeep

Magoo · Post by **Magoo** » 30.10.2008, 17:41

Hi, for connect Lazarus with Ms-ACCESS I am using ODBC. You don't need Zeos.

Try using TODBCConnection (is supplied with Lazarus) or wait version 7.x of Zeos with ADO.

sandeep_c24 · Post by **sandeep_c24** » 31.10.2008, 10:06

There is support for ADO in Zeos but it is only available in Delphi.

Sandeep

Magoo · Post by **Magoo** » 31.10.2008, 12:15

Sandeep,
Zeos Lib for Delphi has support for ADO and ODBC (and work very well), but in Lazarus this options not exists.

I hope view this protocols in future versions of Zeos for Lazarus.

Post by **mdaems** » 01.11.2008, 15:39

First problem in implementing this seems to be the missing OleDB units in Lazarus.
Magoo, do you think you could overcome these problems and contribute the changes needed to get ADO+Lazarus+Zeos working in Zeoslib 7.X?

Mark

baby2test · Post by **baby2test** » 10.12.2009, 14:22

path:\dir\Baza.mdb
DSN=dnsname
ZConnection1=>protocal=ado

ZConnection1=>database
Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=dnsname;DBQ=path:\dir\Baza.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"

marcov · Post by **marcov** » 04.07.2010, 13:17

Mdaems: does Zeos only need ADO headers translated (and if so, which ones?), or do the relevant Delphi units also contain e.g. tdataset implementations for ADO?

I can do the first (but due the size it will take a while), but I don't know much about db components, so can't try to build something equivalent for borland ADO db components

Post by **mdaems** » 29.08.2010, 22:43

marcov,

Attached you'll find the 'minimal' part of OleDB that should be available when compiling zeoslib with ado support in Lazarus (doing this on windows).
Attention : this file is coming from another thread in this forum (http://zeos.firmos.at/viewtopic.php?t=2702) and is in my opinion not legally usable with Lazarus as it's a modified Delphi file. I just stripped everything from it which is not blocking compilation.

Unfortunately I don't understand the GUID and interfaces parts very well, so I don't know how the delphi independent conversion should be done now.

marcov · Post by **marcov** » 31.08.2010, 10:00

mdaems wrote:marcov,

Attached you'll find the 'minimal' part of OleDB that should be available when compiling zeoslib with ado support in Lazarus (doing this on windows).
Attention : this file is coming from another thread in this forum (http://zeos.firmos.at/viewtopic.php?t=2702) and is in my opinion not legally usable with Lazarus as it's a modified Delphi file. I just stripped everything from it which is not blocking compilation.

Unfortunately I don't understand the GUID and interfaces parts very well, so I don't know how the delphi independent conversion should be done now.

I had my collegue check this out, and he confirmed it was a hopefully straight ADO header translation. So, if you only use this you don't use any "higher level" ADO components, which is what I wanted to know.

Don't hold your breath though, that header is *huge*.

The constructs in this file are afaik all supported, FPC has one limitation though that could cause a problem. It can't handle COM exceptions (other than via stdcall), because FPC's exception mechanism is not (yet?) based on SEH.

Dali · Post by **Dali** » 12.04.2011, 19:10

Hello. I am a long-time user of Zeos. I have a Delphi license, but I'm tired of the newer versions and giving Lazarus a look. So, I decided to try to use this oledb unit under Lazarus, but I achieved partial success only and now I'm stuck.

Here's what I did:

Updated to latest zeos svn sources (currently, revision 887).

Copied oledb.pp to src/dbc/

On src/Zeos.inc, commented these conditional defines out:

{$IFNDEF ZEOS_DISABLE_ADO}
//{$IFNDEF FPC}
{$IFNDEF UNIX}
{$DEFINE ENABLE_ADO}
{$ENDIF}
//{$ENDIF}
{$ENDIF}

In uses section of src/component/ZPropertyEditor.pas, commented these conditional defines out:

{$IFNDEF UNIX}
// {$IFNDEF FPC}
{$IFDEF ENABLE_ADO}
, ZDbcAdoUtils
{$ENDIF}
// {$ENDIF}
{$ENDIF}

Added ../../src/ZPlainADO and ../../src/ZPlainADODriver to ZPlain.lpk

Compiled ZPlain with no errors.

Added ../../src/dbc/OleDB, ../../src/dbc/ZDbcAdo, ../../src/dbc/ZDbcAdoUtils, ../../src/dbc/ZDbcAdoMetadata, ../../src/dbc/ZDbcAdoResultSet and ../../src/dbc/ZDbcAdoStatement to ZDbc.lpk

Tried to compile the package, which failed. In ZDbcAdoStatement and in ZDbcAdoMetadata, I found:

{$IFNDEF FPC}
Variants,
{$ENDIF}

That was causing some trouble with vartostr and similar calls, so, I removed the condition. Recompiled CLEAN all the packages, and reinstalled Zeos components.

Placed a TZConnection in a form. Now, to my enthusiasm, I could select "ado" driver under Lazarus. I established the appropriate connection string to connect to a local ACCESS database file and it even connected with no errors at all!

Then, I placed a TDatasource and a TZQuery in the same form, set the SQL code, activated the query, browsed between its fields using a design-time field browser... I just couldn't believe it was so easy... And it wasn't. Because, when I assigned the query to a data aware component, everything crashed.

Messing around with the debugger, I found out that the crash may (or may not) be originated in TZAbstractConnection, presumably in this line:

PWideString(Buffer)^ := RowAccessor.GetUnicodeString(ColumnIndex, Result);

I know there is no support for this, but any clues or directions would be very appreciated. It would be just great to have ado support in Zeos, because I don't like the data-aware components included in Lazarus, which are, at least today, quite slow and buggy.

Thanks in advance.

Dali · Post by **Dali** » 12.04.2011, 19:40

**UPDATE** I'm pretty sure that the problem is there, since it won't fail with numeric fields. It only crashes when retrieving alphanumeric data. (Sometimes with an "Invalid Pointer Operation").

I'm not submitting the bug, because I don't think it is actually a bug. I believe that it's my MDB file not using Unicode. I know how to deal with this issues in other database managers like PostgreSQL but, how can I work around codepage stuff in MS Access?

Dali · Post by **Dali** » 09.05.2011, 21:20

Still stuck, growing in despair.

By the way, does the ZConnection property "codepage=UTF8" do anything at all when using ADO driver, or is it ignored in behalf of the properties set in the ADO connect string?

I mean, does this specify the native codepage selected in the DBM, set the one which will be used in the presentation layer, both, or none?

Codepage issues are usually tricky, but in Lazarus they can become a painful nightmare, specially when coding a cross-platform app...

Post by **mdaems** » 19.05.2011, 22:18

By the way, does the ZConnection property "codepage=UTF8" do anything at all when using ADO driver, or is it ignored in behalf of the properties set in the ADO connect string?

I'm afraid this is not an option supported for ADO.

And even worse : I never used ADO, so I have no clue as to how you should solve this problem.

Mark

Dali · Post by **Dali** » 20.05.2011, 00:41

Thanks anyway, Mark. I'll update to SVN 903 tomorrow and see if the latest patch concerning codepage issues helps and, of course, you'll be the first to know if such thing comes to happen.

To clarify the scenario, let me add that I don't like ADO, but I use it in the Windows version of a corporate app that connects to PostgreSQL, MySQL or FireBird using Zeos and backs the data up to an Access database. Why? Because if I do that, the app can browse the data, print invoices, and many other things from a simple flash drive, without having to install anything in the host machine (ADO comes with Windows since XP, you know). That comes real handy if I need to create on the fly new customer records, work orders, invoices, and so on when I am outside the office. Of course, it also makes it very easy to demonstrate the functionality of my app to my potential customers without needing to carry a laptop or anything else than a small pendrive.

That approach had been working for years with the version I produced using my old Delphi 6 compiler. In fact, it's still working, but it needs some enhancements, new look&feel... Besides, now I want it to run under Linux and, since Delphi versions below 7 are no longer supported by Zeos, and I don't want to buy a license of the latest releases of Delphi, I ported just everything to Lazarus. The project now compiles with no errors in both Windows and Linux, with some anticipated minor issues caused by lost properties which I must still translate. For instance, in Lazarus you can do this in code:

myZQuery.connection := otherContainer.myDatabase;

... But you can't set such property, pointing to a component which is actually placed in other form, in the object inspector. Back in Delphi, that practice was legit.

Anyway, I tried to do the same thing I had done with ADO using ODBC, that is, a TODBCConnection and TSQLQueries. And, yes, it works, but with many bugs. For instance, primary key fields must be set with code for every query and, even then, some locate procedures raise absurd exceptions which force me to locate rows by alternative sequential reads... As I said, slow, buggy, a nightmare.

Sorry for the extension of the post, and thanks again for answering (and, of course, your great work here).