PowerBuilder and SQL Sever ‘row changed between retrieve and update’ error.

One issue I was working on was triggering ‘row changed between retrieve and update‘ error on DataWindowupdate. Code was kinda simple, just retrieve some values in DataWindow, move some values to Deleted buffer and then call update. No other data manipulations etc. Found that table had some database trigger that was fired on delete. Disabled the trigger and there error message was gone. So trigger was causing that. Trigger also was quite simple, just cursor going through deleted rows and selecting and updating few values in other tables.

Googling told me that in Microsoft SQL server, if a table has an insert, update, or delete trigger, the number of affected rows returned to the SQLNRows property of the Transaction object after an INSERT, UPDATE, or DELETE command depends on the driver. With an ADO.NET driver, the value returned is the sum of the rows affected by the command itself and the trigger. When you are connected to Microsoft SQL Server using ADO.NET or OLE DB, you can set the RecheckRows run time database parameter (introduced in PowerBuilder 10.5) to 1 to recheck how many rows of data were affected by the INSERT, UPDATE, or DELETE command itself and return that value in the SQLNRows property. Setting RecheckRows to 1 before issuing an INSERT, UPDATE, or DELETE command causes a SELECT @@ROWCOUNT command to be executed. To improve performance, you should set it only when required, and reset it to the default value of 0 after use. Example: SQLCA.DBParm=”RecheckRows=1″.

Another solution could be setting SET NOCOUNT { ON | OFF } in trigger itself. SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned. The @@ROWCOUNT function is updated even when SET NOCOUNT is ON. SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

As I had to make fix in PowerBuilder application, adding QLCA.DBParm=”RecheckRows=1″ before and QLCA.DBParm=”RecheckRows=0″ after Update call solved this problem.

PowerBuilder – Find windows where datawindow is used

If You have a listo of datawindows and would like to make a list of windows where each of these datawindows are used, this Power-Shell function might help.

By tweaking it You could search for whatewher You need, but basic concept is below.

[Console]::OutputEncoding = [System.Text.Encoding]::UTF8

$windowTitles = "windowsTitles.txt"

New-Item $windowTitles -type file -force

function search ([string]$fullName) {

$shortName =  [io.path]::GetFileNameWithoutExtension($fullName)

$files = gci -include *.srw,*.sru,*.srd -recurse | select-string $shortName | group path | select name

$found = 0

if ($files.Count -eq 0) { return 0 }

foreach ($file in $files)

{

$shortName2 =  [io.path]::GetFileNameWithoutExtension($file.Name) if ($shortName -eq $shortName2) { continue }

$found = $found + 1

Write-Host "Used in: " $shortName2

If ($file.Name.EndsWith(".srw")) {

Write-Host "Used in: " $shortName2 " and its a WINDOW!"

$titleString =  Get-Content $file.Name | select-string -pattern "string title = "

if (-not $titleString) {

Write-Host "Window has no title"

Return 1

}

Write-Host "Windows title is:" $titleString

$title = $titleString.line.replace("string title = ","")

# write title to file

Add-Content -Encoding UTF8 $windowTitles $title

Return 1

} Else {

Return search($file.Name)

}

}

Return $found

}
Use it something like this ( is same PS1 file for example ) :


$files = gci -include *.srd -recurse | Where-Object {$_.FullName -like "*\$folder\*"} | select-string '!!!!ADD YOUR REGEX HERE!!!' | group path | select name
Write-Host "Found number of datawindows:"
Write-Host $files.Count
$index = 0
foreach ($file in $files)
{
if (-Not $file.Name) {break}
$dwShortName =  [io.path]::GetFileNameWithoutExtension($file.Name)
Write-Host "Searching for: " $dwShortName
$found = search($file.Name)
}

PowerBuilder 8 OLE insert crash

I had same work with an old PowerBuilder 8 application maintenance and upgrade. While trying to add OLE control to a windows PB constantly crashed. PB and Windows XP reinstall didn’t helped. I found a hint here http://brucearmstrong.wordpress.com/2007/07/10/problems-using-activex-controls/

In short:
1. Download Process Monitor
2. Add filter to pb80.exe process and path that begins with HKCR\CLSID
3. Try to add OLE with PB (make it crash)
4. Find last entry that ends with Control, search this CLSID with regedit
5. Reregister dll You found.
In my case it was regsvr32 %systemroot%\system32\mstscax.dll
Problem solved.