DF 18.2 - Find Ge error with Db2 - Invalid syntax - ORDER BY

[Samuel Pizarro]

We are getting this error once in a while with a FIND GE operation against a db2 table.

DF 18.2.

    Clear TLgrCondutorTransp
    Move TLgrCondutor.iRn_Condutor to TLgrCondutorTransp.iRn_Condutor
    Find ge TLgrCondutorTransp by 3
...

Error:

Erro genérico 42601 (-104)--[IBM][CLI Driver][DB2/NT64] SQL0104N  An unexpected token ")RDER BY "IRN_CONDUTOR" ASC,"IRN_CONDUTOR" was found following "ONDUTORTRANSP" > ?))".  Expected tokens may include:  "<space>".  SQLSTATE=42601
  Table = TLGRCONDUTORTRANSP (128), Source = DB2_DRV.Find


MSG_ERROR_REPORT (433) - oWebAppError (4) - at address 5837
MSG_ERROR_REPORT (433) - oErrorHandler (23) - at address 16000
MSG_ONSETCALCULATEDVALUE (6021) - oColTransportadora (1928) - at address 47107
MSG_REFRESH (454) - oColTransportadora (1928) - at address 18161
GET_FINDBYROWIDEX (1761) - oTLgrCondutor_Lista_DD (1910) - in native code
GET_FINDBYROWIDEX (1761) - oTLgrCondutor_Lista_DD (1910) - at address 4873
MSG_CLOSEPROMPTLIST (6802) - oPromptList (1924) - at address 26571
MSG_OK (152) - oPromptList (1924) - at address 26614
MSG_ONCLICK (6324) - oOkButton (1930) - at address 47140
GET_HANDLEACTION (5703) - oOkButton (1930) - at address 14700
GET_CALLACTION (6203) - oWebApp (20) - at address 20517
GET_CALLACTION (6203) - oWebServiceDispatcher (36) - at address 20212
GET_DOCALL (1284) - oCallInterface (37) - in native code
GET_DOCALL (1284) - oWebServiceDispatcher (36) - at address 10018
MSG_ONDFFUNC (5001) - oVDFInetSession (26) - at address 8588
MSG_STARTWEBAPP (5789) - oWebApp (20) - at address 15862
[start] - at address 234975


Erro número: 12289 linha: 47107.

when it happens, it keeps happening until you restart the webapp..

please note that the ORDER BY clause get's truncated in the final statement.
WHERE ( ...ONDUTORTRANSP" > ?)))RDER BY ...

Index 3 definition is pretty simple 2 keys segment.. (col + id)
IRN_CONDUTOR ASC, IRN_CONDUTORTRANSP ASC


Has anyone seen this before ?

DAW, any know issue at previous driver releases that might have been already fixed in most recent ones ?

[Martin Moleman]

Samuel,

This does not ring a bell. Can't remember ever seeing this before.
Is this only on this specific table? Is there anything special about it?
You could try and use the latest 6.3 driver and see if that solves the issue: https://support.dataaccess.com/Forum...s-is-available!

[Samuel Pizarro]

Hi Martim.

No, nothing special in the table. So far the problem happened only 2 times and in different tables.
fisrt occurrence was back in Feb-24.

Erro genérico 42601 (-104)--[IBM][CLI Driver][DB2/NT64] SQL0104N  An unexpected token ")RDER BY "IRN_VIAGEM" DESC,"IRN_VIAGEMSEQ" was found following "VIAGEMSEQOPER" < ?))".  Expected tokens may include:  "<space>".  SQLSTATE=42601
  Table = TLGRVIAGEMSEQOPER (100), Source = DB2_DRV.Find


MSG_ERROR_REPORT (433) - oWebAppError (4) - at address 5837
MSG_ERROR_REPORT (433) - oErrorHandler (23) - at address 16000
MSG_DATA_SET_POSTFIND (1751) - oTLgrViagemSeqOper_DD (2975) - at address 5504
MSG_LOCATE_NEXT (393) - oTLgrViagemSeqOper_DD (2975) - in native code
GET_LOADDATA (6402) - oLstProdutos (3267) - at address 21869
MSG_GRIDREFRESH (6399) - oLstProdutos (3267) - at address 21806
MSG_REFRESH (454) - oLstProdutos (3267) - at address 22089
MSG_REQUEST_FIND (385) - oTLgrViagem_DD (2961) - in native code
MSG_REQUEST_FIND (385) - oTLgrViagem_DD (2961) - at address 4917
MSG_FIND (96) - oTLgrViagem_DD (2961) - at address 4590
MSG_ONSHOW (5932) - oViagemInfo (2732) - at address 120920
GET_HANDLEACTION (5703) - oViagemInfo (2732) - at address 14700
GET_CALLACTION (6203) - oWebApp (20) - at address 20517
GET_CALLACTION (6203) - oWebServiceDispatcher (36) - at address 20212
GET_DOCALL (1284) - oCallInterface (37) - in native code
GET_DOCALL (1284) - oWebServiceDispatcher (36) - at address 10018
MSG_ONDFFUNC (5001) - oVDFInetSession (26) - at address 8588
MSG_STARTWEBAPP (5789) - oWebApp (20) - at address 15862
[start] - at address 219171


Erro número: 12289 linha: 21869.

as you can see, it was in a different table.

The interesting part of both , is that once it happens, all find GE against that tables get affected.
you need to restart the webapp in order to “unload and reload the driver” to get away from it.

the other aspect, we have this same webapp deployed in 10 different customers, and the 2 occurrence happened at the same costumer environment. But we use the same df/driver and db2 server/client versions across all o f them.

I am not being able to identify the difference!

just providing our observations, but I understand it doesn’t help too much.

[Vincent Oorsprong]

Samuel,

Is it possible that this is caused by an SQL filter?

[Samuel Pizarro]

It can't be Vincent!

Even if we make use of a psSqlFilter, , this only affectst the WHERE clause content..

We (dev layer) have no way to influence the ORDER BY clause generated by the driver. Keep in mind this is a regular Find GE operation.. not an Embeeded-SQL.

And the problem here is that the ORDER BY, is getting truncated or the O is getting replaced by an ")"

[Focus]

Given it is all CAPS perhaps someone caught the zero instead of the "oh" whilst holding down the shift and hasn't yet spotted it?

[Samuel Pizarro]

Sorry Focus..

Was not able to understand you ...

[WWGT]

Really smells like someone made a fat finger typo somewhere in the driver code

[Garret Mott]

He means that what you are thinking is the letter O is actually zero - IOW a typo.

However, if it happens rarely, I wouldn't think that's the issue - but what do I know?

[Samuel Pizarro]

Hum.

I really don’t think this is the case. As I said, that us nor part of user inputs or sqlfilter ( dev code error ).
The Order by clause is 100% managed by df driver based on the index being used.