Erik's Diary

First Android app published

2011-06-12T10:43:00.003+02:00

Yesterday I published my first Android app on Android market, UnSilence. The background is that I tend to forget to turn my cellphone back to normal after I've set it to silent during a meeting. I have missed too many calls beacuse of that...

So UnSilence helps you be allowing you to set your phone to silent (with or without vibaration) for a period of time, and then automatically turn sound back on.

The main design goal was too keep it simple, one click to start the app, and one click to set to silent, that's all.

Servlet http proxy

2011-03-18T14:40:00.011+01:00

For a while I have been working on a project where we are trying to integrate a windows-based service in a Java EE environment. Basically the problem is to host an Ajax application under an application server like IBM Webspehere or Apache Tomcat. We also want to support portlets following the JSR 268 specifikation.

I googled around a bit and found lots of useful stuff, but nothing that did all that we wanted. The mainly problems was with headers and cookies, since we need to keep a session to the servicein the background we need to make those work. So I ended up writing my own. Not much code, but it might be useful for someone else, as it is pretty general stuff.

The service method

Since we want to handle all HTTP calls, we override the service method of HttpServlet. If you want only some methods (GET and PUT for example) to work you might just override doGet and doPost for example, but we want it all.. The implementation looks like this:

protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String qry = req.getQueryString();
URL url = new URL(protocol, host, port, req.getRequestURI()
+ (qry == null ? "" : "?" + qry));
HttpURLConnection urlConnection = (HttpURLConnection) url
.openConnection();
urlConnection.setDoOutput(true);
urlConnection.setDoInput(true);
urlConnection.setUseCaches(false);
urlConnection.setInstanceFollowRedirects(false);
urlConnection.setRequestMethod(req.getMethod());
handleRequestHeaders(req, urlConnection);
handlePostData(req, urlConnection);
// make the call - get response code
int responseCode = urlConnection.getResponseCode();

resp.setStatus(responseCode);
if (responseCode != HttpURLConnection.HTTP_OK)
log.info("HTTP code:" + responseCode + " "
+ urlConnection.getResponseMessage() + " URL["
+ url.toString() + "] " + req.getMethod());
else
log.debug("HTTP code:" + responseCode + " URL[" + url.toString()
+ "] " + req.getMethod());
handleResponseHeaders(resp, urlConnection);
// send output to client
handleContent(resp, urlConnection, responseCode);
}

This is pretty straight forward. The protocol (http/https), server and port to use are configurable. We take the URI from the request and add the query string, if there is one. Together with the configure host etc, this forms the new URL, which we open, using the same method as in the call. We set some parameters, request headers and post data if there is any.

We then call the URL, check the HTTP response code, and take care of the headers and the response, which all are sent to the client.

Handle headers
The part that we really needed was the headers. This simply transfers the headers between the two connections, with a few exceptions:

private void handleRequestHeaders(HttpServletRequest req,
HttpURLConnection urlConnection) {
// set request headers
Enumeration e = req.getHeaderNames();
while (e.hasMoreElements()) {
String key = (String) e.nextElement();
if (!"Host".equalsIgnoreCase(key)
&& !"Accept-Encoding".equalsIgnoreCase(key)) {
String value = req.getHeader(key);
log.debug("Request Header: " + key + ": " + value);
urlConnection.setRequestProperty((String) key, value);
}
}
//add your own headers here
}

There are two headers that we dont want to transfer from the client's request to our new request:

The Host header, since this will be our proxy server. The new Host header will be added automatically, so we don't need to think about it.
The Accept-Encoding header. This is a fix, the Accept-Encoding header turns gzip on, and since we have not implemented gzip in our proxy we don't want it. Another alternative would be to turn gzipping of requests off in our web server, perhaps this would have been better. If we do transfer th Accept-Encoding header, both servers might zip the content, which will not work.

Finally in this method we add our own headers, which is basically the reason why we need the proxy at all.

When we get the response back from the server we have the corresponding method to handle the response headers:

private void handleResponseHeaders(HttpServletResponse resp,
HttpURLConnection urlConnection) {
// set response headers
Map<string,>> headers = urlConnection.getHeaderFields();
Set<map.entry<string,>>> entrySet = headers.entrySet();
for (Map.Entry<string,>> entry : entrySet) {
String key = entry.getKey();
List<string>> headerValues = entry.getValue();
for (String value : headerValues) {
if (key != null && !"Server".equalsIgnoreCase(key)) {
log.debug("Response Header: " + key + ": " + value);
resp.addHeader(key, value);
}
}
}
}

This is basically the same as the request headers. In this case we just filter the Server header, where our proxy application server will add its own name.

Finally to set it up in web.xml we configure the URLs we want the proxy to handle, set the protocol, host and port and we are ready to go.

NoClassDefFoundError in BlackBerry

2010-09-09T14:47:00.002+02:00

I have been struggling a few days with a strange error in BlackBerry. My app works fine in the emulator, but when you start it on BlackBerry Bold (software version 4.5) you get a NoClassDefFoundError. On BlackBerry Storm (software version 4.6) it works fine, but on Bold2 (version 5.0) again you get NoClassDefFoundError.

The documentation says:

Thrown if the Java Virtual Machine tries to load in the definition of a class (as part of a normal method call or as part of creating a new instance using the new expression) and no definition of the class could be found.

The searched-for class definition existed when the currently executing class was compiled, but the definition can no longer be found.

I have gone through all the changes since the last working version and I finally found it. I had added some fields like this:

fldPortHttp = new TextField("Port:", "", 20, Field.EDITABLE | BasicEditField.FILTER_NUMERIC);

This worked well, with no compilation errors and in the emulator, but caused the NoClassDefFoundError at runtime. The solution was simply to change to:

fldPortHttp = new BasicEditField("Port:", "", 20, Field.EDITABLE | BasicEditField.FILTER_NUMERIC);

BlackBerry Java Application development

2010-09-04T06:55:00.002+02:00

When I first started with BlackBerry development a few years ago it was difficult to find books on BlackBerry development. Since then interest has grown and today it is much easier to find good books on the subject.

I have just read one of the newer, BlackBerry Java Application Development: Beginner's Guide by Bill Foust. This is a useful book, even if you, like myself, is not a Beginner. It gives you a good overivew over the BlackBerry UI, which is probably what differs most from Java ME. It also has some material on more advanced subjects, like saving data and communicating over the network, which even though it uses the same classes and calls as standard Java ME is a bit more complicated.

The future for mobile java

2010-05-09T16:02:00.003+02:00

The last three years or so I have worked mostly with mobile java, starting with Java ME and later moving into BlackBerr and Android. A lot of things has happened, three ears ago Android existed, but ther were hardly any phones available and BlackBerry did exist, but I knew nothing about it.

Today the future of Java ME is uncertain. Programming in Java is a good option on most mobile platforms (except iPhone), but the strongest alternatives are Android and BlackBerry, not Java ME.

There are of course different reasons for this. On the technical side, the User Interface options are very limited. LCDUI is very restricted, you very quickly end up with skipping the higher level API, because it is too restricted, and using the Canvas API, where you basically have to paint everything yourself. Doing input in a canvas based interface is difficult, you can react on key down and up, but the standard does not really allow you to tell if the devices even has a keyboard, or if you need to display a virtual keyboard on the screen.

An even worse problem is the fragmentation of the market. Java implementations differ, and even if it is very probable that your java me code will run on most devices (if it is well written) verifying that it does is not a smal task. And if you want it too look good it is even worse. Emulators do not help much either, verifing that something runs in the emulator does not guarantee that it will work on the device. And it is not even sure that there is an emultor for your target device.

And phone manufacturers keep making new models all the time... Apple basically has one platform and offers upgrade for old versions (sometimes free of charge, sometimes at a small charge) but the competition has not learned from this. Instead they have incompatible version on different devices, where you basically have to test on, if not all, several different versions. Just keeping up with the new models from the main manufacturers ( there are only four or five) takes a lot of time. The so called java verification does not guarantee that your application will run on all Java ME implementations with the features you need, instead you have to verify it again and again as there are new devices.

All in all this leads to that Java ME is not a good alternative for your application development today. The future of java om the mobile is rather in Android or BlackBerry. That is if not one of the major manufacturers take steps to fix this. And frankly I don't see this happening.

An exception to this is possibly Symbian. Java ME implementation on Symbian is pretty good. But it seems like Nokia is more interested in promoting development in C++, or rather the very restricted C++ available on Symbian.

New Antenna release

2010-01-06T21:04:00.002+01:00

Today I released version 1.2.1 of Antenna. The new version includes both bug fixes and new features.

I made a mistake in the 1.2.0 release, which leads to a Null Pointer Exception if the Wireless Toolkit can not be detected. Antenna can detect a lot of toolkit's, but not all, so this has given users some poblems. A patch has been in the repository for a whil, but now it is in a release also.

When the 1.2.0 release was made, ther was no Java ME SDK for Mac available, but now there is. Unfortunately the file we used in 1.2.0 to detect Java ME SDK 3.0 is not included in the Mac version, so toolkit detection fails. This is fixed in the 1.2.1 release, which should work with Java ME SDK for Mac ( I haven't tested it myself, since I don't have a mac).

The realease also includes som contributed patches:
- obfusactor arguments can now be passed to WtkPackage
- Key stor type can be set in WtkSign
- WtkRapc can build libraries

More info on antenna and the release is available here.

Supporting BlackBerry touch devices

2010-01-01T15:28:00.003+01:00

Our BlackBerry application is built using BlackBerry software version 4.5. Unfortunately this means that we can not handle touch events, since they are only supported from version 4.7. Since most of our users have 4.5 or 4.6 this is not an option. Up til now the application has had very limited functionality on touch devices like BlackBerry Storm, running in the so called *compatibility mode' with the virtual keyboard displayed all the time and only about half the screen available for our application.

But now we have made a few changes:
We upgraded the Storm device software (to version 4.7.0.181). This solved the problem with the virtual keyboard, which now can be displayed only when needed.

We revised our application. The main problem is our custom object, which did not work well. This seems to be because the navigationMovement method is not called on a touch device. Since we had some application logic in this method in our custom object, it did not work well. The solution was to move the code to the moveFocus mthod, which is called both on touch and keyborad devices.

