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.

SQL DB Error

7 posts in DataWindow Last posting was on 2008-07-17 01:54:20.0Z
pyanko Posted on 2008-07-15 19:58:30.0Z
Sender: 3fe0.487cfc8b.846930886@sybase.com
From: pyanko
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: SQL DB Error
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <487d0165.40d3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Jul 2008 12:58:30 -0700
X-Trace: forums-1-dub 1216151910 10.22.241.41 (15 Jul 2008 12:58:30 -0700)
X-Original-Trace: 15 Jul 2008 12:58:30 -0700, 10.22.241.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87380
Article PK: 416636

In a PB 10.2 app, there is a datawindow that gets "Row
changed between retrieve and update" message on update (DB
code -3), although the update does take place. SQL Server
database, w/SQLOLEDB connection. There is a update trigger
on the table in the datawindow. When it is disabled, the
update occurs with no messages. I was wondering if there is
some parameter I can use for the SQLOLEDB connection?

Thanks


Bruce Armstrong [TeamSybase] Posted on 2008-07-16 02:48:09.0Z
From: "Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: SQL DB Error
Reply-To: NOCANSPAM_bruce.armstrong@teamsybase.com
Message-ID: <p5oq74h39iup7ms2b2rnc0e9f7l9lmhf4o@4ax.com>
References: <487d0165.40d3.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Jul 2008 19:48:09 -0700
X-Trace: forums-1-dub 1216176489 10.22.241.152 (15 Jul 2008 19:48:09 -0700)
X-Original-Trace: 15 Jul 2008 19:48:09 -0700, vip152.sybase.com
Lines: 30
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87383
Article PK: 416640

No, it's more of a design issue. Your update trigger is apparently
updating one of the columns that PB thinks it's allowed to update.
It's doing a "key and updateable" style update, which means it
includes all of the updateable columns and their original values (the
ones PB originally retrieved) in the where clause of the update
statement. The update trigger is causing the value that PB originally
saw to change, and PB thinks its another user doing it.

If you have an update trigger changing the column, then most likely
you don't want that column editable from the front end. In that case,
simply tell the DataWindow that column isn't updateable (in the update
specifications). PB then won't send it in the where clause of the
update statement, and you won't get the error anymore.

If you do want the column editable by the front end and by the update
trigger, then you have an even larger design issue.

On 15 Jul 2008 12:58:30 -0700, pyanko wrote:

>In a PB 10.2 app, there is a datawindow that gets "Row
>changed between retrieve and update" message on update (DB
>code -3), although the update does take place. SQL Server
>database, w/SQLOLEDB connection. There is a update trigger
>on the table in the datawindow. When it is disabled, the
>update occurs with no messages. I was wondering if there is
>some parameter I can use for the SQLOLEDB connection?
>
>Thanks


pyanko Posted on 2008-07-16 15:03:25.0Z
Sender: 3fe0.487cfc8b.846930886@sybase.com
From: pyanko
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: SQL DB Error
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <487e0dbd.6838.1681692777@sybase.com>
References: <p5oq74h39iup7ms2b2rnc0e9f7l9lmhf4o@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Jul 2008 08:03:25 -0700
X-Trace: forums-1-dub 1216220605 10.22.241.41 (16 Jul 2008 08:03:25 -0700)
X-Original-Trace: 16 Jul 2008 08:03:25 -0700, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87391
Article PK: 416647

Thanks for the info, but the datawindow in question is doing
a Key Columns (only) update and the trigger on the table in
the datawindow affects tables other than the table being
updated in the datawindow. As a side note the datawindow
update occurs without issue in the PB 6.5 version of the
application.

> No, it's more of a design issue. Your update trigger is
> apparently updating one of the columns that PB thinks it's
> allowed to update. It's doing a "key and updateable" style
> update, which means it includes all of the updateable
> columns and their original values (the ones PB originally
> retrieved) in the where clause of the update statement.
> The update trigger is causing the value that PB originally
> saw to change, and PB thinks its another user doing it.
>
> If you have an update trigger changing the column, then
> most likely you don't want that column editable from the
> front end. In that case, simply tell the DataWindow that
> column isn't updateable (in the update specifications).
> PB then won't send it in the where clause of the update
> statement, and you won't get the error anymore.
>
> If you do want the column editable by the front end and by
> the update trigger, then you have an even larger design
> issue.
>
> On 15 Jul 2008 12:58:30 -0700, pyanko wrote:
>
> >In a PB 10.2 app, there is a datawindow that gets "Row
> >changed between retrieve and update" message on update
> (DB >code -3), although the update does take place. SQL
> Server >database, w/SQLOLEDB connection. There is a
> update trigger >on the table in the datawindow. When it
> is disabled, the >update occurs with no messages. I was
> wondering if there is >some parameter I can use for the
> SQLOLEDB connection? >
> >Thanks
>


