Re: EJB3/JPA with Oracle

--===============6700869252204469984==
Content-Type: multipart/alternative; boundary=047d7bf161f8ddede604ff5a080e

--047d7bf161f8ddede604ff5a080e
Content-Type: text/plain; charset=UTF-8

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
>
>

--047d7bf161f8ddede604ff5a080e
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Yes Oracle sucks and is only exceeded in painfulness by DB=
2 which actually has made forward progress in recent years (in that it supp=
orts something other than pessimistic row locking on micros/minis and 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&amp;t=3D972808">https://forum.hibernate.org/viewtop=
ic.php?f=3D1&amp;t=3D972808</a> - backtick your column names in the annotat=
ions (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/">http://www.petrikainulainen.net/progra=
mming/tips-and-tricks/implementing-a-custom-namingstrategy-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">https://code.goog=
le.com/p/hibernate-naming-strategy-for-oracle/source/browse/#svn%2Ftrunk%2F=
src%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">http://java.dzone.com/articles/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">&lt;<a href=3D"mailto:hughallen@mindspring=
.com" target=3D"_blank">[email protected]</a>&gt;</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&#39;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 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 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 class=3D""><i styl=
e=3D"font-family:&#39;Lucida Handwriting&#39;;text-align:-webkit-auto">---<=
/i><div><div style=3D"color:rgb(0,0,0);font-family:Helvetica;font-style:nor=
mal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-heigh=
t:normal;text-align:-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 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 class=3D""><div>On Jul 29, 2014, at 08:37 , Hugh Allen =
&lt;<a href=3D"mailto:[email protected]" target=3D"_blank">hughallen=
@mindspring.com</a>&gt; 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&#39;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]">[email protected]</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>

--047d7bf161f8ddede604ff5a080e--


--===============6700869252204469984==
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

--===============6700869252204469984==--