Re: Exception when setting a CLOB on H2 v2.0.202

[Andrus Adamchik]

Hi Nigel,

Thanks for pointing this out. What you are suggesting sounds like the right 
solution. And we'd gladly accept a PR at https://github.com/apache/cayenne 


Cheers,
Andrus


> On Jan 19, 2022, at 4:38 AM, Nigel Huband  wrote:
> 
> Hi everybody,
>  
> In Cayenne 4.1.1 (and probably other versions) when using CLOB's with H2 
> v2.0.202 an error occurs when attempting to save.
>  
> This is due to a change in behaviour in H2 (likely from 1.x) where a CLOB now 
> needs to be saved as a stream.
>  
> In Cayenne, the CharType() has following method which is executed when saving 
>  CLOB for an H2 DB:
>  
>   public void setJdbcObject(PreparedStatement st, String value, 
> int pos, int type, int scale) throws Exception {
>  
>  // if this is a CLOB column, set the value as 
> "String"
>  // instead. This should work with most drivers
>  if (type == Types.CLOB || type == Types.NCLOB) {
>st.setString(pos, value);
>  } else if (scale != -1) {
>st.setObject(pos, value, type, 
> scale);
>  } else {
>st.setObject(pos, value, type);
>  }
>   }
>  
> As the type is a CLOB, when the st.setString(pos, value) gets executed an 
> exception is thrown from H2 as you now need to set a stream as follows:
>  
> Clob clob = st.getConnection().createClob();
> clob.setString(1, val);
> st.setClob(pos, clob);
>  
> As a fix I'm thinking creating a H2CharType() which overrides the 
> setJdbcObject() similar to the OracleCharType() to contain this functionality.
>  
> I'm happy to submit a PR on GitHub with the solution, please advise?
>  
> Kind regards,
> Nigel.
>  
> 
> NIGEL HUBAND 
> TECHNICAL LEAD
> AVOKA APAC
>  
>  
> Level 2, 1a Rialto Lane, Manly
> New South Wales, Australia 2100
> 
>  
>    
>  
>   temenos.com 
> 
>  
>  
> 
> The information in this e-mail and any attachments is confidential and may be 
> legally privileged. It is intended solely for the addressee or addressees. 
> Any use or disclosure of the contents of this e-mail/attachments by a not 
> intended recipient is unauthorized and may be unlawful. If you have received 
> this e-mail in error please notify the sender. Please note that any views or 
> opinions presented in this e-mail are solely those of the author and do not 
> necessarily represent those of TEMENOS. We recommend that you check this 
> e-mail and any attachments against viruses. TEMENOS accepts no liability for 
> any damage caused by any malicious code or virus transmitted by this e-mail.

Exception when setting a CLOB on H2 v2.0.202

[Nigel Huband]

Hi everybody,

In Cayenne 4.1.1 (and probably other versions) when using CLOB's with H2 
v2.0.202 an error occurs when attempting to save.

This is due to a change in behaviour in H2 (likely from 1.x) where a CLOB now 
needs to be saved as a stream.

In Cayenne, the CharType() has following method which is executed when saving  
CLOB for an H2 DB:

  public void setJdbcObject(PreparedStatement st, String value, int 
pos, int type, int scale) throws Exception {

 // if this is a CLOB column, set the value as 
"String"
 // instead. This should work with most drivers
 if (type == Types.CLOB || type == Types.NCLOB) {
   st.setString(pos, value);
 } else if (scale != -1) {
   st.setObject(pos, value, type, 
scale);
 } else {
   st.setObject(pos, value, type);
 }
  }

As the type is a CLOB, when the st.setString(pos, value) gets executed an 
exception is thrown from H2 as you now need to set a stream as follows:

Clob clob = st.getConnection().createClob();
clob.setString(1, val);
st.setClob(pos, clob);

As a fix I'm thinking creating a H2CharType() which overrides the 
setJdbcObject() similar to the OracleCharType() to contain this functionality.

I'm happy to submit a PR on GitHub with the solution, please advise?

Kind regards,
Nigel.

[signature_224584682]
NIGEL HUBAND
TECHNICAL LEAD
AVOKA APAC


Level 2, 1a Rialto Lane, Manly
New South Wales, Australia 2100


[cid:image002.png@01D45B44.8C6E0030][cid:image003.png@01D45B3E.3EA72B70][cid:image004.png@01D45B3E.3EA72B70][cid:image005.png@01D45B3E.3EA72B70]
  
temenos.com



The information in this e-mail and any attachments is confidential and may be 
legally privileged. It is intended solely for the addressee or addressees. Any 
use or disclosure of the contents of this e-mail/attachments by a not intended 
recipient is unauthorized and may be unlawful. If you have received this e-mail 
in error please notify the sender. Please note that any views or opinions 
presented in this e-mail are solely those of the author and do not necessarily 
represent those of TEMENOS. We recommend that you check this e-mail and any 
attachments against viruses. TEMENOS accepts no liability for any damage caused 
by any malicious code or virus transmitted by this e-mail.