Thread: length of varchar columns
05-17-08, 16:34 #1Registered User
- Join Date
- May 2008
Unanswered: length of varchar columns
I am writing a Java implementation that saves data sent to it into a db2 database.
First a model of the data is sent. It contains information about the data later sent, including the names for the columns, their datatypes and their length. For example a field could be:
Name=Lastname, Type=String, Max Length=50
When these information are sent I create a table (or update it if it already exists and need to be changed) according to these information.
Later, the actual data is sent and I insert it into the table.
My problem is, the given maximal length for string columns is the number of characters, not the length in bytes as varchar would require.
So if i created the table for the previous example as a varchar(50), I can't insert data with 50 characters, and a byte length bigger than 50 (which will happen if it contains a german umlaut for example).
My approach would be to create the columns still with the given length, and insert the data as it is sent.
Once an insert throws an SQLException with the "SQLSTATE: 22001" I run through all data fields and check each length using a query on the database, like:
"select length('<my data'>) as lastname from sysibm.sysdummy1"
If the resulting length is bigger than the column length I enlarge the column and insert the data again.
Can anybody think of a better approach? I don't like this one, it might require multiple size increases during the process...
Last edited by Pupapaya; 05-17-08 at 16:35. Reason: wrong title
05-18-08, 04:52 #2Registered User
Provided Answers: 11
- Join Date
- Apr 2006
this because of utf database. in char50 there is no guaranty that 50 characters would fit. if special characters are being used this nbr will lower. the only solution for this would be to use graphic and vargraphic as datatype
in graphic50 you can always insert 50 characters.Best Regards, Guy Przytula
Database Software Consultant
Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
Information Server Datastage Certified
05-18-08, 06:45 #3Registered User
- Join Date
- Nov 2005
Why not make a column bigger to begin with if you already know that yoou might have a longer data?
If you do not know ahead of time what data is coming and how big, you might want to go back to a drawing board and figure that one out.--
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
DB2 v220.127.116.11 os 18.104.22.168