NitroQuery Error when updating record via Widget EDITGRID

[ThierryC]

I'm getting an error 'Invalid sql statement' when trying to update a record via the EDITgrid widget within NitroQuery
below is what i'm getting in Vverror:

However trying the same statement in Sql runs without problem.?
Update COBIADTA/OBABAR set FLA8AB='X' where rrn(OBABAR) = 4765
Er zijn 1 rijen bijgewerkt in OBABAR in COBIADTA.


i have a job in Valence50 that has msgw ... looking at the joblog: seems i'm getting SQL7022 (user *N is other than actual user xxx)
i'm updating a file to which has a trigger on it.. i think it's related to that..

Joblog:

Gebruiker *N is ander dan actuele gebruiker TCO.                           
SQLERROR. Checking Database handle... SQLSTATE = 42977 Native Error =      
  -7022 Message = "Gebruiker *N is ander dan actuele gebruiker TCO.".      
Verbinding met relationele database CMIS001 bestaat niet.                  
SQLERROR. Checking Database handle... SQLSTATE = 01002 Native Error = 596  
  Message = "Fout opgetreden tijdens DISCONNECT van relationele database   
  CMIS001.".                                                               
Cannot resolve to object TROBABAR1. Type and Subtype X'0201' Authority     
  X'0000'.                                                                 
Functiefout opgetreden.MCH3401 niet voorzien door TROBABAR bij             
  programma-instructie 0000004600, systeeminstructie X'0000'.              
Error occurred while calling program or procedure *LIBL/TROBABAR1 (C G D   
  F).

in VVERROR

Error ID 1	REMOVE
Program:	VVDATASRC	Statement:	
Module:		Message ID:	VV00025
Procedure:
adjustRec
Message Text:
Nitro File Editor: SQL statement ended in error
Help Text:
Update COBIADTA/OBABAR set FLA8AB='X' where rrn(OBABAR) = 4765

Valence Login ID:
TCO
IBM i User:
TCO
Timestamp:
2016-07-26-15.12.11.022000
Job Number:
388002
Calling Program:
VVDATASRC
Call Stack:
VVCALL[VVCALL]-->VVDATASRC[VVDATASRC]-->VVDATASRC[ADJUSTREC]

[robert.swanson]

Hi Thierry,

Would it be possible for you to temporarily disable the trigger on that file and then try the update again, just to confirm that the trigger is indeed the issue? Or perhaps you could just create a separate data source over a file that does not have any triggers (such as DEMOCMAST) and see if that works okay? From what I see in your job log I suspect it will still fail, but let's see what happens...

Nitro Query attempts to establish a handle to the table/file through an SQL CONNECT type of approach before it executes the update. Perhaps there's a security setting on your system that's causing the API to fail when the current user of the CGI job does not match the job user (QTMHHTP1).

[ThierryC]

Hi Robert,

I can confirm that the trigger is indeed the cause... Update runs fine when temporarily disabling the trigger.

I wouldn't know what security setting there could be that causes this...

[robert.swanson]

Interesting. Okay, we'll see if we can replicate this on our end and get back to you.

[robert.swanson]

Hi Thierry,

We're having trouble replicating this one. Even with a trigger program specified, the update is going through without a problem. Is there anything special about your trigger program or the way it's set up on the file?