1. Matt Haas
  2. PowerBuilder
  3. Friday, 6 November 2020 11:11 AM UTC

Hi.
I have a datawindow which worked fine in PB12.6, but is missing data after retrieve in PB2019r2.
retrieve: dw_data.retrieve(lnDstid, lnJmtFrom, lnJmtTarget, lnTimeTarget, lnTimePrefix) => result = some data is missing

if i get the sql-select like dw_data.Object.DataWindow.Table.Select and replace all retrieve parameter with their corresponding value => it work as it should.

It seems the dw has a problem to set the values for the retrieve parameter. I checked the values just before the retrieve line and they are ok.

i am using PB2019r2 build 2353, win10, mysql8, mysql8odbc.

here is the sql select for the dw:

retrieve="SELECT   tm.id,  tm.dstid1,  tm.prsid1, CONCAT(pers.fnam, IFNULL(CONCAT(' ', pers.vnam), ''), ' (', IFNULL(pers.dnno, ''), ')') as cName, dpbr.id, dpbr.kbez as cBereich, IFNULL(dpbr.nType, -1) as dpbr_nType, pers.dnno as nPersNo,
        v_att.nYMD as jhrmnttag, v_att.tsEvent as tsEvent, IFNULL(v_att.nType, -1) as v_att_nType,
        v_dtl.jhrmnttag, v_dtl.dattm, v_dtl.btm, v_dtl.etm, v_dtl.dtlstd
FROM dptl_tdpmn as tm
      INNER JOIN dptg_personal as pers ON tm.prsid1 = pers.id
      INNER JOIN dptl_persmn ON tm.dstid1 = dptl_persmn.dstid1 and tm.prsid1 = dptl_persmn.prsid1
      LEFT OUTER JOIN
                (SELECT t_tdpdtl.dstid1, t_tdpdtl.prsid1, t_tdpdtl.jhrmnttag, TIMESTAMP(t_tdpdtl.jhrmnttag, CONCAT(TRUNCATE(t_tdpdtl.btm/60, 0), ':', MOD(t_tdpdtl.btm,60), ':00')) as dattm, t_tdpdtl.btm, t_tdpdtl.etm, t_tdpdtl.dtlstd, t_tdpdtl.dpbrid1
                FROM dptl_tdpdtl as t_tdpdtl
                WHERE t_tdpdtl.dstid1 = :anDstid and
                      t_tdpdtl.jhrmnttag = :anJmtTarget and
                      t_tdpdtl.btm <= :anTimeTarget and
                      t_tdpdtl.etm >= :anTimeTarget
                ) as v_dtl ON tm.dstid1 = v_dtl.dstid1 and tm.prsid1 = v_dtl.prsid1 and tm.jhrmnttag = v_dtl.jhrmnttag
      LEFT OUTER JOIN
                dptl_dpbr as dpbr ON v_dtl.dpbrid1 = dpbr.id
      LEFT OUTER JOIN
                (SELECT t_attend.nDstid1, t_attend.nPrsid1, t_attend.nPersNo, t_attend.nYMD, :anJmtTarget as jmttarget, t_attend.tsEvent, t_attend.nType  
                FROM  dptl_attendance as t_attend
                     WHERE (t_attend.nDstid1 = :anDstid) and
                      (t_attend.nPrsid1 > 0) and
                      (t_attend.nYMD between :anJmtFrom and :anJmtTarget) and
                      (t_attend.tsEvent = (select max(a.tsEvent) as dattm FROM dptl_attendance as a
                                                  WHERE a.nDstid1  = t_attend.nDstid1 and a.nPrsid1 = t_attend.nPrsid1
                                                  and a.nYMD between :anJmtFrom and :anJmtTarget
                                                  and a.tsEvent <= TIMESTAMP(:anJmtTarget, CONCAT(TRUNCATE(:anTimeTarget/60, 0), ':', MOD(:anTimeTarget,60), ':00'))
                                                  ))
                ) as v_att ON tm.dstid1 = v_att.nDstid1 and tm.prsid1 = v_att.nPrsid1 and tm.jhrmnttag = v_att.jmttarget
WHERE tm.dstid1 = :anDstid and
      tm.jhrmnttag = :anJmtTarget
" arguments=(("anDstid", decimal),("anJmtFrom", number),("anJmtTarget", number),("anTimeTarget", number),("anTimePrefix", number))  sort="tsevent A personal_npersno A " )

Does anybody know if this is a bug or is there something i overlooked?

Regards
Matt

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 6 November 2020 11:54 AM UTC
  2. PowerBuilder
  3. # 1
1
Votes
Undo

Hi,

I haven't used MySQL with PB but if it worked in 12.6 and not in 2019, check if your dbparm connection values are the same in 2019. I'd look especially at the DisableBind and StaticBind parameters.

Hope it helps.

Comment
I thought it might be worth the try, since you mention that it works if you replace the parameters with the values.

You could also do a debug in the sqlpreview event and see if that gives you any clue on what's wrong with the SQL Select statement.

Another thing you could try, is to on tracing in the sqlca.dbms parameter.

regards
  1. Miguel Leeuwe
  2. Friday, 6 November 2020 15:48 PM UTC
i tried it with DisableBind=1 and it seems to work.

Thanks for your help
  1. Matt Haas
  2. Saturday, 7 November 2020 09:31 AM UTC
Glad it works!
  1. Miguel Leeuwe
  2. Saturday, 7 November 2020 12:42 PM UTC
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.