[Dspace-tech] Oracle-using DSpace installations: I'm seeking help debugging DS-1435
Forwarding to DSpace-tech mail list, at the suggestion of Helix84 (thanks!). On 1/16/13 4:10 PM, Pottinger, Hardy J. pottinge...@missouri.edu wrote: Hi, I'm working on trying to find a solution for DS-1435 (https://jira.duraspace.org/browse/DS-1435) which is the bug preventing DSpace 3.0 from running with an Oracle database. I have added comments to the ticket to track my work. If anyone out there is also working on this bug, I urge you to share information in a comment on this ticket. Even something as simple as a confirmation that you, too, also cannot get DSpace 3.0 to work with an Oracle database, would be helpful. Also, if you're in a position to chip in a few cycles to try to work on this bug, Oracle makes a vbox image available here: http://www.oracle.com/technetwork/community/developer-vm/index.html And this image in particular could be very helpful, as it contains everything you'd need to get a dev instance of DSpace running, including a copy of Netbeans: http://www.oracle.com/technetwork/middleware/weblogic/downloads/weblogic-d e veloper-vm-303434.html -- HARDY POTTINGER pottinge...@umsystem.edu University of Missouri Library Systems http://lso.umsystem.edu/~pottingerhj/ https://MOspace.umsystem.edu/ Debug only code. Comments lie. -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
Re: [Dspace-tech] Oracle and DSpace 1.5.1 and ILIKE (again)
Hi, I thought I'd bump this, and try a rephrase. See below for details, but I'd really like to know if anyone is using DSpace 1.5.1, with Oracle as the back end db, and the XMLUI as an interface? If so, I'd like to know if you've encountered the behavior detailed below, and if so, are you living with the behavior, or did you come up with a solution? Our approach has been to use XMLUI as a public interface and JSPUI as our admin interface. But, I'd love to have the XMLUI as an option for an admin interface. --Hardy -Original Message- From: Pottinger, Hardy J. [mailto:pottinge...@umsystem.edu] Sent: Thursday, April 16, 2009 5:06 PM To: dspace-tech@lists.sourceforge.net Subject: [Dspace-tech] Oracle and DSpace 1.5.1 and ILIKE (again) Hi, we are currently running DSpace 1.5.1 (release install, not source) in production, with Oracle as our back end. A message thread from March 25, 2008 to the dspace-tech list identifies the same problem we're having (Oracle doesn't support PostgrSQL's ILIKE operator), and posts a solution (DB-agnostic SQL replacement for the ILIKE operator). I also see that SVN commit 2870, also made on March 25, asserts to solve this problem. However, in our experience, running 1.5.1, we still see this error. A recap: in the XMLUI, when one clicks the People link under Access Control, the following exception is thrown: java.sql.SQLException: ORA-00920: invalid relational operator if you look in the trace, the source of the exception is: at org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326) So, my question is, did SVN commit 2870 get rolled back before 1.5.1 was released? To work around this issue, we've been using the JSPUI as an admin interface. Which actually is going to come in handy since we're in the process of implementing the Minho stats add-on, and that only supports a JSPUI interface. However, it has always bugged me that a big part of the XMLUI admin interface just dies whenever we try to use it. Are there any other institutions running DSpace and using Oracle for a back end that have noticed this behavior? What was your solution? -- HARDY POTTINGER pottinge...@umsystem.edu University of Missouri Library Systems http://lso.umsystem.edu/~hardy/ No matter how far down the wrong road you've gone, turn back. --Turkish proverb -- Stay on top of everything new and different, both inside and around Java (TM) technology - register by April 22, and save $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. 300 plus technical and hands-on sessions. Register today. Use priority code J9JMT32. http://p.sf.net/sfu/p ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech -- Stay on top of everything new and different, both inside and around Java (TM) technology - register by April 22, and save $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. 300 plus technical and hands-on sessions. Register today. Use priority code J9JMT32. http://p.sf.net/sfu/p ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
[Dspace-tech] Oracle and DSpace 1.5.1 and ILIKE (again)
Hi, we are currently running DSpace 1.5.1 (release install, not source) in production, with Oracle as our back end. A message thread from March 25, 2008 to the dspace-tech list identifies the same problem we're having (Oracle doesn't support PostgrSQL's ILIKE operator), and posts a solution (DB-agnostic SQL replacement for the ILIKE operator). I also see that SVN commit 2870, also made on March 25, asserts to solve this problem. However, in our experience, running 1.5.1, we still see this error. A recap: in the XMLUI, when one clicks the People link under Access Control, the following exception is thrown: java.sql.SQLException: ORA-00920: invalid relational operator if you look in the trace, the source of the exception is: at org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326) So, my question is, did SVN commit 2870 get rolled back before 1.5.1 was released? To work around this issue, we've been using the JSPUI as an admin interface. Which actually is going to come in handy since we're in the process of implementing the Minho stats add-on, and that only supports a JSPUI interface. However, it has always bugged me that a big part of the XMLUI admin interface just dies whenever we try to use it. Are there any other institutions running DSpace and using Oracle for a back end that have noticed this behavior? What was your solution? -- HARDY POTTINGER pottinge...@umsystem.edu University of Missouri Library Systems http://lso.umsystem.edu/~hardy/ No matter how far down the wrong road you've gone, turn back. --Turkish proverb -- Stay on top of everything new and different, both inside and around Java (TM) technology - register by April 22, and save $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. 300 plus technical and hands-on sessions. Register today. Use priority code J9JMT32. http://p.sf.net/sfu/p ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
[Dspace-tech] Oracle and DSpace 1.5 and ILIKE
Hi -
I'm trying to install DSpace 1.5 using Oracle. It wasn't until
yesterday that I saw this in the KNOWN_BUGS file:
Oracle compatibility: this release does not support Oracle. Check for
patches or updates to this release to restore Oracle compatibility.
But nevertheless, I thought I had read that people had done successful
installs using Oracle, and I wound up also getting it installed OK, but
now I'm running into a problem in the eperson code when I click the
People link under Access Control.
Specifically, I get this exception:
java.sql.SQLException: ORA-00920: invalid relational operator
and looking through the stack trace, it appears to be coming from
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
where it says
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE
eperson_id = ? OR +
firstname ILIKE ? OR lastname ILIKE ? OR email
ILIKE ?,
new Object[]
{int_param,dbquery,dbquery,dbquery});
I'm pretty sure ILIKE doesn't work in Oracle, but I see this in the
1.4.2 code and I'm pretty sure I was able t create some users in that.
Does anybody have any idea about what's wrong here?
Thanks,
Rick Runyan
-
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
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] Oracle and DSpace 1.5 and ILIKE
Hi,
I think it worked in 1.4.2 only if you used the default jspui interface
(it doesn't call searchResultCount). Manakin/xmlui on the other hand
does call it, so it breaks.
I altered the code to be oracle friendly:
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE eperson_id = ? OR +
lower(firstname) LIKE ? OR lower(lastname) LIKE ? OR lower(email) LIKE ?,
new Object[] {int_param,dbquery,dbquery,dbquery});
I assume that would work in postgres as well, but I haven't tested it.
You would also have to make a similar change in Group.java as well.
I haven't looked too closely at 1.5, but it works in 1.4.2.
Hope this helps.
-Greg
Rick Runyan wrote:
Hi –
I’m trying to install DSpace 1.5 using Oracle. It wasn’t until
yesterday that I saw this in the KNOWN_BUGS file:
“Oracle compatibility: this release does not support Oracle. Check for
patches or updates to this release to restore Oracle compatibility.”
But nevertheless, I thought I had read that people had done successful
installs using Oracle, and I wound up also getting it installed OK,
but now I’m running into a problem in the eperson code when I click
the “People” link under “Access Control.”
Specifically, I get this exception:
java.sql.SQLException: ORA-00920: invalid relational operator
and looking through the stack trace, it appears to be coming from
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
where it says
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE eperson_id = ? OR +
firstname ILIKE ? OR lastname ILIKE ? OR email ILIKE ?,
new Object[] {int_param,dbquery,dbquery,dbquery});
I’m pretty sure “ILIKE” doesn’t work in Oracle, but I see this in the
1.4.2 code and I’m pretty sure I was able t create some users in that.
Does anybody have any idea about what’s wrong here?
Thanks,
Rick Runyan
-
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
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 2008.
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] Oracle and DSpace 1.5 and ILIKE
Cool. A fix *and* and explanation! How can you beat that?
By the way, if you're actually fixing the real code, there are more
ILIKEs in
public static EPerson[] search(Context context, String query, int
offset, int limit)
Thanks,
- Rick
-Original Message-
From: Greg McClellan [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 25, 2008 4:31 PM
To: Rick Runyan
Cc: dspace-tech@lists.sourceforge.net
Subject: Re: [Dspace-tech] Oracle and DSpace 1.5 and ILIKE
Hi,
I think it worked in 1.4.2 only if you used the default jspui interface
(it doesn't call searchResultCount). Manakin/xmlui on the other hand
does call it, so it breaks.
I altered the code to be oracle friendly:
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE eperson_id = ? OR +
lower(firstname) LIKE ? OR lower(lastname) LIKE ? OR lower(email) LIKE
?,
new Object[] {int_param,dbquery,dbquery,dbquery});
I assume that would work in postgres as well, but I haven't tested it.
You would also have to make a similar change in Group.java as well.
I haven't looked too closely at 1.5, but it works in 1.4.2.
Hope this helps.
-Greg
Rick Runyan wrote:
Hi -
I'm trying to install DSpace 1.5 using Oracle. It wasn't until
yesterday that I saw this in the KNOWN_BUGS file:
Oracle compatibility: this release does not support Oracle. Check for
patches or updates to this release to restore Oracle compatibility.
But nevertheless, I thought I had read that people had done successful
installs using Oracle, and I wound up also getting it installed OK,
but now I'm running into a problem in the eperson code when I click
the People link under Access Control.
Specifically, I get this exception:
java.sql.SQLException: ORA-00920: invalid relational operator
and looking through the stack trace, it appears to be coming from
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
where it says
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE eperson_id = ? OR +
firstname ILIKE ? OR lastname ILIKE ? OR email ILIKE ?,
new Object[] {int_param,dbquery,dbquery,dbquery});
I'm pretty sure ILIKE doesn't work in Oracle, but I see this in the
1.4.2 code and I'm pretty sure I was able t create some users in that.
Does anybody have any idea about what's wrong here?
Thanks,
Rick Runyan
-
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
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 2008.
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] Oracle and DSpace 1.5 and ILIKE
On Tue, Mar 25, 2008 at 03:09:43PM -0400, Rick Runyan wrote:
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
where it says
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE
eperson_id = ? OR +
firstname ILIKE ? OR lastname ILIKE ? OR email
ILIKE ?,
new Object[]
{int_param,dbquery,dbquery,dbquery});
I'm pretty sure ILIKE doesn't work in Oracle, but I see this in the
1.4.2 code and I'm pretty sure I was able t create some users in that.
Does anybody have any idea about what's wrong here?
ILIKE is not SQL; it's a PostgreSQL extension. It's a
case-insensitive version of LIKE (which *is* SQL). The query could
probably be rewritten as:
SELECT count(*) as count FROM eperson
WHERE eperson_id = ?
OR LOWER(firstname) LIKE LOWER(?)
OR LOWER(lastname) LIKE LOWER(?)
OR LOWER(email) LIKE LOWER(?)
or as:
SELECT count(*) as count FROM eperson
WHERE eperson_id = ?
OR LOWER(firstname) LIKE ?
OR LOWER(lastname) LIKE ?
OR LOWER(email) LIKE ?
if dbquery is guaranteed to be lowercase already. Possibly these
queries would be slightly slower than what we have now, but I would
suggest that this is a place where a PGism should be sacrificed for
portability.
--
Mark H. Wood, Lead System Programmer [EMAIL PROTECTED]
Typically when a software vendor says that a product is intuitive he
means the exact opposite.
pgpv35eb2RN8Q.pgp
Description: PGP signature
-
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
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] Oracle and DSpace 1.5 and ILIKE
Rick,
Thank you for the bug report. Unfortunately this patch did not make it
into the 1.5 release, which occurred earlier (before the original
email). The announcement of the release will be going out tomorrow
morning. The release has been picked up and is in the Central Maven
Repository and they have a strict policy about not allowing any
release to be revoked. The central repository plays a critical role in
maven build process, when you build DSpace with maven it will check
the repository for a specific version of a software package. Thus,
when someone downloads the DSpace release distribution (i.e. not the
source code) and runs 'mvn package' they will receive the version we
have already published into the central repository. This release of
DSpace is huge, by far the biggest release dspace has ever seen. Bugs
similar to this will likely be found in other less commonly tested
areas of DSpace in the coming few weeks. I expect there will be a
1.5.1 bug fix release soon.
This highlights how important it is for as many people as possible to
participate in the test-a-thon process, especially for those
administrators who use uncommon services such as Oracle, LDAP,
Shibboleth, or SRB. It is impossible for a developer to test these
services without access to: an Oracle database, an LDAP server, a
Shibboleth federation, or an SRB storage account. These options will
only receive as much testing as is provided by the community which
supports these options.
Until 1.5.1 is released, the work arounds available are an SVN
checkout or applying a patch. You can use the source version available
from SVN (dspace-1_5_x r2870) or apply the patch following this email.
As for a technical comment on the actual bug, there are other oracle
compatibility errors in that SQL statement beyond the ILIKE operator
in the statements that follow. The next sql statement issued after
this uses LIMIT and OFFSET, which are not supported by Oracle either.
Graham has posted a potential fix for this issue into the 1.5 branch,
however it has not been tested and may introduce pagination errors
into the UI.
Scott--
On Mar 25, 2008, at 2:09 PM, Rick Runyan wrote:
Hi –
I’m trying to install DSpace 1.5 using Oracle. It wasn’t until
yesterday that I saw this in the KNOWN_BUGS file:
“Oracle compatibility: this release does not support Oracle. Check
for patches or updates to this release to restore Oracle
compatibility.”
But nevertheless, I thought I had read that people had done
successful installs using Oracle, and I wound up also getting it
installed OK, but now I’m running into a problem in the eperson code
when I click the “People” link under “Access Control.”
Specifically, I get this exception:
java.sql.SQLException: ORA-00920: invalid relational operator
and looking through the stack trace, it appears to be coming from
org.dspace.eperson.EPerson.searchResultCount(EPerson.java:326)
where it says
TableRow row = DatabaseManager.querySingle(context,
SELECT count(*) as count FROM eperson WHERE
eperson_id = ? OR +
firstname ILIKE ? OR lastname ILIKE ? OR
email ILIKE ?,
new Object[]
{int_param,dbquery,dbquery,dbquery});
I’m pretty sure “ILIKE” doesn’t work in Oracle, but I see this in
the 1.4.2 code and I’m pretty sure I was able t create some users in
that. Does anybody have any idea about what’s wrong here?
Thanks,
Rick Runyan
-
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
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
Index: dspace-api/src/main/java/org/dspace/browse/BrowseDAOOracle.java
===
--- dspace-api/src/main/java/org/dspace/browse/BrowseDAOOracle.java
(revision 2869)
+++ dspace-api/src/main/java/org/dspace/browse/BrowseDAOOracle.java
(revision 2870)
@@ -887,7 +887,7 @@
params.add(new Integer(limit));
}
-if (offset -1)
+if (offset 0)
{
queryBuf.insert(0, SELECT * FROM ();
queryBuf.append() WHERE rnum?);
Index: dspace-api/src/main/java/org/dspace/eperson/EPerson.java
===
--- dspace-api/src/main/java/org/dspace/eperson/EPerson.java (revision
2869)
+++ dspace-api/src/main/java/org/dspace/eperson/EPerson.java (revision
2870)
@@ -253,14 +253,48 @@
throws SQLException
{
String params = %+query.toLowerCase()+%;
- String dbquery = SELECT * FROM eperson WHERE eperson_id = ? OR
+
- firstname ILIKE
Re: [Dspace-tech] Oracle and dspace
On Fri, 2007-04-13 at 09:37 -0500, Hulbert, Linda A. wrote: The University has begun a project to implement dspace using Oracle. I have heard differing levels of success (mostly not) using Oracle. While I am not an IT person, I will be passing any comments on to our IT and web services colleagues. Ha anyone out there successfully launched dspace using Oracle? Hi Linda, Yes, we are running a number of installations against an Oracle database. Currently these are all based off of version 1.3.2 of DSpace - although as I wasn't personally involved in their setup, I can't comment to what extent we had to alter the code (I do know that there are a number of changes related to our specific requirements, that wouldn't affect others). Unfortunately, the current 1.4.1 release does not support Oracle properly, but the good news is that a number of fixes have been accepted into the code that will be released fairly soon as 1.4.2. There is still testing taking place on this code, but so far I am not aware of any Oracle related problems with the upcoming release, and it *should* just install and run successfully. G -- Graham Triggs Technical Architect Open Repository Tel: +44 (0)20 7631 9942 Skype: grahamtriggs This e-mail is confidential and should not be used by anyone who is not the original intended recipient. BioMed Central Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of BioMed Central Limited. No contracts may be concluded on behalf of BioMed Central Limited by means of e-mail communication. BioMed Central Limited Registered in England and Wales with registered number 3680030 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB - This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ ___ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
[Dspace-tech] Oracle and dspace
The University has begun a project to implement dspace using Oracle. I have heard differing levels of success (mostly not) using Oracle. While I am not an IT person, I will be passing any comments on to our IT and web services colleagues. Ha anyone out there successfully launched dspace using Oracle? Thanks so much for your time. Linda Linda Hulbert, Associate Director Technical Services O'Shaughnessy-Frey Library #5004 University of Saint Thomas 2115 Summit Avenue St. Paul, MN 55105 Phone: (651) 962-5016 Fax: (651) 962-5486email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Edison said: Opportunity is missed by most people because it is dressed in overalls and looks like work. image001.gif Description: GIF image - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.phpp=sourceforgeCID=DEVDEV___ DSpace-tech mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/dspace-tech