In the long run we probably will have to provide different installations for users with 4.5/4.6 or 4.7 software version. Still we do not support more advanced touch screen usage.

Antenna 1.2.0 with support for Java ME SDK 3.0 released

2009-09-27T10:25:00.003+02:00

Yesterday I made the new antenna version with support for Java ME SDK 3.0 available for download. The testers have been positive so far, so it is ready for more general use.

The changes are documented in here. You will also find the matrix with toolkits ad optional libraries there, it seems like Blogger don't really like my html table...

The download is available here.

Java Me toolkit's and support for optional JSR's

2009-09-23T14:32:00.004+02:00

As a part of the new Antenna release I have reviewed the toolkits supported and which JSR's they support. The result is the matrix below.

As you see, many JSR's are widely supported, while others are supported only by a few manufacturers. If you need Bluetooth, PDA, Wireless Messaging, SVG or Mobile Media, they are available in most emulators. But if you need Internationalization, or Open GL your choice is more limited.

Many manufacturers have their own additional APIs. Most used is Nokia UI, which is actually also supported by Sony Ericsson.

JSR API Support	Antenna property	Sun WTK 2.5	Java ME SDK 3.0	MOTODEV Studio for Java ME	Nokia S60 3rd Edition SDK	Nokia N97 SDK	Nokia S40 5^thed	Sony Ericsson SDK 2.5	Sprint WTK 3.3.2	LG SDK 1.3 for Java ME
CLDC version		1.0,1.1	1.0,1.1	1.1	1.1	1.1	1.0, 1.1	1.0, 1.1	1.0, 1.1	1.0, 1.1
MIDP version		1.0,2.0,2.1	1.0,2.0,2.1	2.1(2.0)	2.0,2.1	2.0,2.1	1.0,2.0,2.1	1.0,2.0,2.1	1.0,2.0,2.1	1.0,2.0,2.1
JSR 75 PDA	optionalpda	x	x	x	x	x	x	x	x	x
JSR 82 Bluetooth	Bluetooth	x	x	x	x	x	x	x	x	x
JSR 135 Mobile Media 1.2	mmapi	x	x	x	x	x	x	x	x	x
JSR 172 Web Services	j2mews	x	x	x	x	x	x	x	x	x
JSR 177 Security and Trust Services	satsa	x	x	x	x	x	x	x	x	x
JSR 179 Location	locationservices	x	x	x	x	x		x	x	x
JSR 180 SIP	sipapi	x	x	x	x			x	x	x
JSR 184 Mobile 3D Graphics	java3d	x	x	x	x	x	x	x	x	x
JSR 120 WMA 1.0	wma	x	x	x	x	x	x	x	x	x
JSR 205 Wireless Messaging 2.0	wma2	x	x	x	x	x	x	x	x	x
JSR 209 AGUI	agui		x
JSR 211 Content Handler	contenthandler	x	x	x			x	x	x	x
JSR 226 Scalable 2D Vector Graphics	s2dvgapi	x	x	x	x	x	x	x	x	x
JSR 229 Payment	papi	x	x					x	x	x
JSR 234 Advanced Multimedia Supplements	ams	x	x	x	x	x	x	x	x	x
JSR 238 Mobile Internationalization	miapi	x	x	x				x	x	x
JSR 239 Java Binding for OpenGL ES	opengl	x	x	x				x		x
JSR 256 Mobile Sensor	mobilesensor		x			x				x
JSR 257 Contactless Communication	rfid									x
JSR 280 XML	xml		x
JSR 300 DRM	drm									x
G24 MOTO2MOTO	G24			x
Motorola APIs	motorola			x
Nokia UI API	nokiaui					x	x	x
eSWT API	eswt					x
IAP Info	iapinfo					x

Antenna: Support for LG SDK 1.3 for Java ME and Nokia N97 SDK

2009-09-19T17:07:00.003+02:00

Today I added support for LG SDK 1.3 for Java ME and Nokia N97 SDK to antenna. This is available now in the repository and hopefully soon as a binary download.

Antenna Java ME SDK 3.0 version now available

2009-08-26T22:06:00.002+02:00

My patch for antenna is now comitted into the repository and is available for download. Still no build available, you have to get the sourcecode yourself and build it, or send me a mail and I will send you a jar file. Hopefully the pre-built version will be available soon. Documentation is also not updated yet, but you will find some in this blog.

Antenna support for Java ME SDK 3.0

2009-07-08T06:08:00.006+02:00

For building our Java ME application we use Antenna. It is a great tool, that makes automated builds possible and also works well as your regular build tool. But it lacks support for Java ME SDK 3.0 and other newer toolkits. Since we need that, I have made a contribution to the Antenna project, added as a patch yesterday.
If you want to try the modified verions, send me a mail and I will send you the jar.

How it works

A key property of your Antenna setup is the wtk.home property. It should point to the base directory of your WTK/Java ME SDK installation. Antenna uses this to detect which toolkit you are using. In the older version toolkits are hardcoded into Antenna sourcecode. In the new version textfiles are used to set up available toolkits and their properties. Antenna goes through a list of toolkits, described in the text file autodetect.txt, and checks if the unique files for that specific toolkit are available. If they are, the right toolkit have been found, and properties are loaded from a properties file.

This makes it easy to add a new toolkit or new properties for the existing one.

Supported toolkits

I have tested the new version with the following toolkits:
- SUN Java ME SDK 3.0
- Sun WTK 2.5.2
- MOTODEV SDK for Java ME v2
- Nokia S60_3rd_FP2_SDK_v1.1 (Symbian)
- SPRINT WTK 332
- Samsung_SDK_11 (autodetected as WTK 2.5.2)
- SonyEricsson WTK 2.5.2
- Nokia S40 5th Edition SDK Feature

Pack 1

I have also made some tests with Mpowerplayer, but the stub for CLDC 1.1 provided with Mpowerplayer seems not to match the specification. If I replace it with the one from Suns WTK 2.5.2 it seems OK. I have not tested Mpowerplayers preverifier, since it is Mac based and I use a Windows PC.

Antenna setup

Antenna properties are described here. They work as before, with the new feature that additional libraries can be defined dynamically, in the toolkit properties file, without any need for changes in Antenna source code. If I set up a property for a toolkit like this:


jsr999=lib/jsr999.jar

I can then turn it on in my ant file by setting the property wtk.jsr199.enabled to true. I can also turn on all defined additional libraries by setting property wtk.all.enabled to true. During development I used this, since Antenna checks that all jar files are available.

Adding a new Toolkit
You can add a new toolkit to antenna just by editing a text file and adding another. Do like this:
First you add a new line to the file autodetext.txt (in the res directory). It should look something like this:


#sony ericsson
sonyericwtk2;lib/semc_ext_jp8.jar;lib/cldcapi11.jar

Lines starting with # are comments. The lines defining toolkits should start with the toolkit property filename and then contain a list of files that are unique to this toolkit. File names should be relative to wtk.home.

Then you add a properties file, in this case sonyericwtk2.properties. It looks something like this:


name="Sony Ericsson WTK2"
include=wtk25
nokiaui=lib/nokiaext.jar
semc=lib/semc_ext_jp8.jar
vodafone=lib/vscl21.jar

The properties you could use are:

name: a descriptive name for the toolkit. Use this always.
preverifyversion: 1 means WTK 1 style, support only CLDC 1.0, 2 means WTK 2 style, include Target parameter in preverify command, 3 means WTK 3 style, only add parameter cldc1.0 if cldc 1.0 is used
emulator: exe or jar file for emulator
cldc10,cldc11: libraries for cldc versions
midp10,midp20,midp21: libraries for midp versions
include: include another toolkit definition. Useful if the toolkit is an add-on to another toolkit, like WTK 2.5 or Java ME SDK 3.0

All other properties will be taken for add-on libraries that will be added to classpath if wtk.[propertiy].enabled is true, or if wtk.all.enabled is true.

Please let me know if you have any problems or improvement suggestions.

Catch System.out from BlackBerry simulator

2009-06-30T09:25:00.002+02:00

Just a little trick I just learned. Sometimes I want a quick and easy logging, just to see what's happening in my program. The easiest approach, specially early in the project is simply using System.out. But when you run your program in the emulator you don't see system out anywhere. To see it you need the command line parameter /app-param=JvmDebugFile.

Just run the emulator as follows:


fledge /handheld=9000 "/app-param=JvmDebugFile:bblog.txt"

In the bblog.txt you will get a lot of info, and your System.out output.

Java ME automated build

2009-06-17T11:50:00.000+02:00

So far I have built my Java ME application using Sun Java ME SDK 3.0 and before the SDK was available with Sun Wireless Toolkit. But now we need to streamline the process and introduce automated build.

The tool we use for this is ant, with an add-on for Java ME called antenna. Antenna is an open source project, which provides support for operations like obfuscating, updating manifest and JAD files and signing.

Setup
To use antenna you need to setup the antenna tasks, using taskdef, and define some properties:


 <taskdef resource="antenna.properties"    classpath="${basedir}\tools\antenna.jar" />
  <property name="wtk.home" location="C:\WTK2.5.2"/>
  <property name="wtk.cldc.version" value="1.1"/>
  <property name="wtk.midp.version" value="2.0"/>
  <property name="wtk.proguard.home" value="${basedir}\tools\proguard4.3"/>

As you can see, we have a directory called tools, where we keep the antenna jar and also a proguard installation, which we need for obfuscation.
Antenna also needs to know where your wireless toolkit is installed and which versions of CLDC and MIDP you use. That's it!

Package
Antennas main function is packaging your application into jar and jad files, preverified, obfuscated and with correct manifest and jad files. You can do this step-by-step, with different tasks for each step, but you can also use the wtkpackage task to make them all.This is the approach we have taken.


