Zeoslib Portal

I choose to upgrade my production server from MySQL 4.0 to MySQL 4.1 specifically because I needed prepared statements now, and maybe subqueries in the future, so I was none too pleased to discover than Zeos 6.5.1 uses emulated prepared statements for MySQL 4.1. Worse, when I dug into the 4.1 driver itself, I realized it was cobbled together just well enough to work like mysql 3.23 worked with. The dll's are old and many 4.1+ functions are just not defined.

On top of that, I wasn't wild about the Zeos API either. I'm familiar with the PHP way of doing things, especially with the new PHP Data Objects class released with PHP 5.1.1.

So using Zeos 6.5.1 as a basis, I built a PDO wrapper for Delphi and I intend that it also works with Free Pascal. I completely reworked the 4.1 driver and added a 5.0 driver. I stripped out all the emulated statements. The drivers are 100% native and the interface is very nearly identical to the PHP's PDO interface.

Besides working natively, this means you can truly bind columns to variables as well as binding values. It works with the native "?" markers and also implements PHP's ":variable" named placemarkers. You can execute and bind at the same time, e.g. stmt.execute('3|joe smith|failed'). Due to pascal language constraints, I couldn't exactly mimic that part of the PDO class, but this alternative is also useful.

I also added a little something of my own -- for select statements, you can supply it in 6 parts and the class will assemble the statement in the SQL dialect of the driver being used, including Mysql/postgreSQL/SQLite , ODBC/DB2 , MSSQL/Sybase , Oracle, and interbase/firebird. The idea is you write the select SQL once and can switch database backends without worrying about native sql variances. This function doesn't really support subqueries, but you can always put in regular SQL statements, this is just an option.

In the end, there is not much Zeos left. I stripped most of it out. About the only recognizably bits are the plaindriver files and some odds and ends. Here's the reason why I'm posting this: If there is a great deal of interest of having working native MySQL 4.1 and 5.0 drivers, I will release the code. If that happens, there are some things you need to know:

1. Mysql 3.20 and 3.23 were dropped
2. Mysql 4.0 works, but not for any of the prepared statement code. A message box pops up with a "this function not supported" if you attempt to do so.
3. It's interface-coded such that other drivers can be added
4. I developed this for a reason, I need it, and I have no time to develop it further beyond my own needs.
5. I do have time to maintain it though - if someone gives me code fixes/drivers, I'll add to the repository
6. It would be stored on a public Subversion repository
7. I could set up a little wiki with the basics
8. The object of this PDO class is not span all protocols of all database, so emulated transactions / prep statements aren't supported
9. Despite setting the ground work for db-independence, it's not my goal to build native drivers for all these.
10. Left to my own devices, I might get around to building drivers for SQLite 3 and PostgreSQL 8.1, but that will be on the order of a year or more from now.
11. Getting this to work with Free Pascal / Lazarus is a top priority for me. It may work now, but it needs testing. I will do that, because I need it.
12. Having it work pre-Delphi 7: It might now, but I can't test it, and I won't go out of my way to make it happen.
13. There's no visual component, it's just regular unit files
14. I'm neither a delphi nor a database coding expert, but apparently I know enough of both to be dangerous.
15. I have no intention on rolling these back into zeoslib - the driver files themselves are still compatible (at least with very little modification) so somebody else can use those to update zeos if they wish.

So here's the deal:
Zeos was the closest thing to what I needed, and the code helped me learn enough to be able to write my own wrapper/drivers. I believe the LGPL obligates me to provide the source of my modifications when my application is released, so this is an attempt to fulfill that obligation. It seemed like Zeos development has slowed or stopped, but in either case, I couldn't wait. My best motivation for doing this is having a lot of users/eyes on the code so any bugs get found/fixed and maybe some others can provide drivers I may need later. I'm testing the water, so if no one cares, just forget I said anything.

John

Well that sound great. I'm heavily interested in your Code !!!

