Thursday, September 21, 2006

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

1 comment:

Digital Reality said...

Can you tell me something about the syntax I have to use, when I want to convert a file that contains the T-Sql code? Or do I have to run the tool from the console and paste the code?

Thanks in advance, André :)