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.