Re: EJB3/JPA with Oracle
Andrew Oliver <[email protected]> Tue, 29 Jul 2014 14:23:54 -0700
| Newsgroups | gmane.org.user-groups.trijug.juglist |
|---|---|
| Message-ID | <CAF9=+5PGLTbrme=rDYMsoK7LMKXfsQRGcbh5fTGcAGNbkqLvAA@mail.gmail.com> |
--===============4774546588084535508== Content-Type: multipart/alternative; boundary=047d7b2eda3b23cb9604ff5ba8e8 --047d7b2eda3b23cb9604ff5ba8e8 Content-Type: text/plain; charset=UTF-8 <new-connection-sql> in your jboss connection pool config. Other connection pools have similar stuff. http://www.orafaq.com/node/999 shows what the query should be on various Oracle versions. So it is something like this in your *-ds.xml file: <new-connection-sql> BEGIN EXECUTE IMMEDIATE 'alter session set NLS_COMP=''ANSI'''; EXECUTE IMMEDIATE 'alter session set NLS_SORT='alter session set NLS_SORT=''BINARY_CI'''; END </new-connection-sql> -Andy On Tue, Jul 29, 2014 at 1:42 PM, Hugh Allen <[email protected]> wrote: > Thanks Andy. > > But we got by the TABLE name Case issue. > > Rather is is Case Sensitivity in QUERIES. > - Database has Patient Name Smith > - User searches for SMITH > > On MySql and SQL Server, this is a hit and returns search results. > > NOTHING returned on Oracle unless search is for Smith with matching > capitalization. > > There are Oracle i18n SESSION options for NLS_SORT and NLS_COMP that may > be a solution? But not settable in JPA? > > Manoj replied earlier in this thread with this: > > --- > You should be able to configure the session properties in your DataSource > definition. > For example in TomEE, in tomee.xml there would be a "Resource" definition > which has property "ConnectionProperties" where session props can be set. A > similar config will be available in other containers. > I believe in Jboss you may be able to do something similar via > <new-connection-sql> > --- > > Brute force option seems to be to change all HQL to use UPPER() or > LOWER(), but that is HUNDREDS of queries. > > Other options? This is nasty. > > Thanks, Hugh > > > -----Original Message----- > From: Andrew Oliver > Sent: Jul 29, 2014 3:27 PM > To: [email protected] > Subject: Re: [Juglist] EJB3/JPA with Oracle > > Yes Oracle sucks and is only exceeded in painfulness by DB2 which actually > has made forward progress in recent years (in that it supports something > other than pessimistic row locking on micros/minis and page locking on big > iron). > > 1. Simple Solution: https://forum.hibernate.org/viewtopic.php?f=1&t=972808 > - backtick your column names in the annotations (but it is a big find and > replace code change) > > 2. More complex solution: > > http://www.petrikainulainen.net/programming/tips-and-tricks/implementing-a-custom-namingstrategy-with-hibernate/ > > Example (someone wrote something like this but you may or may not like > their actual table name choices): > > https://code.google.com/p/hibernate-naming-strategy-for-oracle/source/browse/#svn%2Ftrunk%2Fsrc%2Fde%2Fschauderhaft%2Fhibernate > > 3. Been awhile but I think you can also override the column names in XML > but it is kind of crappy and I vaguely remember there are limitations with > that: http://java.dzone.com/articles/persisting-entity-classes > > > -Andy > > > On Tue, Jul 29, 2014 at 10:43 AM, Hugh Allen <[email protected]> > wrote: > >> DOUBLING the size of the database??? This seems a non-starter. >> >> So basically, Oracle was written in 197X or so - I was around at the time >> - EVERYTHING was in CAPS - all mainframe data. >> >> I did my MBA Thesis in 1974 on a mainframe on a 3270 green terminal at >> Merrill Lynch, where I was working, ALL IN CAPS. Had to explain why I had >> not used a TYPEWRITER. >> >> Looks like Oracle is still in 1974. They expect us to load everything in >> the database in UPPER CASE? >> >> This cross-database stuff is harder than it looks. JPA doesn't handle >> this in any way, with ORACLE, which is THE major database? >> >> MySQL was easy. SQL Server only minor issues. Oracle is a nightmare of >> old landmines from the 70s. >> >> What am I missing? >> >> Hugh >> >> -----Original Message----- >> From: Olaf Luetkehoelter >> Sent: Jul 29, 2014 10:11 AM >> To: Hugh Allen >> Cc: [email protected] >> Subject: Re: [Juglist] EJB3/JPA with Oracle >> >> A common way of implementing case insensitive search (and avoid full >> table scans) in Oracle is to add another column for each column that you >> want to search on, copy the to_upper() values of your original fields into >> the new column, add indexes on the new columns and use to_upper in your >> select statements. >> >> thanks, >> Olaf >> *---* >> *Olaf Luetkehoelter* >> [email protected] >> 919-727-6523 >> >> On Jul 29, 2014, at 08:37 , Hugh Allen <[email protected]> wrote: >> >> Hi, >> >> Hope we have some Oracle/JPA experts out there, because Google searches >> are not showing me the answer. >> >> After using MySQL and SQL Server exclusively for years, we are now >> porting our Java EJB3/JPA app to Oracle, based on requirements from a >> client. >> >> Thought this would be a snap but no such luck. Oracle OWNS Java, for >> crying out loud. How hard could this be??? >> >> So I guess this is an Oracle newbie question. >> >> 1) Resolved the 30 character limit on column names by changing JPA >> >> 2) Resolved Oracle's inability to distinguish between NULL and Empty >> String. (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 >> strings.) >> >> 3) But now *CASE SENSITIVE Queries* are the problem. By default all >> Oracle queries are Case Sensitive! This is a ridiculous default, and there >> must be some way around it??? >> >> Discovered there is an Oracle setting for NLS_COMP but this is a SESSION >> parameter and JPA can not control Oracle Sessions? How can this be >> accomplished? >> >> Guessing many of you use Oracle. Any help on setting up Case IN-SENSITIVE >> searches in Oracle? >> >> Without changing all the queries to use LOWER()? >> >> Thanks, Hugh >> >> >> >> >> >> _______________________________________________ >> Juglist mailing list >> [email protected] >> http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org >> >> >> >> _______________________________________________ >> Juglist mailing list >> [email protected] >> http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.org >> >> > --047d7b2eda3b23cb9604ff5ba8e8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-= size:13px;line-height:19.5px"><new-connection-sql> in your jboss conn= ection pool config. =C2=A0Other connection pools have similar stuff.</span>= <div><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size:13px;li= ne-height:19.5px"><br> </span></div><div><font color=3D"#3d3d3d" face=3D"Cabin"><span style=3D"lin= e-height:19.5px"><a href=3D"http://www.orafaq.com/node/999">http://www.oraf= aq.com/node/999</a> shows what the query should be on various Oracle versio= ns. =C2=A0So it is something like this in your *-ds.xml file:</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 = 9;</span><font color=3D"#3d3d3d" face=3D"Cabin"><span style=3D"line-height:= 19.5px">alter session set NLS_COMP=3D''ANSI''';</span><= /font><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size:13px;l= ine-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'a= lter session set NLS_SORT=3D''BINARY_CI''';</span></fon= t></div> <div><span style=3D"color:rgb(61,61,61);font-family:Cabin;font-size:13px;li= ne-height:19.5px">END =C2=A0</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 href=3D"mailto:[email protected]" t= arget=3D"_blank">[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>=C2=A0</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.=C2=A0</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" de= finition which 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 href=3D"mailto:[email protected]" target=3D"_blank">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). =C2=A0<div> <br></div><div>1. Simple Solution:=C2=A0<a href=3D"https://forum.hibernate.= org/viewtopic.php?f=3D1&t=3D972808" target=3D"_blank">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:=C2=A0</div><div><a href=3D"ht= tp://www.petrikainulainen.net/programming/tips-and-tricks/implementing-a-cu= stom-namingstrategy-with-hibernate/" target=3D"_blank">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 href= =3D"https://code.google.com/p/hibernate-naming-strategy-for-oracle/source/b= rowse/#svn%2Ftrunk%2Fsrc%2Fde%2Fschauderhaft%2Fhibernate" target=3D"_blank"= >https://code.google.com/p/hibernate-naming-strategy-for-oracle/source/brow= se/#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:=C2=A0<a href=3D"http://java.dzone.com/ar= ticles/persisting-entity-classes" target=3D"_blank">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 href=3D"mailto:hughallen@mindspring= .com" target=3D"_blank">[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???= =C2=A0<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 any 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 href=3D"mailto:[email protected]" target=3D"_blank">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><di= v style=3D"color:rgb(0,0,0);font-family:Helvetica;font-style:normal;font-va= riant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;te= xt-align:-webkit-auto;text-indent:0px;text-transform:none;white-space:norma= l;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 href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a></font></div><div><font face=3D"Lucida Sans">= <a href=3D"tel:919-727-6523" value=3D"+19197276523" target=3D"_blank">919-7= 27-6523</a></font></div> </div> </div> <br></div><div><div><div>On Jul 29, 2014, at 08:37 , Hugh Allen <<a href= =3D"mailto:[email protected]" target=3D"_blank">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 Empt= y String. (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 st= rings.)<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 href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a><br><a href=3D"http://lists.trijug.= org/mailman/listinfo/juglist_lists.trijug.org" target=3D"_blank">http://lis= ts.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 href=3D"mailto:[email protected]" target=3D"_blank">Juglist@lists= .trijug.org</a><br> <a href=3D"http://lists.trijug.org/mailman/listinfo/juglist_lists.trijug.or= g" target=3D"_blank">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> --047d7b2eda3b23cb9604ff5ba8e8-- --===============4774546588084535508== 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 --===============4774546588084535508==--