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.

Cast a "varchar" as a "binary"

4 posts in General Discussion Last posting was on 2010-02-02 15:37:27.0Z
boekhold@gmail.com Posted on 2010-02-02 09:12:39.0Z
From: "boekhold@gmail.com" <boekhold@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Cast a "varchar" as a "binary"
Date: Tue, 2 Feb 2010 01:12:39 -0800 (PST)
Organization: http://groups.google.com
Lines: 23
Message-ID: <14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com>
NNTP-Posting-Host: 192.165.213.18
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1265101959 15864 127.0.0.1 (2 Feb 2010 09:12:39 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 2 Feb 2010 09:12:39 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: q4g2000yqm.googlegroups.com; posting-host=192.165.213.18; posting-account=W4ZeygoAAACoZ0L7EZ5SeHMMvEmtKh_I
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.5 (KHTML, like Gecko) Chrome/4.0.249.78 Safari/532.5,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!q4g2000yqm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28929
Article PK: 78172

Hi all,

I've got a piece of software that uses Id columns in the database with
datatype "binary(12)". One of the stored procs used by that software
is very inefficient and I am trying to optimize it.

The stored proc is a "generalized search function", and one of the
possible parameters is an explicit "Id", Unfortunately software
doesn't call that proc with binary(12) but instead the binary Id
encoded into a varchar(30). Eg:

CREATE PROC @MyId varchar(30)
AS
BEGIN
SELECT * from mytable where Id = @MyId
END

doesn't actually work.

I need some way of "casting" @MyId back to a binary(12). Convert()
doesn't seem to be able to do that. Any suggestions?

Maarten


"Mark A. Parsons" <iron_horse Posted on 2010-02-02 14:36:30.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Cast a "varchar" as a "binary"
References: <14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com>
In-Reply-To: <14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100126-1, 01/26/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b68386e$1@forums-1-dub>
Date: 2 Feb 2010 06:36:30 -0800
X-Trace: forums-1-dub 1265121390 10.22.241.152 (2 Feb 2010 06:36:30 -0800)
X-Original-Trace: 2 Feb 2010 06:36:30 -0800, vip152.sybase.com
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28932
Article PK: 78170

In addition to Rob's suggestion of providing an example ...

- have you verified that the proc is receiving the same exact varchar(30) string that the client software is sending?
[wondering if there could be an issue with transmitting non-printable characters, eg, control codes/characters]

- any chance the client and ASE hosts are using different endian architectures? [it may be possible to use the
reverse() function to convert the endianness of your varchar(30)/binary(12) values]

boekhold@gmail.com wrote:
> I need some way of "casting" @MyId back to a binary(12). Convert()
> doesn't seem to be able to do that. Any suggestions?
>
> Maarten


Rob V [ Sybase ] Posted on 2010-02-02 10:08:55.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com>
Subject: Re: Cast a "varchar" as a "binary"
Lines: 64
Organization: Sypron BV / TeamSybase / 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; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b67f9b7$1@forums-1-dub>
Date: 2 Feb 2010 02:08:55 -0800
X-Trace: forums-1-dub 1265105335 10.22.241.152 (2 Feb 2010 02:08:55 -0800)
X-Original-Trace: 2 Feb 2010 02:08:55 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28930
Article PK: 78175

It should work:

1> declare @c varchar(10)
2> set @c = 'abcd'
3> select @c, convert(binary(12), @c)
4> go
(1 row affected)

---------- ----------------------------
abcd 0x616263640000000000000000

(1 row affected)


What is it that doesn't do it in your case? Perhaps post an actual example
of the values you're seeing.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<boekhold@gmail.com> wrote in message
news:14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com...
> Hi all,
>
> I've got a piece of software that uses Id columns in the database with
> datatype "binary(12)". One of the stored procs used by that software
> is very inefficient and I am trying to optimize it.
>
> The stored proc is a "generalized search function", and one of the
> possible parameters is an explicit "Id", Unfortunately software
> doesn't call that proc with binary(12) but instead the binary Id
> encoded into a varchar(30). Eg:
>
> CREATE PROC @MyId varchar(30)
> AS
> BEGIN
> SELECT * from mytable where Id = @MyId
> END
>
> doesn't actually work.
>
> I need some way of "casting" @MyId back to a binary(12). Convert()
> doesn't seem to be able to do that. Any suggestions?
>
> Maarten


Sherlock, Kevin [TeamSybase] Posted on 2010-02-02 15:37:27.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com>
Subject: Re: Cast a "varchar" as a "binary"
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b6846b7$1@forums-1-dub>
Date: 2 Feb 2010 07:37:27 -0800
X-Trace: forums-1-dub 1265125047 10.22.241.152 (2 Feb 2010 07:37:27 -0800)
X-Original-Trace: 2 Feb 2010 07:37:27 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28935
Article PK: 78182

Aside from the obvious fact that 30 bytes is larger than 12 bytes, and you
have the potential for implicit truncation there, there isn't any reason why
that search (given 12 bytes or less for @MyId input) shouldn't work.
Implicit conversion from varchar to binary will be done (in fact, any
datatype can implicitly be converted to binary). As also mentioned,
explicit conversion using "convert" should also certainly work (again, for
@MyId values of 12 bytes or less).

For example:

create table myexample (id binary(12) null, id2char varchar(30) null)
go
insert into myexample select convert(varchar(30),'sybase'),
convert(varchar(30),'sybase')
go
select * from myexample where id = convert(varchar(30),'sybase')
go

As suggested, we probably need to work from examples here to understand what
is being passed to @MyId. For the example above, only 'sybase' as a
parameter value would match a record. If a varchar literal representation
of the binary value of 'sybase' ( 0x737962617365 regardless of "endianess" )
is being sent, then you won't see a match.

<boekhold@gmail.com> wrote in message
news:14063015-f87d-4478-bc4c-df307f2c8c7d@q4g2000yqm.googlegroups.com...
> Hi all,
>
> I've got a piece of software that uses Id columns in the database with
> datatype "binary(12)". One of the stored procs used by that software
> is very inefficient and I am trying to optimize it.
>
> The stored proc is a "generalized search function", and one of the
> possible parameters is an explicit "Id", Unfortunately software
> doesn't call that proc with binary(12) but instead the binary Id
> encoded into a varchar(30). Eg:
>
> CREATE PROC @MyId varchar(30)
> AS
> BEGIN
> SELECT * from mytable where Id = @MyId
> END
>
> doesn't actually work.
>
> I need some way of "casting" @MyId back to a binary(12). Convert()
> doesn't seem to be able to do that. Any suggestions?
>
> Maarten