Problem inserting binary into SQLServer

mmaia · **Joined:** Sun Nov 23, 2003 1:46 pm **Posts:** 15

Hi,

I'm having problems to insert binary type using MS sqlServer2000 an hibernate.
Previously during development life cicle I used a String type to store password, but now I have implemented a cripto rotine using Rijndael algoritm to store the password and I'm getting an error.
I'm using byte[] as the java type for the POJO and on hibernate xml mapping file using:

Code:

<property name="senhaCriptografada">
   <column name="SENHA" sql-type="binary" not-null="true"/>
</property>

I'm using weblogic 8.1 and sql server 2000, struts, jstl, and hibernate, the driver is the bea type 4 supplied.
I've done some tests storying the binary result from the cripto algoritm using with plain jdbc and it's working fine. Is there a bug within hibernate and binary types? What I'm doing wrong?

The error follows:
WARNING: SQL Error: 8152, SQLState: 22001
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: [BEA][SQLServer JDBC Driver][SQLServer]String or binary data would be tr
uncated.
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 3621, SQLState: HY000
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: [BEA][SQLServer JDBC Driver][SQLServer]The statement has been terminated
.
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 8152, SQLState: 22001
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: [BEA][SQLServer JDBC Driver][SQLServer]String or binary data would be tr
uncated.
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 3621, SQLState: HY000
24/05/2004 01:09:35 net.sf.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: [BEA][SQLServer JDBC Driver][SQLServer]The statement has been terminated
.
24/05/2004 01:09:35 net.sf.hibernate.JDBCException <init>
SEVERE: could not insert: [br.com.awa.sistemaseguranca.administrativo.manterusua
rios.dto.UsuarioDTO]
java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]String or binary d
ata would be truncated.
at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)
at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)
at weblogic.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Sour
ce)
at weblogic.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Sour
ce)
at weblogic.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown S
ource)
at weblogic.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at weblogic.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unkn
own Source)
at weblogic.jdbc.base.BaseStatement.commonTransitionToState(Unknown Sour
ce)
at weblogic.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at weblogic.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Sour
ce)
at weblogic.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at weblogic.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Source
)
at weblogic.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Source
)
at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStateme
nt.java:94)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:526)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.jav
a:432)
at net.sf.hibernate.impl.ScheduledIdentityInsertion.execute(ScheduledIde
ntityInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:925)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:850)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(Session
Impl.java:768)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:731)
at br.com.awa.sistemaseguranca.administrativo.manterusuarios.web.dao.Usu
arioDAO.cadastrarUsuario(UsuarioDAO.java:25)
at br.com.awa.sistemaseguranca.administrativo.manterusuarios.web.FacadeU
suarios.cadastraUsuario(FacadeUsuarios.java:61)
at br.com.awa.sistemaseguranca.administrativo.manterusuarios.web.struts.
CadastraUsuarioAction.execute(CadastraUsuarioAction.java:25)
at org.apache.struts.action.RequestProcessor.processActionPerform(Reques
tProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:148
2)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run
(ServletStubImpl.java:971)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
pl.java:402)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubIm
pl.java:305)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationActio
n.run(WebAppServletContext.java:6350)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(Authenticate
dSubject.java:317)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:
118)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppSe
rvletContext.java:3635)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestIm
pl.java:2585)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
br.com.awa.sistemaseguranca.administrativo.manterusuarios.UsuarioException: Ocor
reu um erro ao cadastrar o Usu

fgoldin · **Joined:** Wed Apr 07, 2004 1:32 pm **Posts:** 18

I had the same problem, I used the following custom type to map
and byte array in a blob using sql server.

Code:

public class BinaryBlobType implements UserType
{
    public int[] sqlTypes()
    {
        return new int[] {Types.BLOB};
    }

    public Class returnedClass()
    {
        return byte[].class;
    }

    public boolean equals(Object x, Object y)
    {
        return (x == y)
                || (x != null && y != null && java.util.Arrays.equals(
                        (byte[]) x, (byte[]) y));
    }

    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException
    {
        byte[] b = null;
        
        InputStream in = rs.getBinaryStream(names[0]);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();

        byte[] tmp = new byte[1024];
        int read;

        try
        {
            while ((read = in.read(tmp)) > 0)
            {
                baos.write(tmp, 0, read);
            }
            
            b = baos.toByteArray();
            
            in.close();
            baos.close();            
        } 
        catch (Exception exc)
        {
            throw new HibernateException(exc);
        }
        
        return b;
    }

    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException
    {
        byte[] b = (byte[]) value;
        
        ByteArrayInputStream bais = new ByteArrayInputStream(b);
        st.setBinaryStream(index, bais, b.length);
    }

    public Object deepCopy(Object value)
    {
        if (value == null) return null;

        byte[] bytes = (byte[]) value;
        byte[] result = new byte[bytes.length];
        System.arraycopy(bytes, 0, result, 0, bytes.length);

        return result;
    }

    public boolean isMutable()
    {
        return true;
    }
}

mmaia · **Joined:** Sun Nov 23, 2003 1:46 pm **Posts:** 15

Hi,

tx a lot for the help, the error was driving me mad already. I

mmaia · **Joined:** Sun Nov 23, 2003 1:46 pm **Posts:** 15

Hi,

just a moment before I try to implement the suggested solution I did a last try and it worked.

Well, I was using a binary field under sqlserver2000 and declaring the column type as binary as you can see on my previous post. That doesn't work and I was getting that error.

Tha solution that worked was change the db column type to image and declaring the hibernate field type as blob. That solved the problem and I didn't have to implement a custom type as you suggested(was easyer this way).

Tanx a lot for your help anyway,
best regards,

dealover · **Joined:** Thu Dec 11, 2003 11:20 pm **Posts:** 5

Hey mmaia,
I met with the same question,I set pojo property java.sql.Blob and set *.hbm.xml column image.but I cann't store xml file to sql server 2000 database.can you post me your code snippet.Thanks in advance.
My mail address:dealover@21cn.com