Finding wrong Reference name when building dependency tree

beckhamrryyaann commented 7 months ago

Hi Liam, I know you are busy working on this extension trying to get it off the ground....so this is low priority for me, but I did find a bug I believe. See screen shot below where you found the reference PRDBLK2ADD you also brought in the ) I also included screen shot of the sql statement where it didn't work right because it looks like it worked right sometimes.

Also I'm not sure why you are trying to find a reference for `:STMT`. That is just a RPGLE character variable. The tricky part is that STMT variable holds sql that does reference other references! (tables, etc).

Also it looks like you are always one line off when reporting the line number for the error where you can't find a reference.

worksofliam commented 7 months ago

@beckhamrryyaann are you saying PRDBLK2ADD should have been found?

Here's what I need to recreate this:

The source code to PRDBLK2ADD
copy and paste that SQL statement to this issue!

I will fix the :stmt reference bug!

beckhamrryyaann commented 7 months ago

The bug is when you find the reference in this one sql statement (i have pasted below) you bring in the ) after the table name.

--- Source to create PRDBLK2ADD
CREATE OR REPLACE TABLE PRDBLK2ADD ( 
    B2ADDID DECIMAL(5, 0) NOT NULL DEFAULT 0 )   

    RCDFMT PRDBLK2ADD ;

-- sql statement causing the the bug, when you find the reference you are also bringing in the ) at the end.
            Exec Sql Update PrdBlock
                Set cgday     = :leastUtilDay,
                    cgcnstdpt = :pd$dept,
                    cgcnstjob = :pd$jobc
                Where cgblock in (Select b2addid
                                    from prdblk2add) and
                        cgday = 0;

Also I want to bring your attention to the statement numbers in the output that your write to the console.

optimizer/programs/OPTMZR0060-Optimize.PGM.SQLRPGLE:313 - No object found for reference 'PRDBLK2ADD)'

Notice that you say this is line 313, but when i go to the source code this line of code actually starts on 314.

This one line off is consistent across every error you output.

worksofliam commented 7 months ago

@beckhamrryyaann this detail is great.

I am flying today (I'm in CLT right now!) but tomorrow I will likely get this fixed up for you.

beckhamrryyaann commented 7 months ago

Sounds great! CLT. ? I live around the Hickory area of NC! So you are not that far away. Safe travels.

worksofliam commented 7 months ago

Fix is coming out in 0.8.0

IBM / sourceorbit