Re: WITH SQL statement via EGL

[Jason Abreu <abreuj@xxxxxxxxxxxxxxxxx>]

Basically I believe it comes down to the #sql in EGL becomes SQLJ in the 
generated Java.  Using SQL in a prepared statement has no limitations; 
all ISO-standard SQL is allowed (whatever is supported by your system).

Steve Mervosh wrote:
> Jason, I have not used WITH statements within EGL, but I did run into a
> problem on V5R4 i5/OS with recursive statements like WITH in another Java
> app.  In V6R1 i5/OS, this restriction does not exist.
>
> Basically, there are 2 query engines on i5/OS:  CQE and SQE.  Only SQE
> supports recursive WITH statements (and some other more  complex queries).
> If your JDBC DataSource sort property is not using a Sort Sequence of
> sort=hex ( i.e. sort=language, or sort=job, etc), your query on V5R4 will
> get dropped down into CQE which won't support the WITH statement.
>
> I couldn't find anything about EGL not supporting WITH, and I don't have
> any evidence that it does.  But I wanted to mention this gotcha with WITH
> on i5/OS in case that's what you encountered.  I could test it out if
> anyone needs more info - I have a WITH statement I could plug into EGL for
> a test run.
>
>
> ******************************************************************************************************
>
> EGL SQL does not support everything that SQL can do.
> For instance, it doesn't support the WITH clause
> (WITH table1 as ..., table2 as ..., etc).
> So the #sql in EGL may not support the timestamp function.
>
> I believe the best solution would be to use a prepared statement.
> That's all I use any more because of the control I have over the SQL
> statement.
>
> If you have any questions, feel free to email me directly.
>
> Try this:
>
> prepare myStmt from
> "select cmtclaim, cmtdate, cmttime, cmtcomm, cmtuser, cmtprior, " +
> "cmttype from NTFILES.CLMNOTESL2 where cmtclaim = ? and " +
> "timestamp(cmtdate, cmttime) <= timestamp(?, ?) ";
>
> try
>    get comment with myStmt using claimn, datein, timein;
> onException (exc AnyException)
>    //process exception
> end
>
> --Jason
>
> Steve Mervosh
> THOR (Total Hardware and Order Repository)
> Software Development
> IBM Rochester Minnesota
> spm@xxxxxxxxxx