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:
-
Paul Shue
- PowerBuilder
- Monday, 14 March 2022 14:36 PM UTC
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?
Comment
- Paul Shue
- Monday, 14 March 2022 16:14 PM UTC
I just tried and it failed. It is a catch22 situation - if disablebind=0 then update fails but debug (sqlpreview event) shows updateddttm = ?). With disablebind=1 then the update works and debug shows updateddttm = '2022-03-14 13:29:38.805'. Note there is a period rather than a colon preceding milliseconds. I tried both a period and colon and they both fail. What to do?
- mike S
- Monday, 14 March 2022 20:03 PM UTC
post the connection settings you are using, and the odbc driver information
- Paul Shue
- Monday, 14 March 2022 20:37 PM UTC
Using ODB ODBC database profile; Using ODBC Driver 17 for Sql Server; SQLCA.DBParm = "ConnectString='DSN=" + ls_db_connect + ";UID=" + ls_userid + ";PWD=" + ls_pwd + "'"
There are no comments made yet.
Comment
- Paul Shue
- Monday, 14 March 2022 15:13 PM UTC
I tried the trace and here is what I see. The problem is that the actual values for the columns do not display unless I set disablebind= 1. If I do that the update does not fail. I did that in debug and the date value in where clause = '2022-03-08 14:47:13.207' - so what should I put on my db profile syntax tab for datetimeformat to match that?
(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.
(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.
- Chris Pollach @Appeon
- Monday, 14 March 2022 15:22 PM UTC
Thanks Paul ... yes, I was just just going to ask you to set disablebind = 1 so that we could see the actual values. However, I see that you have tried that and when that DBParm value is set it WORKS! I wonder if this situation then is being encountered within the ODBC layer itself? Have you tried using the either the SNC or the newer MSO "native" DB drivers instead of ODBC over to SS?
- Miguel Leeuwe
- Friday, 9 June 2023 16:45 PM UTC
Using Oracle native driver I've also noticed funny sporadic error messages which are resolved by setting disablebind to 1. It shouldn't be that way, but ...
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.
However, you are not allowed to reply to this question.