• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • MS SQL Server Row Changed Error on datetime column

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

Resolved MS SQL Server Row Changed Error on datetime column

  1. Issue
0
Votes
Undo
  1. Glenn Barber
    Glenn Barber
  2. PowerBuilder
  3. Wednesday, 11 March 2020 20:21 PM UTC

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?

 

  1. DataWindow
  2. SQL Server
  3. Update
Row changed between retrieve and update
Unable to set 'XML' template for Data Export in a ...
Responses (7)
Helge Nymoen
Helge Nymoen Accepted Answer Pending Moderation
  1. Saturday, 10 June 2023 08:35 AM UTC
  2. PowerBuilder
  3. # 1

The following solved the problem:
Add

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

to sqlca.dbparm

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Saturday, 10 June 2023 11:10 AM UTC
Thanks for sharing the solution and please mark as resolved.
  1. Helpful
There are no comments made yet.
FILIBERTO SOSA
FILIBERTO SOSA Accepted Answer Pending Moderation
  1. Tuesday, 20 October 2020 21:56 PM UTC
  2. PowerBuilder
  3. # 2

It is just a matter of setting of your DB connection,  try this

SQLCA.DBParm="DisableBind=1

 

Comment
There are no comments made yet.
Glenn Barber
Glenn Barber Accepted Answer Pending Moderation
  1. Thursday, 12 March 2020 02:38 AM UTC
  2. PowerBuilder
  3. # 3

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
Glenn Barber Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 23:28 PM UTC
  2. PowerBuilder
  3. # 4

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
Load more comments
Glenn Barber
  1. Glenn Barber
  2. Thursday, 12 March 2020 03:06 AM UTC
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. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 23 October 2020 02:27 AM UTC


Yes Glen is correct, I remember this happening to me like a year ago with Oracle (not sure if only when running powerserver, getting old). The way I solved it was by doing a "execute immediate :.." with an ALTER... for the date / datetime formats to match the precision of powerbuilder. Can't check right now for the exact statements as I'm on Linux and not connected to the office.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 23 October 2020 02:51 AM UTC
There you go:

string ls_dateformat

ls_dateformat = "ALTER SESSION SET NLS_DATE_FORMAT = ~'dd/mm/yyyy HH24:MI:SS~'"

EXECUTE IMMEDIATE :ls_dateformat using a_trans;



This is what we have to do for ORACLE (in the UK).
  1. Helpful
There are no comments made yet.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 22:26 PM UTC
  2. PowerBuilder
  3. # 5

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
Glenn Barber Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 22:23 PM UTC
  2. PowerBuilder
  3. # 6

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
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 11 March 2020 20:52 PM UTC
  2. PowerBuilder
  3. # 7

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.