Friday, December 30, 2005

Formatting SQL code made easy

I was just taking a quick glance at AMIS technology blog. This blog aggregate contains quite a few high quality Oracle related entries, as well as some entries relating to eclipse and some other open source developments.

There's an entry by Aino Andriessen referring to a an entry to Eddie Awad's blog. Eddie's been searching the web for SQL pretty printers/formatters, and he has come up with a few interesting ones.

Now, I've been needing such a utility ever since I'm using MySQL 5.0 views. Suppose you've made a complex view using a CREATE VIEW statement, and you want to review your code afterwards.

You could of course maintain the source code to your view outside the database and review that, but why not use the information_schema? The VIEWS system view has a column, VIEW_DEFINITION, that contains the SQL SELECT expression underlying your view. That way, you'd be sure to review the actual code: mistakes are out of the question.

Yes, that's very true: you actually are reviewing exactly the code underlying the view...wich is *NOT* the code you entered in your CREATE VIEW statement; actually, it's not at all the code you entered. This feature request (bug #11082) and the link in that bug report to this message on the MySQL internals list illustrate this perfectly.

I'll just paste the CREATE VIEW statement appearing in that message here:


CREATE VIEW DEBITEUR_NAW
(
RELATIEID,
NAAM,
ADRES,
POSTCODE,
PLAATS,
LAND,
DEBITEURNUMMER,
TAV,
BTWNUMMER,
BETAALTERMIJN,
TELEFOON,
FAX,
ACTUEEL,
EMAIL,
KVKNUMMER
) AS
select d.relatieID,
r.naam,
a.adres,
a.postcode,
a.plaats,
l.omschrijving,
d.debiteurnummer,
d.Tav,
d.btwnummer,
d.betaaltermijn,
(select telefoonnummer from relatie_telefoon
where relatieid = r.relatieid and telefooncode = 1 and nummer = 1),
(select telefoonnummer from relatie_telefoon
where relatieid = r.relatieid and telefooncode = 2 and nummer = 1),
r.actueel, r.email, r.kvknummer
from debiteur d
join adres a on (d.relatieid = a.relatieid and a.adrescode = 1)
join relatie r on (d.relatieid = r.relatieid)
left join land l on (a.landcode = l.landcode)
;


Ok, everything's hunky dory here. Now, let's ask the information schema for the view definition:


SELECT view_definition
FROM information_schema.views
WHERE table_schema = schema()
AND view_name = 'DEBITEUR_NAW'
;


And, suprise, surprise:


select `d`.`relatieid` AS `RELATIEID`,`r`.`naam` AS `NAAM`,`a`.`adres` AS
`ADRES`,`a`.`postcode` AS `POSTCODE`,`a`.`plaats` AS
`PLAATS`,`l`.`omschrijving` AS `LAND`,`d`.`debiteurnummer` AS
`DEBITEURNUMMER`,`d`.`tav` AS `TAV`,`d`.`btwnummer` AS
`BTWNUMMER`,`d`.`betaaltermijn` AS `BETAALTERMIJN`,(select
`serp`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`serp`.`relatie_telefoon` where ((`serp`.`relatie_telefoon`.`relatieid` =
`r`.`relatieid`) and (`serp`.`relatie_telefoon`.`telefooncode` = 1) and
(`serp`.`relatie_telefoon`.`nummer` = 1))) AS `TELEFOON`,(select
`serp`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`serp`.`relatie_telefoon` where ((`serp`.`relatie_telefoon`.`relatieid` =
`r`.`relatieid`) and (`serp`.`relatie_telefoon`.`telefooncode` = 2) and
(`serp`.`relatie_telefoon`.`nummer` = 1))) AS `FAX`,`r`.`actueel` AS
`ACTUEEL`,`r`.`email` AS `EMAIL`,`r`.`kvknummer` AS `KVKNUMMER` from
(((`serp`.`debiteur` `d` join `serp`.`adres` `a` on(((`d`.`relatieid` =
`a`.`relatieid`) and (`a`.`adrescode` = 1)))) join `serp`.`relatie` `r`
on((`d`.`relatieid` = `r`.`relatieid`))) left join `serp`.`land` `l`
on((`a`.`landcode` = `l`.`landcode`)))


Is what is returned. Nasty, eh? Actually, this pretty much forces you to maintain a copy of the source outside the database if you're actively maintaining this view.

Suppose you didn't. Or suppose you're not in control of that: you could be in a position where you arrive at a customer that needs a problem fixed, and you're confronted with just the database. What will you do then huh?

Well, now you can just have your code formatted online. This is what it did:


SELECT `D`.`RELATIEID` AS `RELATIEID`,
`R`.`NAAM` AS `NAAM`,
`A`.`ADRES` AS `ADRES`,
`A`.`POSTCODE` AS `POSTCODE`,
`A`.`PLAATS` AS `PLAATS`,
`L`.`OMSCHRIJVING` AS `LAND`,
`D`.`DEBITEURNUMMER` AS `DEBITEURNUMMER`,
`D`.`TAV` AS `TAV`,
`D`.`BTWNUMMER` AS `BTWNUMMER`,
`D`.`BETAALTERMIJN` AS `BETAALTERMIJN`,
(SELECT `SERP`.`RELATIE_TELEFOON`.`TELEFOONNUMMER` AS `TELEFOONNUMMER`
FROM `SERP`.`RELATIE_TELEFOON`
WHERE ((`SERP`.`RELATIE_TELEFOON`.`RELATIEID` = `R`.`RELATIEID`)
AND (`SERP`.`RELATIE_TELEFOON`.`TELEFOONCODE` = 1)
AND (`SERP`.`RELATIE_TELEFOON`.`NUMMER` = 1))) AS `TELEFOON`,
(SELECT `SERP`.`RELATIE_TELEFOON`.`TELEFOONNUMMER` AS `TELEFOONNUMMER`
FROM `SERP`.`RELATIE_TELEFOON`
WHERE ((`SERP`.`RELATIE_TELEFOON`.`RELATIEID` = `R`.`RELATIEID`)
AND (`SERP`.`RELATIE_TELEFOON`.`TELEFOONCODE` = 2)
AND (`SERP`.`RELATIE_TELEFOON`.`NUMMER` = 1))) AS `FAX`,
`R`.`ACTUEEL` AS `ACTUEEL`,
`R`.`EMAIL` AS `EMAIL`,
`R`.`KVKNUMMER` AS `KVKNUMMER`
FROM (((`SERP`.`DEBITEUR` `D`
JOIN `SERP`.`ADRES` `A`
ON (((`D`.`RELATIEID` = `A`.`RELATIEID`)
AND (`A`.`ADRESCODE` = 1))))
JOIN `SERP`.`RELATIE` `R`
ON ((`D`.`RELATIEID` = `R`.`RELATIEID`)))
LEFT JOIN `SERP`.`LAND` `L`
ON ((`A`.`LANDCODE` = `L`.`LANDCODE`)))


Pretty cool huh? I just chose the default options, but you can do stuff like control the letter case and the position of the comma's in lists etc.

It doesn't finish here though: This online tool can take on several input dialects (MS SQL, Oracle, Access, MySQL and generic) and, dig this, several output formats in addition to SQL: PHP, Java, VB and lots and lots more. (In those cases, the code for a string expression in the pertinent language is returned). You can also control

In addition to the online tool, Eddie Awad's blog entry mentions an applet, and a downloadable tool to. I haven't tried those, but you can check em out for yourselves of course.

Thursday, December 29, 2005

MySQL udf.NET, wtf?

I just read a post on Brian Aker's blog hinting on plans to embed MONO, the open source fork of the Microsoft .NET framework, into MySQL as an UDF language, or even an alternative Stored Procedure language.

Brian's post's just a brief mental note made public. It refers to a post on Miguel de Icaza's blog. Unfortunately, the link is broken, but I was able to dig up what I think is the intended adres.

Well, I think this is great and exciting news! I did a couple of things with Microsoft C# .NET, and however shallow my impression might be, I think the framework's solid - I like it. (I like C# too, but that's technically a different matter.) I haven't had an opportunity to check out MONO, but I heard some very promising things about it. (I heard someone claim binary compatibility - imagine, no recompile across OS-platforms!)

Anyone not experienced in C/C++ that did program an UDF will probably welcome the addition of a managed language to write their UDF. Also, having the ability to write stored procedures in some language based on the framework is bound to open up MySQL to a whole new group of users. It will definitely keep the Gap between MS SQL server 2005 and MySQL a lot smaller.

It makes me wonder though. The following quote is from the chapter 17.2.1. CREATE PROCEDURE and CREATE FUNCTION from the MySQL Reference Manual (v 5.0):

A framework for external stored procedures will be introduced in the near future. This will allow you to write stored procedures in languages other than SQL. Most likely, one of the first languages to be supported is PHP because the core PHP engine is small, thread-safe, and can easily be embedded. Because the framework is public, it is expected that many other languages can also be supported.

Question is, is PHP still in the picture? I sure do hope so! What about Java (I'm thinking Oracle compatibilty here)?
Anyway, exciting stuff ahead!

Ubuntu on an external hard drive - It's a megablast!

Wow, it's just such a thrill to have ubuntu sitting here on a portable drive!!

I just hooked my external drive to the IBM Thinkpad laptop I use for my job.

True, for some reason, I had to install the drive first under Windows XP (I'd expected the BIOS to pick it up right away, but it didnt), but after that, I could boot Ubuntu right away, just like I did on my desktop upstairs.

(Some people might regard it as a bit of a makeshift solution, but I like having the internal hard drive as the default boot device. During startup, I just hit whatever function key BIOS happens to accept to pop up a list of boot devices - F8 on my desktop, F12 on my laptop - and pick my external hard-drive if I want to boot Ubuntu. It makes sense, I really cannot be sure the external drive is hooked up, whereas It's safe to assume the interal hard drive's still there ;-).

So, Ubuntu has just awoken in this strange, new, compact body. From there, I used the graphical "networking" tool to set up my wireless adapter (built-in in my thinkpad, my desktop uses and old fashioned umbilical chord) and lo-and-behold: here I am, sitting in the living downstairs, with my laptop posting this.

I admit, I did reboot once after tinkering with the wireless adapter configuration. It just would not work at once, and the rebooting reflex is kinda engraved in my backbone, being a Windows user since the 3.11 version.

Thanks again Dave (a.k.a. DaBruGo), I really, really am so much obliged to you for posting this solution of yours!! I'll just repeat it here: those that want to have a dual boot solution for ubuntu and they want ubuntu to sit on an external drive, look no further! This thread in the ubuntu forums contains indispensible valuable information. Right, people are even posting replies that tell you how to do this with an USB flash memory stick, imagine that!

Wednesday, December 28, 2005

Linux for Human Beings - AND Roland too!



I've been busy exploring open source software for some years now.

I think it was Erik, a friend of mine, that first told me and explained what open source software was about. Then, he was right in the middle of developing the backup tool Abakt and we then discussed XML as a format for storing configuration data. He used (and still uses, I think) an open source SAX parser, I can't remember which one exactly.

Anyway, he introduced me to MySQL, OpenOffice, Mozilla - all these different kinds of products. My initial reaction was one of disbelief: How in the world could this software be free of charge? Put another way, how can it be free of charge, and be of good quality too? What's the catch?

I then briefly explored MySQL (Still a 4.0 I think?) and at the time, I thought I knew what the catch was: MySQL was not that good. At least, not compared to what I was used too (Oracle, MSSQL, Interbase even). It didn't have a lot of things that I used (and still use) a lot. Declarative Check Constraints, Views, Stored Procedures. Besides, it had all these strange features such as really eerie NULL handling. On top of that, you had to configure all that InnoDB stuff and go to all kinds of trouble to enable transactions and foreing keys.

Mozilla was sort of the same story. At the time, I didn't have built-in XSLT support (like IE does), and although it claimed it implemented only standard DOM to perform DHTML, I really was unimpressed as the interface IE offered seemed so much richer and easier to me.

Of course, I just consumed the products, briefly glancing over the features. And my perpective was quite narrow towards open source software, almost exlusively focussed on the costs, and not on the inherent quality or the community process. Some of the things I really did dig then was the Java programming language. I thought that was open source too, because it was free - of cost that is. Also, I had a tendency to compare all this stuff to the equivalents I was familiar with - being mostly Microsoft products, or commercially available software running on the Windows platform.

Gradually, my attitude changed. A product that definitely made a difference in this respect was PHP. At the time, I was using three different technologies to develop web applications: Oracle Mod_plsql, Java servlet pages and Microsoft Active Server Pages. To me, the problem with all these technologies was their lack of portability. I want to develop applications and let my customers decide what underlying infrastructure (Web server, database server, and of course: Operating System) is best for them. With these three, this is an utopy.

I stumbled into PHP again via Erik. Also, I noticed that funny php extension on all these webpages I was browsing, and I made a note I had to check it out sometime, ifnot to know why not to use it. Finally I downloaded it, and read the installation notes. Then, I set up in both Apache and Microsoft Internet Information Server - both up and running within 10 minutes. This was amazing! This was a product that I legally did not pay for, it had clear documentation AND it worked exactly as described on two very different webservers (at least, marketing-wise). Better still than not paying for it, I could even use PHP to build applications on a commercial basis!

(Actually, I was using Apache quite alot then too, but listen up, hear this and try not to laugh at me: I did't know Apache was open source! See, I was familiar with what was (and still is) called "the Oracle HTTP Server powered by apache" wich is about 98% apache that's shipped with the oracle database as of version 8 (among that 2% is that mod_plsql I've blogged about here an there). Mind you, they havent even succeeded in using a Apache 2 based product, it's still Apache 1.3 there. tsk..).

From then on, I looked at open source software from a different point of view. I started using MySQL. The 4.1 is of course objectively a big improvement over the 4.0 version I met initially, but of course, my change of attitude has made all the difference. And my confidence in open source software has got another boost by getting involved (in my own modest way) in the MySQL forums, and in writing the odd blog or article.

However, all this time, I have been glued to the Windows operating system. I've been wanting to convert there too, but there's something about the nature of an OS that doesn't make me change. I mean, I don't like particularly configuring stuff, making my computer and OS see my hardware, solving IRQ conflicts, man you name it.

But now, something has come to my attention that might change all that. It's the ubuntu Linux distribution. Ubuntu advertises itself as "Linux for human beings". They do not explicitly say to what kind of a user base the other Linuxes are targeted, but for now, I'll gladly assume the mean "human beings that do like to configure, make the OS see my hardware and solving IRQ conflicts".

People might be wondering why I'm picking it up so late, but the result's what matters to me. (In case anyone cares, I picked it up through Morgan Tocker's blog wich I picked up through PlanetMySQL: thanks Morgan, thanks PlanetMySQL!).

Actually, I'm writing this blog from my freshly installed Ubuntu. It ships with Mozilla Firefox. I'm really impressed with how it looks, and how fast it works!

I'm still runnin it dual boot with my Windows XP OS, and I'll probably hang on to that too (actually, I haven't got much of a choice there as I need it to serve most of my customers). I had some installation troubles of course: I wanted to run it from an external hard drive, in part because I was a bit afraid the installation might corrupt my windows disk.


Anyway, power to the community, here as well: on the ubuntu forums, it took some 10 hours for me to get the issue solved....using a pointer to an existing thread (that I would've found if I had searched better). So, thanks for the pointer aysiu. And of course, many thanks, and a big thumbs up for my hero of the day: DaBruGo, who has so kindly explained to us all how to set ubuntu up using an external hard drive.

No helpdesk can beat this kind of support - it's just amazing.

Friday, December 23, 2005

Reporting for the Masses: Eclipse/BIRT, Apache Tomcat and MySQL

I got it working...

Eclipse eclipse logo


About two monts or so ago, my attention was caught by that remarkable open source product, Eclipse. (Maybe a lot of people are saying "..duh!.." out loud right now, but hey, I can't hear you)

I wrote the MySQL connector/J example accompanying the article in the Call Level Interface section of Andrew Gilfrin's site www.mysqldevelopment.com, using eclipse, very much to my satisfaction.

When I started to write the example, I was using sun's netbeans, but it was simply too slow. Especially as my source grew bigger (I'm talking only several hundreds of lines here, shouldn't be a big deal) the editor was just lagging and freezing up to a level that simply wasn't acceptable to me. Clearly, I needed something else and eclipse happened to be the first thing I thought of using instead.

BIRT eclipse BIRT logo


Browsing through some of the other, non-core, eclipse projects I noticed the BIRT project. And, quite shortly after becoming aware of BIRT's existence, an article was published on the MySQL website: Using BIRT To Report On Bugzilla in MySQL. I knew then that I had to check it out for sure.

For those that never heard about it, BIRT stands for: "Business Intelligence and Reporting Tools". BIRT lets you extract and present data stored in various types of data sources (such as relational databases, but also comma seperated files, and some others). Such a presentation, or report, can be a powerful tool for the upper and middle management to analyze, control and plan their business. (You can take a look at a DEMO flash movie here)

Of course, reports can have a much more modest purposes too. For example, an order entry system could be required to print out an entire order including order lines so it can be printed out and sent to the client as an invoice.

Now, I have worked with some closed source Business Intelligence and Reporting Tools, and I think it's fair to say that at this moment, BIRT is best characterized as a reporting tool rather than a Business Intelligence tool.

True Business Intelligence tools offer all kinds of instruments to analyze data, quite often in a dynamic, flexible, ad hoc manner. At this moment, BIRT is not such a tool. Those that are wondering what tools qualify as true Business Intelligence tools: I'm thinking of OLAP and Datamining tools such Cognos Powerplay, Oracle Discoverer, Microsoft Analysis Services.

BIRT let's you define sets of data (for example, from a SQL query) and then render that data into a mainly static layout. There is quite some choice of course: Data maybe rendered as simply plaintext, or in a complex tabular grouped format or even as a colourful graph or diagram. It should be possible to add some user interaction too, because you can put hyperlinks and javascript into the report (haven't tried that yet though).

There is also limited support for some analytics. The data drawn from the query can be grouped in various levels. Aggregate functions can be independantly applied to these groupings, showing both detail rows as well as the aggregates (think of a grand total for all ordered items on the invoice).

Actually, I think BIRT bears quite some resemblance to Microsoft Reporting Services. This is especially true when designing Reports. The Eclipse IDE, in which you design BIRT reports, matches Microsoft Visual Studio (which is used to create MS Reporting Services Reports). Both reporting platforms are extensible. Of course, BIRT is so by definition, as it is open source; but the methods for extending Reporting Services are documented and the specification is available. The language in which reports are defined is an XML format for both platforms .

(BTW - the XML-format this is something I like very much in both these products, It offers one all kinds of opportunities to manipulate report sources. For example, because this is all XML, it shouldn't be too hard to translate reports sources from one platform to the other using simple XSLT transformations. Another thing you could do is build your own implementation, generating source code in say, PHP. Again, all you'd need are XSLT transformations).

Of course, there are also some differences between these products. Here are some obvious ones which do not directly impact the functionality of the report:


  • BIRT is open source under the Eclipse Public License - Reporting Services is closed proprietary source software, subject to its own product license

  • BIRT is based on java technology - Reporting Services is built on .NET technology

  • BIRT has jdbc database connectivity - Reporting Services has ODBC and ADO .NET connectivity

  • Syntactically, BIRT expressions are Javascript expression - Reporting Services expression are Visual Basic .NET expressions

  • BIRT does *NOT* have crosstabs as of yet - Reporting Services has of course

  • BIRT datasources are defined on the report level - Reporting Services datasources are defined on the Report Server and/or Project level (wich I think is much more convenient than in BIRT)

  • A lot of output formats are available for Reporting Services - BIRT can only output HTML or PDF (I'm really missing an XML output format)



There are significant differences in Architecture:


  • Although you can use Microsoft Reporting services on various databases (ODBC, ADO) you really need a Microsoft SQL Server instance, always! The report data is processed and cached in a SQL Server database, no matter the actual data source. The actual report is run from the cached data. BIRT is dependant only upon a runtime library. No datastore is required to cache data.

  • Reporting Services is integrated into Microsoft SQL Server and Microsoft Internet Information Services (webserver). So, there, deployment is really easy. BIRT is just a reporting tool. You'll have to hook it up to a Application Server yourself - one capable of running java servlets. Deployment is then done manually, copying the source files to the application server



Tomcat Apache Tomcat logo


Of course, for it to be a serious solution, BIRT really needs to be hooked up to an application server. You can't expect your users to open eclipse to preview their report.

Luckily, the BIRT manual explains exactly how you can run your reports from your Apache Tomcat application server. Some time ago, I installed one of those too, but I never got round to playing around with it. This was my first chance to do that too.

I expected it to be big deal - it's not. In a minimal setup, you need to create a BIRT directory under tomcat's webapps directory and copy one of BIRT's components, the report viewer to that directory. It's a walk in the park, I had it running withing minutes, having read the relevant chapter in the BIRT manual.

MySQL - Sakila MySQL logo



I used the sakila sample database (download here) to do some reporting.

I devised two little queries that I think would be useful had I started a career as a DVD rental shop owner: Rentals per store through time, and Rentals per category.

Here's Rentals per store:

select DATE_FORMAT(last_day(r.rent_date),'%Y') as rent_year
, DATE_FORMAT(last_day(r.rent_date),'%c') as rent_month_number
, DATE_FORMAT(last_day(r.rent_date),'%b') as rent_month_abbreviation
, i.store_id as store
, count(*) as count_rentals
from rental r
inner join inventory i
on r.inventory_id = i.inventory_id
group by rent_year
, rent_month_number
, store


I rendered it as bar graph:

a bar graph of sakila rentals per store through time


Here's Rentals per category:

select c.name category
, count(*)
from rental r
inner join inventory i
on r.inventory_id = i.inventory_id
inner join film f
on i.film_id = f.film_id
left join category c
on f.category_id = c.category_id
group by category


I rendered it as a Piechart:

a pie chart of sakila rentals per category


Quite interesting results, actually. I'm not experienced in DVD rentals, but this does look like quite a homogenous set of data to me. Anyway, I just heard Mike Hillyer is going to discuss the sakila database at the MySQL user conference in april 2006. We'll probably hear exactly how he generated this set.

I just want to add that it took me just a couple of hours to explore BIRT and the underlying XML format (dont worry, designing is all GUI) enough to come up with these. Apart from these graphs, my first report also includes a table of rentals per store with subtotals for several grouping levels. I skipped the tutorials, I just watched the Demo Flash movie (link in the top). My conclusion is that eclipse/BIRT is intuitive enough to build serious reports.

Also, I'm impressed with the performance running the reports. I'm running eclipse/birt, mysql 5.0.17 and Tomcat all on my 1700 Mhz/ 1G Ram Thinkpad, and the graphs appear almost instantly (Yup, disabled my browser cache - it really is fast). We are looking at 16088 rows of rental data - I'm impressed this speed is possible even without the data cache such a used by Microsft Reporting services.

Final thoughts


You can put together a Reporting Platform based exclusively on open source products. It's quite easy to set it all up. BIRT may not be as advanced as some comercial products, but because no financial investments are involved purchasing software, theres no risk in trying. In fact, I think that in a lot of cases, the functionality provided by BIRT is quite sufficient to serve most reporting requirements.

Although BIRT may lack some more advanced features such as crosstabs, most of these will probably be added within an overseeable amount of time. Take a look at the BIRT project plan to see what is going on. I'm quite convinced we won't have to wait too long before we see a crosstab inside BIRT

Tuesday, December 20, 2005

'Doing' MS Access

I've just come home visiting one of my Inter Access co-workers.

Barbara is a very capable Oracle Developer, Information Analyst and BI Consultant. She enjoys the craftmanship so much that she still finds time to help out friends managing their little IT solutions.

For example, one of her friends owns a sports clothing store. She built them an order entry system in that popular little Database by Microsoft known as Access.

As you might expect, the system has grown over time, and has had too little administrative attention. Now, it's about 30Mb of data. This may not seem much, until you try to run a query. Size really is a relative measure.

What's worse, the number of users has grown too. Again, it may not seem much, but five users that are simultaneously working with one Access database is really about the limit. At least, it seems to be the case here.

She decided that it might be a good idea to try porting the application's backend to MySQL, and she asked me if I had any experience in porting such an application. 'Yeah..' I said, lying through my teeth of course, '..sure I have. I do that stuff all the time.'. 'Lyer..', she said, '..but do you think you can give me a hand?'.

Of course, I said yes. I really never did this, although I was aware of the builtin functionalities of the migration toolkit to do the legwork. And I'm always eager to goahead and try these kinds of things, especially since this is sort of a real, mission-critical application. (Just because we're doing this in our spare time, and that this concerns only a small sportsclothes shop does not change the fact that this application is mission-critical and that the migration should be planned and executed carefully).

Luckily, the application was already built in two separate chunks. The front end is an Access application: It contains some stored Queries ('Views'), some Forms and some VB code. The backend is also an access database. This one is worth 35 Mb of base tables. The front end communicates with the backend through a so-called 'linked table'. (For the MySQL techies: this offers the same functionality as the FEDERATED storage engine in MySQL)

In our first endeavours, we used the MySQL Migration Toolkit (download: here) to reverse engineer the database schema and to load the data into MySQL. This really went remarkebly well. We used default options in most of the cases, and I can only conclude that this is a very good way to import an access database into MySQL.

Then, we used the MySQL ODBC driver to link the existing Ms Access front end to the MySQL backend database. The immediate first impression regarding this solution is that query performance is much better than in the old situation, so that's a thumbs up for MySQL. And we didn't even do any tuning sofar.

A bit of a bummer is that we did encounter problems inserting data into tables with socalled AutoNumber columns (such a column is very much like an auto_increment in mysql).
In the fist place, the migration toolkit did NOT automatically map those to mysql auto_increment columns. This was fixed soon enough though. We just added the auto_increment attribute ourselves.

A more serious problem was that Access did not synchronize well upon insertion of a new row. The row would be put into the database, but immediately after insertion, the access front end would show the row as #deleted#. Refreshing the view on the data from inside Access would show the row, proving the insert did indeed take place, and proving access is capable of retrieving the newly inserted row.

We did some searching on the MySQL Access Migration forum but alas, to no avail. Lucky for us, a bit of browsing around brought us to the -unbeatable - reference manual. The "deleted" problem is described there exactly, and a workaround is ginven there too.

(By the way, the manual has an entire section devoted to using the MySQL ODBC driver in combination with Microsoft Access)

We had to change just two things. First, we had to enable the Return matching rows option in the MyODBC configuration dialog. This is available from the ODBC Driver manager. Then, we had to add a timestamp column to the tables:


alter table table1
add dummy timstamp
default current_timestamp
on update current_timestamp


...and that fixed the problem.

A point worth mentioning is that it really doesn't matter how you call that column. You don't even need to use it in your application. It just needs to be there, that's all.

Unfortunately, we are both at a loss as to explain why (and how) this works. We even did some ODBC tracing to see what happened. We couldn't find out how it works, but we could see something else that's interesting. After the INSERT, Access performs a SELECT, presumably to synchronize it's view on the data. Judging by the trace, that SELECT statement does NOT use the timestamp column in the WHEREclause.

Now, there still have to be some tests, but at least, we're very confident that this port to MySQL will be quite successful and without siginificant effort (thanks to the Migration toolkit)

Barbara promised she'd let me know if there's any progress or any troubles, so I will report them back in this blog, '...for the world to knwow...'

Friday, December 16, 2005

Some MySQL stuff I have been doing during my Holidays

During my holiday, I alo spent some time with MySQL.

I've been working on an article series for mysqldevelopment.com. This time, Andrew, Markus and yours truly have teamed up to cover (or at least, introduce) the interfaces you use to access MySQL from the (end-user) application. So far, we've come up with an introduction and an article describing Connector/J, the MySQL JDBC interface.

Given the amount of available API's/Connectors, it seems we've got our work cut out for us. But hey, dear reader, if you feel you want to contribute, don't hesitate and drop us a line.

I've been working on MySQL UDF's too. What a great and powerful feature! As I described in a previous entry (This is too wicked), I'm building a set of UDF's to port between Oracle and MySQL (not neccessarily in that direction)

To me, the most amazing thing sofar has been the discovery that MySQL already implements so much Oracle specific functions. I did notice a few before, but It really became clear to me once I started making an alphabetical list of functions (still busy with it). Or rather, since I started mapping Oracle's alphabetical list of functions from the Oracle SQL Reference to the MySQL functions.

I sure did learn a lot since I wrote my first UDF (Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers). I finally got rid of that pesky #include "winsock.h", and I developed a couple of macros, typedefs and such that make up a nice little reusable toolkit for creating UDF's (well, at least: I think so). I'll think I'll describe it in the blog in the near future.

Thursday, December 15, 2005

Having a bit of a Holiday

I've been enjoying a bit of a holiday lately. Nothing much, just two weeks off from my regular job.

Frankly, not having to go to work, celebrate "Sinterklaas" with my family, reading a novel (Sinterklaas gave me the Dutch translation of Michel Houellebecq's last novel, "La possibilité d’une île", Dank U Sinterklaasje! A certain iPOD Nano device also arrived on the 5th of december - what timing, I love it!) and making a walk on the beach (Yup! we saw a seal, a wild one that is, in Katwijk aan Zee. It looked as if it was making an attempt to swim into the Rhine River, in fact it was at the foot of the dunes, well on the shore).

DuckDB bag of tricks: Processing PGN chess games with DuckDB - Rolling up each game's lines into a single game row (6/6)

DuckDB bag of tricks is the banner I use on this blog to post my tips and tricks about DuckDB . This post is the sixth installment of a s...