I am getting "row changed between retrieve and update" any time I try to datawindow update a table where a datetime is in the list of updateable columns. I tried setting datetimeformat in the ODBC database profile syntax tab but it still fails. Somehow the where clause value for a datetime is not matching the database value. I saw a previous post where an ORACLE fix was to use: ls_dateformat = "ALTER SESSION SET NLS_DATE_FORMAT = ~'dd/mm/yyyy HH24:MI:SS~'". I am using SQL server as indicated on screenshot below. Any ideas? Setting Disablebind=1 will fix the problem but I am concerned that will result in slower performance.
PB version:
- You are here:
-
Home
-
Q&A
-
Q&A
-
PowerBuilder
- Row changed between retrieve and update
- Paul Shue
- PowerBuilder
- Monday, 14 March 2022 14:36 PM UTC
- Friday, 9 June 2023 12:00 PM UTC
- PowerBuilder
- # 1
The following solved the problem:
Add
DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\'''
to sqlca.dbparm
- Monday, 14 March 2022 16:57 PM UTC
- PowerBuilder
- # 2
"Setting Disablebind=1 will fix the problem but I am concerned that will result in slower performance."
setting disablebind will also disable server side cursors. Server side cursors are supposed to make the database run faster which may be the case for repetitive sql.
However, server side cursors usually make PB applications much slower for SQL Server. At least this was the case many versions of sql server ago. There was a pretty good discussion of this in the old sybase forums. I forget the guys' name who figured this all out, and can't find the discussion which is too bad since there is gold in there.
IMO, Disablebind=1 is a good thing.
back to your actual problem: i tested my app with Disablebind=0 and do not have any problems with key&updatable columns where datetime columns have a value and are updateble.
are you sure your datatype is datetime and not datetime2 (i believe datetime2 does have this problem)
- Paul Shue
- Monday, 14 March 2022 17:06 PM UTC
-
Helpful Loading... Helpful 0
- Monday, 14 March 2022 15:46 PM UTC
- PowerBuilder
- # 3
I have not tried using the other drivers because I really dont want to have to introduce so much regression testing. I am hoping to find some kind of fix using the ODB ODBC connection. Does setting disablebind= 1 cause a performance problem? Why does the option on db profile syntax tab not work see screenshot below? Is there any other option you can think of?
- Paul Shue
- Monday, 14 March 2022 16:14 PM UTC
-
Helpful Loading... Helpful 0
- mike S
- Monday, 14 March 2022 20:03 PM UTC
-
Helpful Loading... Helpful 0
- Paul Shue
- Monday, 14 March 2022 20:37 PM UTC
-
Helpful Loading... Helpful 0
- Monday, 14 March 2022 14:48 PM UTC
- PowerBuilder
- # 4
Hi Paul;
Suggestion: I would perform an SQL Trace and see what is going on between the client App and the DBMS.
SQLCA.DBMS = "xxx" // Change to
SQLCA.DBMS = "TRACE xxx"
Regards ... Chris
- Paul Shue
- Monday, 14 March 2022 15:13 PM UTC
(08DAA440): UPDATE states SET statenm = ?, updateddttm = ?, updatedempseq = ?, cpuid = ? WHERE siteseq = ? AND statecd = ? AND statenm = ? AND active = ? AND countrycd = ? AND createdttm = ? AND createempseq = ? AND updateddttm = ? AND updatedempseq = ? AND cpuid = ? AND digital_signature_text IS NULL AND date_format IS NULL AND allow_digital_signatures IS NULL (0.038 MS / 646.912 MS)
(08DAA440): VCHAR Length=8 ID=:1 *Yikes_st*
(08DAA440): DATETIME Length=0 ID=:2
(08DAA440): LONG Length=0 ID=:3
(08DAA440): VCHAR Length=12 ID=:4 *w_state_list*
(08DAA440): LONG Length=0 ID=:5
(08DAA440): VCHAR Length=2 ID=:6 *YY*
(08DAA440): VCHAR Length=5 ID=:7 *Yikes*
(08DAA440): VCHAR Length=1 ID=:8 *Y*
(08DAA440): VCHAR Length=3 ID=:9 *USA*
(08DAA440): DATETIME Length=0 ID=:10
(08DAA440): LONG Length=0 ID=:11
(08DAA440): DATETIME Length=0 ID=:12
(08DAA440): LONG Length=0 ID=:13
(08DAA440): VCHAR Length=12 ID=:14 *w_state_list* (44.074 MS / 690.986 MS)
(08DAA440): EXECUTE: (4.730 MS / 695.716 MS)
(08DAA440): GET AFFECTED ROWS: (0.002 MS / 695.718 MS)
(08DAA440): ^ 0 Rows Affected
(08DAA440): *** ERROR -3 ***(rc -1) : Row changed between retrieve and update.
-
Helpful Loading... Helpful 0
- Chris Pollach @Appeon
- Monday, 14 March 2022 15:22 PM UTC
-
Helpful Loading... Helpful 0
- Miguel Leeuwe
- Friday, 9 June 2023 16:45 PM UTC
-
Helpful Loading... Helpful 0
- Page :
- 1
However, you are not allowed to reply to this question.