Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

JDBC VARCHAR problem

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
joannape
Asked:
joannape
1 Solution
 
jerelwCommented:
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
 
NeutronCommented:
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
 
girionisCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now