<target name="package" depends="compile">
        <mkdir dir="${dist.dir}"/>
        <copy file="${basedir}/myapp.jad" overwrite="true" failonerror="true" tofile="${dist.dir}/myapp.jad"/>
    
        <wtkpackage jarfile="${dist.dir}/myapp.jar"
                            jadfile="${dist.dir}/myapp.jad"
                            obfuscate="true"
                            preverify="true">
               
                <fileset dir="${class.dir}"/>
                <fileset dir="${res.dir}"/>

                <exclude_from_manifest name="myapp-dir"/>
                <exclude_from_manifest name="myapp-host"/>

        </wtkpackage>
        <copy file="${dist.dir}/myapp.jad" overwrite="true" failonerror="true" tofile="${dist.dir}/myappu.jad"/>

        </target>

Comments:

compilation is separate, our package task is made after the compilation
we keep a jad template in the project basedir, which we copy to the dist dir
wtkpackage will create the jar file and update the jad file. We also want it to preverify and obfuscate using proguard
in ${class.dir} we have all classes that should be included
all used resources are in ${res.dir}
we have two parameters in our JAD template that should be only in the JAD file and not in the Manifest file. This allows for changing them without resigning.
as a last step we copy the jad file. This is to have an unsigned copy for use in emulators and phones that don't support the Verisign certificate we use (Nokia S40..)

Signing the application
Antenna also supports signing using the very straightforward wtksign task:


<target name="sign" depends="package">
        <wtksign
            keystore="${basedir}/../cert/keystore.ks"
            jarfile="${dist.dir}/myapp.jar"
            jadfile="${dist.dir}/myapp.jad"
            storepass="******"
            certpass="******"
            certalias="myalias"
        />

    </target>

This will add signature information to the JAD file using the certificate in the keystore and the JAD and jar files are redy to use.

Going native on BlackBerry

2009-06-03T08:35:00.002+02:00

Our application is currently based on Java ME and uses the lcdui to a 100%, even though there are some fixes to make it work better on BlackBerry. Since BlackBerry is getting more and more popular in the market we have decided to make a version that uses the BlackBerry native UI instead of Java ME standard lcdui.

BlackBerry does not allow combining its native UI with lcdui, which means we will have to convert everything, including the midlet. The majority of the code is however in our own classes, since 90% of tha application is Canvas-based. It seems quite possible to make them work in both environments.

Major changes will be:
- our midlet is replaced by a UIApplication
- the Canvas will be replaced by a MainScreen
- instead of the List we use we will have a KeywordFilterField, which will gives the users better functionality.

Our components will be placed in the MainScreen (instead of a Java ME Canvas). To make this work we will use an interface for painting with two different implementations, that encapsulate Java ME Graphics and BlackBerry Graphics. We will also need to handle the different Font classes and lcdui Image versus BlackBerry Bitmap.

Our immediate gains will be:
- scalable fonts, which is a problem for us now, since the Lcdui fonts on BlackBerry Bold are very large
- some UI improvements, like the KeyWordFilterField instead of the List etc
We will also gain access to possiblities like opening an input field on top of ouyr Canvas, with we really need and have a better looking app.

Java ME Search Engine

2009-05-05T13:22:00.003+02:00

Finding information on Java ME issues can be time consuming. A lot of information is available on the Internet, and Google finds lots of it. But most of the time you get Java ME information mixed with a lot of Java SE information.

To solve this I have collected a lot of Java ME resources and created a Google customized Search Engine. This is a first try, based on some links I had collected. There is certainly a lot more resources available out there. If you know one, don't hesitate to send me a mail.

Here is the search box:

Analyzing Java ME memory heap

2009-04-30T08:47:00.008+02:00

We have some memory problems with our Java ME application. On some platforms we get out of memory error after working a while. To some extent this is expected, and the error is handled reasonably well, but nether the less we wanted to get a view of how memory is used.

The Java ME SDK 3.0 includes some tools for this. They are not available in the IDE but has to be started from the command line. So you open two command line windows. In the first you start your midlet:


cd C:\Java_ME_platform_SDK_3.0\bin
C:\Java_ME_platform_SDK_3.0\bin>emulator.exe -Xdevice:DefaultCldcPhone1 -Xdebug -Xrunjdwp:transport=dt_socket,suspend=n,server=y,address=51307 -Xdescriptor:[jadfile]

This should start the emulator with your midlet. I choose the DefaultDldcPhone1 which is configured to have 4 MB of heap. You can change that in the device properties if you want to.

In your second command window you start the memory profiler:


cd C:\Java_ME_platform_SDK_3.0\bin
memory-profiler.exe -host 127.0.0.1 -port 51307

This should open up the memory profiler window. You then press Connect to cennect to your emulator instance.This will take a while but then you get a window something like this:

Now you press resume and run your application and try to recreate your memory problems. After that you press Pause in the memory tool. This will cause the tool to get a memory dump from your runing application. You will get a memory map at the top and at the bottom left a list of classes and primitives. You can select a class in the list and see what instances exist. It is however quite difficult to find information this way. Better is to use the statistics button. This will get you a window something like this:

The data is:

