MySQL Support
MySQL does not allow reserved words like option and delete as field names. So, I needed to change some resources to accommodate that. Wherever, option and delete were used in any statement or view, I have changed them to optionx and deletex. So, pick up the updated resources from workshop.
MySQL does not have many of the standard features that any SQL database must have. That's why there are hundreds of tools available for converting/migrating from MySQL to PostgreSQL, but there isn't a single tool available to do the opposite. I have therefore added a new feature in view definition. In addition to standard sql statement, you can now define database specific statement. 1) For instance:
sql select zid, xmenu, xseq,xoption, ximage, xltype,'portal?page='||xobj as xlink, xpagenot from pxmenu
sqlMySQL select zid, xmenu, xseq,xoption, ximage, xltype,concat('portal?page=', xobj) as xlink, xpagenot from pxmenu
sqlOracle select zid, xmenu, xseq,xoption, ximage, xltype,concat('portal?page=', xobj) as xlink, xpagenot from pxmenu
I think first sql statement is also work in sqloracle
Many of our views will not work on MySQL due to a a serious deficiency in MySQL. It does not support a subquery in the FROM clause. In all such cases you need to create separate views for each such predicates and then add sqlMySQL statement in the original view definition. Please name these views as <original-view-name>_Number_MySQL.table. For instance, in imtrn.table
Current sql is
sql select ztime,zid,xwh,xitem,case when xtypedim='Notes' then '' else xitemext end, xbatch,xdate,xsign,xqty,xqtyalt,xval,xdoctype,xdocnum,xdocrow,xparty,xloc,xtypedim from (select ztime,zid,xwh,xitem,xitemext,xbatch,xdate,xsign,xqty,xqtyalt,xval,xdoctype,xdocnum,xdocrow,xparty,xloc,coalesce((select xtypedim from caitem where zid=tbl.zid and xitem=tbl.xitem),'') as xtypedim ........
Add a custom sql like this:
sqlMySQL select ztime,zid,xwh,xitem,case when xtypedim='Notes' then '' else xitemext end, xbatch, xdate, xsign,xqty,xqtyalt,xval,xdoctype,xdocnum,xdocrow,xparty,xloc,xtypedim from imtrn_1_MySQL ........
And create a new view imtrn_1_MySQL with the following sql:
sql select ztime,zid,xwh,xitem,xitemext,xbatch,xdate,xsign,xqty,xqtyalt,xval, xdoctype,xdocnum,xdocrow,xparty,xloc,coalesce((select xtypedim from caitem where zid=tbl.zid and xitem=tbl.xitem),'') as xtypedim from imtrn_2_MySQL
And then again create another view imtrn_2_MySQL, and so on.
It is best to create a custom folder MySQL and put all these extra views there. If a customer wanst to use MYSQL, just add MySQL in “features” parameters in da.globals.
It's not complicated but extremely tedious. So, the best option is to convince the customer to use something else. MySQL is a simple and fast database for simple queries but not at all suitable for complex systems. Even the transaction features were added only recently.
