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.

Placeholder parameters and iAnywhere JDBC returning conversion error

3 posts in General Discussion Last posting was on 2010-02-24 21:23:47.0Z
Bill Williams Posted on 2010-02-24 20:00:10.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
Subject: Placeholder parameters and iAnywhere JDBC returning conversion error
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
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: <4b85854a$1@forums-1-dub>
Date: 24 Feb 2010 12:00:10 -0800
X-Trace: forums-1-dub 1267041610 10.22.241.152 (24 Feb 2010 12:00:10 -0800)
X-Original-Trace: 24 Feb 2010 12:00:10 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7944
Article PK: 6972

We are running the following prepared statement:

select *, moveableUnitId as muField, ? as area
from LabelMgrGetMultiBatchData(?)

For both placeholder parameters, the argument is 'PTL'. These arguments are
set as strings.

The procedure returns a result set: "movebleUnitId" varchar(50), "pccMuId"
varchar(50), "boxSize" varchar(50), "printDate" varchar(10)

As you can see in the select we add two more columns, muField and area where
the value of area is actually one of our parameters.

When we run this with iAnywhere (11.0.1.2376) JDBC, we get an error "cannot
convert 'PTL' to int" when we do the ResultSet.next method.

If we run this same statement using jConnect, it works just fine.

Is jConnect letting us get away with something it shouldn't?


Karim Khamis [Sybase iAnywhere] Posted on 2010-02-24 20:52:57.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Placeholder parameters and iAnywhere JDBC returning conversion error
References: <4b85854a$1@forums-1-dub>
In-Reply-To: <4b85854a$1@forums-1-dub>
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: <4b8591a9$1@forums-1-dub>
Date: 24 Feb 2010 12:52:57 -0800
X-Trace: forums-1-dub 1267044777 10.22.241.152 (24 Feb 2010 12:52:57 -0800)
X-Original-Trace: 24 Feb 2010 12:52:57 -0800, vip152.sybase.com
Lines: 38
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7945
Article PK: 6971

The query works with jConnect because jConnect replaces the parameters
with host variables under the covers. While doing so makes queries like
this work, the practice of replacing parameters with host variables can
also be performance inhibiting. If you want to stick to the iAnywhere
JDBC driver, then you will probably need to rewrite your query with a
cast as follows:

select *, moveableUnitId as muField, cast(? as varchar(256)) as area
from LabelMgrGetMultiBatchData(?)

Note that when performing the cast, try sticking to a char(n) or
varchar(n) where n is large enough to hold all possible values. If you
go to long varchar, then you will introduce other performance problems.

Karim

Bill Williams wrote:
> We are running the following prepared statement:
>
> select *, moveableUnitId as muField, ? as area
> from LabelMgrGetMultiBatchData(?)
>
> For both placeholder parameters, the argument is 'PTL'. These arguments are
> set as strings.
>
> The procedure returns a result set: "movebleUnitId" varchar(50), "pccMuId"
> varchar(50), "boxSize" varchar(50), "printDate" varchar(10)
>
> As you can see in the select we add two more columns, muField and area where
> the value of area is actually one of our parameters.
>
> When we run this with iAnywhere (11.0.1.2376) JDBC, we get an error "cannot
> convert 'PTL' to int" when we do the ResultSet.next method.
>
> If we run this same statement using jConnect, it works just fine.
>
> Is jConnect letting us get away with something it shouldn't?
>
>


Bill Williams Posted on 2010-02-24 21:23:47.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <4b85854a$1@forums-1-dub> <4b8591a9$1@forums-1-dub>
Subject: Re: Placeholder parameters and iAnywhere JDBC returning conversion error
Lines: 42
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: <4b8598e3$1@forums-1-dub>
Date: 24 Feb 2010 13:23:47 -0800
X-Trace: forums-1-dub 1267046627 10.22.241.152 (24 Feb 2010 13:23:47 -0800)
X-Original-Trace: 24 Feb 2010 13:23:47 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7947
Article PK: 6975

Thanks for the info.

"Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com> wrote in message
news:4b8591a9$1@forums-1-dub...
> The query works with jConnect because jConnect replaces the parameters
> with host variables under the covers. While doing so makes queries like
> this work, the practice of replacing parameters with host variables can
> also be performance inhibiting. If you want to stick to the iAnywhere JDBC
> driver, then you will probably need to rewrite your query with a cast as
> follows:
>
> select *, moveableUnitId as muField, cast(? as varchar(256)) as area from
> LabelMgrGetMultiBatchData(?)
>
> Note that when performing the cast, try sticking to a char(n) or
> varchar(n) where n is large enough to hold all possible values. If you go
> to long varchar, then you will introduce other performance problems.
>
> Karim
> Bill Williams wrote:
>> We are running the following prepared statement:
>>
>> select *, moveableUnitId as muField, ? as area from
>> LabelMgrGetMultiBatchData(?)
>>
>> For both placeholder parameters, the argument is 'PTL'. These arguments
>> are set as strings.
>>
>> The procedure returns a result set: "movebleUnitId" varchar(50),
>> "pccMuId" varchar(50), "boxSize" varchar(50), "printDate" varchar(10)
>>
>> As you can see in the select we add two more columns, muField and area
>> where the value of area is actually one of our parameters.
>>
>> When we run this with iAnywhere (11.0.1.2376) JDBC, we get an error
>> "cannot convert 'PTL' to int" when we do the ResultSet.next method.
>>
>> If we run this same statement using jConnect, it works just fine.
>>
>> Is jConnect letting us get away with something it shouldn't?
>>