class name or primitive. a [ means 'array of'
object number (or number of objects rather)
size
average size
% of heap
live %
old generation %

Since you can not search in this table I copy it al and paste into a spereadsheet, where I can find my classes easier.

Java ME SDK and SSL

2009-04-28T16:43:00.002+02:00

I have been struggling a few days with getting Https communication to work in Java ME SDK 3.0, but with little success so far. To be able to create a Https connection I need to get the SDK to accept the (self-signed) certificate on my test server. The SDK does not however use the Java SE keystore but has its own (actually it has a lot of keystore's) with its own tool to update it. So far I have not managed to get my certificate into the right keystore. Tried asking on Sun's forum, but they don't seem to be interested in the problem at all.

So instead I used Nokia's S60 emulator, which gives me the option to accept the server certificate even though it can not be verified. Perhaps I should give up on the SDK for now, specially since the support and documentation is not really good enough.

Programming for BlackBerry with Java ME

2009-03-16T13:54:00.004+01:00

Research in Motion BlackBerry phones support the Java ME standard, so for a Java ME developer is easy to get started on BlackBerry. But a few things do not work as expected...

Menu
The BlackBerry has a very consistent user interface, with a menu key and a back key. These keys can not be catched by your Java ME canvas, pressing them will not call the keyPressed() method as you might have expected. Instead the menu key will always open the menu. If you have not added Java ME commands to the Canvas, the BlackBerry will create a menu itself, with 'Close' as the only alternative. The back key will trigger the command with the command type EXIT if there is one and do nothing is there is no such command.

This basically means that you should always use Java ME Commands in yor BlackBerry application. There is no other way within the Java ME API to get the BB keys to work as expected.

Other keys
There are no Nokia-style softkeys on a BlackBerry keyboard. But there is a trackball (trackwheel on older models) and a qwerty keyboard.

The trackwheel sends Canvas UP, DOWN, RIGHT and LEFT codes and trackball press can also be handled and expected. Your application should probably use these keys a lot. Key repeat is however not supported, so if the user scrolls the trackball you will get a series of keyPressed events and no keyRepeated.

The qwerty keys will call keyPressed with codes as expected, you can use getKeyName. Just remember that some models have a smaller keyboard, with two characters per key. You will always get the first character.

The return, backspace and space keys will also deliver key events as expected. The shift and alt keys will however not. Instead they affect the keycode delivered to the application. That is ok for the qwerty keys, but also it means that you can not tell if the user is holding down shift and scrolling or pressing the trackball.

Fullscreen mode
You Java ME Canvas will always cover the full screen. The call to setFullScreenMode has no effect. This also means that the sizeChanged method will never be called. So you should make sure that your application does not depend on it beging called.

The conclusion is that the BlackBerry Java ME implementation is a good one, and can be very well be used as a base for your implementation on BlackBerry. But if your midlet is based on the Canvas class and developed for Nokia-style phones you will probably need to make some changes, mainly with menues, softkeys etc. You can them detect at runtime if the midlet is on a BlackBerry or not and adjust to that.

Java ME on BlackBerry

2009-03-12T09:11:00.000+01:00

One of the platforms you can support with Java ME is BlackBerry. BlackBerry devices are a bit different from your ordinary cellphone. They are very good at email and includes a handful of applications.

Most of the applications for BlackBerry use the native Java API, but the device also supports Java ME midlet applications. But there are a few points to note...

Create COD files
BlackBerry supports standard jar/jad files to some extent, and installing your application entering a jad URL in the phones browser might work very well. But my experience is that sometimes it doesn't. Converting the jad/jar files to BlackBerry cod file format on your PC might be a better idea.

For this you need BalckBerry Java development tools. I use BlackBerry JDE 4.6.0, but there are new versions. You find them here.

From the command line you run something like:


"C:\Program Files\Research In Motion\BlackBerry JDE Component Package 4.6.0\bin\rapc" import="C:\Program Files\Research In Motion\BlackBerry JDE Component Package 4.6.0\lib\net_rim_api.jar" codename=XXX -midlet jad=yyy.jad yyy.jar

This will take your yyy jar and jad files and create XXX.cod. It will also modify your jad file and add a lot of BlackBerry-specific lines to it. This makes the JAD file much larger, so will probably not want to use it for non-BlackBerry devices, even if the original data is still there (some devices have restrictions on jad file size).

If your midlet is large, it will be split into several cod files. They will all be in the yyy.cod, which might by a zip archive. So you need to check if it is (you can also check the modified jad file to see if there are several cod files in it). If it is you should unpack it and put the individual cod files on your web server together with the modified jad file. Make sure that the cod mimetype is configured on your web server, and your cod file is ready to install.

Portable Java ME development

2009-03-09T11:58:00.005+01:00

The last six months or so I have been busy with developing a new application in Java ME environment. I have done some work in this envirnment before, but even though the platform has not changed much, my impression is that the quality of the implementations have improved a lot.

The goal was to support a wide selection of mobile phones:
- Nokia, both System 40 phones and Symbian System 60
- Research in Motion BlackBerry, at least the latest models like Bold and storm
- Sony Ericsson devices, where we started with Symbian UIQ models and now target mostly Xperia X1, based on windows mobile
- Samsung
- LG
- Motorola
etc...

Main focus is on more powerful, smartphone segment, but the application works well also on smaller phones.

Java ME was not the only possible choice. There is always the possibility to use native tools, for Symbian and BlackBerry at least. Our choice was to start developing on Java ME and see if it works and later add Symbian and/or BlackBerry specific code if needed. So far we have not needed it. And what surprises me is that it now works very well and very portable. When we try a new device it almost always works right away. We might need to add some code to make better use of the keyboard or make it look better, but generally it works well.

It is actually more of a problem to make it work in emulator, which surprises me. A special problem is with hasPointerEvents() method, which is supposed to tell us if the device has a touch screen or not. Since the application changes its user interface slightly depending on if there is a touch screen available or not this is a very important method for us. And on the phones it always works, but in the emulator it does not.

Another problem with the emulators is to find a good-looking emulator for demonstration purposes. There are a lot of good devices out ther, like Sony Ericsson Xperia X1 and Nokia S60 phones, but I have not managed to find good emulators for them. For the X1 I have so far not found a Java ME emulator at al, and for S60 phones there is one, but it does not look good enough for demonstration puposes. Guess this is because the development of new devices is so fast that manufacturers don't have the time to fix emulators.

LWUIT first impressions

2008-10-16T09:21:00.006+02:00

Last week or so I have been working with the Leight Weight UI Toolkit for Java ME. We started with drawing everything by hand using Java ME LCDUI and Canvas class, but we feel that this is not viable in the long run. We want a toolkit that gives us a higher level API and handles differences between different devices.

Impressions so far are good. It was some work converting our application for Lcdui to Lwuit, but less than expected. Performance is OK, our main problem is network traffic, which has nothing to do with Lwuit, but the toolkit does not seem to slow it down, possible even speeding things up a little (since our old interface repainted the whole screen a bit too often). We haven't really used much of the visual goodies of the toolkit yet, and that is not our main reason for using lwuit, but surely we will use them eventually.

Lwuit is inspired by Swing. In my opinion they follow Swing a bit too close. I don't really see the need for Layout Managers on these very small devices. Our layout in our main screen is position based and needs a scrollable canvas, which lwuit gives us. So far we use the (undocumented) CoordinateLayout, which seems to work as expected, possibly we will have to implement our own, but since we don't want the LayoutManager to ever move the objects and set the X and Y coordinates ourselves, it shouldn't be too difficult.

Handy features, like drawing a text centered or making a dotted line, that exist in Lcdui are not available in Lwuit for compatibility with Swing. We used both these features in our interface, so we had to modify Lwuit so that we can use them, which was not difficult. We don't really see that compatibility with Swing is important, since the interface is designed for mobile devices with small screens and limited keyboards and possibly touch screen.

Error preverifying class - java/lang/NoClassDefFoundError: java/lang/Object

2008-08-21T15:46:00.002+02:00

Is an error you might see when preverifying your midlet. Basically the problem is that the verifier can not find the referenced classes - and if it complains about java.lang.Object it basically can not find anything.

We had this today, and the reason was that our tool (IntelliJ) generated a not so good ant file, with the classpath for the jre. That is not what the preverifier expects. Rather it should be something like:
-classpath C:\WTK2.5.2\lib\cldcapi11.jar;C:\WC:\WTK2.5.2\lib\midpapi21.jar

From applet to MIDlet

2008-08-21T14:35:00.001+02:00

We have started to look into making a mobile client. Our current approach is to make a JME client, partly based on our existing Java client, based on Java SE and Swing and runnable as an applet or a standalone application. The rendering part will need to be rewritten but we hope to be able to reuse communication with the server and some of the internal logic.

Items we found so far:

we use both socket and http communication. Both need to be rewritten to use the microedition Connector. So far the changes are small, but we will need to keep different classes for Java ME and SE versions
we use gzip, in the SE version java.util.GZIPInputstream which is not included i Java ME
enums is a big problem, looks like we will have to convert those used to old-fashioned int and classes with constant definitions.
minor problems like String.split() (no such method in JME, and no StringTokenizer either) needs to be solved.

Multiple applet instances problem solved (?)

2008-07-31T08:17:00.005+02:00

We have had some problems with our applet getting instanciated several times. Seems like it is not only started twice but there are also several instances created by the browser. We have spend quite a lot of time on troubleshooting this and trying to find a solution, and now it seems like we have found a solution:

We changed the APPLET tag in our HTML page to a OBJECT tag. This has to be made specifically for Microsoft Internet Explorer, since it is not supported in other browsers like Firefox and Safari. So in our HTML file we check which browser the user has, and use the OBJECT tag if it is Internet Explorer, the APPLET tag otherwise, something like this:


var html = "<applet archive='MyJar.jar' code='MyApplet.class' height='700' width='1000'>";
           if (navigator.appName == 'Microsoft Internet Explorer'){
                  // Use object tag for MS Internet Explorer
                  html = "<object classid='clsid:8AD9C840-044E-11D1-B3E9-00805F499D93'" +
                      " width='1000' height='700'>";
                  html += "<param name='code' value='MyApplet.class'/>";     
                  html += "<param name='archive' value='MyJar.jar'/>";     
           }

It seems like this helps, but we do need more testing. Possibly we need to change to using the EMBED tag for other browsers but for now we stick to the APPLET tag.

There is a discussion on this in the Java developer forums here.

Formatting java log records

2008-07-03T15:27:00.005+02:00

While configuring logging levels for different parts of your software is flexible in java logging, formatting log records are not. In terms of what is delivered with the jre you have two alternatives:

SimpleFormatter
XMLFormatter

None of these give you any choice on what to include. SimpleFormatter tells you todays date in every record, but not what thread is printing. XMLFormatter dumps everything, but since the xml file is not valid (at least not on mac) it is not easy to process, you have to edit it to make it valid first. So we decided to write our own formatter, which is not difficult. Here it comes:


public class ThreadFormatter extends Formatter {

  public String format(LogRecord record) {
      return "Thread-" +
              record.getThreadID() + " " +
              record.getMillis() % 10000 + " [" +
              record.getSourceClassName() + ":" +
              record.getSourceMethodName() + "] " +
              record.getMessage() + "\n";
  }
}

Most important change (besides printing it all on one line in a more compact format) is that the Thread ID is included. The date is actually not very interesting for us, we just display part of the timestamp to give a rough idea of what takes time in the system.

This works very well, when we run our application standalone or in NetBeans applet viewer. But when we run the application as an applet it will not (for security reasons) load the log formatter class. Feels like the guys at Sun didn't think about logging from applets when the designed java logging. What we would need is a standard log formatter where you can configure the log format, without needing to load your own class. Perhaps we will get that in a later version....

Mac Java logging

2008-07-02T08:56:00.003+02:00

Continued with the java logging work, specially on the mac. Found the following:

logging to console is not possible, since java intercepts all console ouput and writes it to the logg... Creates a loop that fills th console. I have not found any way to turn this off, please drop me a line if you find one..
logging to file with the standard XMLFormatter (the default if you dont change the configuration) produces (almost) an xml file.
The 'XML' file has encoding "MacRoman" , which I have never heard of, but it seems like it is almost standard. Safari (Apple's own browser) does not know it, and complains 'Unsupported encoding MacRoman'. Firefox does not complain about the encoding however.
The 'XML' file has a root tag of <log>. There is no closing tag, so it's an invalid xml. If you want to open the file in Firefox you have to add </log> by hand.
If you print xml strings to your log (which we do) the XMLFormatter tries to escape them, but does not succeed, so you might have to remove them.

Seems like there are a few bugs here....

Java Applet logging

2008-06-27T08:02:00.002+02:00

The last couple of days I have been working on logging from our applet. Frustrating indeed, but finally it works, almost as planned.

Our first approach was to do the configuration programatically, something like:


//get the root logger
Logger logger = Logger.getLogger("");
//create a ConsoleHandler
Handler handler = new ConsoleHandler();
handler.setLevel(Level.FINE);
logger.addHandler(handler);
logger.setLevel(Level.FINE);

Now, this works very well in the Applet Viewer, but when you run your applet in a browser you'll get an exception, since the applet can not change the Handler configuration.

Well, there is the getAnonymusLogger() function, which is designed for use in applets. An anonymous logger would mean that we miss much of the benefits of the flexibel configuration, which is based on named loggers, but I guess it is better than nothing. But while using this method makes it possible to the the level on the Logger, you still can not configure the handler, so you will not get any logging output still.

So, we had to go for the configuration file. This is actually not so bad. You find the logging.properties file (it is under jre.lib) and configure your logging there. For console logging, you have to change the level of the ConsoleHandler (configured by default, but set to INFO level):


java.util.logging.ConsoleHandler.level = FINE

If you want logging to file, you can add the FileHandler instead:


handlers= java.util.logging.FileHandler, java.util.logging.ConsoleHandler

And of course, you turn your logging on:


com.xx.package.level = FINE

Now on my Windows PC this works both in applet viewer and in browser. But when I try the ConsoleHandler on my Mac, there is another gotcha, Bug ID 6585429 LoggerTraceListener causes infinite loop under some circumstances. On my mac the console simply loops and writes the same message over and over again. Seems like this is a known bug, and there should be a fix available, but for now we are stuck with logging to file on the Mac.

Shopping List gadget updated

2008-06-23T13:32:00.002+02:00

My shopping list gadget was one of my first, and is still my most popular. It was originally written as an inline gadget, and has been inline all the time, up till a few days ago. But lately I had complaints about problems with it. So this weekend a took a look. It seems like inline gadgets do not work as before anymore. Inline is actually not supported for new gadgets, so this is not a surprise. So I had to rewrite it and change the type. I also removed all the __MODULE_ID__, which are no longer needed, since it is rendered in an IFRAME.

Web site revised

2008-06-21T18:51:00.002+02:00

I have spent some hours on revising my private web site jubic.se. The new site is based on css positioning instead of the old one, based on html tables. Much esiser to maintain and hopefully a bit more modern look. Some changes to the content too, mainly on the main page, which is re-written. The site contains motsly material on my Google gadgets. I hope to have the time to add some more the following weeks, and possibly also some MySql material later.

Blogger problem solved!!

2008-06-13T12:51:00.003+02:00

At last I manged to get my javascript code into blogger. The problem was with < and > which Blogger don't like. So i had to create small strings and use instead:

var lt = String.fromCharCode(60);
var gt = String.fromCharCode(62);

And now everything works as it should! Check it out at http://rockpopfavorites.blogspot.com/

It is behind the YouTube search at the right, which is actually a Google gadget (not yet published) where parameters are set dynamically.

Blogger hates me.....

2008-06-13T09:32:00.004+02:00

I have been trying most of the week to add some HTML and javascript code to a blog. It looks OK in the edit window but every time I save it, Blogger destroys my beautiful javascript code... It tries to format it, moves fragments of code around and destroys it completely. Escpecially special chars (including the not so special " and ' chars and whitespace!!!) seems almost impossible. I have spent several hours of my precious spare time on this.

Applet lifecycle

2008-06-04T20:05:00.003+02:00

Lately I have been working with applets. This is new to me, since I have been mostly a server and web guy, with J2EE and servlets as my specialities, and some J2ME. And now I have run into a strange problem....

An applet's life cycle has four methods:
- init() when it is created
- start() when it is shown
- stop() when it is no longer shown
- destroy() for releasing resources

There is an old exception to this, that som browsers (Internet Explorer, possibly some other too) sometimes i9nvokes init() more than once. We had some problems with our applet which seemed to be caused by this. But when it made a fix for this, it did not help...

Some more investigation showed that actually Internet Explorer did not only invoke init() twice but also created two instances and invoked both init() and start() on both. This seems to happen the first time an applet is run, but possibly not the second. Very strange indeed....

Create thumbnail in J2ME

2008-03-18T11:26:00.005+01:00

The latest week I have been working on a J2ME project where we need to display thumbnails of piuctures from the mobile camera. I suppose this is a common scenario, but we had some unexpected problems..

The main reason for the problem is that mobile cameras today are very good. This means that images often will be very big, too big to display, but also to big to handle as an image in the J2ME environment. If you try Image.createImage() you will get an out-of-memory exception. This means that you can not create an Image even to create a thumbnail. In stead you have to create the thumbnail image straight from the stream, without creating a full-size image first.

There are different ways to do this, but since we work with a target platform that is quite new, and supports JSR-234 we chose to use this feature. A small utility function to do the resize could look something like this:



public static Image resizeImage(InputStream src, int width, int height)
{
 MediaProcessor mp;
 try {
    mp = GlobalManager.createMediaProcessor("image/jpeg");
    mp.setInput(src, MediaProcessor.UNKNOWN);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    mp.setOutput(bos);
    ImageTransformControl fc = (ImageTransformControl) mp
        .getControl("javax.microedition.amms.control.imageeffect.ImageTransformControl");
    fc.setTargetSize(width, height, 0);
    fc.setEnforced(true);
    fc.setEnabled(true);

    mp.complete();
    src.close();
    return Image.createImage(bos.toByteArray(), 0, bos.size());
  } catch (MediaException e) {
      System.out.println("MediaException in Utils.resizeImage():" + e.getMessage());
  } catch (IOException e) {
      System.out.println("IOException in Utils.resizeImage():" + e.getMessage());
  }
  return null;
}

RSS Reader Gadget updated

2008-02-13T08:07:00.001+01:00

I have made a minor update to the RSS reader gadget. You can now set how old items that should be displayed. Increases usability quite a lot.
Check it out here.

Web enabling MySQL

2007-12-08T09:38:00.000+01:00

Mark Atwood has published an interesting post with his wishlist for MySQL. One of his points is already (partly) covered with the LOAD XML command I made for MySQL 6.0 (or is it 5.2).

The main problem with it is perhaps that it works (like the LOAD DATA command) only with files, but in a web world you want it to be able to take input from other sources. You also need to be able to send an XML to several tables, with foreign keys coordinated etc.

Mark also has a wishlist for the opposite, the SELECT OUTFILE command. I have made a couple of stored procedures to help with this, but an extended SELECT OUTFILE would be mucke aesier to use and give much more possibilities. It would be fun to work on this, we'll see if I have the time, now that I am almost ready with my relocation..

RSS reader gadget

2007-11-16T10:03:00.000+01:00

For a while I have been using a small PC program for the feeds I keep a look on. It has lot's of functionality, and with some work I can get it to do just what I want to, which is very simple: just display a list of my feeds, where I can expand a feed and look at the posts.

But this gave me some problems... Sometimes it crashed, and I loose all my settings. I then have to set up everything again, which is a lot of work. Also I only had it on one of the computers I use (three in all), which meant I could only follow the feeds on that computer.

So the last time it crashed for me, i deinstalled it and made a Google gadget for feeds instead. So far it is very basic, the main advantage over the ones I have seen is that it handles multiple feeds ( there are excellent gadgets for handling only one feed). Probably I will add more options in the future.

Back to the J2ME world

2007-11-01T14:51:00.001+01:00

I have got a new assignment, for a J2ME midlet this time. Primary target is not a small cellphone, but the new BlackBerry Curve. This means we have to think differently, the BlackBerry has a muck better interface, larger screen and a qwerty keyboard. We have to make sure they are used the best way possible. My previous J2ME experience has been with cellphones from Nokia, Sony Ericsson and Motorola, this is something different.

The BlackBerry environment means that additional preprocessing has to be done. Not a big thing, but yet another point that differs from PC/Windows client, where you just develop form Windows (possible verify on both XP and Vista) and don't care about the hardware. The J2ME platform is still not that mature (will it ever be?), you have to verify on the major phone brands, both that it works and looks OK.

Import and Export

2007-10-24T05:34:00.000+02:00

Recently we have been doing some work with export and import of data from and to MySql databases. Our customer wants to be able to export configuration data from one MySql instance and import it into another.

The obvious tool to use is mysqldump. But since we need to edit the exported data (just replace some codes) before importing it into the other instance we use the --tab option. This means that instead of one big file (or actually redirected standard output) we get two files per table, one with the CREATE TABLE command and one with the data.

In the next step we run some scripts to modify the data. This is quite easy, since data is in text files with one record per row.

Finally we import the data into the new instance. While the default mode of mysqldump creates a file you can run in the command line client, mysqldump --tab does not. You have to create that yourself, but it is not very complicated. You need the following lines for every table:

source [tablename].sql
LOCK TABLES [tablename] WRITE;
ALTER TABLE [tablename] DISABLE KEYS ;
LOAD DATA INFILE '[path][tablename].txt' INTO TABLE [tablename] ;
ALTER TABLE [tablename] ENABLE KEYS ;
UNLOCK TABLES;

The ALTER TABLE and LOCK/UNLOCK commands are not absolutely necessary, but they speed things up. We have not measured the speed of this approach, but the general impression is that it is fast.

Probably this method is faster than the default mode of mysqldump, because data is not sent through the parser, where there has been some performance problems. LOAD DATA INFILE is in most cases very fast.

Out of memory......

2007-10-22T13:37:00.001+02:00

Last week or so has been busy. We are working on our next release, which seems to work well on a functional level. But we get problems with Out of memory errors...

There are different causes for this, but the main cause is with Hibernate. The part that causes the most problems is where we read quite a lot of data and generate data for the online part of the system. The data is stored as BLOBs in the database, and Hibernate is used througout. We want to be sure that everything or nothing is processed, so it is all done in one tranmsaction.

Now it seems that even though we do not read from the table with the BLOBs, only write to it, Hibernate keeps all records in the session cache, filling the memory. To me this seems very strange. We dont need this cache, once the records are written to the database, they should be removed from the cache. In my experience this is a very common scenario, that you do not read the records created again in the same process. In fact it is good practice to get all data before inserting, so you do not need to read the same record again.

It is also difficult to understand that Hibernate does not take care of Out of memory conditions. A good cache should not grow without limit, it should purge data when memory usage is too high. This is not something you should have to do in your application.

The problem seems to be solved for now, but since data volumes in the system will grow, it will probably reappear. We will have to do something more permanent for the future. Probably this will be to bypass Hibernate for the BLOB insert, and do this with JDBC calls.

Moving !!

2007-10-11T13:41:00.000+02:00

I haven't had much time for this blogging for a while, and probably won't have until perhaps in december. Most of my spare time is spend on moving.

After 23 years I am leaving Lund. Not going too far though, just some 60 kilometers, to Helsingborg. From the University town of Lund, where Sony Ericsson is the largest employer, to the harbour town of Helsingborg, with IKEA:s IT department.

Hibernate proxy classes and reflection

2007-10-03T13:42:00.000+02:00

I have been struggling the last few days with a small tool to export and import data from a system using Hibernate Object Relational mapping. To make it fairly general, and avoid having to change it when attributes are added to the data model we based it on Java reflection. Fairly straightforward, create an XML file with classnames as tagnames and fields as attributes.

The first problem was object relations. My initial plan was to simply follow them, but sinces they are all bi-directional this would mean looping forever. So we had to add a setup where you tell the tool which object relations should be included.

The next problem was worse. It seems that Hibernate somtimes does not create the right objects when loading objects from the database. Instead so called 'proxy classes' are created. This means that reflection does not work as expected... You cannot use Field to acces the fields, instead you have to use the getter methods. OK, not to complicated, once you know. Unfortunately we had one attribute which did not follow naming standards, so we needed a small hack to make this work. What I would have liked is a way to get the real class and skip the proxy. But i guess you can't get everything.

SQL mode quiz answers

2007-09-07T11:30:00.000+02:00

My quiz about SQL mode was a bit more difficult than I expected. The question was which ones of the following statements are affected by a change from the default SQL mode to ANSI mode.

I used the INFORMATION_SCHEMA tables for examples throughout. This is to make it easy to try for yourself, without needing to create tables etc. If you use the Query Browser to try this out you should beware that setting the sql_mode might not work, since the Query Browser by default creates a new database connection for every statement. The easiest way to check this si to use the 'set global sql_mode' command, but you should not do this on a production database, since strange things might happen....

If you want to read the documentation, you find it here.

Now for the solution...

a) SELECT concat(table_name,' ',column_name) FROM information_schema.COLUMNS

This was quite an easy one. CONCAT is a MySQL extension to ANSI SQL, but it is not disabled by the ANSI SQL mode. The command will work just as well whatever the SQL mode. But CONCAT is not portable to other databases. Oracle has a CONCAT function, but it takes only two arguments. SQL Server has no CONCAT, and I don't know any other databases that has one.

b) SELECT TABLE_NAME||' '||COLUMN_NAME FROM information_schema.COLUMNS

That was also an easy one. Double pipes is the ANSI standard way of string concatenation. In MySQL it means OR.

So, in ANSI mode you will get:

+-------------------------------------+

| TABLE_NAME||' '||COLUMN_NAME |

+-------------------------------------+

| CHARACTER_SETS CHARACTER_SET_NAME

| CHARACTER_SETS DEFAULT_COLLATE_NAME

| CHARACTER_SETS DESCRIPTION

etc

In default mode:

+------------------------------+
| TABLE_NAME||' '||COLUMN_NAME |
+------------------------------+
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |

c) SELECT count (*) FROM information_schema.COLUMNS
This was a tricky one. I guess it was not easy to see, but there is actually a space between count and the left parenthesis. This makes it invalid in MySQL standard mode:

mysql> SELECT count (*) FROM information_schema.COLUMNS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '*) FR
OM information_schema.COLUMNS' at line 1

Change to ANSI mode, and it works:

mysql> set sql_mode = 'ANSI';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count (*) FROM information_schema.COLUMNS;
+-----------+
| count (*) |
+-----------+
| 503 |
+-----------+
1 row in set (0.11 sec)

So, this is a statement that works differently....

d) SELECT "TABLE_NAME" FROM information_schema.COLUMNS
This was also quite easy. Double quote is according to ANSI standard used for identifiers like table and column names. You only need them if you have characters that would otherwise be illegal or if you want to have mixed case in your identifiers.

By default MySQL uses double quotes for strings. This means that the select will give the following result:

+------------+
| TABLE_NAME |
+------------+
| TABLE_NAME |
| TABLE_NAME |
| TABLE_NAME |
| TABLE_NAME |

If you change to ANSI, you will get the following result:

+----------------+
| TABLE_NAME |
+----------------+
| CHARACTER_SETS |
| CHARACTER_SETS |
| CHARACTER_SETS |
| CHARACTER_SETS |
| COLLATIONS |

e) SELECT `TABLE_NAME` FROM information_schema.COLUMNS
This is the MySQL alternative to ANSI double quote. It does not follow ANSI standard, but the MySQL ANSI mode will not turn it off. I do not know another database that allows this syntax, so if you try to convert your system you will have to remove them.

Conclusion
Statements b, c and d are the ones that will behave differently if you change to ANSI mode. Statements a and e will work the same.

SQL mode quiz

2007-09-05T21:03:00.002+02:00

MySQL follows ANSI SQL standard quite close. But there are a few points where it differs. To make MySQL follow the ANSI standard more closely you can use the SQL_MODE system system variable.

If you set the SQL mode to ANSI, MySQL will interpret you SQL commands differently. Some of your SELECT statements might work differently or not at all. But which ones? Let's see if you know....

