Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    1

    Unanswered: length of varchar columns

    Hi,
    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...

    Greeting, Christian
    Last edited by Pupapaya; 05-17-08 at 16:35. Reason: wrong title

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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 v9.7.0.6 os 6.1.0.0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •