You are viewing a plain text version of this content. The canonical link for it is here.
Posted to slide-user@jakarta.apache.org by Justin Lipton <ju...@ns.xn.com.au> on 2007/04/20 09:06:43 UTC
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
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
Re: Oracle CLOB patch
Posted by Eirikur Hrafnsson <ei...@idega.is>.
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: slide-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: slide-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org