5 Replies Latest reply on Oct 4, 2004 12:22 PM by Viet

    Non-SQL92 SQL Statement Strategy

    Marc Lavergne Newbie

      Ok, some SQL statements used in the EJB xdoclet defs extend beyond SQL92 causing portability issues. To cite a particular example, from org.jboss.nukes.core.ejb.FileEJB:

      SELECT DISTINCT SUBSTRING(f.fileId, 1, LOCATE('/', f.fileId, LENGTH(?1) + 2)) FROM file AS f WHERE f.fileId LIKE CONCAT(?1, '%/%')

      This statement uses several MySQL specific extensions / names, namely: SUBSTRING(), LOCATE(), AS x, and CONCAT()

      I need a strategy for making this sort of SQL portable. Here are the options as I see them:

      1) Define Ant filters to insert DB specific code at compile time
      2) Modify the XDoclet template to insert DB specific code at compile time
      3) Extend XDoclet jboss tags to include something like @jboss.query.mysql, ...oracle, etc
      4) Create a set of SQL functions to mimic the desired routines
      5) Create a java class to mutate the SQL statement to the DB specific format
      6) Ignore problem and hope it goes away

      My gut feeling is that Ant filters -or- the XDoclet template are the way to go with this but I'd like some input from others as to the preferred route since this could have pretty far reaching implications. Thanks!

        • 1. Re: Non-SQL92 SQL Statement Strategy
          Marc Lavergne Newbie

          you can do it with log4j.xml, afair :

          set CONSOLE level as DEBUG
          set category org.jboss to INFO
          set category org.jboss.ejb.cmp.jdbc to DEBUG

          • 2. Re: Non-SQL92 SQL Statement Strategy
            Viet Master

            this is EJBQL or JBossQL so it is portable accross different database since it is compiled by CMP2 into the good SQL dialect by the engine.


            "mlavergn" wrote:
            Ok, some SQL statements used in the EJB xdoclet defs extend beyond SQL92 causing portability issues. To cite a particular example, from org.jboss.nukes.core.ejb.FileEJB:

            SELECT DISTINCT SUBSTRING(f.fileId, 1, LOCATE('/', f.fileId, LENGTH(?1) + 2)) FROM file AS f WHERE f.fileId LIKE CONCAT(?1, '%/%')

            This statement uses several MySQL specific extensions / names, namely: SUBSTRING(), LOCATE(), AS x, and CONCAT()

            I need a strategy for making this sort of SQL portable. Here are the options as I see them:

            1) Define Ant filters to insert DB specific code at compile time
            2) Modify the XDoclet template to insert DB specific code at compile time
            3) Extend XDoclet jboss tags to include something like @jboss.query.mysql, ...oracle, etc
            4) Create a set of SQL functions to mimic the desired routines
            5) Create a java class to mutate the SQL statement to the DB specific format
            6) Ignore problem and hope it goes away

            My gut feeling is that Ant filters -or- the XDoclet template are the way to go with this but I'd like some input from others as to the preferred route since this could have pretty far reaching implications. Thanks!


            • 3. Re: Non-SQL92 SQL Statement Strategy
              Viet Master

              you can do it with log4j.xml, afair :

              set CONSOLE level as DEBUG
              set category org.jboss to INFO
              set category org.jboss.ejb.cmp.jdbc to DEBUG

              • 4. Re: Non-SQL92 SQL Statement Strategy
                Marc Lavergne Newbie

                To close out this thread, it turned out to be a bug in XDoclet (I've submitted a patch), so there's no further action needed by the Nukes team.

                • 5. Re: Non-SQL92 SQL Statement Strategy
                  Viet Master

                  good to hear about that. where is the state of your work ? I saw you both commited to head and 1.1, can you report about it please ?