Suppose you change your sql mode setting from none to ANSI. Which of the following statements will be affected:

a) SELECT concat(table_name,' ',column_name) FROM information_schema.COLUMNS
b) SELECT TABLE_NAME||' '||COLUMN_NAME FROM information_schema.COLUMNS
c) SELECT count (*) FROM information_schema.COLUMNS
d) SELECT "TABLE_NAME" FROM information_schema.COLUMNS
e) SELECT `TABLE_NAME` FROM information_schema.COLUMNS

Follow up on LOAD XML

2007-08-29T10:41:00.000+02:00

A few weeks ago (time flies..) i posted a short note about my LOAD XML contribution. It has actually been available for some time, veven if it is not so easy to find. It is included in MySql 5.2.5:
download
documentation - quite good I think!

It is a bit sad that it is included in 5.2 (still in alfa) and not in 5.1. Since it is a separate command it will not affect existing installations and would not be risky. That faster it gets included the faster we will get feedback on it, which is what I believe is needed right now. Is this something that MySql users need and will use? Or will they use other tools to read XML data into their MySql databases?

LOAD XML reads data into a MySql table in much the same way as LOAD DATA does and in fact the implementation is based on LOAD DATA. This means that it does not parse the entire XML document ( I belive MySql xml function ExtractValue() does that) but instead reads one row at a time from the xml file and inserts it into the database. This makes reading of large xml files possible at quite a decent speed, althoug not as fast as LOAD DATA.

The mapping between XML and the table is based on two assumptions:
- there should be an XML tag that corresponds to a record in the table. You name this tag in the 'ROWS IDENTIFIED BY' clause
- both tags and attributes are matched automatically with column names and values
inserted into the tables. Tags and attributes that do not match columns are simply ignored

When I implemented this the scenario it was designed for is an integration scenario, where you receive data in an XML file periodically and dump it into your database.

There are of course limitations you should be aware of:
- CDATA is not supported and binary fields can not be handled
- it can only load data from a file
- the XML is not verfied, an illegal XML could very well be processed, no validation with xml schema or DTD is made
- there is no namespace support

All in all I think this could be quite a useful function. Arnold Daniels, who worked with me on this contribution initially, is planning some improvements. Among other things to make this work with XML data that is not read from a file.

Personally I will perhaps make some minor fixes, but probably start on something entierly different. Don't know what yet, your suggestions are welcome....

LOAD XML contribution added to MySQL

2007-08-13T20:24:00.001+02:00

I got news from the MySQL people that my LOAD XML contribution is added to release 5.2. Check the manual page.

The aim of the contribution is to simplify reading of XML data into a MySQL database. The LOAD XML command supports three different xml formats:
- field values as attributes
- field values as tags
- the format produced by the mysql -x command, with the field name as an attribute and field value as a tag

If the LOAD XML command finds a matching field in the target table, the value is inserted, otherwise it is ignored. If you have used the LOAD DATA command (most of us have) you should recognize much of the functionality, LOAD XML works much the same way.

More on XML output from MySql

2007-08-06T10:52:00.000+02:00

In a previous post I described how you could create XML output from SQL using stored functions. But that was a flat structure, with only one table and simple XML structure with a tag for every row in the result set, and column values as attributes or sub tags. Part of the real power of the XML format is that it supports more complicated structures, with hierarchical data. If you need to produce XML for integration with another application you will probably have to produce a XML with data from several tables, and a hierarchical structure. To accomplish this in MySQL we can use the GROUP_CONCAT function, combined with our XML stored functions.

An example:
We take our select statement from the previous post, that gives us a list of tables in xml format:

select xml_tag('table',null, concat(xml_attr('name',t.table_name),
xml_attr('engine',t.engine)),null)
from information_schema.tables t
where t.table_schema = 'INFORMATION_SCHEMA'

We then add a join with the columns table to get column data:

inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema

and replace the last parameter of the xml_tag function call, the one for the subtags with a new xml_tag function call to produce a column tag:

xml_tag('column',c.column_name,null,null)

This gives us:

select xml_tag('table',null, concat(xml_attr('name',t.table_name),
xml_attr('engine',t.engine)),xml_tag('column',c.column_name,null,null))
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema
where t.table_schema = 'INFORMATION_SCHEMA'

Running this gives us:

<table name="CHARACTER_SETS" engine="MEMORY" >
<column>CHARACTER_SET_NAME</column></table>
<table name="CHARACTER_SETS" engine="MEMORY" >
<column>DEFAULT_COLLATE_NAME</column></table>
<table name="CHARACTER_SETS" engine="MEMORY" >
<column>DESCRIPTION</column></table>
<table name="CHARACTER_SETS" engine="MEMORY" >
<column>MAXLEN</column></table>
<table name="COLLATIONS" engine="MEMORY" >
<column>COLLATION_NAME</column></table>
etc...

OK, this is some kind of structure, column tags inside the table tag. But still we have one table tag for every column, but we want one for every table, with all the column tags inside. To accomplish this we add a group by clause, and use the GROUP_CONCAT function for the column tags. This gives us the following SELECT:

select xml_tag('table',null, concat(xml_attr('name',t.table_name),
xml_attr('engine',t.engine)),GROUP_CONCAT(xml_tag('column',c.column_name,null,null) separator ''))
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema
where t.table_schema = 'INFORMATION_SCHEMA'
group by t.table_name, t.engine

And the following result:

<table name="CHARACTER_SETS" engine="MEMORY" >
<column>CHARACTER_SET_NAME</column>
<column>DEFAULT_COLLATE_NAME</column>
<column>DESCRIPTION</column>
<column>MAXLEN</column>
</table>
<table name="COLLATIONS" engine="MEMORY" >
<column>COLLATION_NAME</column>
<column>CHARACTER_SET_NAME</column>
<column>ID</column>
<column>IS_DEFAULT</column>
<column>IS_COMPILED</column>
<column>SORTLEN</column>
</table>
etc

OK, this is what we wanted! Just one small problem left... When I run this on my laptop I get the error 1260 with the following message:
%d line(s) were cut by GROUP_CONCAT()
And the result is only 7 rows. This is because the group_concat_max_len has a value that is too small. You can reset it with the command:

SET [SESSION | GLOBAL] group_concat_max_len = val

I set it with the command:

SET GLOBAL group_concat_max_len = 2048

I then rerun the command, and there is no error, and 23 records.

Code for the stored functions is available at MySql forge.

Comments on MySql XML output

2007-07-14T17:21:00.000+02:00

I have had some comments on my stored functions for XML output from MySql. First of all, publishning the code as a part of a blog post was not a good choice. All xml chars, like < and > must be escaped, and I missed some. I should probably have used the xml_escape function to precess my text...

That is fixed now, and also I have put the function definitions on the MySql forge, here. Please do not hesitate to tell me if you find any errors, or have suggestions for improvement.

Another comment was about the xml functionality of 'mysql --xml'. Yes, there is a function for generating xml output from MySql, I do know that. But if you need to output xml from your application there are a number of problems with it:
1) it has to be invoked from the command line, so you need to start a command shell, redirect output somewhere etc. This is not always possible and might not be a good alternative even if it is.
2) you have no control over the xml format, you would probably need to add som XSL processing to get the format you need. This will make it complicated, and XSL knowledge is not that common.