Jeremy Lakeman Posted on 2008-07-16 07:18:04.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: SQL DB Error
Date: Wed, 16 Jul 2008 00:18:04 -0700 (PDT)
Organization: http://groups.google.com
Lines: 18
Message-ID: <1f7f7eb1-1e9f-4692-bceb-5961fecd43f1@2g2000hsn.googlegroups.com>
References: <487d0165.40d3.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 1216192684 32642 127.0.0.1 (16 Jul 2008 07:18:04 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 16 Jul 2008 07:18:04 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 2g2000hsn.googlegroups.com; posting-host=203.122.242.105; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9) Gecko/2008052906 Firefox/3.0,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 fly.essential.com.au:3128 (squid/2.5.STABLE8), 1.0 asp.essential.intranet:3128 (squid/2.5.STABLE8)
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!2g2000hsn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87386
Article PK: 416642


On Jul 16, 4:58 am, pyanko wrote:
> In a PB 10.2 app, there is a datawindow that gets "Row
> changed between retrieve and update" message on update (DB
> code -3), although the update does take place. SQL Server
> database, w/SQLOLEDB connection.  There is a update trigger
> on the table in the datawindow.  When it is disabled, the
> update occurs with no messages.  I was wondering if there is
> some parameter I can use for the SQLOLEDB connection?
>
> Thanks

PB throws that error when it runs it's generated update statement and
the database reports that no rows were effected (eg sqlca.rowcount or
equivalent low level value is zero).

Run the update statement that PB generates in some other interactive
sql program and see if the database is reporting how many rows were
effected.


pyanko Posted on 2008-07-16 15:57:08.0Z
Sender: 3fe0.487cfc8b.846930886@sybase.com
From: pyanko
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: SQL DB Error
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <487e1a54.6a30.1681692777@sybase.com>
References: <1f7f7eb1-1e9f-4692-bceb-5961fecd43f1@2g2000hsn.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: 16 Jul 2008 08:57:08 -0700
X-Trace: forums-1-dub 1216223828 10.22.241.41 (16 Jul 2008 08:57:08 -0700)
X-Original-Trace: 16 Jul 2008 08:57:08 -0700, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87393
Article PK: 416649

Your response was the key. The way the trigger was written,
it reported 0 rows affected. I modified the trigger code so
that it reports >0 rows affected and the update occurs
without issue in the PB datawindow.

Thanks for responding.

> On Jul 16, 4:58 am, pyanko wrote:
> > In a PB 10.2 app, there is a datawindow that gets "Row
> > changed between retrieve and update" message on update
> > (DB code -3), although the update does take place. SQL
> > Server database, w/SQLOLEDB connection.  There is a
> > update trigger on the table in the datawindow.  When
it
> > is disabled, the update occurs with no messages.  I
was
> > wondering if there is some parameter I can use for the
> SQLOLEDB connection? >
> > Thanks
>
> PB throws that error when it runs it's generated update
> statement and the database reports that no rows were
> effected (eg sqlca.rowcount or equivalent low level value
> is zero).
>
> Run the update statement that PB generates in some other
> interactive sql program and see if the database is
> reporting how many rows were effected.


Scott Morris Posted on 2008-07-16 13:09:20.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <487d0165.40d3.1681692777@sybase.com>
Subject: Re: SQL DB Error
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487df300$1@forums-1-dub>
Date: 16 Jul 2008 06:09:20 -0700
X-Trace: forums-1-dub 1216213760 10.22.241.152 (16 Jul 2008 06:09:20 -0700)
X-Original-Trace: 16 Jul 2008 06:09:20 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87388
Article PK: 416644


<pyanko> wrote in message news:487d0165.40d3.1681692777@sybase.com...
> In a PB 10.2 app, there is a datawindow that gets "Row
> changed between retrieve and update" message on update (DB
> code -3), although the update does take place. SQL Server
> database, w/SQLOLEDB connection. There is a update trigger
> on the table in the datawindow. When it is disabled, the
> update occurs with no messages. I was wondering if there is
> some parameter I can use for the SQLOLEDB connection?
>
> Thanks

Apparently this continues to be a problem as it has been found in previous
versions of PB. Most likely, the same suggestions made back then still
apply - if you're really interested try searching the newsgroup archives.
Try setting nocount on (or off) in the trigger. You can also try executing
some trivial statement within the trigger (as the last statement executed)
that will force the rowcount global variable (withint the server) to be
non-zero.


M. Searer Posted on 2008-07-17 01:54:20.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <487d0165.40d3.1681692777@sybase.com>
In-Reply-To: <487d0165.40d3.1681692777@sybase.com>
Subject: Re: SQL DB Error
Lines: 17
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487ea64c$1@forums-1-dub>
Date: 16 Jul 2008 18:54:20 -0700
X-Trace: forums-1-dub 1216259660 10.22.241.152 (16 Jul 2008 18:54:20 -0700)
X-Original-Trace: 16 Jul 2008 18:54:20 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87404
Article PK: 416660

make sure all yours triggers have:

SET NOCOUNT ON
set at the top of the trigger
otherwise the database will report back that some rows were affected causing your problem.

"pyanko" wrote in message news:487d0165.40d3.1681692777@sybase.com...
> In a PB 10.2 app, there is a datawindow that gets "Row
> changed between retrieve and update" message on update (DB
> code -3), although the update does take place. SQL Server
> database, w/SQLOLEDB connection. There is a update trigger
> on the table in the datawindow. When it is disabled, the
> update occurs with no messages. I was wondering if there is
> some parameter I can use for the SQLOLEDB connection?
>
> Thanks