LOAD XML submitted

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

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

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

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

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

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:

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

Limitations

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

More Conversion tool problems

My conversion tool is getting better, soon time to release.

What makes it possible is really that T-SQL is such a poor language, not much to take care of. But that is also the problem, since som features are far from perfect, T-SQL code contains a lot of complicated constructs, like error handling with goto's, and checking @@error efter every SQL statement. Converting that to MySQL would make horrible code, in a system where much better error handling is available. But there is currently no GOTO in MySQL...

So that will be unsolved, GOTO's and labels etc. Also references to SQL Server specific system tables, views, procedures and varibles will be largely unconverted.

Conversion of Microsoft T-SQL to MySQL

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?

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

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

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.