Oh one more thing

In my last few posts I have been nattering on about DataAccessor an old Java app that set me on my path to Perl. Looking back some 20 years now I see that despite it working well and being successful I think we had on our hands one very large anti-pattern. At least when it came to any other SQL database

I do remember having a look at the new thingy called MySQL and investigating it to see if we should create SQLDataAccessor for it. I had an email trail with David Axmark (how do you forget a name like that) asking if MySQL did 'hierarchical queries' as we need them on SIP. His answer something like this

'Well no, not yet. But you are welcome to write one one. Attached is some of the code I think you will need to start...'

About 15 .c and .h files where attached and had I some C skills I might have had a different career path.

We did start working with MySQL and Linux but then one of the program leads had this question

How come I can't do a rollback or even a commit?

Well as I was sort of the point guy with MySQL I diligently sent Dave an email. This was the response:

Yeah to get that to work you have to do a table lock yourself and then unlock the table but we should have a workable commit and rollback working by the 3.2 release....

By the way I have also attached some code if you are interested we a just trying to get the Group by and Having working correctly with ...

Early days I guess. Anyway back to the point of the blog

The Java SQLDataAccessor was written to work for just Oracle SQL, which at the time, late 90s early 00s, did not seem odd to us as that was the only player on the block. To make it work for any other SQL database we would had had to copy and rewrite large chunks of code. Painted ourselves into a corner I guess?

Jump ahead a few years and the Perl version of DataAccessor and things had changed, Oracle had lost its stranglehold, MySQL finally got a Group by and a Commit and was now part of LAMP, there was SQLServer, Sybase, even Gupta was making a comeback and there was something called Ingress out there.

Well the good old Perl DBI and its various DBD drivers came to the rescue as it was a simple thing to adapt to any SQL DB the basic DataAccessor code. Well as The Fates would have it, before the fist Perl DataAccessor was finished for LAMP things changed yet again.

It seems you can't run a company with 20+ employees without selling something, and the only paying customer did not want to be on LAMP or MySQL they waned Windows NT and to use Oracle. So it was get that Oracle version working and out the door yesterday. We lucked out on that one as Activestate released their first version or Perl 5.8 with DBD::Oracle just a few days before.

So from this orginal code snippet you can see

     if ($field->is_identity() and $field->sequence() ){
         $value_str.= $field->sequence().".nextval";
         $self->returning(DBIx::DA::Returning->new({params=> [DBIx::DA::Param->new({name=>$field->name(),value=>\$field})]}));

we never did get SQLDataAccessor to be DB agnostic as even with a paying client the company did not last that long and we where on our separate ways again.

As for DataAccesesor it still had some life in it as the code was open source, I think it was on SEUL for a time and I did have a copy. I my own version of it on a number of small personal projects and even the odd paying customer as well.

I finally put what I have handy on my github account the other day where it will stay as my plan is to release a newer version out to the world.

But that is another story!!


Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations