David Smeringe weblog

by Josef from Eden Foundation.

Having an old MS Access database full of important data can be a real nuisance for cross-platform strategies. Lately, I’ve been evaluating what approach to use for making a Java desktop app run on Linux. The tricky thing is that the database it interfaces with is in the Microsoft Access format, developed at the time when that product was new and cutting edge (yes, that was in version 1.1). Now Access’ engine library ADO won’t run natively on Linux, so this far, my program has been limited to Windows by making use of the jdbc:odbc-bridge. Today, with Linux desktops quickly gaining importance, this needs to change. Migrating the database to another system like MySQL is a bit of a job, so I searched for less draconian alternatives. I found a promising library, Stels-MDB, that claims to handle Access’ MDB files through native Java code. I downloaded the trial version and gave it a run. Here is a summary of my experiences:

Switching the database driver from jdbc:odbc to Stels-MDB was very easy. It was just to adjust the two connection strings for the driver name and parameters, and voilà. I could run my app. But Stels does not behave identically to Microsoft’s ODBC driver, so there were some issues to look at.

Case sensitivity

Stels-MDB was case sensitive. This meant that a search criteria of “DALLI” used in a query would not detect records containing the value “Dalli”… A major issue in my eyes. Fortunately, support at J-Stels Software seems to be very good. Within a day or two, they had recompiled a new version of the driver with an option that removes case-sensitivity. I think SQL searches should be case-insensitive by default, but anyway, their solution was satisfactory as it only required me to make a trivial modification to the JDBC connection string. Great guys!

Different SQL syntax

Stels-MDB does not use the same SQL syntax as MS Access. Access, in fact, deviates from the Ansi SQL92 standard, and that’s a pity, because Access’ unique traits (such as using brackets for quoting fields) adds nothing of value compared to the standard syntax. So Stels could choose two smart ways here, either run the ugly MS syntax for compatibility with legacy Access-based apps, or they could follow the ANSI standard so that the Stels engine can be seamlessly plugged in and out of vendor-independent database apps that don’t care which underlying database you are using, only that you have a valid JDBC driver. Unfortunately, Stels have opted for a syntax that has some quirks of its own, probably to simplify coding and to keep the style with their other query engines (for CSV files and more). The most basic things work well; quoting fields can be done both through double quotes and backticks. But other ordinary stuff has to be done through special functions. A date value for instance, has to be wrapped into the function “to_date()” to be recognised. So as a developer, you easily risk embedding “Stels-only” sql into your program code. Or, you’ll have to create your statements through an sql-generating library[link], where you add a subclass to cover Stel’s peculiarities, which is what I did.

Syntax of AnsiSQLFormatter:
SELECT "Order ID", "Product ID", "Order Date" FROM "Orders" WHERE "Order Date"='25-Nov-2008 21:24:15'
Syntax of MsaccessSQLFormatter:
SELECT [Order ID], [Product ID], [Order Date] FROM [Orders] WHERE [Order Date]=#25-Nov-2008 21:24:15#
Syntax of StelsSQLFormatter:
SELECT `Order ID`, `Product ID`, `Order Date` FROM `Orders` WHERE `Order Date`=to_date('2008-11-25 21:24:15','yyyy-MM-dd HH:mm:ss')

Limited SQL features

Another problem I ran into with my queries, was that they were too complex for the subset of features currently supported by Stels. Subqueries, in particular, were not popular, they provided me with messages like this:

java.sql.SQLException: [StelsMDB JDBC driver] Can't parse SQL query: [SQL Engine API] Incorrect syntax near the word 'FROM' (position: 39). See realized SQL specification in the driver documentation. SQL query was: 'SELECT MAX(`Time`) AS `MaxTime` FROM (`Microplots` INNER JOIN `MicroplotInfo` ON `Microplots`.`Plot`=`MicroplotInfo`.`Plot` AND `Microplots`.`Bar`=`MicroplotInfo`.`Bar`) WHERE `Microplots`.`Year`=2008'

I also missed some common functions like Day(), Month() and Year(). But I could probably easily have added these to Stels myself, because it provides an extension mechanism for custom-made functions.

Jackcess under the hood

Although Stels-MDB is closed-source, it is based on the open-source library Jackcess. From what I understand, it basically wraps Jackcess’ non sql-based api into a query engine so you can use it in more normal ways, and, well, run queries on your data. Unfortunately, just as Stels’ query engine is not fully mature, Jackcess’ table handling doesn’t seem to be yet either. My tables were certainly of the more demanding type – developed over the years through basically every past version of Access, with lots of indexes and foreign keys (referential integrity in MS Access terms). They’re actually a headache in relationships. Knowing that the mdb format is cryptic and undocumented (I haven’t seen MS make any effort to help the developer community access this format, despite the product’s name…), I was not surprised that Jackcess had some hiccups. I couldn’t figure out what the root causes were, or didn’t spend enough time searching. Some problems were solved by compacting my database and removing obsolete indexes, whilst others wouldn’t go away. Here’s one error that stuck:

[StelsMDB JDBC driver] Error executing sql: [StelsMDB JDBC driver] Can't write 'Seeds' on the disk. Error was: Can't insert a record in the table 'Seeds' [Jackcess] FIXME cannot write indexes of this type yet.

I couldn’t reproduce this problem in a simpler, test database that I built from scratch, but then again, I did not invest the necessary time to make a full inquiry into the matter. But it’s clear from the FIXME that some features have not been implemented in Jackcess yet.

Performance

A little use of a stopwatch when running my code through Stels gave me a rough comparison of its speed to that of the ADO engine running through the jdbc:odbc layer. Stels is slow. The operations I timed took 6 – 10 times longer with Stels than with Access’ engine. This would have been ok if I was plundering mdb files for data in a series of one-time jobs. But it was too slow for running an ambitious desktop app with high demands on its database back-end.

Conclusion

All in all, Stels-MDB is a brave effort that really had my hopes up. But it isn’t mature enough for more high-end needs. I am very glad to see that there are people trying to provide solutions to us Java programmers sitting around with important mdb-files and wanting to take full advantage of the potentials of platform independence. I hope that both J-Stel Software and the Jackcess project make steady progress until that dream comes true. Figuring out the mdb format without official documentation from its software house is a formidable challenge… I also hope that Stels-MDB moves towards standard ANSI sql syntax, and still wonder why MS Access did not do so more than a decade ago. For now, we’ll have to look for something else here at Eden Foundation. Perhaps outright migration to MySQL? We’ll see…

Stuck in work process I launched Thunderbird to skim through the TYPO3 newslists. Apparently I haven’t checked that one for some time since Thunderbird suggested me to get the latest 500 posts Anyway. There was a really funny, or should I say sad, posting about a Russian rebrand/fork of TYPO3. First he uses a pseudonym posting news about a fork, then he denies ‘borrowing’ TYPO3 code and icons, finaly changing into admiting having to change a few lines of code and some icons.. doh.. – check out the site and judge for yourself.

It’s really sad that people just can’t see the fundamental philosophy of TYPO3 and the intentions of it’s original inventor Kasper Skårhöj, releasing his work as GPL:d open source software – Inspire people to share!

I few days earlier I stumbled upon a swedish fork/overlay work of TYPO3 called TYPO3 magenta. I got the similar feelings over this one as with that Russian guys fork. Though this is a bit more balancing on the edge. The TYPO3 name is still there but with a suffix. It’s open stated to be built on TYPO3. From the website (freely translated from Swedish using google..):

“TYPO3 Magenta is a web-based system to manage, organize and publish content on websites. TYPO3 Magenta is a further development of the publishing tool TYPO3. TYPO3 is based on open source which means significantly lower costs compared with commercial counterparts. TYPO3 Magenta has developed with the user in focus. The primary user group is Swedish small-and medium-sized enterprises.”

It’s sold in a license form with system, hosting and support.