So, there are a number of situations where the 'mysql --xml' approach is not very suitable, but the stored function might do the job. But certainly you should consider the 'mysql --xml' possibility before choosing.

XML output from MySql

2007-07-13T14:08:00.000+02:00

Many times you need to produce XML output from your MySql database. MySql has no built-in support for creating XML in it's SQL implementation but you can easily add support using the stored function feature.

To make xml output easier I have made three small stored SQL functions. They produce xml fragments, which you can easily put together to get a complete xml:

xml_escape(value) replace characters not allowed in xml with the escape sequences
xml_attr(name, value) create an xml attribute
xml_tag(tagname, tagvalue, attrs, subtags) create a tag, optionally with

Lets see some examples. First a real simple one:

select xml_tag('table',table_name,null,null)
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'

Gives you something like:

<table>CHARACTER_SETS</table>
<table>COLLATIONS</table>
<table>COLLATION_CHARACTER_SET_APPLICABILITY</table>
etc...

To make this a valid xml, you need to add an xml header and a root tag, surrounding all the content. Something like this:

<?xml version="1.0" encoding="UTF-8"?>
<list>
your content...
</list>

That will give you a real simple xml, with a list of the tables
in the information schema. But perhaps your specification says
you should present the data as attributes and not as text nodes.
Let's try that:

select xml_tag('table',null,xml_attr('name',table_name),null)
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'
Gives you:
<table name="CHARACTER_SETS" />
<table name="COLLATIONS" />
<table name="COLLATION_CHARACTER_SET_APPLICABILITY" />
etc..

Since we don't want a text node we let the tagvalue parameter be null.
Instead we use the xml_attr function to format an xml attribute and
send it in in the attrs parameter.

But what if we need several attributes? You can not write a stored
function with variable number of arguments, so we use the MySql
standard function concat() for this.

select xml_tag('table',null, concat(xml_attr('name',table_name),
xml_attr('engine',engine)),null)
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'
gives you:
<table name="CHARACTER_SETS" engine="MEMORY" />
<table name="COLLATIONS" engine="MEMORY" />
<table name="COLLATION_CHARACTER_SET_APPLICABILITY"
engine="MEMORY" />
<table name="COLUMNS" engine="MyISAM" />

And if you want your data as text nodes in sub tags, you do the
same trick with the last parameter in xml_tag, the subtags parameter:

select xml_tag('table',null,null,
concat(xml_tag('name',table_name,null,null),
xml_tag('engine',engine,null,null)))
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'
Results in:
<table><name>CHARACTER_SETS</name><engine>MEMORY</engine></table>
<table><name>COLLATIONS</name><engine>MEMORY</engine></table>
<table><name>COLLATION_CHARACTER_SET_APPLICABILITY</name>
<engine>MEMORY</engine></table>
etc..

Of course you can combine attributes, subtags and a text node,
but I leave that for yourself to experiment with.

The stored functions are defined like this:

DELIMITER $$

