Delphi Handbooks Collection


Delphi XE Handbook


Delphi 2010 Handbook


December 16, 2010

Guest Post: "…Parenthetically, Oracle XE Breaks DBX" by Keith Tolbert

This is a guest post by Keith Tolbert on using Oracle XE with dbExpress. I'm always happy to host practical advice form Delphi developers (particularly if they don't have a blog of their own).

I Must Say … Parenthetically, Oracle XE Breaks DBX

Guest post by Keith Edward Tolbert

The DBX Promise

At the last Borland Conference, John Kaster gave a great presentation on the promise of Delphi's DBX technology. I was really impressed, thinking 'How cool is that … one set of light-weight components, connecting to any of the major database engines.' And DBX has delivered on that promise, except for Oracle 10g XE (or so it would seem).

The Oracle 10g XE Issue

A couple of years ago, when I decided to leave the BDE in the dust, I decided to go with DBX. Since I am from the 'try small and simple first' school-of-thought, I installed Oracle 10g XE (local edition) and tried to connect with DBX, and then tried, and tried again. I was always slapped with the ORA-12154 exception, 'TNS:could not resolve the connect identifier specified'. What the heck is going on here?

'like walking 40 miles of bad road' - Dylan

After a while, frustration turns into resignation, so I looked elsewhere:

  • My Delphi developer friends: Like me, they tried and tried, without success.

  • A Delphi Guru: we spent about 10hrs hammering on it from every conceivable direction

  • DevArt, the creator of DBX technology: They struggled with me for over a month. Read it and weep/laugh (your choice) http://www.devart.com/forums/viewtopic.php?t=18878

  • Embarcadero Support: Not a clue on connecting to Oracle XE. The support person thought that because my OS (Windows 7) is 64-bit, then I must also have installed the 64-bit version of Oracle. Not true.

Along the way, I did pick up a few clues

  • I can connect to Oracle XE with the PLSQL Developer tool. Why is that? While PLSQL Developer is written in Delphi, it connects with their own connection component. So, I concluded, that must be the reason it can connect but DBX cannot. (this is a false positive)

  • I can connect easily to SQL Server, InterBase, and Firebird. So, the DBX technology is mature

  • Then at CodeRage 5, John Kaster and David I., both assured me that it was doable. In fact, David I. has this set-up on his own machine (finally, a ray of hope)

  • Furthermore, I was not able to connect ANY Embarcadero database tools to Oracle XE. What is the possibility of that? These tools may not even be using DBX! Clearly, the issue is wider than the DBX.

  • While the Embarcadero Support person was not able to help me connect the ER Studio Repository to Oracle XE, he did try to steer me towards using SQL Server Express, instead. This made me cast my eyes toward Oracle XE, itself

  • Yesterday, I noticed that when I replaced the SQLConnection1.Database property value of 'XE' with 'localhost:1521:XE' (kind of forcing the issue), the Oracle exception thrown was not the ubiquitous ORA-12154, but rather, 'ORA-06413, Connection not open.'

  • After some research on the web, it turns out that there are two or three conditions that can throw an ORA-06413 exception. One of them is that there cannot be any character in the path of the executable which Oracle regards as invalid!

Final Destination

Well ... if this is the case, then which is the invalid character? I immediately noticed that the Delphi IDE (my currently running executable) and all of the non-connecting Embarcadero database tools are under the 'C:\Program Files (x86)' directory, while PLSQL Developer, that does connect, was not! Could it be possibly be the parentheses?

I stopped and thought about it a minute. Although my Delphi IDE is running under the 'C:\Program Files (x86)' directory, my test application is not. Therefore, if my hypothesis is correct, setting the SQLConnection1.Connected to 'True,' at design time should throw an exception, however, the compiled application should not.

I compile, I run the application outside of the IDE, and click the Connect button … and ...Bang! I got the connection!! Finally!!!

