something interesting

[Jack Daniel]

Hi e'body,

I have got into something interesting..

I am presently working on DB2 tables created by somebody else...

I have a column in a table which has something like 1234, but he declared it
as VARCAHR(255) ??? i do not know why.. hmm !!! maybe he ....

and i was thinking of changing it to NUMERIC(4,0) OR VARCHAR(4)..

It really does not let me do it..

Ok, i thought let me play with java code..
firstly i wanted to change it because, look when i query, and when i have something like resultSet.getString(1) where it should return a String it returns a String but like ( "1234 ); there is no closing double quote... hmmm interesting... so when i perform Integer.parseInt( returned String) .. throws a NumberFormatException; ohh my gosh.. some bug here??

I noticed the value "1234 and not "1234" when i was debugging...
kind of interesting...

maybe we can discuss about it.. something like a bug ...

Thanks

PS: If possible you guys can help me why it is giving problem when i am altering the table, i used :

"ALTER TABLE CASES ALTER COLUMN CSCODE SET DATA TYPE NUMERIC(4,0) "

does not let me, i also tried
"ALTER TABLE CASES ALTER COLUMN CSCODE SET DATA TYPE VARCHAR(4) "

WHICH GIVES AN ERROR SAYING..

ERROR: [SQL0952] Processing of the SQL statement ended. Reason code 10.
[ September 16, 2004: Message edited by: Bear Bibeault ]

[Jyothi Lature]

Hi,

Its bcoz, it has value like "1234 which is not in NUMERIC format.
Try changing the datatype to VARCHAR(5), it should allow you to do...

[Jack Daniel]

Thanks...

Well, i think i did not make it clear in my first message...

I will explain it some other way...
firstly is varchar(255) the maximum limit for a data type declaration??

i am saying, it is some kind of a bug.. in the (java written code by SUN..)
plz correct me if i am wrong anywhere..

ok i have

String code=resultSet.getString(1); where resultSet is an instance of
ResultSet

and here there is something going wrong, when you are talking about String it should be in double quotes(its value),.. but the above code is returning a String which would be like "1234 instead of "1234".. it is a bug by SUN code.. is that so??

The next line if I say code.equals("1234"), it is going to say False;
because "1234" is different from "1234;

You understand what I am saying.. I guess it is not that easy..only when you experience this, you will figure out ..
That was why i was asking is varchar(255) the maximum limit?? i guess nobody will declare a datatype like that,.. so it works fine.. The problem is because of the declaration as varchar(255).. ??
i do not know how to explain this,..
anyway...nice if you understood what i am trying to say..

My feel, is this is something like a bug in Sun written class (the class that implements java.sql.ResultSet) or am i going wrong somewhere..

Thanks

[Lu Battist]

What would help make it clear for everyone is printing out the value from the rs.getString(1) like this:

System.out.println("value is '" + rs.getString(1) + "'");

getString() doesn't put double quotes around anything - just returns the string value from the database. If the output to the console is '"1234', that doesn't mean anything is wrong. And I highly doubt there is any bug in the JDBC code. What it implies though, is that you can't reclassify the column numberic because it is not numberic.

Also, the varchar limit is not 255 for DB2, we routinely use varchar that are set at 3000 - consult the manual for the limit. Unless you know the data coming in extremely well, I'd suggest not changing it at all. Shortening a varchar from 255 to say 50 is not doing much for you at all because this is a variable sized datafield anyway its not going to waste a bunch of space if its only several characters long instead of 250. If it were CHAR(255) then you may really need to do something as CHAR will always consume that much space.

Lastly, if you are absolutly positive that the values should all be numberic. Save off all the primary key, and the column. Drop the column from the table and re-add it as a numberic, then load your data back in for that column after converting any "bad" values to "good" ones. Then the database type will prevent any new "bad" entries from occuring.

[Jack Daniel]

Thanks...

I just did what you told:

System.out.println("value is '" + rs.getString(1) + "'");

This is the output from console : value is '1193

why did that single quote come, which is not visible, when i look at the database table..??

Thanks for your idea..
Anyway i will try to fix this, by some way..

[Jack Daniel]

oops..

sorry the single quote is given in the System.out...

BUT why is the closing single quote not showing up???

hmm.. anyway.. I really am lost what is going on...

[Jack Daniel]

Hi,.. I think i understood what is happening..

I have also tried :

System.out.println( "length: "+res.getString(1).length() );

and the output at console : length: 255
there you go, so it looks like a bug in DB2 on AS400 ,
when it is declared as VARCHAR, i guess it is supposed to give the result as 4; but gave 255??

I looked at the create table and this is what is shown:
CODE VARCHAR(255)

So.. it is actually considering it as CHAR(255) ??? which is not possible..

Maximum length of CHAR: 254 (as far as DB2 is concerned )
Reference: https://aurora.vcu.edu/db2help/db2s0/frame3.htm#db2s0417

So, when JDBC is retrieving a CHAR(255) (which is not valid), I am coming across a problem..

So i think, its a problem with DB2 on AS400..

Any other comments or suggestions, please share..

Thanks

[Lu Battist]

This is interesting! I take it there is no ending single quote way at the end of the output. You could try to modify the select statement:

select rtrim(code) from ... where ...

This will probably not work if it is really failing to put the last single quote on that string. Wonder if there is some weird backspace or delete character captured in the actual database.

If you have access to db2 client. Try to run it directly.
db2-> select '"' || code || '"' from ... where ...

This will let you see how long it really is, but if there are control chars in there it may still look the same as the JDBC output.
[ September 17, 2004: Message edited by: Lu Battist ]

[Lu Battist]

One last idea, its more of a workaround.

select substr(code, 1, 5) from ... where ...
[ September 17, 2004: Message edited by: Lu Battist ]

[Jack Daniel]

select substr(code, 1, 5) from ... where

Yeah I know, thats a nice work around way.. thanks..

I infact did :

resultSet.getString(1).substring(0,4); and everything works fine now..

Thanks ;-)