Hi

I have discovered an error (I think) in generated java code, when I use the useCurrentSchema option, and the SQL statement contains a UNION.

The second part of the generated sql statement does not contain the schema name:

EGL statement:

  try
    open F000PSF_RSI01
      with #sql{
        SELECT ADCOMP, ADUSER
          FROM VADMAUTH AD
         WHERE
           ADUSER = :ADUSER
         UNION                      
        SELECT USCOMP, USUSER    
          FROM USERBAS    
         WHERE USUSER = :ADUSER
         ORDER BY 1 ASC
      }      
      into ADCOMP, ADUSER
      for F9ADFDB ;
    onException F9ERS() ;
  end

Generated java statement:

String $sql = "SELECT ADCOMP, ADUSER "
                    + "FROM " + ezeProgram.egl__io__sql__SQLLib.currentSchema.getValue() + "VADMAUTH AD " + "WHERE ADUSER = " + "? " + "UNION SELECT USCOMP, USUSER FROM USERBAS WHERE USUSER = " + "? " + "ORDER BY 1 ASC ";

I need to use the useGeneratedSchema option, as the Schema name can be picked dynamically within my EGL web application, so adding the schema name outside the application is not possible.

Any suggestions what to do ? Or is this a EGL generation bug ?

Environment: RBD v9.5.0.1. Generation as EGL Web transactions, running within WAS Liberty and Tomcat on Windows and Linux.

TIA

Morten Hansen, IBM Denmark

Hello Morten,

It indeed seems a bug in the EGL generation. We don't have this problem because we use on our SQL driver the URL:
jdbc:<machine name>;prompt=false;naming=system;libraries=*libl

And also on our Websphere application server this setting is activated. This way the librarylist from the user is used.

Greetings, Jeroen

Hi Morten,

It looks like you have found a defect so can you kindly raise a PMR with your full test case attached please?

Once I have the PMR I will be happy to pick it up to pursue a fix with development for you asap.

Best Regards
Mark

Mark Hall
HCL Technologies
RBD L2 Support

Thanks alot Mark

I was doing some testing on the newest v9.5.1.1 - to see if the error was disappeared. And now I don't have to pursue that one.

I will make a PMR - I hope. But as an IBM Internal customer of EGL/RBD I don't have a 'real' customer number, so I know it is a challenge.

Stay tuned.

Morten Hansen, IBM Denmark

Hi Morten

I think I saw a similar problem just recently with the IN operator, and I presume it could be the same with Exists, different type of 'Joins' etc.

Try to add the table name (USERBAS) to your EGL SQL record like:

Record yourRecord type sqlRecord  {
  tableNames = [["VADMAUTH", "AD"], ["USERBAS", "US"]] …

… and then Generate your project again, hopefully this will work. At least it did for me.

Here's a link to the documentation:
https://www.ibm.com/support/knowledgecenter/en/SSMQ79_9.5.1/com.ibm.egl.lr.doc/topics/regl_data_sql_current_schema.html

Notice this line in particular:
The EGL I/O statement must specify an EGL SQLRecord. The tableNames or tableNameVariables properties of the record identify the table names that need qualification.

So it might not be a bug after all. :-)

Best regards
Peter Vilhelmsen, Xact Consulting
+45 40 28 98 80

Hi Peter

long time no see - happy to hear from you.

And - you're absolutely right. When I add the second db2 table USERBAS in the SQL Record, then the generated java sql statement is correct:

String $sql = "SELECT ADCOMP, ADUSER "
+ "FROM " + ezeProgram.egl__io__sql__SQLLib.currentSchema.getValue() + "VADMAUTH AD " + "WHERE ADUSER = " + "? " + "UNION SELECT USCOMP, USUSER FROM " + ezeProgram.egl__io__sql__SQLLib.currentSchema.getValue() + "USERBAS WHERE USUSER = " + "? " + "ORDER BY 1 ASC ";

So - problem fixed, and no error left.

Thx alot for this. I owe you a large beer when we meet next time.

PS. Mark - thx for your help too. If you come to Denmark I will supply you with a danish beer too :-)

Very Best Regards

Morten Hansen, IBM Denmark

This little change did the trick:

I added the second table ,["USERBAS", "US"] to tableNames as Peter suggested, and after that the generated java is correct -  the schema name is included in the sql statement.

Record F9ADFDB-USERBAS type sqlRecord  {
  tableNames = [["VADMAUTH", "AD"],["USERBAS", "US"]], keyItems = [ADCOMP]
  }
  3 ADCOMP ADCOMP { column = "ADCOMP", isSqlNullable = yes, sqlDataCode = 453} ;
  3 ADUSER ADUSER { column = "ADUSER", isSqlNullable = yes, sqlDataCode = 453} ;
  3 ADNAME ADNAME { column = "ADNAME", isSqlNullable = yes, sqlDataCode = 453} ;
  3 ADDATE ADDATE { column = "ADDATE", isSqlNullable = yes, sqlDataCode = 453} ;
end // end F9ADFDB

Morten Hansen, IBM Denmark