Now I need to confirm this is the case. First, I uninstall the ER Studio Repository and re-install it outside of the 'C:\Program Files (x86)' directory and it connects to Oracle XE, first time. Then I move my test application, that does connect, to under the 'C:\Program Files (x86)' directory and it throws the ORA-06413 exception.

Lastly, I can see no other character in this directory name which could possibly be 'invalid,' according to Oracle. I recognize that I have not proven it is the parentheses, but I am convinced it is the parentheses. In order to prove it I would have to remove the parentheses from the ''C:\Program Files (x86)' directory. Sorry, but someone with more time than me will have to do that. While I am not adamant, I do feel comfortable saying that any executable under the ''C:\Program Files (x86)' directory will throw an exception when it attempts to connect to Oracle 10g XE (local).

The DBX Promise Fulfilled

DBX does indeed live up to its promise. The issue is not with DBX, specifically, nor with Delphi, generally, it is with Oracle's rule of invalid path characters.

The Moral of this Story:

Of course, the BIG question is 'Did Microsoft know the parentheses would break Oracle Express?' Yes, Microsoft SQL Server Express does compete directly with Oracle Express. But did they do this intentionally? That's the question. Of course, there is no way I could ever know this.

However, I do think a relevant question to consider is, “Could Microsoft have used another way to denote 'x86,' without using parentheses?” I hope this does not make me a conspiracy theorist. :-)

Each developer will have to answer this for him/herself.

ps. I will make sure this info gets to the DevArt and Embarcadero support teams.

pps. Over the years, I have received so much help from the Delphi community, I intend this little contribution to be my 'thank you'.





 

19 Comments

A known issue 

That's been a known issue since a long time. Unlike 
other Oracle 10g installers that install Oracle in 
<disk>:\Oracle by default (where <disk> is the disk 
with most free space), XE is installed in <program 
files>. And Oracle, like Java and its applications, 
often doesn't like Windows paths (remember Oracle 
runs on several platforms, and the others are all 
*nix flavours, or alike). Add to this that the Oracle 
official support documentation is not free, and 
people may have a hard time to find solutions to 
issue like this). Last but not least, a patchset for 
XE was never released, issues like this has been 
fixed in later releases of the Oracle client (and 
that's a good reason not to use XE, IMHO, although I 
use Oracle).
Anyway Embarcadero was wrong about the 64 bit 
installation: because Delphi is not a 64 bit 
application, it can't call the Oracle 64 bit client. 
It needs the 32 bit client to talk to a 64 bit 
database.
Comment by Luigi D. Sandon on December 18, 09:20

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

 “Could Microsoft have used another way to denote
'x86,' without using parentheses?”

I doubt it. 

After decades of dealing with them I have to conclude
that they really aren't that intelligent ;-)

Rob
Comment by Robert Horbury-Smith on December 18, 09:26

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

"But did they do this intentionally? That's the 
question"

I don't even understand this line of thinking. 
Parentheses are valid in Win32 and POSIX file names, 
how is this anything other than an Oracle bug?

Comment by davis on December 18, 09:54

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

Same issue have order Adobe installer 
(CS3? Last version without x 64 
Photoshop).

