Re: Oracle CLOB patch

Thanks for sharing this info, we too have had that problem for a  
while now with our apps running on Oracle, we changed our property  
table to use Clob but then we got some issues and ended up with a  
corrupt Slide database. Hope we can try again on a test server with  
your patch :D

best regards
Eirikur, Idega Software


On Apr 20, 2007, at 7:06 AM, Justin Lipton wrote:

> Hi,
>
> We often use the Oracle as a store. Some of our properties are  
> quite long >4000 bytes so we changed the PROPERTY_VALUE field to be  
> a CLOB.
> However we were still getting errors  ( ORA-01461: can bind a LONG  
> value only for insert into a LONG column).
> It turns out that the temporary table being created in Oracle was  
> causing this error - so splitting the SQL into a SELECT than an  
> INSERT provided the solution.
>
> Here's the patch we used (we're actually using the 2.1 branch but  
> this patch should work against the HEAD).
>
> Regards,
> Justin.
>
> --- StandardRDBMSAdapter.java   2007-04-20 16:53:27.000000000 +1000
>
> +++ StandardRDBMSAdapter2.java  2007-04-20 16:58:15.000000000 +1000
>
> @@ -640,28 +640,54 @@
>
>         }
>
>     }
>
> -    public void createRevisionDescriptor(Connection connection,  
> Uri uri, NodeRevisionDescriptor revisionDescriptor)
>
> +        public void createRevisionDescriptor(Connection  
> connection, Uri uri, NodeRevisionDescriptor revisionDescriptor)
>
>         throws ServiceAccessException {
>
>         PreparedStatement statement = null;
>
>         try {
>
>             assureVersionInfo(connection, uri, revisionDescriptor);
>
> -            createVersionLabels(connection, uri, revisionDescriptor);
>
> +
>
> +            for (Enumeration labels =  
> revisionDescriptor.enumerateLabels(); labels.hasMoreElements();) {
>
> +                long labelId = assureLabelId(connection, (String)  
> labels.nextElement());
>
> +                try {
>
> +                    statement =
>
> +                        connection.prepareStatement(
>
> +                            "insert into VERSION_LABELS  
> (VERSION_ID, LABEL_ID) select VERSION_ID, ? from VERSION_HISTORY  
> vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and  
> vh.REVISION_NO = ?");
>
> +                    statement.setLong(1, labelId);
>
> +                    statement.setString(2, uri.toString());
>
> +                    statement.setString(3,  
> revisionDescriptor.getRevisionNumber().toString());
>
> +                    statement.executeUpdate();
>
> +                } finally {
>
> +                    close(statement);
>
> +                }             }
>
>             for (Enumeration properties =  
> revisionDescriptor.enumerateProperties(); properties.hasMoreElements 
> ();) {
>
> +                NodeProperty property = (NodeProperty)  
> properties.nextElement();
>
> +                // Broken into 2 parts...a JML 20070416
>
> +                // to prevent Oracle temp table data error
>
> +                long vid = 0;
>
>                 try {
>
> -                    NodeProperty property = (NodeProperty)  
> properties.nextElement();
>
>                     statement =
>
>                         connection.prepareStatement(
>
> -                            "insert into PROPERTIES  
> (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_T 
> YPE,IS_PROTECTED) select vh.VERSION_ID, ?, ?, ?, ?, ? from  
> VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and  
> u.URI_STRING = ? and vh.REVISION_NO = ?");
>
> -                    int protectedProperty = property.isProtected 
> () ? 1 : 0;
>
> -                    statement.setString(1, property.getNamespace());
>
> -                    statement.setString(2, property.getName());
>
> -                    statement.setString(3, property.getValue 
> ().toString());
>
> -                    statement.setString(4, property.getType());
>
> -                    statement.setInt(5, protectedProperty);
>
> -                    statement.setString(6, uri.toString());
>
> -                    statement.setString(7,  
> revisionDescriptor.getRevisionNumber().toString());
>
> +                            "select vh.VERSION_ID from  
> VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and  
> u.URI_STRING = ? and vh.REVISION_NO = ?");
>
> +                    statement.setString(1, uri.toString());
>
> +                    statement.setString(2,  
> revisionDescriptor.getRevisionNumber().toString());
>
> +                    ResultSet res = statement.executeQuery();
>
> +                    if (res.next()) {
>
> +                        vid = res.getLong(1);
>
> +                    } else {
>
> +                         throw new ServiceAccessException(service,  
> "URI and version not found "+uri.toString()+" "+  
> revisionDescriptor.getRevisionNumber().toString());
>
> +                    }
>
> +                } finally {
>
> +                    close(statement);
>
> +                }
>
> +                 try {                     statement =  
> connection.prepareStatement("insert into PROPERTIES VALUES  
> (?, ?, ?, ?, ?, ?)");
>
> +                    int protectedProperty = property.isProtected 
> () ? 1 : 0;                     statement.setLong(1, vid);
>
> +                    statement.setString(2, property.getNamespace());
>
> +                    statement.setString(3, property.getName());
>
> +                    statement.setString(4, property.getValue 
> ().toString());
>
> +                    statement.setString(5, property.getType());
>
> +                    statement.setInt(6, protectedProperty);
>
>                     statement.executeUpdate();
>
>                 } finally {
>
>                     close(statement);
>
> @@ -671,7 +697,8 @@
>
>             throw createException(e, uri.toString());
>
>         }
>
>     }
>
> -
>
> +
>
> +
>
>     public void removeRevisionContent(Connection connection, Uri  
> uri, NodeRevisionDescriptor revisionDescriptor)
>
>         throws ServiceAccessException {
>
>         try {
>
>                                                                        
>                                               79,1          Bot
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>