RowChanged between Retrieve and Update
Shawn
development tool! As of today, I've lost a lot of respect
for the datawindow...maybe someone can help me regain that.
First things first, I'm using Powerbuilder 11.5.1 and MySQL
5.1 on Windows XP Pro SP2.
Basically, what I'm dealing with is this: we have our
datawindows set up to validate updates using key and
updateable columns. That ensures that we do not update a
row that updates over someone else's changes.
What is happening is this: let's assume we retrieve a row
of data and before we issue an update back to the database
using the data in our datawindow, someone else updates that
row. What do you think happens when the user in the
application requests an update? Well, you would think the
update would fail. Not only does it NOT fail, it returns an
update result of 1 (success). WHAT?!?!?!? In effect,
nothing is updated in the database because the where clause
finds no matching rows but the user THINKS something is
updated because the result returned is success.
I have no idea what the internals of Powerbuilder is doing
to allow a datamodified row that updates no rows to have a
result of success but is there a way to overcome this?
Does the datwindow use SQLNRows internally to determine if
the update was successful? How can it rely on SQLNRows when
Powerbuilder's own documentation states that SQLNRows is
database dependent and therefore not always available for
use. I know for a fact that it is not available with MySQL
so if Powerbuilder is using SQLNRows to determine updaterow
success internally then no wonder I'm experiencing this
problem.
What we have to do in some of our embedded SQL is to issue a
select count(*) with the desired where criteria which then
allows us to know if the row is available for update. Since
we couldn't rely on SQLNRows, this was our only option
rather than issuing an update and checking the count of the
rows returned. Is there anyway to standardize that this be
done in the datawindow rather than what I'm assuming it
does...which again, is use SQLNRows internally for its
update-rowcount processing?
If not, I will have to write ancestor code that will process
prior to update and check for the existence of each row
being updated. Is there a way to access the syntax of the
SQL update commands that the datawindow will issue? If I
can pull those out, then I can take everything from the
where clause on and prefix it with a select count(*) and
make sure my matching row exists for update.
I'm sorry, this is a nightmare. The datwindow is the pillar
of Powerbuilder. To have it behaving like this with a
market-leading database solution blows my mind.
Thanks in advance all for your help.
Shawn
Jeremy Lakeman
The ODBC spec is very loose. Using each DBMS can often require custom
code. Thankfully PB has abstracted away these details and allows you
to configure the required custom behaviour in a file called
pbodbcXXX.ini (where XXX is based on your version of PB) and they've
provided some defaults for some well known databases.
According to this page;
http://bugs.mysql.com/bug.php?id=20537
[7 Nov 2006 2:46] John McQuade
A setting in the PowerBuilder ODBC INI file (pbodbxx.ini) in the
Sybase Shared Directory
Add:
[MySQL]
PBSupportBindSelect='NO'
PBSupportBindUpdate='NO'
PBSupportDBBind='NO'
This resolves the issue.
Shawn
I appreciate your help but I'm confused. I read back
through the bug reports and the solution you provided was
for data retrieval problems and the like. My problem is not
a data retrieval problem. My data retrieves and updates
just fine in almost all instances. It's unfortunately also
updating in instances when it should NOT be. Here's what
happens because I've seen it elsewhere after converting to
MySQL, specifically in embedded SQL.
1. The datawindow retrieves a row of data requested by user
#1
2. User #1 makes changes
3. BEFORE user #1 can save those changes, user #2 makes
changes to that row in the database and saves the changes
back to the database
4. User #1 now tries to save his/her changes to the
database
5. Since key update option on the datawindow KEY AND
UPDATEABLE COLUMNS is selected, AN ERROR SHOULD OCCUR
however in my case no error is occurring even though no
update was successfully made to the database
Here's what really happens...
1. The update is issued by the datawindow issuing an update
to a row that no longer exists. It is valid SQL so the
update request is successful even though no matching rows
were found for update.
2. Value SQLNRows would typically be used to verify that a
row was updated HOWEVER SQLNRows does NOT return a value
within Powerbuilder when connected to MySQL (it's always
zero).
3. Since the update sytax was valid without errors and it
can't tell based on SQLNRows whether the update was
successful or not, it seems to be returning an update result
of success which then resets the datawindow update flags
I tried the settings you mentioned and it had no effect on
this outcome.
Shawn
The update command is issued against the database. No rows
are found
Shawn
CUSTOMER ID TABLE
--------------------------------
Customer ID (PK) Name
1 Michaell
2 William
3 Richard
User #1 retrieves customer ID 1 for Michaell since the name
should be Michael.
User #2 also retrieves customer ID 1
User #1 gets a phone call and is distracted
User #2 updates the record and saves it back to the database
so that the name is now Michael
User #1 finishes the phone call, changes the name and hits
update. The update should fail since KEY AND UPDATEABLE
COLUMNS is selected as the update type within the
datawindow. Instead, the update finds no rows to update
because the where clause IS "WHERE CUSTOMER_ID = 1 AND
NAME = 'MICHAELL'" however the user seemingly thinks the
update was successful because Powerbuilder treated it like a
successful update.
In this simple example no harm would have been done but
imagine if you thought you had successfully updated
financial information to the system only for this problem to
have occurred and nothing been saved.
Jeremy Lakeman
to the DB?
Chris Pollach
This could be simply because of your DW Update settings (menu
"Rows=>Update Properties" ...
1) If the DW is set to "Key Columns" then as long as the primary key is the
same - then the DWO will just overlay the data from the other update.
2) If the DW is set to "Key and Modified Columns" then if the same column is
updated you will be notified - otherwise the update will go through.
(my favourite setting BTW <g>)
3) If the DW is set to "Key and Updatable Columns" then no update will go
through if changes have been made by another DB thread.
HTH
--
Regards ... Chris
ISUG - NA RUG Director
http://chrispollach.pbdjmagazine.com
<Shawn> wrote in message news:4ab2a006.4ce...@sybase.com...
Shawn
YOU ARE RIGHT! I have Update Properties set to Key and
Updateable Columns. The update result that's being returned
is SUCCESS however since values have changed behind the
scenes, the row did NOT actually get updated. This should
be returning an error result not a success result. That's
why I'm asking if behind the scenes Powerbuilder uses the
equivalent of SQLNROWS to determine if any rows were in fact
updated.
Shawn
Chris Pollach
Using that setting, the DWO generates a where clause to Set a new column
value as long as the old columns data are the same. If MySQL is not
returning an error to the DWO on that where clause - you have DBMS issues -
not PB. I would suggest that you set an SQL TRACE option on in PB and see
what return codes are present to the DB driver.
Of course, you could always use a real DBMS instead <bg>
<Shawn> wrote in message news:4ab51dd4.23d...@sybase.com...
Peter Piechutzki
same problem here using MySql Server 5.0.51b-community-nt and ODBC Client
5.01.05.00.
SQLCA.DBParm = "ConnectString='DSN=32bit
MySql;UID=user;PWD=*****;server=172.nnn.nnn.nnn;db=testdb;option=3280904',OJSyntax='ANSI'"
In our PBDOB115.ini:
[MySQL]
PBSyntax='MySQL_SYNTAX'
[MySQL_SYNTAX]
GetIdentity='Select @@identity'
In the same scenario you mentioned in this thread. Update() returns 1
meaning success althogh phisical update actually fails. We
too are missing "Row changed between retrieve and update" error Message.
Hi Chris,
what do you mean with "a real DBMS". I just wonder why international
companies with huge amounts of data like insurances and aircrafts are
using mysql for their data storage in a "fake DBMS". Before making
such statements you should check how mysql outperforms MS SQL, Unify
SQLBase, Sybase and Oracle by far! It's about time Sybase improves
interaction
with THE leading open source DBMS. My assumption to the ignorance on MySQL
support is that Sybase is just scared to loose customers for their ASE
product...
Does anyone know where the Syntax for PBODBnnn.INI is documented? I've been
looking for such a documentation for years now.
And now back to the original Question from Shawn: How does PB get the Return
Value for the update() method ? Is it using SQLNRows?
regards Peter
Chris Pollach
Locking and row changed notification varies by DBMS vendor. Also, the
various DB drivers can make or break you as well. With ODBC you have to
remember that PB's driver is only an interface to the real ODBC driver. So
this problem may be in the ODBC layer - not, in the PB ODBC interface.
Saying that, PB's interface is dependant on the ODBC.ini file and its
settings.
I was just teasing you on the "real DBMS". :-)
"Peter Piechutzki" <ppiec...@chriwa.de> wrote in message
news:4aba2027@forums-1-dub...
Paul Horan[Sybase]
-
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/
"Peter Piechutzki" <ppiec...@chriwa.de> wrote in message
news:4aba2027@forums-1-dub...
> Hi Shawn,
>
> same problem here using MySql Server 5.0.51b-community-nt and ODBC Client
> 5.01.05.00.
>
> SQLCA.DBParm = "ConnectString='DSN=32bit
> MySql;UID=user;PWD=*****;server=172.nnn.nnn.nnn;db=testdb;option=3280904',OJSyntax='ANSI'"
>
> In our PBDOB115.ini:
> [MySQL]
> PBSyntax='MySQL_SYNTAX'
>
> [MySQL_SYNTAX]
> GetIdentity='Select @@identity'
This syntax controls retrieving the autoincrement or identity value
following an INSERT statement. It has nothing to do with the return code
from an UPDATE statement.
>
> In the same scenario you mentioned in this thread. Update() returns 1
> meaning success althogh phisical update actually fails. We
> too are missing "Row changed between retrieve and update" error Message.
I think this might be a flaw in the MySQL ODBC driver. Have you done an
ODBC Trace to see what's being sent back through the driver?
>
> Hi Chris,
> what do you mean with "a real DBMS". I just wonder why international
> companies with huge amounts of data like insurances and aircrafts are
> using mysql for their data storage in a "fake DBMS". Before making
> such statements you should check how mysql outperforms MS SQL, Unify
> SQLBase, Sybase and Oracle by far! It's about time Sybase improves
> interaction
> with THE leading open source DBMS. My assumption to the ignorance on MySQL
> support is that Sybase is just scared to loose customers for their ASE
> product...
Wrong assumption. Sybase ASE doesn't consider MySQL a competitor.
SQLAnywhere might, but not ASE.
http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-faq.html
>
> Does anyone know where the Syntax for PBODBnnn.INI is documented? I've
> been looking for such a documentation for years now.
It's not... I'll ask Product Management if they're comfortable releasing
any internal doco on this file.
>
> And now back to the original Question from Shawn: How does PB get the
> Return Value for the update() method ? Is it using SQLNRows?
It gets it from the ODBC driver. Issuing an Update statement that updates 0
rows is not, in and of itself, an error in the eyes of the DBMS. The
SQLDBCode should be "100", meaning "no rows satisfied the search criteria".
PB should say to itself "hmmm, I sent an update statement for a row in the
primary buffer, but the SQLDBCode I got back was 100...". And that should
trigger the "Row changed between retrieve and update". If the MySQL ODBC
driver is not providing the SQLDBCode of 100 in that case, then there's your
answer.
-P-
>
>
> regards Peter
>
>
>
Peter Piechutzki
many lols here on the great link to
http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-faq.html, liked
that one.
And yes - i't has drawbacks to other databases - but it's free ;-)
A documentation on the pbodb.ini woul'd surely also interest others using a
database not directly implemented by sybase and maybe even allow the PB
community to implement DB support to an even larger amount of DBMS Systems.
And also thx for the great answer, where to look for the cause of our
problem. I'll do a odbc trace and check with mysql if this is intentioned
"feature" or a "bug" in the mysql driver we're using and get back to this
thread on that issue.
regards
Peter
"Paul Horan[Sybase]" <phoran_remove@remove_sybase.com> schrieb im
Newsbeitrag news:4aba750f$1@forums-1-dub...
Peter Piechutzki
been changed by another user in the same scenario.
created a new database in access. created a link to our above mentioned test
table in access. retrieved in access, retrieved same data in pb ide. changed
and saved in pb. tried to change in access, acces says row has been changed.
so correct behavior.
other way around: retrieved data in access and same in pb, changed in
access, changed in pb. pb change takes place, no error is generated, access
change is overwritten. This is when update properties in pb are set to Where
Clause "Key Columns"
If set to "Key and Updatable" or "Key and Modified" pb does not fire any
error message with update, the change of data doesn't take place. so in the
datawindow we see different data as on the physical database until we again
retrieve the data. We do not know the PB update actually failed.
PB115 eb4-564 ENTER SQLExecute
HSTMT 01B71BA0
PB115 eb4-564 EXIT SQLExecute with return code 0 (SQL_SUCCESS)
HSTMT 01B71BA0
PB115 eb4-564 ENTER SQLRowCount
HSTMT 01B71BA0
SQLLEN * 0x0012C038
PB115 eb4-564 EXIT SQLRowCount with return code 0 (SQL_SUCCESS)
HSTMT 01B71BA0
SQLLEN * 0x0012C038 (0)
So is PB using the Return Code of SQLExecute or Value of SQLRowCount?
Is there some way i can trace and see the actually returned values for
SQLRowCount?
Is there some way i can manipulate where pb get's the expected response in
the PBODB.INI file similar to GetIdentity='Select @@identity' needed for
mysql?
I know there are some issues with the row_count() function in mysql and
would like to find a work around to this serious problem for all mysql
users.
Btw there is no difference if setting flag "Return matched rows instead of
affected rows" in Mysql odbc options.
thanks to everyone in this thread for all your help
regards peter
"Paul Horan[Sybase]" <phoran_remove@remove_sybase.com> schrieb im
Newsbeitrag news:4aba750f$1@forums-1-dub...