Next button was disabled when you 
select Program Files (x86). 
Comment by DiGi [http://www.qr.cz] on December 18, 10:01

Guest Post â?¦Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

XE finally pays only under Linux ... here it makes 
life easier, you don't have to link the database ... 
sthg. like a make install... (libasio)

Under Windows take the version from the technet and 
use the personal edition. As long as you just develop 
it is ok. Use the instant client, especially for 
Delphi anything else does not really pay, during 
development, you only have to set the ORA_HOME or 
ORCL_HOME (Linux) and TNS_ADMIN. 

Isolate your Delphi world, especially when you use 
the .net stuff and the VS IDE integration you come to 
another level of multi home problems also under 11.x. 
when the installer upgrades the home and the server 
runs on your machine. On the serverside it happened 
that installer left the order in home selector 
untouched but removed the oci.dll. This is fun then.

For this it always good to have to components 
available tools are made with. In case of 
SQLDetective and PL-SQL Developer (allrounautomations 
stuff), Horra and others (Devart) or whatever ...

This is interesting then, but if someone has no idea 
how the SQL.net is/has to be configured ... you run 
crazy. The PL-SQL Developer takes the settings in a 
different order than devarts implementation ... the 
problem is the ORA-HOME on the server side are 
treated different then those on the client when the 
client is upgraded ... be careful. If you run into 
problems - check the settings in to oracle home 
selector....

The whole XE thing, was a pain when combined with 9.x 
especially Oracle afterwards has cleaned up the way 
multi homes are treated. 

Usually it should be better to provide the 
environment variables in a scirpt before executing 
Freepascal/Lazarus or Delphi or the application.

Anyway, without Oracle support you don't get the 
patches and without them in practice development 
especially maintenance is a pain, you cannot 
reproduce bugs, the devil is the detail.

Anyway the XE does work with other Oracle clients and 
for this simply use the instant client. Set the 
environmet variables in script an your are better off.
Comment by Michael Thuma on December 18, 10:23

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

What MS has to do with Oracle error?

Parentheses are valid!

http://msdn.microsoft.com/en-us/library/aa365247.aspx
Comment by Marcelo from Brazil on December 18, 12:05

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

Did you post any questions on Stack Overflow or the
Delphi newsgroups?  I would have answered your
question and saved you a lot of time and
misinformation!  The bug is issue #3807408 in their
(Oracle's) bug tracking system and has been known for
years.  Why don't they fix it?  I wish I knew!  Note
that it has nothing to do with Delphi, it affects any
application installed in a directory with a
parenthesis in the path.
Comment by Mark Ford [] on December 18, 12:23

Known Issue?  

Luigi,

Thanks for responding to my post.

I don't think this issue was very well known, these 
didn't know about it: 1) Delphi dev friends, 2) a 
Delphi guru, 3) Devart (the creator of DBX), and 4) 
Embarcadero support.

Also, if I am reading your response correctly, you 
seem to be saying the issue is with 'where' Oracle is 
installed.  That is not the issue.  The issue is the 
location of any application trying to connect to 
Oracle.  I apologize if I was not clear. :-)

Comment by Keith Tolbert on December 18, 19:07

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

 Keith - it is an issue with the parenthesis, so 
people will move the app outside the program files 
directory and that's it I think.

x64 + x32 is solved very academically under Windows. 
They will have to realize that a they maybe one day 
will have to provide a file system abstraction that 
works and not Explorer trallala. This is nice for end 
user desktops with 3 programs installed and lots of 
photos and videos.

Anyway intersting that bringing this fact to our 
attention, but the installations I know in Enterprises 
have their own app dirs and don't care about Program 
Files (x86) directoy - I doubt there will be a patch 
from Oracle concerning this. They are simply not 
interested in providing something for free on a long 
term:) and also not databases on Windows. They have 
their Linux and ... and this counts for them.

Mike
Comment by Michael Thuma on December 19, 09:34

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

Keith,
I know the issue is from the application is started 
from, not where Oracle in installed. It was me not 
being clear, I was saying Oracle itself is known to 
have issues with some paths (as Java, BTW), and even 
with case sensitivity.
Because of the default paths in the main products, 
some issued with different ones may go undetected in 
their own tests. This one happens with any app using 
OCI from a directory with brackets. That's because 
the client sends the server some informations about 
the application connecting, and some Unix C 
programmers look to dislike anything that was 
introduces in operating systems after 1969.
The issue was known in the Oracle community, at least 
since 2005. Despite its origin, I am afraid today 
Oracle is far less used by Delphi developers, and 
those using it may not have access to Oracle support. 
I wonder, anyway, that both DevArt and Embarcadero 
support have not access to Oracle support (what was 
once called Metalink), where it would have been easy 
to find the bug and the patch/patchset to fix it. But 
again, to download a patch/patchset one needs an 
active support plan, which is not available for XE, 
and XE itself was never officially patched.
Comment by Luigi D. Sandon on December 19, 17:01