The code has been working very well, and I'm very productive using it. I'm still shaking out some minor things, but I've already used it extensively with great success. I'm also in the process of testing it with FPC. That didn't go well last night, unfortunately. I found what I think is a bug in FPC that's preventing it from working (see my bug report: http://www.freepascal.org/bugs/showrec.php3?ID=4885 )

Not sure when I can release the code, mainly because setting up a separate repository and typing a basic wiki entry is going to take some time, which is on short order right now.

I'm really looking forward to your code !

Hello jrmarino,

a new zeos devlopment team formed in January 2006.
So we would like to integrate your changes for the next Zeos Release...
btw ... on team schedule is also the usage of non emulated prepare statements for mysql, postgres ... also metadatachaching and some features ... freepascal and lazarus is in heavy testing ...

To your Bug Report (FPC) turn off all checks (Rangecheck etc...) rebuild all - it should work ...

Maybe we can convince you to support zeos development a little bit?

greets,
firmos

Thanks, I just discovered on my own about the range checking trick, (FPC Bug 4510). Developers attempted fix in early january, but it's not been worked on since, very frustrating. Wasted several more hours on that last night, but I'm on track to get this working for FPC.

I think you can definitely benefit from the mysql 5.0 driver itself, and my 3 latest mysql dlls. It's not so much different from the zeos versions of 4.1 and 4.0.

The rest, I don't think can be integrated very easily. In some ways, I re-invented the wheel, mostly to cut off the "fat" that I didn't need and partly to completely understand the code. I also threw out the entire API and re-wrote it as I indicated above. The zeos philosophy appears to favor a consistent API at the possible expense of performance (see prep. statements) where as I have no use for the emulation.

Like I said above, I don't have the time to devote to zeos development as I'm leading a full scale effort on my own and we have a very tight, ambitious schedule. I'll give back what I can, but unfortunately I can't do any programming for zeos.

Work could actually flow in the other direction. The "library" I wrote maintained the zeos-like interfaces, so plugging in other database drivers can be done. I know it sounds like duplication of effort, but the goals of the two libraries are slightly different and have incompatible API's.

Ok - We see - If we set up a SVN Repository for you,
would you like to check in your code ?
We provide svn and resources on a fast server to Zeos Development too.

Maybe you contribute your code as a new project with your goals and the users decide wich api's they use, if you want to do that

So if we can find the Plain Drivers as least common thing both projects can
benefit from each other ...

greets,

firmos

I have my own SVN server, so that's not necessary, thanks. I'm not quite ready to check anything in because I'm still testing it, especially with FPC. I just got it compiling without errors or warnings, and its running under FPC 2.0.2 (finally!).

I hate how big the FPC executables are, about 5x the size of delphi executables. This library with mysql 4.0, 4,1, and 5.0 compiles to ~1.1 MB in FPC on windows (with debug info). It does run on just plain FPC though, you don't need to have lazarus installed. I'm off to test it on my FreeBSD machines now, I've been using FPC in windows for the last two days.

Maybe you could strip (with strip) your executables.
We also develop with FPC using Lazarus as IDE.
(we mainly code daemon applications ...)

So if you are ready to release something - we would be very happy to see your enhancements.

In the meantime you said you want eye's on your code. If you give us the possibility to look on your plain drivers you give us the chance to safe a huge amount of time, as we work on similar things - and you got the chance to get some feedback maybe some bugfixes.

If you are interested you can contact me under
>helmut.hartl dot firmos dot at<

thanks,

helmut

Helmut, that was a great tip!
Since my last post, I was successful in compiling and executing PDO on FreeBSD, so I went to bed happy (I'm in the same timezone as you here in Holland). However, the executable was a whopping 1226kb. After running "strip" on it, the executable reduced to 571kb, a 53% reduction. Now if I figure out this smartlinker thing...

I'm about ready to release the code. I have a wiki, so I want to write up a basic instruction manual. Since it's heavily inspired by PHP's PDO class, I'll probably just "port" the instructions over as well. Once that is done, adding an SVN repository won't take long.

Quick question as you may know: On the unix libraries, right now I have the mysql client library hardcoded to the freebsd path "/usr/local/lib/mysql/libmysqlclient.so". Do I need the full path, or are these library directories standard include paths?

Hello jrmarino,

i have no freebsd experience - so i can only guess :-)
On linux you can use libmysqlclient.so, if the dynamic library loader is configured right, and the lib paths are ok (there are usually links with version info set).

So on a well configured system you only need the short name but maybe with version info as: libmysqlclient.so.10 or libmysqlclient.so.14.0.0.

HTH,
helmut

Helmut,

Ok, it's done. I have spent most of the weekend fully documenting the interface on a Trac wiki. When you see the contents of the wiki, you will understand.

The new home of "Pascal Data Objects" is https://trac.synsport.com:8000/index.php/pdo/wiki
Unfortunately you have to get through an http authorization (couldn't be helped), but the username and password is "guest/guest".

I really recommend that you go there first, but here's the svn url: https://svn.synsport.com/pdo/trunk
It should allow you to pull the files anonymously. There's also a link to pull down the snapshot in a zip file on the wiki for those without a subversion client.

I've been doing a lot of last minute tweaks this weekend to get it ready for public view, (a lot!) so it's possible that I actually broke something, but the included examples all work on delphi, FPC win32, and FPC freeBSD, which is a good sign.

And yes, FreeBSD is like linux in that it just "found" the mysql library without having to put in the true path. I really believe PDO is going to be useful to a lot of people, so if you agree, maybe Zeoslib can put up an annoucement on the portal page.

Any bug reports can be posted directly on a Trac ticket and right now the wiki is open for anyone to modify, and as long as the intentions are good, people are welcome to make those modifications as they see fit.

Hope you guys enjoy it,
John