But as I said, I find this solution better – while not good. I’m guessing it’s mostly about a nifty back end skinn. Refactoring TYPO3 this way is like saying “TYPO3 is good but old school. We make it modern and better with our own version”. And the main issue is still – what would a company loose going 100% TYPO3 and sharing improvements rather then trying to be so special?

I will be honest. We started our business with two CMS solutions. CMS made simple and TYPO3. One simple CMS for the simple sites and one advanced for the larger ones. We promoted them as simple and advanced cms. However during time I realized that both we and the solutions themselves would benefit much more exposing TYPO3 as it is, with it’s huge community and existing brand name rather then trying to make it seem more like our own, and so far it’s just been true. I think that the more credibility and reward the community gets for it’s efforts the better TYPO3 will be.

After all, the community is a vital part of TYPO3 together with it’s open source base. Alongside with that TYPO3 is also a very strong brand and should be promoted and empowered so.

Inspire people to share! – it will get us so much further

I’ve been looking at the XING networking platform from time to time, but never put myself into adding an account. However, now I’m there -> https://www.xing.com/profile/David_Smeringe <- and the first impression is quite positive. It feels very business focused as well as having great tools to maintain your network. Sadly there’s not that many swedes afaik. Maybe I just wasn’t looking under the right stone..

So, if your’e a XING user and think you apply to my network pattern (web development, business partners, TYPO3 people, and some more..), please include me in yours!

See my LinkedIn profile as well..

Seriously. We’re using Exchange as our group and mail server. And it’s been working rather good I must say. What I thought. Found out now that – at least since SP2 – we’ve had serious troubles sending to mail servers using grey listning. funny… One fix is to add a batch script restarting smtp server every day (forcing it to resend the queued mail). I gave up and added a smarthost instead and now I rely on my broadband provider.

So now I’m waiting for people to start reacting on my old mails finally delivered. Wooho.

For anyone else forced to play exchange-guru on your own due to costs or whatever.. this is what I did:

• Added a scheduled batch script restarting the smtp server
  net stop smtpsvc
  net start smtpsvc
• Added a non-existing registry key:
  HKLM\System\CurrentControlSet\Services\SMTPSVC\Queuing\
  “GlitchRetrySeconds” – DWORD set to 300.
• Added my broadband providers smtp server as a smarthost in Exchange following http://www.arrowmail.co.uk/howto/smrthost.aspx

Hope helps someone else. Now back to real work. Back to my PHP code, away from Exchange, away from Windows server..

Finally I took a quick test of TYPO3 version 4.3 alpha 1. Read the link for a detailed release info. However I’d like to highlight one update a bit extra for you. The TypoScript editing extension that made life prittier highlightning and helping out with the indentation for the TS code when editing has now got itself a usefull and very nicely implemented code completion function. Look at the image below.

It’s perfect for people like me that just cant get all those object and attribute names to stick in memory.

Now, I’m having a hard time waiting for next alpha or maybe beta so I can test the new front end editing fuzz which at least seems awsome!. Check it out at Jeff Segars blogg.

I’ve actually had trouble with this for quite some time. I’m using the commerce extension for TYPO3 for a site. The plan is to turn the site into an e-com site eventually, but starting of with plain product listings so I thought I’d give the commerce extension a shot. It’s quite comprehensive. Amongst a lot of things it has a good internal structure to allow all kinds of variants of an article – especially suitable for things like textiles where you have a combination of color and sizes making an article.

One big downside I feel currently is the template setup. It’s heavily templated, but still not really as flexible as I’d like. Could be a competence/knowledge issue – but nah! I’m smart! really..

However I’ve had some real troubles showing the prices. Really. Seems so basic, but I just couldn’t get into it. Today I logged into the #typo3 channel @ freenode irc. Thanks to friendly help from PiMB there I got it working. It all had to do with setting the currency. I ended up with this in my TS:

The default for ..pi1.currency was – “currency” – and not a real currency – like SEK or EUR. That’s all. the other FE commerce plugings actually has EUR as default.

