Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

JDBC VARCHAR problem

Posted on 2002-05-09
4
Medium Priority
?
550 Views
Last Modified: 2008-03-03
I am inserting data into an SQL Server database from a coarse-grained EJB. When a string is inserted into a VARCHAR field in the database, the string is taking up the full length of the VARCHAR field, rather than just the length of the string - any ideas?

This problem does not occur with my fine-grained EJBs which use stored procedures to insert data.

Thanks in advance.
0
Comment
Question by:joannape
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 3

Expert Comment

by:jerelw
ID: 6998659
Use ResultSetMetaData to get your max sizes, and then truncate your varchar to to 'fit' in your field.

Or use static longs to limit your data to the size of your fields.
0
 
LVL 4

Accepted Solution

by:
Neutron earned 800 total points
ID: 7000687
hi joannape :-)

jerelw's solution is usable, only I wouldn't use ResultSetMetaData because you have to make a select on the table to get access to it. Instead I would use DatabaseMetaData to retrieve column information. Here is the snippet:

-------snip-------8<-------
...
public static final int TYPE = 0;
public static final int TYPE_NAME = 1;
public static final int PRECISION = 2;
public static final int SCALE = 3;
...

public HashMap getColumnDefinitions( Connection con, String catalog, String schema, String table )
{
    HashMap columns = new HashMap();
   
    String columnName;
    Object[] columnDefinition;
   
    try
    {
        ResultSet rs = con.getMetaData().getColumns( catalog, schema, table, "%" );
       
        while (rs.next())
        {
            columnName = rs.getString( 4 ).toLowerCase();
           
            columnDefinition = new Object[4];
            columnDefinition[TYPE] = new Integer( rs.getInt( 5 ) );
            columnDefinition[TYPE_NAME] = rs.getString( 6 );
            columnDefinition[PRECISION] = new Integer( rs.getInt( 7 ) );
            columnDefinition[SCALE] = new Integer( rs.getInt( 9 ) );
           
            columns.put( columnName, columnDefinition );
        }
    }
    catch( SQLException e )
    {
        System.out.println( "Cannot execute DatabaseMetaData#getColumns method for table:"+table+" Message:"+e.getMessage() );
    }
   
    return columns;
}

public String truncateIfNecessary( String column, String value, HashMap columns )
{
    Object[] def = columns.get( column );
   
    int type = ((Integer)def[PRECISION]).intValue();
    int precision = ((Integer)def[PRECISION]).intValue();
   
    if (type==java.sql.Types.VARCHAR)
    {
        if (value.length() > precision)
        {
            value = value.substring( 0, precision );
        }
    }
   
    return value;
}
-------snip-------8<-------

Now when you need the column information you can have code like this:

...
HashMap columns = getColumnDefinitions( someConnection, null, "MYSCHEMA", "MYTABLE" );

preparedStatement.setString( index, truncateIfNecessary( "COLUMNNAME", sValue, columns ) );
...

-

If you don't need all the columns like it is presented here, you can put in the hashmap only VARCHAR fields and then you need only the PRECISION value stored and not the Object[4].

You could also have one hashmap for all the tables, so you can use tableName+"."+columnName as key for hashmap entries.

'hope this helps

Greetings,
    </ntr> :)
0
 
LVL 35

Expert Comment

by:girionis
ID: 8741083
No comment has been added lately, so it's time to clean up this TA.

I will leave a recommendation in the Cleanup topic area that this question is:

- points to Neutron

Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

girionis
Cleanup Volunteer
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question