Euss 0.9.5.1 not compatible with MySQL 5

Jun 29, 2012 at 9:57 AM
Edited Jun 29, 2012 at 11:08 AM

Hi folks,

My bussines logic works perfectly with SQL 2000/2005/2008/2008R2/2012, ORACLE 9/10/11 and today I have tested it with MySQL v5.0.51.

I'm getting a SQL syntax error at this sentence:

SELECT COUNT (*  ) FROM (SELECT 'Evalos:Logica:Usuarios:Opcion' AS `EntityType` , e0.`OP_CODI` AS `pkOP_CODI` , e0.`OP_CODI` AS `Codigo` , e0.`OP_ADMI` AS `EsAdministrador` , e0.`OP_VCAL` AS `EditarAbsentismos` , e0.`OP_VCAL` AS `VerAbsentismos` , e0.`OP_AFVER` AS `CorreccionesReglasFNL` , e0.`OP_SCAL` AS `CorreccionesAuto` , e0.`OP_EMAR` AS `Marcajes` , e0.`OP_MINC` AS `MarcajesIncidencias` , e0.`OP_ETIM` AS `MarcajesTiemposIncidencias` , e0.`OP_MCAL` AS `Calendarios` , e0.`OP_CFAS` AS `CalendariosFechasAnteriores` , e0.`OP_AINS` AS `RegistrosInsertar` , e0.`OP_AELI` AS `RegistrosEliminar` , e0.`OP_AMOD` AS `RegistrosModificar` , e0.`OP_AFIL` AS `FiltrosInsertar` , e0.`OP_AEXT` AS `ExternosBuscar` , e0.`OP_APEG` AS `ExternosPegar` , e0.`OP_FILT` AS `FiltroPersonal` , e0.`OP_FILTVISITAS` AS `FiltroVisitas` , e0.`OP_FILTEMP` AS `FiltroEmpresa` , e0.`OP_FILTDEP` AS `FiltroDepartamento` , e0.`OP_FILTSEC` AS `FiltroSeccion` , e0.`OP_FILTAREA` AS `FiltroArea` , e0.`OP_MENU` AS `Menus` , e0.`OP_VISI` AS `ModuloVisitas` , e0.`OP_WOFL` AS `WorkflowValidacion` , e0.`OP_ANOM` AS `ModuloAnomalias` , e0.`OP_ACCE` AS `ModuloCCE` , e0.`OP_ADNIVAC` AS `GestionarNivelesAcceso` , e0.`OP_FILTNIVAC` AS `FiltroNivelesAcceso` , e0.`OP_ADIMP` AS `GestionarImpUni` , e0.`OP_IMPEMD` AS `ImpUniEspacioMinimoDisco` , e0.`OP_IMPNFV` AS `ImpUniMaximoFilasVisualizar` , e0.`OP_DIVPER` AS `DivisionesPersonal` , e0.`OP_FILTTERM` AS `FiltroTerminales` , e0.`OP_FILTINC` AS `FiltroIncidencias` , e0.`OP_ESTADISTICAVISITA` AS `Estadisticas` , e0.`OP_BORRARVISITA` AS `BorrarVisitas` , e0.`OP_PLANOS` AS `ModuloPlanos` , e0.`OP_ADMINPLANOS` AS `AdministradorPlanos` FROM `OPCIONES`  AS e0 WHERE ((e0.`OP_CODI` = 'USUARIO' ) ) ) AS e1

the error is:

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 '*  ) FROM (SELECT 'Evalos:Logica:Usuarios:Opcion' AS `EntityType` , e0.`OP_CODI`' at line 1

In SQL Developer, when I remove SELECT COUNT (* ) FROM (   and   ) AS e1, query returns values correctly.

 

map.config:

<?xml version="1.0" encoding="utf-8"?>
<PersistenceEngines xmlns="http://euss.evaluant.com/schemas/EngineConfiguration.xsd"
DefaultEngine="MYSQL">
<PersistenceEngine Name="MYSQL" Factory="Evaluant.Uss.SqlMapper.SqlMapperProvider">
<ConnectionString>
Server=10.0.255.131;Database=test;User ID=test;Password=test
</ConnectionString>
<Driver>Evaluant.Uss.SqlMapper.MySqlDriver</Driver>
<Dialect>Evaluant.Uss.SqlMapper.MySqlDialect</Dialect>
<MappingFileName>~/DigitekLogic.eum.xml</MappingFileName>
<Metadata Type="assembly">DigitekLogic</Metadata>
<Metadata Type="metadata">~/DigitekLogic.meta.xml</Metadata>
</PersistenceEngine>
</PersistenceEngines>

