Re: EJB3/JPA with Oracle
Hugh Allen <[email protected]> Wed, 30 Jul 2014 06:22:39 -0400 (GMT-04:00)
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <30118875.1406715759591.JavaMail.root@elwamui-darkeyed.atl.sa.earthlink.net> |
--===============3618945277549509298==
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=UTF-8
<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-size: 13px;color: rgb(0, 0, 0);font-family: ari=
al, sans-serif;">Many thanks to Manoj and Andy, who SOLVED this problem.<di=
v><br></div><div>Manoj correctly concluded we were using JBoss, and his rep=
ly led us in the correct direction.</div><div><br></div><div>Andy's reply n=
ailed it.</div><div><br></div><div>We now have case-insensitive sorts and s=
earches with JPA/Oracle.</div><div><br></div><div>FYI - here is the exact c=
hange required in JBoss.</div><div><br></div><div><div style=3D"color: rgb(=
34, 34, 34); font-size: 12.727272033691406px;"><div><datasources xmlns=
=3D"<a href=3D"http://www.jboss.org/ironjacamar/schema" target=3D"_blank" s=
tyle=3D"color: rgb(17, 85, 204);">http://www.jboss.org/<wbr>ironjacamar/sch=
ema</a>"</div><div> xmlns:xsi=3D"<a href=3D"http://www.w3.org/2=
001/XMLSchema-instance" target=3D"_blank" style=3D"color: rgb(17, 85, 204);=
">http://www.w3.org/<wbr>2001/XMLSchema-instance</a>"</div><div> &nbs=
p;xsi:schemaLocation=3D"<a href=3D"http://www.jboss.org/ironjacamar/schema"=
target=3D"_blank" style=3D"color: rgb(17, 85, 204);">http://<wbr>www.jboss=
.org/ironjacamar/<wbr>schema</a> <a href=3D"http://docs.jboss.org/iron=
jacamar/schema/datasources_1_0.xsd" target=3D"_blank" style=3D"color: rgb(1=
7, 85, 204);">http://docs.jboss.org/<wbr>ironjacamar/schema/<wbr>datasource=
s_1_0.xsd</a>"></div><div> </div><div><!-- The datasource=
is bound into JNDI at this location. We reference <span style=3D"font=
-size: 12.727272033691406px;">this in META-INF/persistence.xml --></span=
></div><div> <datasource jndi-name=3D"java:/VdoxxDS"</div><d=
iv> pool-name=3D"VdoxxDS" enabled=3D"true"</div><div>&n=
bsp; use-java-context=3D"true" use-ccm=3D"true"> &n=
bsp; </div><div><span style=3D"font-size: 12.727272033691406px; white=
-space: pre-wrap;">=09</span><span style=3D"font-size: 12.727272033691406px=
;"> </span><span style=3D"font-size: 12.727272033691406px;"> <=
connection-url>jdbc:oracle:</span><wbr style=3D"font-size: 12.7272720336=
91406px;"><span style=3D"font-size: 12.727272033691406px;">thin:@localhost:=
1521:ORCL</</span><wbr style=3D"font-size: 12.727272033691406px;"><span =
style=3D"font-size: 12.727272033691406px;">connection-url></span></div><=
div> <driver>oracle</driver></div><div>&nbs=
p; <security></div><div> &nb=
sp; <user-name>XXXXX</user-<wbr>name> </div>=
<div><span style=3D"white-space: pre-wrap;">=09=09</span> <p=
assword>YYYYY</password></div><div> </secur=
ity></div><div><span style=3D"white-space: pre-wrap;">=09</span> &n=
bsp;<font color=3D"#ff0000"><new-connection-sql>BEGIN</font></div><di=
v><font color=3D"#ff0000"><span style=3D"white-space: pre-wrap;">=09=09</sp=
an> EXECUTE IMMEDIATE 'alter session set NLS_COMP=3DANSI';</font=
></div><div><font color=3D"#ff0000"><span style=3D"white-space: pre-wrap;">=
=09=09</span> EXECUTE IMMEDIATE 'alter session set NLS_SORT=3DBI=
NARY_CI';</font></div><div><font color=3D"#ff0000"><span style=3D"white-spa=
ce: pre-wrap;">=09=09</span> END;</font></div><div><font color=
=3D"#ff0000"><span style=3D"white-space: pre-wrap;">=09</span> &=
lt;/new-connection-sql></font></div><div> </datasource>=
;</div><div></datasources></div></div></div><div> </div><div><br=
></div><div><br></div><div><br><br><blockquote style=3D"padding-left: 5px; =
margin-left: 0px; border-left: #0000ff 2px solid; font-weight: normal; font=
-style: normal; text-decoration: none; font-size: 10pt; font-family: arial,=
sans-serif; color: black;">-----Original Message-----
<br>From: Andrew Oliver <[email protected]>
<br>Sent: Jul 29, 2014 5:23 PM
<br>To: [email protected]
<br>Subject: Re: [Juglist] EJB3/JPA with Oracle
<br><br><div dir=3D"ltr"><span style=3D"color:rgb(61,61,61);font-family:Cab=
in;font-size:13px;line-height:19.5px"><new-connection-sql> in your jb=
oss connection pool config. Other connection pools have similar stuff=
.</span><div><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size=
:13px;line-height:19.5px"><br>
</span></div><div><font color=3D"#3d3d3d" face=3D"Cabin"><span style=3D"lin=
e-height:19.5px"><a target=3D"_blank" href=3D"http://www.orafaq.com/node/99=
9">http://www.orafaq.com/node/999</a> shows what the query should be on var=
ious Oracle versions. So it is something like this in your *-ds.xml f=
ile:</span></font></div>
<div><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size:13px;li=
ne-height:19.5px"><br></span></div><div><span style=3D"color:rgb(61,61,61);=
font-family:Cabin;font-size:13px;line-height:19.5px"><new-connection-sql=
></span></div>
<div><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size:13px;li=
ne-height:19.5px">BEGIN</span></div><div><span style=3D"color:rgb(61,61,61)=
;font-family:Cabin;font-size:13px;line-height:19.5px">EXECUTE IMMEDIATE '</=
span><font color=3D"#3d3d3d" face=3D"Cabin"><span style=3D"line-height:19.5=
px">alter session set NLS_COMP=3D''ANSI''';</span></font><span style=3D"col=
or:rgb(61,61,61);font-family:Cabin;font-size:13px;line-height:19.5px"><br>
</span></div><div><font color=3D"#3d3d3d" face=3D"Cabin"><span style=3D"lin=
e-height:19.5px">EXECUTE IMMEDIATE 'alter session set NLS_SORT=3D'alter ses=
sion set NLS_SORT=3D''BINARY_CI''';</span></font></div>
<div><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size:13px;li=
ne-height:19.5px">END </span><br></div><div><span style=3D"color:rgb(=
61,61,61);font-family:Cabin;font-size:13px;line-height:19.5px"></new-con=
nection-sql></span></div>
<div><br></div><div><br></div><div>-Andy</div></div><div class=3D"gmail_ext=
ra"><br><br><div class=3D"gmail_quote">On Tue, Jul 29, 2014 at 1:42 PM, Hug=
h Allen <span dir=3D"ltr"><<a target=3D"_blank" href=3D"mailto:hughallen=
@mindspring.com">[email protected]</a>></span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div><div style=3D"font-size:13px;color:rgb(=
0,0,0);font-family:arial,sans-serif">Thanks Andy.<div> </div><div>But =
we got by the TABLE name Case issue.</div>
<div><br></div><div>Rather is is Case Sensitivity in QUERIES.</div><div><sp=
an style=3D"font-size:10pt">- Database has Patient Name Smith</span></div><=
div><span style=3D"font-size:10pt">- User searches for SMITH</span></div><d=
iv>
<br></div><div>On MySql and SQL Server, this is a hit and returns search re=
sults. </div><div><br></div><div>NOTHING returned on Oracle unless sea=
rch is for Smith with matching capitalization.</div><div><br></div><div>The=
re are Oracle i18n SESSION options for NLS_SORT and NLS_COMP that may be a =
solution? But not settable in JPA?</div>
<div><br></div><div>Manoj replied earlier in this thread with this:</div><d=
iv><br></div><div>---</div><div class=3D""><div><span style=3D"font-family:=
Geneva,Arial,Helvetica,sans-serif;font-size:12px">You should be able to con=
figure the session properties in your DataSource definition.</span><div sty=
le=3D"font-family:Geneva,Arial,Helvetica,sans-serif;font-size:12px">
For example in TomEE, in tomee.xml there would be a "Resource" definition w=
hich has property "ConnectionProperties" where session props can be set. A =
similar config will be available in other containers.</div>
<div style=3D"font-family:Geneva,Arial,Helvetica,sans-serif;font-size:12px"=
>I believe in Jboss you may be able to do something similar via <new-con=
nection-sql></div></div></div><div>---</div><div><br></div><div>Brute fo=
rce option seems to be to change all HQL to use UPPER() or LOWER(), but tha=
t is HUNDREDS of queries.</div>
<div><br></div><div>Other options? This is nasty.</div><div><br></div><div>=
Thanks, Hugh<div><div class=3D"h5"><br><div><br><blockquote style=3D"paddin=
g-left:5px;margin-left:0px;border-left:#0000ff 2px solid;font-weight:normal=
;font-style:normal;text-decoration:none;font-size:10pt;font-family:arial,sa=
ns-serif;color:black">
-----Original Message-----
<br>From: Andrew Oliver <u></u>
<br>Sent: Jul 29, 2014 3:27 PM
<br>To: <a target=3D"_blank" href=3D"mailto:[email protected]">jugli=
[email protected]</a>
<br>Subject: Re: [Juglist] EJB3/JPA with Oracle
<br><br><div dir=3D"ltr">Yes Oracle sucks and is only exceeded in painfulne=
ss by DB2 which actually has made forward progress in recent years (in that=
it supports something other than pessimistic row locking on micros/minis a=
nd page locking on big iron). <div>
<br></div><div>1. Simple Solution: <a target=3D"_blank" href=3D"https:=
//forum.hibernate.org/viewtopic.php?f=3D1&t=3D972808">https://forum.hib=
ernate.org/viewtopic.php?f=3D1&t=3D972808</a> - backtick your column na=
mes in the annotations (but it is a big find and replace code change)</div>
<div><br></div><div>2. More complex solution: </div><div><a target=3D"=
_blank" href=3D"http://www.petrikainulainen.net/programming/tips-and-tricks=
/implementing-a-custom-namingstrategy-with-hibernate/">http://www.petrikain=
ulainen.net/programming/tips-and-tricks/implementing-a-custom-namingstrateg=
y-with-hibernate/</a><br>
</div><div><br></div><div>Example (someone wrote something like this but yo=
u may or may not like their actual table name choices):</div><div><a target=
=3D"_blank" href=3D"https://code.google.com/p/hibernate-naming-strategy-for=
-oracle/source/browse/#svn%2Ftrunk%2Fsrc%2Fde%2Fschauderhaft%2Fhibernate">h=
ttps://code.google.com/p/hibernate-naming-strategy-for-oracle/source/browse=
/#svn%2Ftrunk%2Fsrc%2Fde%2Fschauderhaft%2Fhibernate</a><br>
</div><div><div><br></div><div>3. Been awhile but I think you can also over=
ride the column names in XML but it is kind of crappy and I vaguely remembe=
r there are limitations with that: <a target=3D"_blank" href=3D"http:/=
/java.dzone.com/articles/persisting-entity-classes">http://java.dzone.com/a=
rticles/persisting-entity-classes</a></div>
</div><div><br></div><div><br></div><div>-Andy</div></div><div class=3D"gma=
il_extra"><br><br><div class=3D"gmail_quote">On Tue, Jul 29, 2014 at 10:43 =
AM, Hugh Allen <span dir=3D"ltr"><<a target=3D"_blank" href=3D"mailto:hu=
[email protected]">[email protected]</a>></span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex"><div><div style=3D"font-size:13px;color:rgb(=
0,0,0);font-family:arial,sans-serif">DOUBLING the size of the database???&n=
bsp;<span style=3D"font-size:10pt">This seems a non-starter.</span><div>
<br></div><div>So basically, Oracle was written in 197X or so - I was aroun=
d at the time - EVERYTHING was in CAPS - all mainframe data.</div><div><br>=
</div><div>I did my MBA Thesis in 1974 on a mainframe on a 3270 green termi=
nal at Merrill Lynch, where I was working, ALL IN CAPS. Had to explain why =
I had not used a TYPEWRITER.</div>
<div><br></div><div>Looks like Oracle is still in 1974. They expect us to l=
oad everything in the database in UPPER CASE?</div><div><br></div><div>This=
cross-database stuff is harder than it looks. JPA doesn't handle this in a=
ny way, with ORACLE, which is THE major database?</div>
<div><br></div><div>MySQL was easy. SQL Server only minor issues. Oracle is=
a nightmare of old landmines from the 70s.</div><div><br></div><div>What a=
m I missing?</div><div><br></div><div>Hugh<br><br><blockquote style=3D"padd=
ing-left:5px;margin-left:0px;border-left:#0000ff 2px solid;font-weight:norm=
al;font-style:normal;text-decoration:none;font-size:10pt;font-family:arial,=
sans-serif;color:black">
<div>-----Original Message-----
<br>From: Olaf Luetkehoelter <u></u>
<br>Sent: Jul 29, 2014 10:11 AM
<br>To: Hugh Allen <u></u>
<br>Cc: <a target=3D"_blank" href=3D"mailto:[email protected]">jugli=
[email protected]</a>
<br>Subject: Re: [Juglist] EJB3/JPA with Oracle
<br><br><u></u><u></u><u></u><u></u><u></u><u></u>A common way of implement=
ing case insensitive search (and avoid full table scans) in Oracle is to ad=
d another column for each column that you want to search on, copy the to_up=
per() values of your original fields into the new column, add indexes on th=
e new columns and use to_upper in your select statements.<div>
<br></div><div>thanks,</div></div><div>Olaf<br><div><div><i style=3D"font-f=
amily:'Lucida Handwriting';text-align:-webkit-auto">---</i><div><div style=
=3D"color:rgb(0,0,0);font-family:Helvetica;font-style:normal;font-variant:n=
ormal;font-weight:normal;letter-spacing:normal;line-height:normal;text-alig=
n:-webkit-auto;text-indent:0px;text-transform:none;white-space:normal;word-=
spacing:0px;word-wrap:break-word">
<div><font face=3D"Lucida Handwriting"><i>Olaf Luetkehoelter</i></font></di=
v><div><font face=3D"Lucida Sans"><a target=3D"_blank" href=3D"mailto:ol42@=
nc.rr.com">[email protected]</a></font></div><div><font face=3D"Lucida Sans"><=
a target=3D"_blank" href=3D"tel:919-727-6523" value=3D"+19197276523">919-72=
7-6523</a></font></div>
</div>
</div>
<br></div><div><div><div>On Jul 29, 2014, at 08:37 , Hugh Allen <<a targ=
et=3D"_blank" href=3D"mailto:[email protected]">hughallen@mindspring=
.com</a>> wrote:</div><br></div><div><blockquote type=3D"cite">
Hi,<br><br>Hope we have some Oracle/JPA experts out there, because Google s=
earches are not showing me the answer.<br><br>After using MySQL and SQL Ser=
ver exclusively for years, we are now porting our Java EJB3/JPA app to Orac=
le, based on requirements from a client.<br>
<br>Thought this would be a snap but no such luck. Oracle OWNS Java, for cr=
ying out loud. How hard could this be???<br><br>So I guess this is an Oracl=
e newbie question.<br><br>1) Resolved the 30 character limit on column name=
s by changing JPA<br>
<br>2) Resolved Oracle's inability to distinguish between NULL and Empty St=
ring. (Oracle calls an Empty String an IMPOSSIBLE VALUE! But in fact it is =
very possible and the DEFAULT in the app, which has NOT NULL for all string=
s.)<br>
<br>3) But now *CASE SENSITIVE Queries* are the problem. By default all Ora=
cle queries are Case Sensitive! This is a ridiculous default, and there mus=
t be some way around it???<br><br>Discovered there is an Oracle setting for=
NLS_COMP but this is a SESSION parameter and JPA can not control Oracle Se=
ssions? How can this be accomplished?<br>
<br>Guessing many of you use Oracle. Any help on setting up Case IN-SENSITI=
VE searches in Oracle?<br><br>Without changing all the queries to use LOWER=
()?<br><br>Thanks, Hugh<br><br><br><br><br><br>____________________________=
___________________<br>
Juglist mailing list<br><a target=3D"_blank" href=3D"mailto:[email protected]=
rijug.org">[email protected]</a><br><a target=3D"_blank" href=3D"htt=
p://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org">http://list=
s.trijug.org/mailman/listinfo/juglist_lists.trijug.org</a><br>
</blockquote></div></div><br></div></div><u></u><u></u><u></u><u></u></bloc=
kquote></div></div></div>
<br>_______________________________________________<br>
Juglist mailing list<br>
<a target=3D"_blank" href=3D"mailto:[email protected]">Juglist@lists=
.trijug.org</a><br>
<a target=3D"_blank" href=3D"http://lists.trijug.org/mailman/listinfo/jugli=
st_lists.trijug.org">http://lists.trijug.org/mailman/listinfo/juglist_lists=
.trijug.org</a><br>
<br></blockquote></div><br></div>
<u></u></blockquote></div></div></div></div></div></div>
</blockquote></div><br></div>
</[email protected]></blockquote></div></div></body></html>
--===============3618945277549509298==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Juglist mailing list
[email protected]
http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org
--===============3618945277549509298==--