Re: EJB3/JPA with Oracle
Hugh Allen <[email protected]> Tue, 29 Jul 2014 16:42:02 -0400 (GMT-04:00)
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <15572658.1406666523287.JavaMail.root@mswamui-valley.atl.sa.earthlink.net> |
--===============0460149332291744473==
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;">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><span style=3D"font-size: 10pt;">- Database has Patient =
Name Smith</span></div><div><span style=3D"font-size: 10pt;">- User searche=
s for SMITH</span></div><div><br></div><div>On MySql and SQL Server, this i=
s a hit and returns search results. </div><div><br></div><div>NOTHING =
returned on Oracle unless search is for Smith with matching capitalization.=
</div><div><br></div><div>There are Oracle i18n SESSION options for NLS_SOR=
T 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><div><br=
></div><div>---</div><div><span style=3D"font-family: Geneva, Arial, Helvet=
ica, sans-serif; font-size: 12px;">You should be able to configure the sess=
ion properties in your DataSource definition.</span><div style=3D"font-fami=
ly: Geneva, Arial, Helvetica, sans-serif; font-size: 12px;">For example in =
TomEE, in tomee.xml there would be a "Resource" definition which has proper=
ty "ConnectionProperties" where session props can be set. A similar config =
will be available in other containers.</div><div style=3D"font-family: Gene=
va, Arial, Helvetica, sans-serif; font-size: 12px;">I believe in Jboss you =
may be able to do something similar via <new-connection-sql></div></d=
iv><div>---</div><div><br></div><div>Brute force option seems to be to chan=
ge all HQL to use UPPER() or LOWER(), but that is HUNDREDS of queries.</div=
><div><br></div><div>Other options? This is nasty.</div><div><br></div><div=
>Thanks, Hugh<br><div><br><blockquote style=3D"padding-left: 5px; margin-le=
ft: 0px; border-left: #0000ff 2px solid; font-weight: normal; font-style: n=
ormal; text-decoration: none; font-size: 10pt; font-family: arial,sans-seri=
f; color: black;">-----Original Message-----
<br>From: Andrew Oliver <[email protected]>
<br>Sent: Jul 29, 2014 3:27 PM
<br>To: [email protected]
<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 class=3D"">-----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 class=3D""><i styl=
e=3D"font-family:'Lucida Handwriting';text-align:-webkit-auto">---</i><div>=
<div style=3D"color:rgb(0,0,0);font-family:Helvetica;font-style:normal;font=
-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal=
;text-align:-webkit-auto;text-indent:0px;text-transform:none;white-space:no=
rmal;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 class=3D""><div>On Jul 29, 2014, at 08:37 , Hugh Allen =
<<a target=3D"_blank" href=3D"mailto:[email protected]">hughallen=
@mindspring.com</a>> wrote:</div><br></div><div class=3D""><blockquote t=
ype=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>
</[email protected]></blockquote></div></div></div></body></html>
--===============0460149332291744473==
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
--===============0460149332291744473==--