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.

LEN returning incorrect field length

4 posts in General Discussion Last posting was on 2012-07-18 17:10:26.0Z
Ian Harrison Posted on 2012-07-18 09:32:57.0Z
Sender: 76d5.5006812e.1804289383@sybase.com
From: Ian Harrison
Newsgroups: sybase.public.ase.general
Subject: LEN returning incorrect field length
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <500682c9.76ff.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 18 Jul 2012 02:32:57 -0700
X-Trace: forums-1-dub 1342603977 172.20.134.41 (18 Jul 2012 02:32:57 -0700)
X-Original-Trace: 18 Jul 2012 02:32:57 -0700, 172.20.134.41
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31257
Article PK: 74146

I'm running the follow SQL

SELECT LEN(Col) FROM MyTable

I get a list of 22's as the result set which would indicate
that the all the values in that column are 22 characters
long.

If I inspect the table definition then I see that Col is
defined as a user defined varchar. I check the definition
for that user defined varchar and it shows its length as 16.
If I check syscolumns I also see this column is 16
characters long.

My question is, when running this command through
Interactive SQL 11.0.1, build 2430 against ASE version 15.5
how is it that I get 22 when the maximum field length is 16.

Thanks.


"Mark A. Parsons" <iron_horse Posted on 2012-07-18 13:25:36.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: LEN returning incorrect field length
References: <500682c9.76ff.1681692777@sybase.com>
In-Reply-To: <500682c9.76ff.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5006b950$1@forums-1-dub>
Date: 18 Jul 2012 06:25:36 -0700
X-Trace: forums-1-dub 1342617936 172.20.134.152 (18 Jul 2012 06:25:36 -0700)
X-Original-Trace: 18 Jul 2012 06:25:36 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31259
Article PK: 74147

I don't use the Interactive SQL GUI but fwiw ...

What is your dataserver version?

len() was introduced with an early version of ASE 12.5.x. I'm *assuming* your reference to 11.0.1 means your
Interactive SQL was written on OpenClient 11.x. I'm thinking there could be an issue with the OC 11.x code not being
able to handle the 12.5.x result ...

1 - what happens when you use a newer version of Interactive SQL that was designed with SDK 15.x? (ie, what happens if
you use a tool that was built with the latest/greatest SDK version?)

2 - what happens when you use the command line 'isql' that comes with the dataserver? (ie, what happens if you use a
tool that was built with the same SDK version as the dataserver ... the dataserver that is generating the len() output?)

3 - do you see the same issue when using char_length()? (this has been around a lot longer than len() so it would
interesting to see if the OC 11.x has problems with its results)

You explicitly mention a user-defined datatype, but don't mention any other tests you may have run ...

4 - do you see comparable discrepancies when checking other columns (system and user-defined datatypes)?

On 07/18/2012 03:32, Ian Harrison wrote:
> I'm running the follow SQL
>
> SELECT LEN(Col) FROM MyTable
>
> I get a list of 22's as the result set which would indicate
> that the all the values in that column are 22 characters
> long.
>
> If I inspect the table definition then I see that Col is
> defined as a user defined varchar. I check the definition
> for that user defined varchar and it shows its length as 16.
> If I check syscolumns I also see this column is 16
> characters long.
>
> My question is, when running this command through
> Interactive SQL 11.0.1, build 2430 against ASE version 15.5
> how is it that I get 22 when the maximum field length is 16.
>
> Thanks.


Bret Halford Posted on 2012-07-18 15:13:58.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:13.0) Gecko/20120614 Thunderbird/13.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: LEN returning incorrect field length
References: <500682c9.76ff.1681692777@sybase.com>
In-Reply-To: <500682c9.76ff.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5006d2b6$1@forums-1-dub>
Date: 18 Jul 2012 08:13:58 -0700
X-Trace: forums-1-dub 1342624438 172.20.134.152 (18 Jul 2012 08:13:58 -0700)
X-Original-Trace: 18 Jul 2012 08:13:58 -0700, vip152.sybase.com
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31260
Article PK: 74149


On 7/18/2012 3:32 AM, Ian Harrison wrote:
> I'm running the follow SQL
>
> SELECT LEN(Col) FROM MyTable
>
> I get a list of 22's as the result set which would indicate
> that the all the values in that column are 22 characters
> long.
>
> If I inspect the table definition then I see that Col is
> defined as a user defined varchar. I check the definition
> for that user defined varchar and it shows its length as 16.
> If I check syscolumns I also see this column is 16
> characters long.
>
> My question is, when running this command through
> Interactive SQL 11.0.1, build 2430 against ASE version 15.5
> how is it that I get 22 when the maximum field length is 16.
>
> Thanks.
>

What character set is the server using?

I think it could help if, instead of just telling us about
the output, you post the actual outputs of

sp_help MyTable

and

sp_help <name_of_user_defined_type>

and

select @@version

It would also be interesting to see a few rows of the actual
data values, perhaps

select
top 3
col,
len(col),
convert(binary(30), col)
from
MyTable


can you reproduce the issue with another table, something like this:

create table MyTable2 (col <name_of_user_defined_type> )
go
insert MyTable2 select name from sysobjects
go
select col, len(col) from MyTable2
go

Cheers,
-bret


J Posted on 2012-07-18 17:10:26.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: LEN returning incorrect field length
Reply-To: J@bogusemailAddress.com
Message-ID: <5006ece9.526246609@forums.sybase.com>
References: <500682c9.76ff.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Jul 2012 10:10:26 -0700
X-Trace: forums-1-dub 1342631426 172.20.134.152 (18 Jul 2012 10:10:26 -0700)
X-Original-Trace: 18 Jul 2012 10:10:26 -0700, vip152.sybase.com
Lines: 26
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31262
Article PK: 74150

On 18 Jul 2012 02:32:57 -0700, Ian Harrison wrote:

Not sure how you check but 16 hex is 22 decimal.

J

>I'm running the follow SQL
>
>SELECT LEN(Col) FROM MyTable
>
>I get a list of 22's as the result set which would indicate
>that the all the values in that column are 22 characters
>long.
>
>If I inspect the table definition then I see that Col is
>defined as a user defined varchar. I check the definition
>for that user defined varchar and it shows its length as 16.
> If I check syscolumns I also see this column is 16
>characters long.
>
>My question is, when running this command through
>Interactive SQL 11.0.1, build 2430 against ASE version 15.5
>how is it that I get 22 when the maximum field length is 16.
>
>Thanks.