Thanks Mark 

 "Did you post any questions on Stack Overflow or the
Delphi newsgroups?  I would have answered your 
question and saved you a lot of time and 
misinformation!"

Thanks Mark!  I appreciate the offer.  I wish I could 
turn back the clock. :-)  You are right, I did not 
check out Stack Overflow.  (I hate to confess this, 
but I only discovered Stack Overflow when I started 
following Delphi Feeds, about six months ago.)  
However, I do remember searching a couple of Delphi 
newsgroups about a year ago.  Heck, I even searched 
Google with every permutation of many, many terms 
('Delphi','DBX','Oracle 10g','Oracle 10g XE',
'ORA-12154', etc.) and came up empty-handed.

"The bug is issue #3807408 in their (Oracle's) bug 
tracking system and has been known for years."

I don't doubt this.  But the dots of 'ORA-12154' 
(which usually points to a TNS issue) and Oracle 
bug '#3807408' are very far a part, and, for me, 
anyway, were difficult to connect.  So, I just 
thought I would try to bring it to the attention of 
other Delphi developers, who may not know about it.'

"Why don't they fix it?  I wish I knew!"

Yea, I agree 100%.  How difficult can this be? 

"Note that it has nothing to do with Delphi, it 
affects any application installed in a directory with 
a parenthesis in the path."

Again, I agree.  I tried to make this clear in my 
blog: "I do feel comfortable saying that any 
executable under the 'C:\Program Files (x86)' 
directory will throw an exception when it attempts 
to connect to Oracle 10g XE (local) ... The issue is 
not with DBX, specifically, nor with Delphi, 
generally, it is with Oracle's rule of invalid path 
characters."

Thanks Mark, I guess there really is the 'wisdom of 
the crowds.'
Comment by Keith Tolbert on December 19, 19:03

Thanks Mike 

 "Keith - it is an issue with the parenthesis, so 
people will move the app outside the program files 
directory and that's it I think."

Yea, that's the best way.  

There may be one problem though. I just upgraded to 
RAD Studio XE yesterday and of course I installed it 
in a directory NOT under '(x86).'  However, the 
inline AQTime install failed.  I wonder if it is 
requiring me to put Delphi under '(x86).'  That would 
be terrible. :-(

[Marco, I am not trying to turn a blog into a 
newsgroup. So if this last paragraph is extraneous, 
just delete it.  Thanks.]

"Anyway intersting that bringing this fact to our 
attention,"

Thank you.  I do think it is helphing other Delphi 
developers.  Before I posted the answer to the Devart 
thread, there were about 35 hits.  In just the last 
six weeks, it has sky-rocketed to 1150!  (At least 
that was the count a couple of days ago.)  This is 
10x some other posts from around the same time.

" but the installations I know in Enterprises 
have their own app dirs and don't care about Program 
Files (x86) directoy - "

Yes, this is absolutely true.  Perhaps the 'try small 
at first' school of  thought does not always 
translate well.  This might also explain why this is 
not all over the net.

"I doubt there will be a patch  from Oracle 
concerning this. They are simply not  interested in 
providing something for free on a long term:) and 
also not databases on Windows. They have 
their Linux and ... and this counts for them."

Haha :-D  You are right.

Comment by Keith Tolbert on December 19, 19:24

Folder paths.... 

Again, I can only wonder why some developers blame 
Windows and praise Linux, and then under Windows they 
adopt dangerous behaviours they would never be able 
to adopt under Linux, like putting executables 
wherever they want. <program files> is not simply a 
folder. Is a folder with proper executable 
permissions. Put your executables wherever you like 
without setting proper permissions, and you carve 
large security holes in the system. Then blame 
Windows because it is not as secure as Linux. Yes, it 
is not secure as Linux because it actually allows 
lazy and lame developers to break security without 
stopping them. And if it does, they blame Windows 
again because it is not "compatible" and "break 
existing applications" - yes, but only the ones 
written by lame, lazy developers.
Comment by Luigi D. Sandon on December 20, 10:10

