Oracle CLOB patch

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_TYPE,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