we were using OpenAmeos10 with Sybase, and just upgraded OpenAmeos to 11. But since Sybase is practically dead, it is a good idea to use a living RBDMS engine instead. Since we have no MS-SQL server running, I tried to setup Ameos repository on one of our MySQL instances using ODBC.
Actually I did it and working almost flawlessly :) (just need to install 32bit(!) MySQL ODBC connector an QT's QODBC3 driver handles a rest of it)
I have created a rule file (templates/ct/oms/sql/mysql.rules) see attached.
-MySQl using different quote on database names : soo called back-quote `
-MySQL does not allow "#" symbol in temp table name
and a few minor differences what successfully handled in the rules file.
And it is working well, with one exception what cannot be "fixed" from the rule files, it is deep in the OMS query layer.
The error in the log:
info: SELECT t0.* FROM xx_file t0 WHERE (t0.xx_type = 401704 AND (t0.xx_name not like '
error: OMS Error 11000: Failed Error while executing SQL: [MySQL][ODBC 5.1 Driver][mysqld-5.1.49]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 ''\')) ORDER BY t0.xx_name' at line 3 QODBC3: Unable to execute statement
Looks like MySQL using a strict (different) escaping scheme so '\' is not a valid literal.
However I switched back my MySQL server instance to "ANSI" mode, where the escape character is quote ' (by default it was backslash \ )
I got same error in each cases as above. :(
This OMS error occures when a DiagramEditor's Open dialog is try to populate itself with diagram names.
I think the error is related to how the wildcard character (*) in like ($) operator of OMS is translated to SQL level.
Example1: selecting filenames starting with 1 on Ameos Desktop Advanced OMS query : file[name $ '1*']
The error in log:
info: SELECT t0.* FROM xx_file t0 WHERE t0.xx_name like '1
error: OMS Error 11000: Failed Error while executing SQL: [MySQL][ODBC 5.1 Driver][mysqld-5.1.49]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 ''\'' at line 2 QODBC3: Unable to execute statement
error: Syntax error in query: file[name $ '1*' ]
So it is sure now, that MySQL has an issue with the translated OMS queries...
And also found that MySQL does not support the SELECT ... INTO ... syntax.
You have to use the INSERT INTO ... SELECT .. syntax to accomplish there.
info: SELECT DISTINCT t0.xx_from_node_id AS xx_from_node_id INTO xxx_tempT0007G608124ameozso FROM xx_link t0, xx_link_ref t1 WHERE (t0.xx_type = 601822 AND t0.xx_id = t1.xx_link_id)
error: OMS Error 11000: Failed Error while executing SQL: [MySQL][ODBC 5.1 Driver][mysqld-5.1.49]Undeclared variable: xxx_tempT0007G608124ameozso QODBC3: Unable to execute statement
error: "D:\Programs\OpenAmeos11.0\templates\uml\rules\qrl\trees.qrl", line 359: node[UmlClass && node_refs[file[type==UmlClassDiagram]] && !in_links[UmlContains && link_refs[file[UmlClassDiagram]]] && !out_links[UmlGeneralization && link_refs]] sort by name is not a valid query
And that was the point where I gave up trying...
I have collected my thoughts and logs and wrote it down.
Someday someone may use it.
Currently I have other work to do, may continue the investigation later on.