eum.xml:

  <Entity type="Evalos:Logica:Usuarios:Opcion" table="OPCIONES">
    <Id field="OP_CODI">
      <Generator name="business">
        <Property name="dbType">String</Property>
      </Generator>
    </Id>

    <Attribute name="Codigo" field="OP_CODI" db-type="String" size="20" not-null="true" />
    <Attribute name="EsAdministrador" field="OP_ADMI" db-type="String" size="1" not-null="true" />
    <Attribute name="EditarAbsentismos" field="OP_VCAL" db-type="String" size="1" not-null="true" />
    <Attribute name="VerAbsentismos" field="OP_VCAL" db-type="String" size="1" not-null="true" />
    <Attribute name="CorreccionesReglasFNL" field="OP_AFVER" db-type="String" size="1" not-null="true" />
    <Attribute name="CorreccionesAuto" field="OP_SCAL" db-type="String" size="1" not-null="true" />
    <Attribute name="Marcajes" field="OP_EMAR" db-type="String" size="1" not-null="true" />
    <Attribute name="MarcajesIncidencias" field="OP_MINC" db-type="String" size="1" not-null="true" />
    <Attribute name="MarcajesTiemposIncidencias" field="OP_ETIM" db-type="String" size="1" not-null="true" />
    <Attribute name="Calendarios" field="OP_MCAL" db-type="String" size="1" not-null="true" />
    <Attribute name="CalendariosFechasAnteriores" field="OP_CFAS" db-type="String" size="1" not-null="true" />
    <Attribute name="RegistrosInsertar" field="OP_AINS" db-type="String" size="1" not-null="true" />
    <Attribute name="RegistrosEliminar" field="OP_AELI" db-type="String" size="1" not-null="true" />
    <Attribute name="RegistrosModificar" field="OP_AMOD" db-type="String" size="1" not-null="true" />
    <Attribute name="FiltrosInsertar" field="OP_AFIL" db-type="String" size="1" not-null="true" />
    <Attribute name="ExternosBuscar" field="OP_AEXT" db-type="String" size="1" not-null="true" />
    <Attribute name="ExternosPegar" field="OP_APEG" db-type="String" size="1" not-null="true" />
    <Attribute name="FiltroPersonal" field="OP_FILT" db-type="String" size="1000" not-null="false" />
    <Attribute name="FiltroVisitas" field="OP_FILTVISITAS" db-type="String" size="200" not-null="false" />
    <Attribute name="FiltroEmpresa" field="OP_FILTEMP" db-type="String" size="1000" not-null="false" />
    <Attribute name="FiltroDepartamento" field="OP_FILTDEP" db-type="String" size="1000" not-null="false" />
    <Attribute name="FiltroSeccion" field="OP_FILTSEC" db-type="String" size="1000" not-null="false" />
    <Attribute name="FiltroArea" field="OP_FILTAREA" db-type="String" size="1000" not-null="false" />
    <Attribute name="Menus" field="OP_MENU" db-type="String" size="200" not-null="false" />
    <Attribute name="ModuloVisitas" field="OP_VISI" db-type="String" size="1" not-null="true" />
    <Attribute name="WorkflowValidacion" field="OP_WOFL" db-type="String" size="1" not-null="true" />
    <Attribute name="ModuloAnomalias" field="OP_ANOM" db-type="String" size="1" not-null="true" />
    <Attribute name="ModuloCCE" field="OP_ACCE" db-type="String" size="1" not-null="true" />
    <Attribute name="GestionarNivelesAcceso" field="OP_ADNIVAC" db-type="String" size="1" not-null="true" />
    <Attribute name="FiltroNivelesAcceso" field="OP_FILTNIVAC" db-type="String" size="1000" not-null="true" />
    <Attribute name="GestionarImpUni" field="OP_ADIMP" db-type="String" size="1" not-null="true" />
    <Attribute name="ImpUniEspacioMinimoDisco" field="OP_IMPEMD" db-type="Int32" not-null="true" />
    <Attribute name="ImpUniMaximoFilasVisualizar" field="OP_IMPNFV" db-type="Int32" not-null="true" />
    <Attribute name="DivisionesPersonal" field="OP_DIVPER" db-type="String" size="1" not-null="false" />
    <Attribute name="FiltroTerminales" field="OP_FILTTERM" db-type="String" size="1000" not-null="true" />
    <Attribute name="FiltroIncidencias" field="OP_FILTINC" db-type="String" size="1000" not-null="true" />
    <Attribute name="Estadisticas" field="OP_ESTADISTICAVISITA" db-type="String" size="1" not-null="true" />
    <Attribute name="BorrarVisitas" field="OP_BORRARVISITA" db-type="String" size="1" not-null="true" />
    <Attribute name="ModuloPlanos" field="OP_PLANOS" db-type="String" size="1" not-null="true" />
    <Attribute name="AdministradorPlanos" field="OP_ADMINPLANOS" db-type="String" size="1" not-null="true" />
  </Entity>

