Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

RowChanged between Retrieve and Update

13 posts in DataWindow Last posting was on 2009-09-29 14:18:16.0Z
Shawn Posted on 2009-09-17 20:45:58.0Z
Sender: 4c0f.4ab296f5.1804289383@sybase.com
From: Shawn
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: RowChanged between Retrieve and Update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab2a006.4cee.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Sep 2009 13:45:58 -0700
X-Trace: forums-1-dub 1253220358 10.22.241.41 (17 Sep 2009 13:45:58 -0700)
X-Original-Trace: 17 Sep 2009 13:45:58 -0700, 10.22.241.41
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89960
Article PK: 419153

I'm shocked that I'm seeing this behavior from an Enterprise
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 Posted on 2009-09-18 00:33:04.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: RowChanged between Retrieve and Update
Date: Thu, 17 Sep 2009 17:33:04 -0700 (PDT)
Organization: http://groups.google.com
Lines: 85
Message-ID: <b394e92b-f083-476e-9b98-528c0e18b332@d34g2000vbm.googlegroups.com>
References: <4ab2a006.4cee.1681692777@sybase.com>
NNTP-Posting-Host: 203.122.242.105
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1253233984 2721 127.0.0.1 (18 Sep 2009 00:33:04 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 18 Sep 2009 00:33:04 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: d34g2000vbm.googlegroups.com; posting-host=203.122.242.105; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-Via: 1.1 fly.essential.com.au:3128 (squid/2.5.STABLE8), 1.0 asp.essential.intranet:3128 (squid/2.5.STABLE8)
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!d34g2000vbm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89961
Article PK: 419145


On Sep 18, 5:45 am, Shawn wrote:
> I'm shocked that I'm seeing this behavior from an Enterprise
> 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

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 Posted on 2009-09-18 01:18:04.0Z
Sender: 3271.4aae5048.1804289383@sybase.com
From: Shawn
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: RowChanged between Retrieve and Update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab2dfcc.5619.1681692777@sybase.com>
References: <b394e92b-f083-476e-9b98-528c0e18b332@d34g2000vbm.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Sep 2009 18:18:04 -0700
X-Trace: forums-1-dub 1253236684 10.22.241.41 (17 Sep 2009 18:18:04 -0700)
X-Original-Trace: 17 Sep 2009 18:18:04 -0700, 10.22.241.41
Lines: 137
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89962
Article PK: 419148

Jeremy,

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

> On Sep 18, 5:45 am, Shawn wrote:
> > I'm shocked that I'm seeing this behavior from an
> > Enterprise 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
>
> 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 Posted on 2009-09-18 01:50:12.0Z
Sender: 3271.4aae5048.1804289383@sybase.com
From: Shawn
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: RowChanged between Retrieve and Update
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ab2e754.56c7.1681692777@sybase.com>
References: <4ab2dfcc.5619.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Sep 2009 18:50:12 -0700
X-Trace: forums-1-dub 1253238612 10.22.241.41 (17 Sep 2009 18:50:12 -0700)
X-Original-Trace: 17 Sep 2009 18:50:12 -0700, 10.22.241.41
Lines: 184
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89963
Article PK: 419149

A simple customer ID example:

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,
>
> 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
>
> > On Sep 18, 5:45 am, Shawn wrote:
> > > I'm shocked that I'm seeing this behavior from an
> > > Enterprise 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
> >
> > 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.
> >


Jeremy Lakeman Posted on 2009-09-18 05:08:56.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: RowChanged between Retrieve and Update
Date: Thu, 17 Sep 2009 22:08:56 -0700 (PDT)
Organization: http://groups.google.com
Lines: 189
Message-ID: <b260c1f8-cb3b-4146-8a54-b14ec3bf8e6b@b18g2000vbl.googlegroups.com>
References: <4ab2dfcc.5619.1681692777@sybase.com> <4ab2e754.56c7.1681692777@sybase.com>
NNTP-Posting-Host: 203.122.242.105
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1253250536 32507 127.0.0.1 (18 Sep 2009 05:08:56 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 18 Sep 2009 05:08:56 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: b18g2000vbl.googlegroups.com; posting-host=203.122.242.105; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-Via: 1.1 fly.essential.com.au:3128 (squid/2.5.STABLE8), 1.0 asp.essential.intranet:3128 (squid/2.5.STABLE8)
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!b18g2000vbl.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89964
Article PK: 419152

Can you paste the database transaction properties you use to connect
to the DB?

On Sep 18, 10:50 am, Shawn wrote:
> A simple customer ID example:
>
> 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,
>
> > 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
>
> > > On Sep 18, 5:45 am, Shawn wrote:
> > > > I'm shocked that I'm seeing this behavior from an
> > > > Enterprise 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
>
> > > 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.


Chris Pollach Posted on 2009-09-18 20:02:04.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4ab2a006.4cee.1681692777@sybase.com>
Subject: Re: RowChanged between Retrieve and Update
Lines: 84
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ab3e73c@forums-1-dub>
Date: 18 Sep 2009 13:02:04 -0700
X-Trace: forums-1-dub 1253304124 10.22.241.152 (18 Sep 2009 13:02:04 -0700)
X-Original-Trace: 18 Sep 2009 13:02:04 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89967
Article PK: 419157

Hi Shawn;

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.4cee.1681692777@sybase.com...
> I'm shocked that I'm seeing this behavior from an Enterprise
> 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


Peter Piechutzki Posted on 2009-09-23 13:18:31.0Z
From: "Peter Piechutzki" <ppiechutzki@chriwa.de>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4ab3e73c@forums-1-dub> <4ab51dd4.23d6.1681692777@sybase.com> <4ab76f9a$1@forums-1-dub>
Subject: Re: RowChanged between Retrieve and Update
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
X-EsetScannerBuild: 5701
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aba2027@forums-1-dub>
Date: 23 Sep 2009 06:18:31 -0700
X-Trace: forums-1-dub 1253711911 10.22.241.152 (23 Sep 2009 06:18:31 -0700)
X-Original-Trace: 23 Sep 2009 06:18:31 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89985
Article PK: 419174

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'

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 Posted on 2009-09-23 18:41:43.0Z
From: "Chris Pollach" <cpollach@travel-net.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4ab3e73c@forums-1-dub> <4ab51dd4.23d6.1681692777@sybase.com> <4ab76f9a$1@forums-1-dub> <4aba2027@forums-1-dub>
Subject: Re: RowChanged between Retrieve and Update
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aba6be7$1@forums-1-dub>
Date: 23 Sep 2009 11:41:43 -0700
X-Trace: forums-1-dub 1253731303 10.22.241.152 (23 Sep 2009 11:41:43 -0700)
X-Original-Trace: 23 Sep 2009 11:41:43 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89990
Article PK: 419178

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" <ppiechutzki@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'
>
> 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
>
>
>


"Paul Horan[Sybase]" <phoran_remove Posted on 2009-09-23 19:20:47.0Z
From: "Paul Horan[Sybase]" <phoran_remove@remove_sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4ab3e73c@forums-1-dub> <4ab51dd4.23d6.1681692777@sybase.com> <4ab76f9a$1@forums-1-dub> <4aba2027@forums-1-dub>
Subject: Re: RowChanged between Retrieve and Update
Lines: 84
Organization: Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aba750f$1@forums-1-dub>
Date: 23 Sep 2009 12:20:47 -0700
X-Trace: forums-1-dub 1253733647 10.22.241.152 (23 Sep 2009 12:20:47 -0700)
X-Original-Trace: 23 Sep 2009 12:20:47 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:89991
Article PK: 419179

in line:

-
Paul Horan[Sybase]
http://blogs.sybase.com/phoran/

"Peter Piechutzki" <ppiechutzki@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 Posted on 2009-09-29 09:26:03.0Z
From: "Peter Piechutzki" <ppiechutzki@chriwa.de>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4ab3e73c@forums-1-dub> <4ab51dd4.23d6.1681692777@sybase.com> <4ab76f9a$1@forums-1-dub> <4aba2027@forums-1-dub> <4aba750f$1@forums-1-dub>
Subject: Re: RowChanged between Retrieve and Update
Lines: 109
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-EsetScannerBuild: 5739
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac1d2ab$1@forums-1-dub>
Date: 29 Sep 2009 02:26:03 -0700
X-Trace: forums-1-dub 1254216363 10.22.241.152 (29 Sep 2009 02:26:03 -0700)
X-Original-Trace: 29 Sep 2009 02:26:03 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90010
Article PK: 419197

Hi Paul,

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

> in line:
>
> -
> Paul Horan[Sybase]
> http://blogs.sybase.com/phoran/
>
> "Peter Piechutzki" <ppiechutzki@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 Posted on 2009-09-29 14:18:16.0Z
From: "Peter Piechutzki" <ppiechutzki@chriwa.de>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4ab3e73c@forums-1-dub> <4ab51dd4.23d6.1681692777@sybase.com> <4ab76f9a$1@forums-1-dub> <4aba2027@forums-1-dub> <4aba750f$1@forums-1-dub>
Subject: Re: RowChanged between Retrieve and Update
Lines: 146
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-EsetScannerBuild: 5741
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ac21728$1@forums-1-dub>
Date: 29 Sep 2009 07:18:16 -0700
X-Trace: forums-1-dub 1254233896 10.22.241.152 (29 Sep 2009 07:18:16 -0700)
X-Original-Trace: 29 Sep 2009 07:18:16 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90014
Article PK: 419200

my question now is, why does ms-access notify me correctly, that the row has
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...

> in line:
>
> -
> Paul Horan[Sybase]
> http://blogs.sybase.com/phoran/
>
> "Peter Piechutzki" <ppiechutzki@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
>>
>>
>>
>
>