[Dspace-tech] FW: HANDLE update issue

[McGee, Thomas A.]

I'm trying to update a lot of communities and collections that were set up prior to our HANDLE being assigned. So according to the "cookbook" I'm doing:

update-handle-prefix 123456789 10420 (10420 being the new HANDLE number)

After seeing how many items are being updated and selecting "yes," the all-too-familiar-by-now string of Java errors:

Have you taken a backup, and are you ready to continue? [y/n]: y
Updating handle table... 214 items updated
Updating metadatavalues table... Exception in thread "main" java.sql.SQLException: ORA-00911: invalid character

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at org.dspace.storage.rdbms.DatabaseManager.updateQuery(DatabaseManager.java:354)
at org.dspace.handle.UpdateHandlePrefix.main(UpdateHandlePrefix.java:97)

Is this YAOI (yet another Oracle incompatibility)? I'm tempted to go into the database and manually write some SQL commands to update the tables. Is that even an option? After the metadatavalues table is updated, what else has to be done?

_____________________________________
Tom McGee
Senior Digital Media Specialist
Seton Hall University
400 South Orange Ave., South Orange, NJ 07079
973.275.2992

[Stuart Lewis]

Hi Tom,

> I'm trying to update a lot of communities and collections that were set up
> prior to our HANDLE being assigned. So according to the "cookbook" I'm doing:
>
> update-handle-prefix 123456789 10420 (10420 being the new HANDLE number)
>
> After seeing how many items are being updated and selecting "yes," the
> all-too-familiar-by-now string of Java errors:
>

> Is this YAOI (yet another Oracle incompatibility)? I'm tempted to go into the
> database and manually write some SQL commands to update the tables. Is that
> even an option? After the metadatavalues table is updated, what else has to be
> done?

After the metadatavalues table has been updated, all that happens is the
search and browse indexes are rebuilt, but this can be run manually using
[dspace]/bin/index-update

The query that is failing is:

UPDATE metadatavalue SET text_value= (SELECT 'http://hdl.handle.net/' ||
handle FROM handle WHERE handle.resource_id=item_id AND
handle.resource_type_id=2) WHERE text_value LIKE 'http://hdl.handle.net/%';

If you are able to find out what is causing the error by running it
directly, please post your solution and we'll get the script updated.

Many thanks,


Stuart
_________________________________________________________________

Gwasanaethau Gwybodaeth Information Services
Prifysgol Aberystwyth Aberystwyth University

E-bost / E-mail: Stuart...@aber.ac.uk
Ffon / Tel: (01970) 622860
_________________________________________________________________

[François Parmentier]

I once rewrite the update-handle-prefix script to work with oracle, instead of postgresql (in version 1.4.2 of DSpace).

I mainly replaced
---8<---
    echo "update handle set handle=overlay(handle placing '$2' from 1 for $oldPrefixLen) where handle like '$1%';" | psql
    # update the metadatavalue table entries
    echo "update metadatavalue set text_value=overlay(text_value placing '$2' from 23 for $oldPrefixLen) where text_value like 'http://hdl.handle.net/$1%';" | psql
---8<---
with
---8<---
echo "update handle set handle='$2'||substr(handle,$oldPrefixLen+1) where handle like '$1%';" | sqlplus $DSPACE_CNXSTR
    # update the metadatavalue table entries
    echo "update metadatavalue set text_value='http://hdl.handle.net/'||'$2'||substr(text_value,23+$oldPrefixLen) where text_value like 'http://hdl.handle.net/$1%';" | sqlplus $DSPACE_CNXSTR
---8<---
where $DSPACE_CNXSTR is the connection string to Oracle.

This worked for me.

Only my 2 cents.
--
François PARMENTIER / INIST-CNRS

------------------------------------------------------------------------------
SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can't happen without you.  Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/
_______________________________________________
DSpace-tech mailing list
DSpac...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-tech

[McGee, Thomas A.]

Following up on my question of last week on the failed UPDATE queries for changing handles on an Oracle database, this is what happened.

 

Stuart Lewis suggested that the failing query was this:

UPDATE metadatavalue SET text_value= (SELECT 'http://hdl.handle.net/' || handle FROM handle WHERE handle.resource_id=item_id AND

handle.resource_type_id=2) WHERE  text_value LIKE 'http://hdl.handle.net/%';

 

When I ran that in the Oracle SQL Developer application, I got an error something like “no statement at cursor.” I simply deleted the semicolon and ran:

 

UPDATE metadatavalue SET text_value= (SELECT 'http://hdl.handle.net/' || handle FROM handle WHERE handle.resource_id=item_id AND

handle.resource_type_id=2) WHERE  text_value LIKE 'http://hdl.handle.net/%'

 

Which worked. Could that really be all that it is?

[Stuart Lewis [sdl]]

Hi Tom,

 

Would you mind trying an experiment for us? If you remove the semicolon from [dsapce-src]/dspace-api/src/main/java/org/dspace/handle/UpdateHandlePrefix.java run mvn package, and ant update, does the script [dspace]/bin/update-hanmdle-prefix then run OK?

 

I suspect it will fix the problem.

 

If you could confirm this, we’ll get it fixed ready for the next release of DSpace.

 

Thanks,

 

 

Stuart