CLOB Patch

[[ d i t h e r f i x ]]

As some of you may know, we use MS SQL Server 2005 with the jTDS driver for our uPortal database here at Johns Hopkins.  I came across an issue when exporting entities using Cernunnos where CLOB database values were not being properly handled by the QueryTask.  Here's a quick rundown of the scenario…

 

From our uPortal source, I attempted to export a layout, for instance:

 

ant crn-export -Dtype=layout -Dsysid=johndoe

 

Which ultimately invokes:

 

uportal-impl/src/main/resources/org/jasig/portal/io/export-layout.crn

 

Which executes the following SQL query:

 

SELECT uplp.struct_id, uplp.struct_parm_nm, uplp.struct_parm_val

FROM up_layout_param uplp LEFT JOIN up_layout_struct upls ON uplp.struct_id = upls.struct_id AND uplp.user_id = upls.user_id AND uplp.layout_id = upls.layout_id

WHERE upls.user_id = (select distinct user_id from up_user where user_name='johndoe') AND upls.layout_id = 1

 

The param nodes returned from the script looked like:

 

<param>

<name>dlm:origin</name>

<value>net.sourceforge.jtds.jdbc.ClobImpl@17b1c02</value>

</param>

 

As you can see, something funky goes on when translating the value from the database.  The UP_LAYOUT_PARAM.STRUCT_PARM_VAL column in our database is of the "ntext" type, which is equivalent to a CLOB in MS SQL.  The *actual* exported param node should look like:

 

<param>

<name>dlm:origin</name>

<value>u27l1s11</value>

</param>

 

The attached patch corrects this problem by performing a type lookup using the ResultSetMetaData, and if the column value is a CLOB it will perform the proper value conversion to a String.  Please take a look and let me know if you think this should be committed back to the project, or if it might be modified to achieve the same goal using a better method.  If it looks okay, I can go ahead and commit it.

 

Best,

 

--Chris

 

--

[ c h r i s d o y l e ]

Johns Hopkins University

Sr. System Software Engineer, IT@JH

 

"Eleven. Exactly. One louder."

 

[Andrew Wills]

Chris,

Awesome patch, please check it in. Here's an issue ticket for you:
http://code.google.com/p/cernunnos/issues/detail?id=58

Please commit to both 1-0-0-patches and trunk.

Thanks again!

drew wills

[[ c h r i s d o y l e ]]

Done, committed to trunk and rel-1-0-0-patches branch, and issue
status updated to Fixed!

--Chris

On Nov 6, 5:50 pm, "Andrew Wills" <wills.d...@gmail.com> wrote:
> Chris,
>
> Awesome patch, please check it in.  Here's an issue ticket for you:http://code.google.com/p/cernunnos/issues/detail?id=58
>
> Please commit to both 1-0-0-patches and trunk.
>
> Thanks again!
>
> drew wills
>
> On Thu, Nov 6, 2008 at 3:08 PM, [ d i t h e r f i x ]
>