1. Glenn Barber
  2. PowerBuilder
  3. Wednesday, 11 March 2020 20:21 PM

We are updating a MS SQL Server Database using a datawindow with Modified Columns used as the Update Criteria.  We are always getting a failure "Row changed between retrieve and Update"  (PB 2017 R3)

A datetime column in the database seems to be the cause of the problem.  We automatically get a mismatch between the value retrieved and used in the modified value in the where clause.  This looks to be a thousands of a second value which is not being stored by the datawindow which causes the Update using that value to fail.

We need to test to see if another task has similarly modified this row, so use of the modified is required.

Is this a known problem and is there a getaround?

 

Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 12 March 2020 02:38 AM
  2. PowerBuilder
  3. # 1
0
Votes
Undo

No GetDate Function.  The Retreve is done by simple datawindow.

If we set the value in the datawindow using the expression prior to the update as below

datetime ldt_todaynow

ldt_todaynow =  DateTime(Today(),Now()

ids_datastorename.SetItem(ll_rowno, "calcstarted", ldt_todaynow)

and we inspect the value post update in the database using the SQL Workbench, we see thousandths of a second in the database that were not visible in the ldt_todaynow value that was used to update the database.

It appears that the datawindow value retrieved is not exactly the same as that in the database (missing the thousandths) and so the Update using the calstarted value in WHERE clause fails.

If we null the prior value in the database prior to the update, the update works.

Comment
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 23:28 PM
  2. PowerBuilder
  3. # 2
0
Votes
Undo

We are retrieving the row into the datawindow with a standard retrieve. 

 

The date had initially been set with 

ids_datastorename.SetItem(ll_rowno, "calcstarted", DateTime(Today(),Now())).

If the value is left null in the table when we do this update, we don't have a problem.

when we set the value in the database we see thousands of a second in the database.  But the GetItemDateTime on the field after the update does not show the thousands.

Comment
This is the divide-and-conquer part of debugging > Could you (A) set DisableBind = 1 -- (B) Grab SQL sent to database via SQLPreview of the DataWindow control. -- I'm very keen to know what is sent FROM PB towards the ODBC driver.

I don't recall losing fractions of seconds altogether when using data type = DateTime.
  1. Michael Kramer
  2. Thursday, 12 March 2020 02:49 AM
There was a problem reported like this with Oracle some time back - this app is newly on 2017 and we have found other unexpected changes as well . Unfortunately all this id done with datastores, so I may need to introduce some layers in the library to be able to trap SQL preview to see what is actual being submitted in the where clause.
  1. Glenn Barber
  2. Thursday, 12 March 2020 03:06 AM
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 22:26 PM
  2. PowerBuilder
  3. # 3
0
Votes
Undo

Look at the SQL and see if the GETDATE() function is used anywhere in this process thread. If so, replace it with a simple retrievel, or SET the date in the database and then retrieve that set date.


Olan

 

 

Comment
There are no comments made yet.
Glenn Barber Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 22:23 PM
  2. PowerBuilder
  3. # 4
0
Votes
Undo

Hi Michael 

The Database is SQL Server 2017  14.0.2027

date type is datetime

column name is calcstarted

primary key is identity as int

The Connection is SQLServer Native ODBC Version 12

I don't see any other connection parameters so must be default

I note that we don't remember seeing this with earlier versions of SQL Server.

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 20:52 PM
  2. PowerBuilder
  3. # 5
0
Votes
Undo

The "legacy datatype" = datetime in MSSQL should match PowerBuilder's and the DataWindow engine's datatype = datetime whereas datetime2 and datetimeoffset can have problems. Also, smalldatetime may behave unexpectedly since it only supports minutes so any seconds or fractions of seconds will be lost.

Couple of questions:

  • What is your MSSQL version?
  • What exact datatype is your datetime column in the database?
  • What is that column's name?
  • What columns (incl. datatype) form the primary key columns in the DataWindow?
  • How is database connection configures?
    • DBMS (PB's DB driver)
    • StaticBind -- default = 1 (checked)
    • DisableBind -- default = 1 (checked)
    • DateTimeFormat -- default = empty

 

/Michael

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.