meta.xml has not entries

Any suggestion?

Cheers

Coordinator
Jun 29, 2012 at 1:51 PM

Hi,

Wow the number of server you've tested is amazing. It's been a long time since MySQL was not tested. I'm going to investigate ASAP.

Have you tried to have the correct MySQL syntax for this query ? It would be wonderful it you could provide it. Anyway, what you've already provided should be enough to reproduce the issue.

Regards and thanks for using it.

Nicolas Penin

Jul 2, 2012 at 1:20 PM

Hi Nicolas,

the issue is very easy: SELECT COUNT (*  ) FROM is invalid. This query works fine with COUNT( but not with COUNT ( -extra spaces between T and ( -.

So this query works fine now at SQL Developer:

 

SELECT COUNT(*  ) FROM (SELECT 'Evalos:Logica:Usuarios:Opcion' AS `EntityType` , e0.`OP_CODI` AS `pkOP_CODI` , e0.`OP_CODI` AS `Codigo` , e0.`OP_ADMI` AS `EsAdministrador` , e0.`OP_VCAL` AS `EditarAbsentismos` , e0.`OP_VCAL` AS `VerAbsentismos` , e0.`OP_AFVER` AS `CorreccionesReglasFNL` , e0.`OP_SCAL` AS `CorreccionesAuto` , e0.`OP_EMAR` AS `Marcajes` , e0.`OP_MINC` AS `MarcajesIncidencias` , e0.`OP_ETIM` AS `MarcajesTiemposIncidencias` , e0.`OP_MCAL` AS `Calendarios` , e0.`OP_CFAS` AS `CalendariosFechasAnteriores` , e0.`OP_AINS` AS `RegistrosInsertar` , e0.`OP_AELI` AS `RegistrosEliminar` , e0.`OP_AMOD` AS `RegistrosModificar` , e0.`OP_AFIL` AS `FiltrosInsertar` , e0.`OP_AEXT` AS `ExternosBuscar` , e0.`OP_APEG` AS `ExternosPegar` , e0.`OP_FILT` AS `FiltroPersonal` , e0.`OP_FILTVISITAS` AS `FiltroVisitas` , e0.`OP_FILTEMP` AS `FiltroEmpresa` , e0.`OP_FILTDEP` AS `FiltroDepartamento` , e0.`OP_FILTSEC` AS `FiltroSeccion` , e0.`OP_FILTAREA` AS `FiltroArea` , e0.`OP_MENU` AS `Menus` , e0.`OP_VISI` AS `ModuloVisitas` , e0.`OP_WOFL` AS `WorkflowValidacion` , e0.`OP_ANOM` AS `ModuloAnomalias` , e0.`OP_ACCE` AS `ModuloCCE` , e0.`OP_ADNIVAC` AS `GestionarNivelesAcceso` , e0.`OP_FILTNIVAC` AS `FiltroNivelesAcceso` , e0.`OP_ADIMP` AS `GestionarImpUni` , e0.`OP_IMPEMD` AS `ImpUniEspacioMinimoDisco` , e0.`OP_IMPNFV` AS `ImpUniMaximoFilasVisualizar` , e0.`OP_DIVPER` AS `DivisionesPersonal` , e0.`OP_FILTTERM` AS `FiltroTerminales` , e0.`OP_FILTINC` AS `FiltroIncidencias` , e0.`OP_ESTADISTICAVISITA` AS `Estadisticas` , e0.`OP_BORRARVISITA` AS `BorrarVisitas` , e0.`OP_PLANOS` AS `ModuloPlanos` , e0.`OP_ADMINPLANOS` AS `AdministradorPlanos` FROM `OPCIONES` AS e0 WHERE ((e0.`OP_CODI` = 'USUARIO' ) ) ) AS e1

Please can you provide a fix for version 0.9.5.1? or should I update to latest sources?

Cheers

 

Coordinator
Jul 8, 2012 at 7:27 PM

Hi,

The bug correction has just been saved to the source control. You may retrieve it and compile euss if you want. It is available in the Versions/1.0 source folder.

 

A beta of the revamped version will be available soon. Would you be ready to test it ?

 

Cheers,

Nicolas Penin

Jul 10, 2012 at 9:14 AM

Thanks a lot Nicolas,

I will test this version 1 and I wll be in touch if an other error is reported.

Cheers

Jul 10, 2012 at 12:26 PM

Nicolas,

I downloaded relesase 98643 and I found these issues in version 1:

1. I had had to delete Evaluant.Uss.Cache files because they were not included at release

2. For MySQL, MAX and MIN did not work. I added this code after yourt count and now MAX and MIN worked fine:

        static MySqlDialect()
        {
            COUNT = "COUNT";
            MAX = "MAX";
            MIN = "MIN";
        }

Please give a shout when you update your source with these issues.

Cheers