DROP FUNCTION IF EXISTS `xml_escape` $$
CREATE FUNCTION `xml_escape`( tagvalue VARCHAR(2000))
RETURNS varchar(2000)
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
tagvalue,'&','&'),
'<','<'),
'>','>'),
'"','"'),
'\'',''');
END $$

DROP FUNCTION IF EXISTS `xml_attr` $$
CREATE FUNCTION `xml_attr`(tagname VARCHAR(2000),
tagvalue VARCHAR(2000))
RETURNS varchar(2000)
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN CONCAT(' ', tagname ,'="',xml_escape(tagvalue),'" ');
END $$

DROP FUNCTION IF EXISTS `xml_tag` $$
CREATE FUNCTION `xml_tag`(tagname VARCHAR(2000),
tagvalue VARCHAR(2000),
attrs VARCHAR(2000),
subtags VARCHAR(2000))
RETURNS varchar(2000)
BEGIN
DECLARE result VARCHAR(2000);
SET result = CONCAT('<' , tagname);
IF attrs IS NOT NULL THEN
SET result = CONCAT(result,' ', attrs);
END IF;
IF (tagvalue IS NULL AND subtags IS NULL) THEN
RETURN CONCAT(result,' />');
END IF;
RETURN CONCAT(result ,'>',ifnull(xml_escape(tagvalue),''),
ifnull(subtags,''),'</',tagname, '>');
END $$

DELIMITER ;

Wishes & Gifts

2007-07-06T11:34:00.000+02:00

For a while I have been working on a new Google Gadget. The idea is to keep lists of your wishes and gifts for Christmas or other occasions. I sometimes have problems with coming up with new ideas for gifts both for myself and others, so this should be a help. This is what the gifts tab looks like:

The wishes tab is simpler, with less functionality, no search etc.

The gadget was a bit more complicated than I thought, so it tok some time, but finally it was ready yesterday. Here is the link to add it to your Google homepage:

Add to Google homepage

Top 5 best MySql practices

2007-06-28T13:14:00.000+02:00

My opinion on MySql best practices, the new new 'chain letter' started by Ronald Bradford. As always, this is a developers point of view.

0. Make a database design. Databases generated automatically might work very well in development, but when you have large data volumes and lots of concurrent users they do not.

1. Be consistent in your choice of datatypes. I prefer to use just a subset in a system. Try to stick with SMALLINT UNSIGNED or INTEGER UNSIGNED for keys. Always use the same datatype for the same term in all tables, no joins with datatype conversion! Do not store numbers as CHAR/VARCHAR fields.

2. Make sure there are indexes supporting all searches that are part of routine system usage. If you have tables that are very small it might be better not to index them, but this should be an active decision.

3. Verify your database design with real volumes and tests covering the planned usage. A modern computer can quickly scan (without indexes) more data than you will enter by hand, so you will probably need a dump from a production system or generated data.

4.Use SQL_MODE ANSI and TRADITIONAL for new systems. For old ones, you might have to allow a less strict mode.

5. Choose your storage engine with care. Use InnoDB (and keep an eye on Falcon!!) if you need transactions, but even if you do, MYISAM migh be a good choice for some tables.

MySql performance in a .NET environment

2007-06-27T09:45:00.000+02:00

The last two weeks I have been working on a .NET application using MySQL (3.21) for data storage. The application is basically a stand-alone PC application where MySql is used to store a lot of data from test equipment.

This is a bit different from what I spend most of my time on. No web site with lost of concurrent session, but instead a lot of installations of the software on local PC's. The also means we can not tune parameters on a live system, we have to set them before delivering to customers. Basically this is the main problem, since we don't know how the system will be run.

Well, if the scenario is different, the most important action was the same. Changing the field order in one of the indexes to match the queries better was our number one action, and the impact was larger than all other actions together. If your indexes are not righ, you should fix it before anything else.

An upgrade to MySql 5.41 and .net connector 5.0.7 was another obviuos task. I am a bit surprised that it was so easy. The only real problem we had was with merge tables. The initially did not work, but after we made a ALTER TABLE on all underlying tables even the merge tables were OK.

Performance improvement after upgrading was not large, but when we switched shared memory on, there was a noticeable performance improvement. The application transfers a lot of data between MySql to the applcation, mainly to produce graphs, so this should be important. We also switched both key cache and query cache on, but since we do not know how the application will be used, it is hard to determine the effect of this.

We had some problems with connections not being closed initially. But turning off connection pooling fixed this. Turning it on from the start was simply a reflex of an old web programmer, I'm afraid. Connecion pooling is not relevant in this scenario.

All in all a quite successful project. Performance is OK now, and the bottleneck is not the database, so we will look at some other changes, mainy in the graph package.

MySql merge table problems

2007-06-21T14:40:00.000+02:00

While upgrading fom MySql 3 to Myql 5 our only real problem was with merge tables. We have two in the system, both did not work. Createing the merge table did not give an eror, bt when we tried to select from it, we get the error message 1168:
Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

This kind of error message gives my the creeps. Or?? Why doesn't it say which one of the cases it is? Surely these are three different cases and it should be easy to separate them. Is it difficult to add error messages to MySql? Or a lazy programmer??

OK, we try to look at the table definition i MySql's own tool the Query browser. But if you try to do 'Edit Table' in the Query Browser, you get the following error message:
A MySql eror message was encountered. The eror message is:
Cannot fetch table information
The following error occurred:Unable to open underlying table which is differently
defined or of non-MyISAM type or doesn't exist (1168)
OK, we knew that, but we wanted to take look at the definition to check that it matches the underlying table.

OK time to check for bugs. We finally find one, saying that of our three possible causes, it is actually a fourth: the table does exist, is of MyISAM type, and definitions match. But it is of an old MyISAM version... Fix is to make an ALTER TABLE on all the underlying tables, this will fix the version also, and everything is OK.

For the future I think we will look into other solutions. Merge tables are very restrictive and for our purposes a view with UNON ALL would work just as well, since we already make inserts into the underlying tables and have no updates. A quic test also says that the performance is about the same. Or, possibly, we will wait for MySql 5.1 with partitions.

MySQL performance enhancements

2007-06-05T10:35:00.000+02:00

I have been working on a new assignment this week, with the goal to improve performance in a .NET application using MySQL (3.21) for data storage. The application handles a lot of data in what is basically a single user system.

The main rason for the performance problems was heavy searches where indexes did not match. Simply switching the order of columns in the composite key fixed most of it.

In the old version they copied data to a temporary table, to solve the slow query problem. Once we had the indexes fixed, we could drop this solution and use the ase table in all cases. This also speeded it up a lot.

The change to MySQL 5.0 made a few other changes possible
- switch to shared memory for communication between database and application
- buffering of indexes
- query caching
which all ment further improved performance

All in all quite successful so far. Performance is OK now, and the bottleneck is not the database, so we will look at some other changes.

It has been great fun, .Net is a new environment for me, but it was quite easy to get into it.

More gadgeteering....

2007-05-30T10:29:00.000+02:00

I am a great music fan, and from time to time can't stop myself from singing a song or two, mostly rock classics. Although I know a lot of lyrics, often I can't recall them all.

Now they are all out there on the net, but sometimes difficult to find. So I made a small Google custom search engine, and a gadget to go with it, like this:

Surprisingly what you can do with so little, the power of the web is amazing!

Pricerunner Google Gadget

2007-05-16T17:52:00.000+02:00

Made another Google gadget the other day, this time doing a search in Pricerunner, to compare prices and products. I believe Pricerunner is a very good service, and widely used in Sweden.

The gadget looks like this:

Today it supports Pricerunner in the UK, Germany, Sweden and Denmark. Hopefully I will be able to add US and France soon.

You could also try it here.

Add Pricerunner to your Google homepage.

Turning the result around

2007-05-10T13:09:00.000+02:00

In many cases you don't want your the result from your SQL statement in just a plain listing, but organized in columns, or whith data grouped on status, period or whatever. This can often be accomplished with a combination of the IF function and SUM or another group function.

For example get your sales per customer grouped by period:
SELECT customer_no AS CUSTNO, cust_name AS CUSTNAME,
sum(if(period = '200701',amount,0) as Jan_Amount,
sum(if(period = '200702',amount,0) as Jan_Amount
FROM otd.salestrans_hist s
WHERE period IN ('200701','200702')
GROUP BY customer_no, cust_name

Or number of open and closed records in each class:
SELECT class, sum(if(status='Open',1,0)) as open,
sum(if(status='Closed',1,0)) as closed
FROM table_name
GROUP BY class

In the examples I have used the MySQL IF function. You could also use CASE, which is SQL standard.

Indexing, indexing and indexing....

2007-04-25T11:39:00.000+02:00

Saw this table definition in a system I was working on:

CREATE TABLE session_role (
Session_sessionId bigint(20) NOT NULL,
activeRoles_roleId varchar(255) NOT NULL,
PRIMARY KEY(Session_sessionId,activeRoles_roleId),
UNIQUE KEY activeAccounts_actorId (activeRoles_roleId),
KEY `FK38CC06CCF5B03D50` (Session_sessionId),
KEY `FK38CC06CC4085AE4` (activeRoles_roleId),
CONSTRAINT `FK38CC06CC4085AE4` FOREIGN KEY (activeRoles_roleId)
REFERENCES role(roleId),
CONSTRAINT `FK38CC06CCF5B03D50` FOREIGN KEY (Session_sessionId)
REFERENCES session (sessionId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is four indexes for a two-column table! Of course nobody has actually written this, this is generated by JBoss/Hibernate, with a MySQL database used for storing data. Be very careful with code generators....

Google gadget

2007-04-11T12:53:00.000+02:00

I have spent some time on a Google gadget, Shopping List, and now it is released.

Helps you remeber what to buy. It can also be printed and sent via mail, Gmail or other.

Click the link below to add to your Google homepage.

It is amazing what you can do with a little javascript and html. The small format also makes it necessary to think about your design.

Add "Shopping List" to your Google homepage

Started with EJB3

2007-03-08T08:53:00.000+01:00

I have just started on a new project, where we will use EJB 3.0. I am very sceptical to the EJB concept, because some of the worst systems I have seen use EJB. Complicated to write, very hard to maintain and with very bad performance, mostly because they generate a lot of database traffic.

Version 3.0 is a big improvement, that's for sure. But is it better than the old POJO way, with Database Access Objects and SQL/JDBC? That remains to see. Development is so far fast, but will performance be OK? If everything goes OK, we will roll out the system in thousands of installations. We can not make performance tuning when the systems are up and running, which according to my experience is necessary on EJB systems. The performance has to be ok from day one.

LOAD XML submitted

2006-12-12T10:42:00.000+01:00

I have been working on an addition to MySQL the latest month's or so, adding XML import to MySQL. This weekend I decided that the version was good enough to submit to MySQL. You can find it at http://lists.mysql.com/internals/34169. Good enough to submit yes, probably not good enough for production, but feedback would be good.

But all in all I am quite satisfied with it.
- focus is on easy-of-use, with automatic matching of tags and attributes to field names
- support for three different xml formats: using tags for field values, using attributes or the format used by oth MySQL tools (not a good format, but anyway..)
- works very much like th CSV file load that has been a feature of MySQL for very long

I can think of some additions, like support for binary fields (probably does not work today), better handling of character encoding etc, improved handling of the SET clause etc. But then, there are always improvements that could be made, and probably there are others that I have not thought of that are more important.

Tsql2mysql update

2006-11-06T13:53:00.000+01:00

Released a new version of the procedure converter from SQL Server to MySQL. The changes are driven by user requests.

I have added support for temporary table creation: in SQL server you create a temporary table by adding INTO #temptable to your select. This is converted to MySQL CREATE TEMPORARY TABLE and the # is dropped.

Also some minor changes, like support for very large SQL statements, better pretty-printing and improved support for CASE statements.

MySQL Developer Certification

2006-10-06T13:34:00.000+02:00

Received the final papers about my MySQL 5.0 Developers certification. Seems we are only two certified in Sweden so far, feels strange to be one of the few. But probably most people working with MySQL don't bother about certifying.

Supporting the procedure converter

2006-10-06T12:01:00.000+02:00

Had a support question on the T-SQL to MySQL procedure converter. Seems like the first (current) version crashes when SQL statements are very long. Made a fix, and also improved the printing of long lines. Don't know if I am going to release it as is, or add more pretty-rpinting perheps. Also noticed that my support case uses + for string concatenation, which is not converted by the current version.

Some comments on the procedure converter

2006-09-21T10:04:00.000+02:00

The T-SQL to MySQL procedure converter is of course developed based on what I needed when I made it. I have made it as a window's executable because we run the system on windows (guess that is common with SQL Server installations...). There is no fancy user interface (in fact no user interface at all) because I didn't need it etc.

T-SQL constructs not handled:

String concatenation: since + is used in T-SQL both for concatenation strings and for addition you need to know the datatype of the arguments to know if it should be converted. The tool does no attempt to do this, even if some cases (text constants, declared variables or parameters) would be quite easy.
GOTO's and labels: The code I converted contains a lot of GOTO's. A very common construction is:
SELECT /UPDATE/DELETE/INSERT
....
SELECT @p_err_num = @@ERROR
IF (@p_err_num <> 0) GOTO OnError
COMMIT TRANSACTION
GOTO Done
OnError:
ROLLBACK TRANSACTION
Done:
RETURN( @p_err_num )

I don't want to convert this! The MySQL/ANSI error handler is so much better, and there is no GOTO. So, the goto's and lables will be commented out and you will have to convert it by hand.
Dateadd: MySql has the same functionality in the date_add function, but parameter order is different. Since function calls can be nested, the tool would need to parse the code more than it does to be able to fix this. This is on my list, but not yet implemented.
Select TOP: This corresponds to the MySQL LIMIT clause. But since T-SQL allows parameters perhaps using the SET SQL_SELECT_LIMIT would be better. On the other hand this would be complicated in cursors.. Since we have only very few procedures where this is used, we do it by hand so far. Perhaps in a coming release...
Dynamic SQL: T-SQL execute could be replaced by MySQL PREPARE/ EXCUTE/ DROP PREPARE but the command would be a text string, and the tool does not, and should not, convert text strings. Probably you should check why you are using dynamic SQL in your code, and consider using another approach, or convert the text strings to MySQL by hand. Dynamic SQL is advanced and should only be used if you really need it.

Erik

T-SQL to MySQL procedure converter released

2006-09-18T11:10:00.000+02:00

Yesterday evening I finally released the procedure converter at Sourceforge.net. I am quite satisfied with the result, it covers quite a lot. But the user interface is virtuallt non-existant, and the parsing needs some improvement. The problem is that for more complex conversion tasks (like shifting order for function parameters) I would need better parsing of the source code.

Features:

converts CREATE PROCEDURE/FUNCTION syntax
adds semi-colon at the end of statements
converts IF and WHILE statements to MySQL syntax
sorts the code in the correct order, with declarations at the top and instructions
after that
removes @, [ and ] from identifiers
converts comments to MySQL syntax (a space in the 3^rd position for -- comments)
adds a CONTINUE HANDLER FOR NOT FOUND if the procedure contains cursors
comments out GOTO and labels (you will have to restructure this yourself, since there is no GOTO in MySQL stored procedures)
converts some data types (nvarchar, identity, smalldatetime, money, smallmoney)
Converts function with a direct MySQL correspondence (getdate, getutcdate, len, replicate)

Limitations

No conversion of GOTO:s and labels
no MySQL error handling added

Conversion of Microsoft T-SQL to MySQL

2006-09-04T08:16:00.000+02:00

Spent some time on a tool for converting T-SQL stored procedures to MySQL during the weekend. It is looking quite good, but new problems keep popping up all the time. Like the differences in WHILE statement syntax, different naming of database objects etc.

Guess at some time I will have to be satisfied with a tool that leaves som problems unsolved, but not quite yet... I'll spend some more time on it before I release it.

Should databases always be normalized?

2006-08-29T11:21:00.000+02:00

I posted a comment on this subject today. My point is not that normalization is not good as a rule, but rather that there are times that you could optimized your design by deviating from the model.

The example seems to be such a case, where you avoid having a table with very many records by storing the data in a list (violates first normal form). More about this later...

You could also take a look at Denormalization.

Moving from MS SQL Server to MySQL

2006-08-28T14:26:00.000+02:00

This spring I have added some new modules to our system. The old ones are all based on Microsoft SQL Server, but for the new ones we decided to go with MySQL as a part of a strategy of moving to MySQL with the whole system. We decided to use InnoBase as a storage engine and Hibernate for accessing the database from our Java application. No strange decisions, I believe.

The development phase is now over, with no major problems, and the system is up and running. During this autumn we will see how it works, but I'm nit very worried, it seems stable.

Unfortunately the customers strategy is today unclear, so we're stuck in a situation with two databases. Personally, I feel that using MS SQL Server in a Java environment is a questionable approach, since Microsoft is positioning the product as a part of the .net platform. And the 2000 version, which we are currently running, is getting old...

Enter the blog universe

2006-08-28T13:24:00.000+02:00

OK, finally registered for a blog, as I have been thinking about for quite a while. Let's see where this leads to, will I have the energy to keep it up?

And in english (more or less...) too! Why would a swede write in english? Well, mainly because the content will be mostly computer related, where english seems like the natural language. Might be a few more personal entries too, but that is really not the purpose.