Anyway. by this post I’d like to state that the open source community is (at least around TYPO3) very friendly and helpsome. – and – thanks PiMP for the hint

The site – still in a bit of progress (have some wierd positioning in the templates) – can be found here (swedish) http://www.byylva.se/. High quality carpets and other textiles

cheers. And a happy new Year!

As soon as Adobe CS4 was released we got our hands on it. Since I’ve been doing quite a lot Flash things lately I was hoping the new version would reveal some greate new features. However after trying it for couple of weeks now, I can only summarize the experience as; grumpy. To begin with Flash crashes for me when opening most of my old cs3 files, doesn’t recognize an old flash project file (.flp), crashes totaly if I’m to fast clicking around (hey.. it’s fun to click around with the mouse). The last behaviour is the same for the other nifty tools such as Illustrator and Fireworks.

They die right in front of me leaving me with a broken (long-time-since-last-save) file. Some say it’s about fonts, some say it’s about somethingelse. I saw someone who solved it by buying a new computer..

I contacted Adobe in for the subject, posted an error report and gave them full dumps of my machines current state.. getting the reply to go through a bunch of help yourself links with twenty steps each! Things like that make me grumby.

Anyway.. I’ve tried reinstalling the entire CS4 premium package (uninstall and install taking for ages) without success.. and yeah. one last thing.. Acrobat reader now crashes if I try to open a pdf link within the (a/any) browser. Thanks..

regards,
A grumpy web developer

I admit. I’m an IDE and RAD lover. And I enjoy trying out new development enviroments all the time in the quest for the perfect work tools. After using Eclipse for quite a long time for PHP, I switched to Activestate’s Komodo 4.4 IDE. Komodo is a really nice and slick IDE with by far the best PHP code completition I’ve found in any tool. The debugging is easy to setup using xdebugger and it’s easy to analyse your code using watches and analysing tools. One tool I found useful was the Watch file tool wich could be compared to the tail -f command in Linux (allows you to ‘stream’ the data of a file to output. useful to analyze log files on the fly for instance).

However, I think Komodo suffers a bit from a ruff UI, loosing variables and values in debugging sessions, forgets it’s line position and break point positions from time to time and has a limited subversion integration.

Then I found NetBeans early access for PHP. This was a really nice surprise, however I couldn’t get debugging working so I switched back to Komodo again. But now Netbeans.org released the RC1 of version 6.5. After some fiddeling I got the debbuger working (I guess it really didn’t have to do with rc1 but still..) and by that I’ve got my new favourite environment. Main advantages are for me:

• The debugger is the best PHP debugger I’ve tried.
• A slick and fast UI. Very easy and intuitive to rearrange panels and views almost in any way I’d like
• Superb subversion support. Direct markers for changes, easy shortcuts to view diffs and revert actions.
• Good refactoring support (things like Ctrl+R to refactor a variable etc).
• Built in SQL editor with code completion
• Active and easy class/property inspector (Komodo lists all open files, Netbeans shows only active or selected file in file list without opening it)
• and so on..

A screen shot attached, editing a TYPO3 ext project for a client.. note the SVN popup..

TYPO3 bug hunt day is an official day with an official IRC channel for hunting down bugs together. I missed the first two bug hunting days, but this time I really hope to be able to help out. I like the initiative of a bug day to get the general community more involved into TYPO3. Let’s hunts some bugs shall we? (c’mon. join me )

Me and my colleague decided to register for the annual TYPO3 conference – t3con08. This year it’s taking place in Berlin, which will be cool as I’ve never been there. But the best part will hopefully be to meet a lot of TYPO3 users and developers – perhaps even one or two from freenode channel #typo3 where I’m hanging around from time to time.

Not only did we register for the conference but also to become association members for Merea, our company, which feels like the right choice to do as we focus our business more and more around TYPO3 services. It’s a good way of giving back something to the product and community as well as taking further engagement with TYPO3.

So, I hope to meet a lot of friendly faces and get loaded with inspiration and knowledge.