-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: need help with jdbc:odbc sql 2000 problem with padded spaces
PostPosted: Mon Nov 06, 2006 10:40 am 
Newbie

Joined: Fri Jul 07, 2006 9:53 am
Posts: 11
Hibernate version: 3.0 and 3.2

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:sql server 2000
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Hello. Im hoping someone here could solve a little problem for me. Ive tried this on both hibernate 3.0 and the latest version, 3.2, both seem to have the same problem.

Now this only happens upon the read from the database (ive tested it), but when it pulls information out, it will pad spaces to the end of any varchar field.

Example: if a field is a varchar(20) and the word 'test' is in there, it will return the word 'test' tacked on with 16 other spaces to the end of it.

Now I have found out that if i switch to the JTDS driver from the jdbc:odbc driver, it will fix that problem. However, I was told we must use the jdbc:odbc driver (Im not the project leader). So does anyone have any suggestions on how to clear up this mess using the jdbc:odbc driver?

Thanks in advance.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 06, 2006 10:51 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
Well, I haven't used this in a while (DB2400 returned padded strings, but we switched to Oracle), but here's a UserType that will trim returned strings. Just set your mapping files to use this class instead of String.

Code:
/*
* Created on Jun 4, 2005
*/
package com.whatever.hibernate.usertypes;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;

public class TrimmedString implements UserType {
   
       public TrimmedString() {
           super();
       }

       public int[] sqlTypes() {
           return new int[] { Types.CHAR };
       }

       public Class returnedClass() {
           return String.class;
       }

       public boolean equals(Object x, Object y) throws HibernateException {
           return (x == y) || (x != null && y != null && (x.equals(y)));
       }

       public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException {
           String val = rs.getString(names[0]);
           return val != null ? val.trim() : null;
       }

       public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
           st.setString(index, (String)value);
       }

       public Object deepCopy(Object value) throws HibernateException {
           if (value == null) return null;
           return new String((String)value);
       }

       public boolean isMutable() {
           return false;
       }
      
      public Object assemble(Serializable cached, Object owner)
         throws HibernateException {
         return cached;
      }
      public Serializable disassemble(Object value) throws HibernateException {
         return (Serializable)value;
      }
      public int hashCode(Object x) throws HibernateException {
         return x.hashCode();
      }
      public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
         return original;
      }
}



Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 06, 2006 11:03 am 
Newbie

Joined: Fri Jul 07, 2006 9:53 am
Posts: 11
My apologizes. I meant to say it happens upon insert. I've just tested it again to double check. Unfortunately since its happening on insert, I cant use a class like this. This is also a problem because im able to put .trim() everywhere on the new version of our program, but i am unable to put them in the old version. So somehow I need to get hibernate to not pad the spaces on the end upon inserting a varchar to sql server 2000 using the jdbc:odbc driver.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 06, 2006 11:08 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
If it does not occur when you switch to JTDS driver, it likely means that the driver is responsible for the padding and not hibernate. Which still seems odd to me, but it's not the only thing that Microsoft does that make me scratch my head. ;) I'd look deeper into the driver settings, maybe there's something there.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 06, 2006 11:11 am 
Newbie

Joined: Fri Jul 07, 2006 9:53 am
Posts: 11
I agree that its probably the driver(since Ive switched it out with the JTDS and it was fine), but unfortunately its not my call to switch to the new driver. Im just hoping that someone may have come across a fix that did not include switching out the driver.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 06, 2006 11:19 am 
Expert
Expert

Joined: Tue Jul 11, 2006 10:21 am
Posts: 457
Location: Columbus, Ohio
Sorry bro, I've only come across this type of padding issue when I accidentally use char(20) instead of varchar(20). Even then, the Hibernate-generated SQL for inserts does not include padded spaces. If the driver is inserting spaces into varchar fields, I say let it. Then strip the spaces on reads either manually in the pojo or using a UserType. If anyone complains, at least you can point your finger at someone else!


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 06, 2006 11:25 am 
Newbie

Joined: Fri Jul 07, 2006 9:53 am
Posts: 11
Well its nice "well if we switch to JTDS its fine", but here is where the main problem really lays. I can switch it to use a customer userType or put .trim()'s all over the place in this version. But the old version of the problem must still run and use the same database. And I cant change the old program to use .trim()'s or anything like that. And when that information is pulled out on the old program, all those spaces will be there (this includes when it checks your password :P) And im stuck using the jdbc:odbc driver.


I know its a long shot of someone actually figuring it out, but if there is a fix for this problem that does not involve switching drivers and using userTypes, i would be very happy. :)

Thank you all for your quick replies and thank you to anyone that may post a fix for this.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.