Thanks Luigi 

 "Again, I can only wonder why some developers blame 
Windows and praise Linux, and then under Windows they 
adopt dangerous behaviours they would never be able 
to adopt under Linux, like putting executables 
wherever they want."

Yes, I agree.  I am now studying to learn Linux.  
Hopefully, by the time Delphi runs on Linux, I will 
have a little experience.

" <program files> is not simply a 
folder. Is a folder with proper executable 
permissions. Put your executables wherever you like 
without setting proper permissions, and you carve 
large security holes in the system."

That's a good point.  I wonder if Oracle made 
the 'Parentheses are invalid characters' rule in an 
attempt to improve security for Oracle.

"Yes, it 
is not secure as Linux because it actually allows 
lazy and lame developers to break security without 
stopping them. And if it does, they blame Windows 
again because it is not "compatible" and "break 
existing applications" - yes, but only the ones 
written by lame, lazy developers."

I know what you are saying, Luigi.  I also want good 
security.  But I also want my Delphi IDE to be able 
to tell me it is making the connection to Oracle 10g 
XE (local) and this cannot happen if Delphi is 
located under the '(x86)' directory.  It's like a no-
win situation.  But I want you to know I understand 
and even agree with your comments, but my practical 
concerns outweigh it, in this case.  Thanks.
Comment by Keith Tolbert on December 20, 14:51

Guest Post Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

 Although you said you can't "prove" that the problem 
is with the parenthesis, you can do a pretty easy 
test:  compile a delphi EXE and run it from a folder 
with a "(" and without a "(".  If the change leads to 
failure you have pretty definitively proven the cause.
Comment by Louis Kleiman [] on December 20, 17:40

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

Keith!

>>that's a good point.  I wonder if Oracle made 
the 'Parentheses are invalid characters'
Be happy that they accept vocals:) 

Have fun
Mike
Comment by Invlaid characters on December 20, 19:30

Guest Post …Parenthetically, Oracle XE Breaks DBX by Keith Tolbert 

@Keith: instead of changing the Delphi directory, 
download the Oracle Instant Client 
(http://www.oracle.com/technetwork/topics/winsoft-
085727.html, any version from 10.2.0.3 will work, 
IIRC), or the full Oracle client 
(http://www.oracle.com/technetwork/database/enterprise
-edition/downloads/index.html), and set the Oracle 
Home accordingly. They will connect to XE as well.
Comment by Luigi D. Sandon on December 20, 21:53

Nice Catch Louis 

"... :  compile a delphi EXE and run it from a folder 
with a "(" and without a "("."

oooo ... that's right!  

As you read in my blog, I did this, but with only 
the '(x86)' directory.  But to 'prove' it I could 
have created my own directory with a parenthesis and 
repeat the same test there.

Nice catch.

Obviously, I was still thinking inside 
the 'C:\Program Files (x86)' box.

Thanks.
Comment by Keith Tolbert on December 21, 14:35

I think I tried that Luigi 

Thanks for the download-the-Oracle client advise, but 
I think I did this already, as you will see in my 
thread at Devart: 

http://www.devart.com/forums/viewtopic.php?t=18878

However, if you actually have an '.exe' application 
that is located under the 'C:\Program Files (x86)' 
directory and it connects to Oracle 10g XE (local), 
then please get my email from Marco and contact me.

[I want to be a nice guest for Maro and not turn this 
blog into a newsgroup.]

Again, Luigi, thank you so much.

Comment by Keith Tolbert on December 21, 14:43


Post Your Comment

Click here for posting your feedback to this blog.

There are currently 0 pending (unapproved) messages.