[Dspace-tech] Best practices for scripted metadata modifications
We have a few metadata maintenance related tasks (e.g. scan through the metadata of items changed since last run and make sure the metadata complies to certain rules) that we want to automate with scheduled tasks. As I see it we have the following options: - shell / perl scripts that use psql and finish by running index-all - standalone java applications that use jdbc to modify database contents - java applications based on the DSpace API I guess using the API would be the safest way to access the metadata, but I'm wondering if it's overkill for operations that could easily be carried out with a small number of relatively simple SQL statements in a script. On the other hand, bypassing the API and tweaking database contents manually carries the risk of screwing stuff up unintentionally, unless one's 100% sure about the scripts.. Any thoughts about this? Some sample code for how to use the API would also come in handy, if someone happens to have something they want to share. Best regards Mathias Hjelt - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] Database question
Hello Mika, this error says, you have duplicated values in a column with unique index. For DSpace 1.4 you can check all unique index as follow: select name, count(*) from epersongroup group by name having count(*)1; select short_description, count(*) from BitstreamFormatRegistry group by short_description having count(*)1; select email, count(*) from eperson group by email having count(*)1; select namespace, count(*) from MetadataSchemaRegistry group by namespace having count(*)1; select short_id, count(*) from MetadataSchemaRegistry group by short_id having count(*)1; select handle, count(*) from Handle group by handle having count(*)1; select item_id, count(*) from WorkflowItem group by item_id having count(*)1; select email, count(*) from RegistrationData group by email having count(*)1; select checksum, count(*) from History group by checksum having count(*)1; You result for the queries should be always 0 rows. But NULL Values are not equal, so you can have multiple rows for a unique index with NULL values. For example in our Database we have 6 rows in table History with checksum NULL. select checksum, count(*) from History group by checksum having count(*)1; checksum | count --+--- | 6 (1 row) select * from history where checksum is null; history_id | creation_date | checksum +---+-- 467492 | | 467493 | | 467494 | | 467500 | | 467501 | | 467502 | | (6 rows) Maybe that helps Bernadette Mika Stenberg schrieb: While reindexing Dspace database I get following error. Wonder whats causing this and how could I fix this? reindexdb: reindexing of database dspace failed: ERROR: could not create unique index DETAIL: Table contains duplicated values. -Mika - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] Best practices for scripted metadata modifications
On Tue, Sep 11, 2007 at 09:39:05AM +0300, Mathias Hjelt wrote: I guess using the API would be the safest way to access the metadata, but I'm wondering if it's overkill for operations that could easily be carried out with a small number of relatively simple SQL statements in a script. On the other hand, bypassing the API and tweaking database contents manually carries the risk of screwing stuff up unintentionally, unless one's 100% sure about the scripts.. Any thoughts about this? Yes, using the DSpace API would be the officially recommended route. Writing scripts would probably be much faster (in terms of development time), but support would be more difficult and you would have to have a lot of confidence that you got it right :) Something else to consider is that if you write scripts, you would need to check that they still worked every time you updated your DSpace installation (which isn't going to be very often, presumably). I don't have any code samples, but depending on what you're doing (and which version of DSpace you're using), the DSpace Developer guide by Richard Jones may be of interest: http://wiki.dspace.org/static_files/7/7d/DevelopersDocumentation.pdf cheers, Jim -- James Rutherford | Hewlett-Packard Limited registered Office: Research Engineer | Cain Road, HP Labs | Bracknell, Bristol, UK | Berks +44 117 312 7066 | RG12 1HN. [EMAIL PROTECTED] | Registered No: 690597 England The contents of this message and any attachments to it are confidential and may be legally privileged. If you have received this message in error, you should delete it from your system immediately and advise the sender. To any recipient of this message within HP, unless otherwise stated you should consider this message and attachments as HP CONFIDENTIAL. - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] [Dspace-general] Failure on new 1.4.2 ant fresh_install
Hello, Am 11.09.2007 um 09:31 schrieb Munro, LG, Mr [EMAIL PROTECTED]: Hi George, Ant is on version 1.6.5-3 and apache on 2.0.55-4 Java version is: java-1.5.0-sun Any ideas? I reread your Error message. ant is not capable to solve the regular expression on line 88 of build.xml. Before, I pondered why it fails with such a simple regex. But then, it tells you: It could not find a suitable regexp matcher. The architecture of the tool is modular and it requires some supporting libs. To ensure that everything is fine, ant has a switch for testing the environment. Run ant -diagnostics. There should be listed something like this: /Your/path/to/apache-ant/lib/ant-apache-regexp.jar I bet the diagnostics will report problems and you can solve them by running aptitude to either upgrade or uninstall and reinstall ant. Please post back to the list using shift-reply. Bye, Christian - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] Problem with statistics
Hi, Well, the relevant part of this is the only thing that's not in English!! But the context is clear enough - it's having trouble parsing a valid date. Can you confirm that the start / end dates that you are passing to the script are of the form '-mm-dd'. If your input is valid, then you've got a problem with one of the metadata value fields. You'll need to check the text_value of all the dc.date.accessioned entries (probably metadata_field_id=11). It looks like the offending item is of the correct format (-mm-ddThh:mi:ssZ), but contains invalid data. G On Mon, 2007-09-10 at 08:49 +, Andrea Garrido Fernández wrote: Hi: I have a problem when I try to execute the perl scripts for generating statistics. When I try to run the LogAnalyser class with a start or end date, I have an error like this: java.sql.SQLException: ORA-01841: el valor (completo) del año debe estar entre -4713 y +, y no debe ser igual a 0 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:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:259) at org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:384) at org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:395) at org.dspace.app.statistics.LogAnalyser.getNumItems(LogAnalyser.java:1264) at org.dspace.app.statistics.LogAnalyser.processLogs(LogAnalyser.java:535) at org.dspace.app.statistics.LogAnalyser.main(LogAnalyser.java:327) -- I have tried many things, incluiding the solution given in this forum before, using the REGEXP_REPLACE , but I find it impossible because oracle don´t recognize this expression. Colud yo help me, please? I am a bit lost. Thanks, Andrea _ Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio con MSN Entretenimiento. http://entretenimiento.msn.es/ - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech This email has been scanned by Postini. For more information please visit http://www.postini.com - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
[Dspace-tech] Cannot view thumbnails
Hi, with ItemImporter tool i specify a jpg bitstream to belong to ORIGINAL bundle and a second image to THUMBNAILS bundle. The ingesting works fine but the thumbnail is not shown. Has anyone any suggestion or idea why this happens? A have set in dspace.cfg file the following properties: webui.browse.thumbnail.show = true webui.browse.thumbnail.maxheight = 150 webui.browse.thumbnail.maxwidth = 150 webui.item.thumbnail.show = true webui.browse.thumbnail.linkbehaviour = item Thanks- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
[Dspace-tech] DSpace OAI
If I restrict access to a collection / community in DSpace, does the restrictions also apply on the data that can be accessed through OAI? Thanks, Mika - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] DSpace OAI
Hi Mika No, as things stand the OAI interface will show all items regardless of any access restrictions you set. Cheers Paul Paul A S Needham Electronic Information Specialist Kings Norton Library Cranfield University -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mika Stenberg Sent: 11 September 2007 10:41 To: 'dspace-tech@lists.sourceforge.net' Subject: [Dspace-tech] DSpace OAI If I restrict access to a collection / community in DSpace, does the restrictions also apply on the data that can be accessed through OAI? Thanks, Mika - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] DSpace OAI
Hi Mika, no, the metadata of every item is visible through OAI regardless of restrictions. Claudia Mika Stenberg schrieb: If I restrict access to a collection / community in DSpace, does the restrictions also apply on the data that can be accessed through OAI? Thanks, Mika - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
Re: [Dspace-tech] Problem with statistics
Sorry, I have realized that the query that doesn't work is : SELECT COUNT(*) AS num FROM item WHERE in_archive = 1 AND withdrawn = 0 AND item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') AND TO_TIMESTAMP( TO_CHAR(text_value), '-mm-ddThh24:mi:ssZ' ) TO_DATE('2007-09-01', '-MM-dd') AND TO_TIMESTAMP( TO_CHAR(text_value), '-mm-ddThh24:mi:ssZ' ) TO_DATE('2007-10-21', '-MM-dd') ) AND item_id IN ( SELECT item_id FROM metadatavalue WHERE text_value LIKE '%Thesis or Dissertation%' AND metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type' AND qualifier IS NULL) ) This query gives me error in dspace and in my sql+ worksheet. Sorry and thank yoy very much, Andrea Garrido From: Graham Triggs [EMAIL PROTECTED] To: Andrea Garrido Fernández [EMAIL PROTECTED] CC: dspace-tech@lists.sourceforge.net Subject: Re: [Dspace-tech] Problem with statistics Date: Tue, 11 Sep 2007 09:42:50 +0100 Hi, Well, the relevant part of this is the only thing that's not in English!! But the context is clear enough - it's having trouble parsing a valid date. Can you confirm that the start / end dates that you are passing to the script are of the form '-mm-dd'. If your input is valid, then you've got a problem with one of the metadata value fields. You'll need to check the text_value of all the dc.date.accessioned entries (probably metadata_field_id=11). It looks like the offending item is of the correct format (-mm-ddThh:mi:ssZ), but contains invalid data. G On Mon, 2007-09-10 at 08:49 +, Andrea Garrido Fernández wrote: Hi: I have a problem when I try to execute the perl scripts for generating statistics. When I try to run the LogAnalyser class with a start or end date, I have an error like this: java.sql.SQLException: ORA-01841: el valor (completo) del año debe estar entre -4713 y +, y no debe ser igual a 0 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:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:259) at org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:384) at org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:395) at org.dspace.app.statistics.LogAnalyser.getNumItems(LogAnalyser.java:1264) at org.dspace.app.statistics.LogAnalyser.processLogs(LogAnalyser.java:535) at org.dspace.app.statistics.LogAnalyser.main(LogAnalyser.java:327) -- I have tried many things, incluiding the solution given in this forum before, using the REGEXP_REPLACE , but I find it impossible because oracle don´t recognize this expression. Colud yo help me, please? I am a bit lost. Thanks, Andrea _ Dale rienda suelta a tu tiempo libre. Mil ideas para exprimir tu ocio con MSN Entretenimiento. http://entretenimiento.msn.es/ - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech This email has been scanned by Postini. For more information please visit http://www.postini.com _ Acepta el reto MSN Premium: Correos más divertidos con fotos y textos increíbles en MSN
Re: [Dspace-tech] Problem with statistics
Andrea, It looks like this is an optimizer issue in Oracle - ie. that it is applying the TO_TIMESTAMP to all the records first, rather than just those that match the metadata_field_id. I've tried rewriting the query so that it uses an inline view to obtain all the metadata records for the date accessioned field, and then apply the TO_TIMESTAMP to that... but bizarrely that doesn't seem to be working either. For now, I suggest that you separate out: SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned' As a distinct query, store the result in a variable, and then replace the: metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') with a ...metadata_field_id = + variable + ... That should at least make it work. File a bug report, and add list a reference to it under Bug Fixes / To Do here: http://wiki.dspace.org/index.php/Next_Release_Status and I'll address it properly for the 1.5 release when I check / add-in the Oracle support. G On Tue, 2007-09-11 at 10:32 +, Andrea Garrido Fernández wrote: Sorry, I have realized that the query that doesn't work is : SELECT COUNT(*) AS num FROM item WHERE in_archive = 1 AND withdrawn = 0 AND item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') AND TO_TIMESTAMP( TO_CHAR(text_value), '-mm-ddThh24:mi:ssZ' ) TO_DATE('2007-09-01', '-MM-dd') AND TO_TIMESTAMP( TO_CHAR(text_value), '-mm-ddThh24:mi:ssZ' ) TO_DATE('2007-10-21', '-MM-dd') ) AND item_id IN ( SELECT item_id FROM metadatavalue WHERE text_value LIKE '%Thesis or Dissertation%' AND metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type' AND qualifier IS NULL) ) This query gives me error in dspace and in my sql+ worksheet. Sorry and thank yoy very much, Andrea Garrido From: Graham Triggs [EMAIL PROTECTED] To: Andrea Garrido Fernández [EMAIL PROTECTED] CC: dspace-tech@lists.sourceforge.net Subject: Re: [Dspace-tech] Problem with statistics Date: Tue, 11 Sep 2007 09:42:50 +0100 Hi, Well, the relevant part of this is the only thing that's not in English!! But the context is clear enough - it's having trouble parsing a valid date. Can you confirm that the start / end dates that you are passing to the script are of the form '-mm-dd'. If your input is valid, then you've got a problem with one of the metadata value fields. You'll need to check the text_value of all the dc.date.accessioned entries (probably metadata_field_id=11). It looks like the offending item is of the correct format (-mm-ddThh:mi:ssZ), but contains invalid data. G On Mon, 2007-09-10 at 08:49 +, Andrea Garrido Fernández wrote: Hi: I have a problem when I try to execute the perl scripts for generating statistics. When I try to run the LogAnalyser class with a start or end date, I have an error like this: java.sql.SQLException: ORA-01841: el valor (completo) del año debe estar entre -4713 y +, y no debe ser igual a 0 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:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1062) at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.dspace.storage.rdbms.DatabaseManager.query(DatabaseManager.java:259) at org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:384) at org.dspace.storage.rdbms.DatabaseManager.querySingle(DatabaseManager.java:395) at org.dspace.app.statistics.LogAnalyser.getNumItems(LogAnalyser.java:1264) at org.dspace.app.statistics.LogAnalyser.processLogs(LogAnalyser.java:535) at
Re: [Dspace-tech] Problem with statistics
Thank you, I´ve solved it! The problem was that in LogAnalyser.java there were two if clauses in a wrong order, i.e, I changed in the method public static Integer getNumItems(Context context, String type) these if clauses: if (type != null) { query.append( AND item_id IN ( + typeQuery + ) ); } if (startDate != null || endDate != null) { query.append( AND item_id IN ( + dateQuery.toString() + ) ); } This is the right order, before if (startDate != null || endDate != null) {. was first, so when you ran the query it gave the error because it was looking for in an empty set (or something similar). Thank yoy very much for your help, I will fix it as a bug. Best regards, Andrea From: Graham Triggs [EMAIL PROTECTED] To: Andrea Garrido Fernández [EMAIL PROTECTED] CC: dspace-tech@lists.sourceforge.net Subject: Re: [Dspace-tech] Problem with statistics Date: Tue, 11 Sep 2007 12:37:22 +0100 Andrea, It looks like this is an optimizer issue in Oracle - ie. that it is applying the TO_TIMESTAMP to all the records first, rather than just those that match the metadata_field_id. I've tried rewriting the query so that it uses an inline view to obtain all the metadata records for the date accessioned field, and then apply the TO_TIMESTAMP to that... but bizarrely that doesn't seem to be working either. For now, I suggest that you separate out: SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned' As a distinct query, store the result in a variable, and then replace the: metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') with a ...metadata_field_id = + variable + ... That should at least make it work. File a bug report, and add list a reference to it under Bug Fixes / To Do here: http://wiki.dspace.org/index.php/Next_Release_Status and I'll address it properly for the 1.5 release when I check / add-in the Oracle support. G On Tue, 2007-09-11 at 10:32 +, Andrea Garrido Fernández wrote: Sorry, I have realized that the query that doesn't work is : SELECT COUNT(*) AS num FROM item WHERE in_archive = 1 AND withdrawn = 0 AND item_id IN ( SELECT item_id FROM metadatavalue WHERE metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'date' AND qualifier = 'accessioned') AND TO_TIMESTAMP( TO_CHAR(text_value), '-mm-ddThh24:mi:ssZ' ) TO_DATE('2007-09-01', '-MM-dd') AND TO_TIMESTAMP( TO_CHAR(text_value), '-mm-ddThh24:mi:ssZ' ) TO_DATE('2007-10-21', '-MM-dd') ) AND item_id IN ( SELECT item_id FROM metadatavalue WHERE text_value LIKE '%Thesis or Dissertation%' AND metadata_field_id = ( SELECT metadata_field_id FROM metadatafieldregistry WHERE element = 'type' AND qualifier IS NULL) ) This query gives me error in dspace and in my sql+ worksheet. Sorry and thank yoy very much, Andrea Garrido From: Graham Triggs [EMAIL PROTECTED] To: Andrea Garrido Fernández [EMAIL PROTECTED] CC: dspace-tech@lists.sourceforge.net Subject: Re: [Dspace-tech] Problem with statistics Date: Tue, 11 Sep 2007 09:42:50 +0100 Hi, Well, the relevant part of this is the only thing that's not in English!! But the context is clear enough - it's having trouble parsing a valid date. Can you confirm that the start / end dates that you are passing to the script are of the form '-mm-dd'. If your input is valid, then you've got a problem with one of the metadata value fields. You'll need to check the text_value of all the dc.date.accessioned entries (probably metadata_field_id=11). It looks like the offending item is of the correct format (-mm-ddThh:mi:ssZ), but contains invalid data. G On Mon, 2007-09-10 at 08:49 +, Andrea Garrido Fernández wrote: Hi: I have a problem when I try to execute the perl scripts for generating statistics. When I try to run the LogAnalyser class with a start or end date, I have an error like this: java.sql.SQLException: ORA-01841: el valor (completo) del año debe estar entre -4713 y +, y no debe ser igual a 0 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:216) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966) at
[Dspace-tech] Problem with indexing 1.4.2 (update)
Hi... This is an update to the indexing problem that I wrote about a problem with DSpace 1.4.2 When I did an index-all, I got the following error: Indexing all Items in DSpaceERROR: Relation dctyperegistry does not exist Several people suggested that I had a CLASSPATH problem. Sure enough, in my .cshrc profile in my CLASSPATH I had /usr/local/tomcat/webapps/dspace/WEB-INF/classes and /usr/local/tomcat/webapps/dspace-oai/WEB-INF/classes AND since I had compiled my 1.4.2 release BUT had not uploaded to Tomcat, yet, the classes of the old version of DSpace were being called. Thanks to all those who wrote to me. Does anyone have any ideas as to what I'm doing wrong? *** George Kozak Coordinator Web Development and Management Digital Media Group 501 Olin Library Cornell University 607-255-8924 *** [EMAIL PROTECTED] - This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse012070mrt/direct/01/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech