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