Prev: CURSOR WITH RETURN AND UPDATE?
Next: How to change Enterprise server edition to Workgroup server edition ?
From: Michael B on 29 Sep 2009 10:27
Hello,

In a previous thread, I asked you why my code in a prepared statement
didn't work.

I've again several bugs. Here's an example with the table :

db2inst1(a)dev01:~$ db2 "describe select * from rsskeyword"
Column Information
Number of columns: 2
SQL type Type length Column name Name length
496 INTEGER 4 FEED_ID 7
448 VARCHAR 255 KEYWORD 7


According what you've said (Lennart and Serge) my SQL simplified Prepared
statement is:
select feed_id, position(cast(cast(? as char(100)) as varchar(100)) in
keyword using OCTETS) as position from rsskeyword

But when ? = '', I have 0 as result. I expect 1.

Here's in DB2 term some tries replacing the parameter directly :


db2inst1(a)dev01:~$ db2 "select feed_id, position(cast('' as varchar(255)) in
keyword using OCTETS) as position from rsskeyword"

FEED_ID POSITION
----------- -----------
42 1

1 record(s) selected.

db2inst1(a)dev01:~$ db2 "select feed_id, position(cast('' as varchar(100)) in
keyword using OCTETS) as position from rsskeyword"

FEED_ID POSITION
----------- -----------
42 1

1 record(s) selected.

db2inst1(a)dev01:~$ db2 "select feed_id, position(cast(cast('' as char(100))
as varchar(100)) in keyword using OCTETS) as position from rsskeyword"

FEED_ID POSITION
----------- -----------
42 0

(THIS is the interesting one for prepared statement!)

1 record(s) selected.

db2inst1(a)dev01:~$ db2 "select feed_id, position('' in keyword using OCTETS)
as position from rsskeyword"

FEED_ID POSITION
----------- -----------
42 1

1 record(s) selected.



What's wrong ?
Thanks you!

Michael
From: Lennart on 29 Sep 2009 11:04
On Sep 29, 4:27 pm, Michael B <m74649...(a)spambob.com> wrote:
> Hello,
>
> In a previous thread, I asked you why my code in a prepared statement
> didn't work.
>
> I've again several bugs. Here's an example with the table :
>
> db2inst1(a)dev01:~$ db2 "describe select * from rsskeyword"                                                                                                                                              
>  Column Information
>  Number of columns: 2
>  SQL type     Type length  Column name    Name length
>  496   INTEGER          4  FEED_ID        7
>  448   VARCHAR        255  KEYWORD        7
>
> According what you've said (Lennart and Serge) my SQL simplified Prepared
> statement is:
> select feed_id, position(cast(cast(? as char(100)) as varchar(100)) in
> keyword using OCTETS) as position from rsskeyword
>

Why are you casting ? as char(100)? Is there some reason that you are
not using one cast alone, as in:

select feed_id, position( cast(? as varchar(100)) in keyword using
OCTETS) as position from rsskeyword


/Lennart

[...]

From: Michael B on 29 Sep 2009 16:40
Le Tue, 29 Sep 2009 08:04:53 -0700 (PDT), Lennart a �crit :

> On Sep 29, 4:27�pm, Michael B <m74649...(a)spambob.com> wrote:
>> Hello,
>>
>> In a previous thread, I asked you why my code in a prepared statement
>> didn't work.
>>
>> I've again several bugs. Here's an example with the table :
>>
>> db2inst1(a)dev01:~$ db2 "describe select * from rsskeyword" � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � �
>> �Column Information
>> �Number of columns: 2
>> �SQL type � � Type length �Column name � �Name length
>> �496 � INTEGER � � � � �4 �FEED_ID � � � �7
>> �448 � VARCHAR � � � �255 �KEYWORD � � � �7
>>
>> According what you've said (Lennart and Serge) my SQL simplified Prepared
>> statement is:
>> select feed_id, position(cast(cast(? as char(100)) as varchar(100)) in
>> keyword using OCTETS) as position from rsskeyword
>>
>
> Why are you casting ? as char(100)? Is there some reason that you are
> not using one cast alone, as in:
>
> select feed_id, position( cast(? as varchar(100)) in keyword using
> OCTETS) as position from rsskeyword
>
>
> /Lennart
>
> [...]

