Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved Row changed between retrieve and update

  1. How-to
0
Votes
Undo
  1. Paul Shue
    Paul Shue
  2. PowerBuilder
  3. Monday, 14 March 2022 14:36 PM UTC

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:

Charts used with Powerbuilder datawindow ending up...
MS SQL Server Row Changed Error on datetime column
Responses (4)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 14 March 2022 14:48 PM UTC
  2. PowerBuilder
  3. # 1

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

 

Comment
Paul Shue
  1. Paul Shue
  2. 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.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. 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?

  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. 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 ...
  1. Helpful
There are no comments made yet.
Paul Shue
Paul Shue Accepted Answer Pending Moderation
  1. Monday, 14 March 2022 15:46 PM UTC
  2. PowerBuilder
  3. # 2

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
Load more comments
Paul Shue
  1. Paul Shue
  2. 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?
  1. Helpful
mike S
  1. mike S
  2. Monday, 14 March 2022 20:03 PM UTC
post the connection settings you are using, and the odbc driver information
  1. Helpful
Paul Shue
  1. Paul Shue
  2. 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 + "'"
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Monday, 14 March 2022 16:57 PM UTC
  2. PowerBuilder
  3. # 3

"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)

 

 

 

Comment
Paul Shue
  1. Paul Shue
  2. Monday, 14 March 2022 17:06 PM UTC
I am sure they are datetime datatype.

  1. Helpful
There are no comments made yet.
Helge Nymoen
Helge Nymoen Accepted Answer Pending Moderation
  1. Friday, 9 June 2023 12:00 PM UTC
  2. PowerBuilder
  3. # 4

The following solved the problem:
Add

DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\'''

to sqlca.dbparm

Comment
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.