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.

Time field formating

5 posts in General Discussion Last posting was on 2007-10-19 19:52:43.0Z
Mike Posted on 2007-10-18 20:11:58.0Z
Sender: 14f5.4717bc98.1804289383@sybase.com
From: Mike
Newsgroups: ianywhere.public.general
Subject: Time field formating
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4717be0e.1531.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Oct 2007 13:11:58 -0700
X-Trace: forums-1-dub 1192738318 10.22.241.41 (18 Oct 2007 13:11:58 -0700)
X-Original-Trace: 18 Oct 2007 13:11:58 -0700, 10.22.241.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6434
Article PK: 2895

We have data coming from our mainframe system with time
formatted as hh.mm.ss (11.45.10). We are running into the
problem that once data is in this Time column it cannot be
updated. It errors off saying that the row has changed
since it was last read.

My question is this - Does a sybase column defined as Time
get upset when Time is formated as hh.mm.ss? All adds
execute just fine with the exception of odd millisecond data
being present. We are only sending hours, minutes and
seconds.

Is there any thing inside sybase that would allow an add to
execute correctly but not the update?

Thanks.


Breck Carter [Team iAnywhere] Posted on 2007-10-19 11:51:33.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Time field formating
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <fh5hh35455kcimmdl599pj6qm8kqpne9uv@4ax.com>
References: <4717be0e.1531.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 19 Oct 2007 04:51:33 -0700
X-Trace: forums-1-dub 1192794693 64.7.134.118 (19 Oct 2007 04:51:33 -0700)
X-Original-Trace: 19 Oct 2007 04:51:33 -0700, bcarter.sentex.ca
Lines: 52
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6437
Article PK: 2891


On 18 Oct 2007 13:11:58 -0700, Mike wrote:

>We have data coming from our mainframe system with time
>formatted as hh.mm.ss (11.45.10). We are running into the
>problem that once data is in this Time column it cannot be
>updated. It errors off saying that the row has changed
>since it was last read.

Please show us the exact error message.

That kind of message is usually produced by a client application
(e.g., PowerBuilder) rather than the database server, when it checks
to see if someone *else* has updated the row between the point
(earlier) when you retrieved it and the point (now) when you are
trying to save your changes. You can cause this to happen on a single
connection by doing this: dw.Retrieve(), change a row and dw.Update(),
change the same row and *try* to dw.Update() again. The solutions (in
PowerBuilder, anyway) are to re-retrieve the DataWindow after the
first Update(), or to change the way the DataWindow generates the
WHERE clause on the SQL UPDATE statements it generates.

>My question is this - Does a sybase column defined as Time
>get upset when Time is formated as hh.mm.ss? All adds
>execute just fine with the exception of odd millisecond data
>being present. We are only sending hours, minutes and
>seconds.

The internal formatting of the TIME datatype is fixed. The *input
format* can vary, when converting from an input string value to the
TIME datatype.

However, a string value like '10.11.12' is not valid as a time. At
best it will be treated as a timestamp value with a zero time
component, OR it will raise an error (e.g., 10.15.12) because some
part of the date portion is invalid.

Please tell us exactly how the hh.mm.ss values are being processed.

Breck

>
>Is there any thing inside sybase that would allow an add to
>execute correctly but not the update?
>
>Thanks.

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Mike Posted on 2007-10-19 13:33:05.0Z
Sender: 28d7.4718ab25.1804289383@sybase.com
From: Mike
Newsgroups: ianywhere.public.general
Subject: Re: Time field formating
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4718b211.2978.1681692777@sybase.com>
References: <fh5hh35455kcimmdl599pj6qm8kqpne9uv@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Oct 2007 06:33:05 -0700
X-Trace: forums-1-dub 1192800785 10.22.241.41 (19 Oct 2007 06:33:05 -0700)
X-Original-Trace: 19 Oct 2007 06:33:05 -0700, 10.22.241.41
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6446
Article PK: 2896

> Please show us the exact error message.

Error message is this:
"Row cannot be located for updating. Some values may have
been changed since it was last read."

In this method that updates the data, we open a recordset
(yes crappy VB6 stuff), modify any updated values, update
the recordset and then finally close the recordset. All of
this happens in this one method with no other calls to other
methods prior to the Update being called and closing the
recordset.

This same method is used for ALL of the updates that are
processed by this application all but these updates are
handled successfully. If I modify the same transaction that
error'd of so that the time is not updated, it executes
correctly as well.

All databases are on a disconnected client so nothing
outside of that particular tablet pc is using the database.

> The internal formatting of the TIME datatype is fixed. The
> *input format* can vary, when converting from an input
> string value to the TIME datatype.
>
> However, a string value like '10.11.12' is not valid as a
> time. At best it will be treated as a timestamp value with
> a zero time component, OR it will raise an error (e.g.,
> 10.15.12) because some part of the date portion is
> invalid.
>
> Please tell us exactly how the hh.mm.ss values are being
> processed.

DB2 stores a time value as '10.11.12' and the mainframe here
sends our tablet pc's this data in a delimited txt file with
no changes to the time format. So on the tablet we are
dumping this string value '10.11.12' directly into the
recordset field value. This format has been in place since
the dawn of time here. Prior to Sybase we were using
Microsoft Access on our tablets and never had this problem.
But we have found that Microsoft lets you get away with a
lot of things that it shouldn't have.

We have transactions from other systems that format the time
as '10:11:12' and I have found no problems when updating
these.

The other question that the mainframe people are asking me
is "Why are adds working and updates are not?". Is there
something inside Sybase that handles this time data
differently during an Add versus an Update?

When I look at the database after the Add, the time field
shows the following: 11:33:53.3050000
This would be the result of setting the recordset value as
'11.33.53'. But if I view one that was sent down as
'11:33:53' I see the following: 11:33:53

Thank you for your help!!


Breck Carter [Team iAnywhere] Posted on 2007-10-19 18:59:32.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Time field formating
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <ciuhh35csa6c9pm20c516bt5cvms5tghev@4ax.com>
References: <fh5hh35455kcimmdl599pj6qm8kqpne9uv@4ax.com> <4718b211.2978.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 19 Oct 2007 11:59:32 -0700
X-Trace: forums-1-dub 1192820372 64.7.134.118 (19 Oct 2007 11:59:32 -0700)
X-Original-Trace: 19 Oct 2007 11:59:32 -0700, bcarter.sentex.ca
Lines: 125
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6449
Article PK: 4760


>When I look at the database after the Add, the time field
>shows the following: 11:33:53.3050000
>This would be the result of setting the recordset value as
>'11.33.53'. But if I view one that was sent down as
>'11:33:53' I see the following: 11:33:53

SQL Anywhere doesn't treat TIME values differently on insert versus
update. I am guessing your environment is passing more digits when it
actually inserts the column, than it uses in the WHERE clause to make
sure the old value has not changed since the initial select.

The misleading error message you see is most likely coming from code
on the client application side. From your point of view, it might be
code buried down deep on the database interface logic, so *you* might
think of it as "database code", but trust me, from the database
server's point of view, it's on the client side.

The error message may be coming from logic that works like this
pseudocode:

UPDATE target-table
SET data-column = 'new-data-value'
WHERE primary-key = 'primary-key-value'
AND data-column = 'old-data-value';

IF no rows were updated
THEN the predicate "data-column = 'old-data-value'" probably failed
and that probably happened because someone else changed the current
value of data-column to be different from the 'old-data-value' that
you previously retrieved... in other words, "this row changed between
retrieve and update".

...that's not true of course, but that's what it thinks :)

So here's my guess: something is truncating the TIME value used in the
WHERE, but not in the INSERT. For example...

INSERT target-table ( ... data-column, ... )
VALUES ( ..., '11:33:53.305', ... );

...then later on...

UPDATE target-table
SET data-column = 'new-data-value'
WHERE primary-key = 'primary-key-value'
AND data-column = '11:33:53';

which will give "no rows updated" and hence cause the misleading
message.

Breck




On 19 Oct 2007 06:33:05 -0700, Mike wrote:

>> Please show us the exact error message.
>
>Error message is this:
>"Row cannot be located for updating. Some values may have
>been changed since it was last read."
>
>In this method that updates the data, we open a recordset
>(yes crappy VB6 stuff), modify any updated values, update
>the recordset and then finally close the recordset. All of
>this happens in this one method with no other calls to other
>methods prior to the Update being called and closing the
>recordset.
>
>This same method is used for ALL of the updates that are
>processed by this application all but these updates are
>handled successfully. If I modify the same transaction that
>error'd of so that the time is not updated, it executes
>correctly as well.
>
>All databases are on a disconnected client so nothing
>outside of that particular tablet pc is using the database.
>
>> The internal formatting of the TIME datatype is fixed. The
>> *input format* can vary, when converting from an input
>> string value to the TIME datatype.
>>
>> However, a string value like '10.11.12' is not valid as a
>> time. At best it will be treated as a timestamp value with
>> a zero time component, OR it will raise an error (e.g.,
>> 10.15.12) because some part of the date portion is
>> invalid.
>>
>> Please tell us exactly how the hh.mm.ss values are being
>> processed.
>
>DB2 stores a time value as '10.11.12' and the mainframe here
>sends our tablet pc's this data in a delimited txt file with
>no changes to the time format. So on the tablet we are
>dumping this string value '10.11.12' directly into the
>recordset field value. This format has been in place since
>the dawn of time here. Prior to Sybase we were using
>Microsoft Access on our tablets and never had this problem.
>But we have found that Microsoft lets you get away with a
>lot of things that it shouldn't have.
>
>We have transactions from other systems that format the time
>as '10:11:12' and I have found no problems when updating
>these.
>
>The other question that the mainframe people are asking me
>is "Why are adds working and updates are not?". Is there
>something inside Sybase that handles this time data
>differently during an Add versus an Update?
>
>When I look at the database after the Add, the time field
>shows the following: 11:33:53.3050000
>This would be the result of setting the recordset value as
>'11.33.53'. But if I view one that was sent down as
>'11:33:53' I see the following: 11:33:53
>
>Thank you for your help!!

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com