Because of a "memory" of an other class I've in the code where an integer
had to be compare with a string. (aid = integer). I don't know other
solution than make a double cast to turn the integer into varchar:

db2 "select cast(aid as varchar(10)) from answers"
SQL0461N A value with data type "SYSIBM.INTEGER" cannot be CAST to type
"SYSIBM.VARCHAR". SQLSTATE=42846

db2 "select cast(cast(aid as char (10)) as varchar(10)) from answers"

1
----------
1

1 record(s) selected.
From: Serge Rielau on 29 Sep 2009 22:26
Ok that makes sense. The cast is available in DB2 9.7 btw.

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
From: Lennart on 30 Sep 2009 00:21
On 29 Sep, 22:40, Michael B <m74649...(a)spambob.com> wrote:
> Le Tue, 29 Sep 2009 08:04:53 -0700 (PDT), Lennart a écrit :
>
>
>
> > On Sep 29, 4:27 pm, Michael B <m74649...(a)spambob.com> wrote:
> >> Hello,
>
> >> In a previous thread, I asked you why my code in a prepared statement
> >> didn't work.
>
> >> I've again several bugs. Here's an example with the table :
>
> >> db2inst1(a)dev01:~$ db2 "describe select * from rsskeyword"                                                                                                                                              
> >>  Column Information
> >>  Number of columns: 2
> >>  SQL type     Type length  Column name    Name length
> >>  496   INTEGER          4  FEED_ID        7
> >>  448   VARCHAR        255  KEYWORD        7
>
> >> According what you've said (Lennart and Serge) my SQL simplified Prepared
> >> statement is:
> >> select feed_id, position(cast(cast(? as char(100)) as varchar(100)) in
> >> keyword using OCTETS) as position from rsskeyword
>
> > Why are you casting ? as char(100)? Is there some reason that you are
> > not using one cast alone, as in:
>
> > select feed_id, position( cast(? as varchar(100)) in keyword using
> > OCTETS) as position from rsskeyword
>
> > /Lennart
>
> > [...]
>
> Because of a "memory" of an other class I've in the code where an integer
> had to be compare with a string. (aid = integer). I don't know other
> solution than make a double cast to turn the integer into varchar:
>
> db2 "select cast(aid as varchar(10)) from answers"                                                                                                                      
> SQL0461N  A value with data type "SYSIBM.INTEGER" cannot be CAST to type
> "SYSIBM.VARCHAR".  SQLSTATE=42846
>
> db2 "select cast(cast(aid as char (10)) as varchar(10)) from answers"                                                                                                  
>
> 1        
> ----------
> 1        
>
>   1 record(s) selected.

Ok, I think I understand. I think the problem is that the cast to char
(100) results in a 100 char long string padded with spaces. Does it
help to trim it as in:

select feed_id, position(cast(trim(cast(? as char(100))) as varchar
(100)) in
keyword using OCTETS) as position from rsskeyword

db2 "with rsskeyword (feed_id, keyword) as (values (3, 'Whatabout this
123456789')) select feed_id, position(cast(trim(cast(1 as char(100)))
as varchar(100)) in keyword using OCTETS) as position from rsskeyword
"

FEED_ID POSITION
----------- -----------
3 16

1 record(s) selected.

db2 "with rsskeyword (feed_id, keyword) as (values (3, 'Whatabout this
123456789')) select feed_id, position(cast(trim(cast('' as char(100)))
as varchar(100)) in keyword using OCTETS) as position from rsskeyword
"

FEED_ID POSITION
----------- -----------
3 1

1 record(s) selected.


However, I don't really like the idea of sql with generic types as
arguments. Say that 1 out of a 1000 arguments is an int, and the rest
is chars. That means a lot of unnecessary functions calls ( since the
day I found a query that did T1 join T2 on ltrim(T1.x) = ltrim(T2.x)
I'm a bit sceptic about these :-). One possibility to avoid this is to
write an sql function for each type:

create function my_position ( p int , ...) ...
create function my_position ( p char(100), ...) ...

DB2 will resolve what function to use via the type of the parameter.
Just a thought

/Lennart

 |  Next  |  Last
Pages: 1 2
Prev: CURSOR WITH RETURN AND UPDATE?
Next: How to change Enterprise server edition to Workgroup server edition ?