Insert Blob using SQLQuery

ellusionistza · **Joined:** Mon Mar 15, 2010 11:11 am **Posts:** 2

I need to get a row from one table and insert the same row into another table. Everything works fine except for inserting blobs. I am getting the following error.

Quote:

insert into eproduct values (420681515,426875589,'','G-TEL 100','','','',0,0,0,1,0,0,1,0,0,'Bla Bla Bla Bla Bla Bla Bla',org.hibernate.lob.SerializableBlob@6a086a,org.hibernate.lob.SerializableBlob@14e8936)

2010-03-15 17:21:19,871 ERROR [org.hibernate.util.JDBCExceptionReporter] - <ORA-01729: database link name expected
>

If I insert the last to values (which are blobs) as null the insert works fine.

Code:

   public Product insertProduct(Product product) {

      Session session = getSession();
      
      String sql="insert into " + PRODUCT +" values (" + product.getId() + "," + 
      product.getProductType().getId() + ",'"  + convertStringNulls(product.getProductCode())+ "','" 
      + convertStringNulls(product.getProductName())+ "','" + convertStringNulls(product.getModelName()) + "','" 
      + convertStringNulls(product.getProductURL())+ "','" 
      + convertStringNulls(product.getSapMaterialCode())+ "'," 
      + convertBooleanValue(product.isBusiness())+ "," + convertBooleanValue(product.isDealerWeb())+ "," 
      + convertBooleanValue(product.isDiscontinued())+ "," + convertBooleanValue(product.isFullSupport())+ "," +
      convertBooleanValue(product.isLimitedSupport())+ "," 
      + convertBooleanValue (product.isOutOfStock())+ ","
      + convertBooleanValue(product.isResidential())+","+ convertBooleanValue(product.isSpecialOffer())+"," 
      + convertBooleanValue (product.isTrolley())+ ",'"
      + convertStringNulls(product.getSummaryDescription())+"'," 
      + product.getImage() + "," + product.getSummaryImage() + ")";
      
      try {        
          SQLQuery query = session.createSQLQuery(sql);        
          query.executeUpdate();
       } catch (HibernateException he) {
          throw he;
       } finally {
          session.close();
       }  
       
       return product;
   }

The blob fields above (product.getImage() and product.getSummaryImage()) are of type Blob. Anybody got a solution?

hallmit · **Posted:** Mon Mar 15, 2010 5:16 pm

try to use a parameterized query rather than string approach

String sql="insert into " + PRODUCT +" values (?, ?, ...,?)"

SQLQuery query = session.createSQLQuery(sql);

query.setBinary(int position, byte[] val) //for blob
query.setBoolean(int position, boolean val) //no need convertBooleanValue()
....
position - the position of the parameter in the query string, numbered from 0.

take a look at https://www.hibernate.org/hib_docs/v3/a ... Query.html

ellusionistza · **Joined:** Mon Mar 15, 2010 11:11 am **Posts:** 2

hallmit wrote:

try to use a parameterized query rather than string approach

String sql="insert into " + PRODUCT +" values (?, ?, ...,?)"

SQLQuery query = session.createSQLQuery(sql);

query.setBinary(int position, byte[] val) //for blob
query.setBoolean(int position, boolean val) //no need convertBooleanValue()
....
position - the position of the parameter in the query string, numbered from 0.

take a look at https://www.hibernate.org/hib_docs/v3/a ... Query.html

Thank you. Everything is working.