Connect dbexpress to Oracle 10g XE

Connect dbexpress to Oracle 10g XE

Postby Keith » Tue 31 Aug 2010 19:12

Greetings,

Over the years I have tried to connect dbExpress to Oracle 10g XE (local) with Delphi 2005, 2006, 2007, 2009, and now 2010 on Windows XP, Vista, and now Windows 7. (always using the highest version of Delphi)

I have tried CodeGear forums. DelphiGroups, and other discussion sites, all to no avail.

Has anyone ever heard of anyone actually connecting with Delphi (2005 or above) to Oracle 10g XE (local) using dbExpress?

No disrespect is intended here, but I am soooo frustrated and exhausted.

A simple 'Yes' will do. I will post my details AFTER I have found someone who has actually done this.

Thank you so much,

Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Wed 01 Sep 2010 08:24

Hello,

Please send me the following information so that I could help you:
- the exact version of dbExpress driver for Oracle.
- the exact version of Oracle server and client
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Wed 01 Sep 2010 16:43

Greetings Alex P.

Thank you so much for your guiding hand. I truely do appreciate it.

Issue: On set SQLConnection1.Active to 'True', I receive the ubiquitous 'ORA-12154' exception
(TNS: could not resolve the connect identifier specified)


Here is the information you requested:
1) - the exact version of dbExpress driver for Oracle
1) dbexpress140.bpl
2) Version: 14.0.3615.26342
3) Location: C:\Windows\system32\
2) - the exact version of Oracle server and client
1) Server:
1) Product Name: Oracle 10g XE
2) Version: 10.2.0
3) Service Name: OracleServiceXE
4) File Name:
1) Windows Explorer: oracle.exe
2) However, from the Windows Services dialgue, the Properties
dialogue, in the General tab lists this as the file name:
ORACLE.EXE XE
Note the addidional ' XE' at the end
5) Location: c:\oraclexe\app\oracle\product\10.2.0\server\bin\
2) Client:
1) Name: oci.dll
2) File Version: 10.2.0.1
3) Location: C:\oraclexe\app\oracle\product\10.2.0\server\BIN
4) Date modified: 2006.2.1
5) Permissions:
1) Auth Users: Read & execute, Read
2) SYSTEM: Full control
3) Admins: Full control


Here is other infornmation that might help you:

GENERAL:
1) Oracle 10g XE is running on my local machine
2) My machine is not connected to any network
3) There two Profiles on my Windows 7 machine (Administrator, Owner)
1) The Owner Profile is the only one I use
4) The Delphi app is minimal: one form with TSQLConnection component
(app compiles)
5) Project Location: C:\Users\Owner\Documents\RAD Studio\Projects\


VERIFICATIONS:
1) I have verified that the following Windows Services are running:
1) OracleServiceXE
2) OracleXEClrAgent
3) OracleXETNSListener
2) I have verified the tnsnames.ora by connecting to Oracle with PL/SQL
Developer dev tool
1) This commercial app is written in Delphi
2) However, they have written their own Oracle driver


HARDWARE:
1) CPU: Intel Core 2 Duo CPU 07350 @ 2.00GHz
2) Available Memory: >4Gig


SOFTWARE:
1) OS:
1) Windows Ultimate
2) Version: 7.6.1 (Build 7600)
2) Delphi:
1) Delphi 2010 Architect edition
2) Version 14.0.3593.25826
3) tnsnames.ora:
1) There is one and only one tnsnames.ora on my entire C drive
2) Location: C:\oraclexe\app\oracle\product\10.2.0
\server\NETWORK\ADMIN
3) This is the first entry in my tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Owner-PC)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
4) There are only two other tnsnames.ora entries:
1) EXTPROC_CONNECTION_DATA =
2) ORACLR_CONNECTION_DATA =


TSQLConnection properties:
1) Connected: False
2) ConnectionName: ORACLECONNECTION
3) GetDriverFunc: getSQLDriverORACLE
4) KeepConnection: True
5) LibraryName: dbxora.dll
6) LoadParamsOnConnect: False
7) LoginPrompt: False
8) Name: SQLConnection1
9) Params:
1) DriverName: Oracle
2) Database: (I have tried the following)
1) XE
2) localhost:1521:XE
3) DEDICATED:1521:XE
3) UserName: (I have tried the following)
1) hr (the oracle-suggested user)
2) SYSDBA
3) SYSOPER
4) Password: hr (oracle default)
5) RowsetSize: 20
6) BlobSize: -1
7) ErrorResourceFile: (this is blank)
8) LocaleCode: 0000
9) IsolationLevel: ReadCommitted
10) OS Authentication: I have tried the following:
1) True
2) False
11) Multiple Transaction: False
10) Tag: 0
11) VendorLib: oci.dll
NOTE: I have tried all combinations of Params properties with multiple values listed above.


OTHER:
1) The Delphi docs say that double-clicking on TSQLConnection
component, that a dialog should open. This does NOT happen.
1) I don't know if the docs are wrong or the component is not working

If you need any other info, please let me know.

Again, Alex, thank you for all your hard work.

Keith

ps. I had this in a nice outline, but in Preview mode, all formatting is lost
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Thu 02 Sep 2010 09:03

Hello,


Try to create a new application and execute the same code. Please provide me the result of executing this code.

Standard dbExpress driver for Oracle

SQLConnection:= TSQLConnection.Create(nil);
SQLConnection.DriverName := 'Oracle';
SQLConnection.Params.Add('DataBase = XE);
SQLConnection.Params.Add('User_Name = hr');
SQLConnection.Params.Add('Password = hr');
SQLConnection.Connected:= true;

Devart dbExpress driver for Oracle
(You can download the trial version of dbExpress driver for Oracle from http://www.devart.com/dbx/oracle/download.html)

SQLConnection:= TSQLConnection.Create(nil);
SQLConnection.DriverName := 'DevartOracle';
SQLConnection.Params.Add('DataBase = XE');
SQLConnection.Params.Add('User_Name = hr');
SQLConnection.Params.Add('Password = hr');
SQLConnection.Connected:= true;

Direct mode

SQLConnection:= TSQLConnection.Create(nil);
SQLConnection.DriverName := 'DevartOracleDirect';
SQLConnection.Params.Add('DataBase = localhost:1521:XE');
SQLConnection.Params.Add('User_Name = hr');
SQLConnection.Params.Add('Password = hr');
SQLConnection.Connected:= true;
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Thu 02 Sep 2010 17:50

Greeting Alex,

I tried the first code block you supplied and it threw the same exception as before. I can send you my pas file, if you wish.

I then downloaded the dbExpress Driver for Oracle, using the link you supplied to me.

After I ran the install application, the new components did not show up in my Delphi IDE. (yes, I bounced Delphi and then the machine itself.)

Next, I read the ReadMe.html/Windows installation notes. It only said the TCRSQLConnection has the same functionality as TSQLConnection. I have no idea what this is about.

Next, I checked the Components|Install packages dialog, thinking it was there, but just not checked for inclusion. It was not in the Components|Install packages dialog listing.

Next, I looked for a bpl anywhere under ...\DbxOda\, so I could add it manually through Components|Install packages dialogue. One does not exist.

Next, I looked for a dpk under ...\DbxOda\, so I could build it manually and then install it myself. I found it under ...\DbxOda\Source\Delphi14\, associated with this project:
C:\Program Files (x86)\Devart\DbxOda\Source\Delphi14\dclcrdbx140.dproj

However, when I opened this project, an 'Upgrading Project' dialog was raised that made me cautious.

Because this dialog refers to '... dclcrdbx140.dproj.2007" and I do not have Delphi 2007 (I only have Delphi 2010 on this machine), I decided not to go any further, lest I do the wrong thing. (It kind of scares me to see the '2007' under the Delphi14 dir.)

Am I supposed to compile this project to make the bpl, so I can add it to the Delphi 2010 IDE?

Again, thank you for your patience with me.

Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Fri 03 Sep 2010 13:59

Hello,


A Devart dbExpress driver is not a visual component, and it is not shown in the component palette, it is a dll wich adds a driver to the standard TSQLConnection component.
To select the Devart Oracle driver double click the TSQLConnection component and
select 'Devart Oracle' in the TSQLConnection dialog window.

Try to connect to your Oracle Server using any Oracle database tools, for example,
OraDeveloper Studio (http://www.devart.com/dbforge/oracle/st ... nload.html),
if you get the same error (ORA-12154), you need to check your tnsnames.ora file.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Fri 03 Sep 2010 20:57

Greetings Alex,

Thank you for your instructions that the Devart dbx is not visual. I'm glad I did not build that cdlcrdbx140.dproj application.

As, I mentioned yesterday, with the first block of code you sent to me, I received the same ORA-12154 exception.

Likewise, with the second block of code ('DevartOracle'), I received the same ORA-12154, yet again.

However, with the third block of code ('DevartOracleDirect'), I receved this different exception:

"Project Project1.exe raised class ESocketError with message 'Windows socket error: The requested address is not valid in its context (10049), on API 'connect'.

Does this new exception tell you anything?

Since, we received a new type of exception, perhaps we are getting closer?

I have verified that Oracle 10g XE service is running.

Also, with respect to your last suggestion, about connecting with a db dev tool; I have already covered this possibility. As you will remember, back in my second post, under the VERIFICATIONS section, you will see that I have verified the tnsnames.ora, as I can connect to it with PL/SQL Developer, a db dev tool.

Thanks again for all your hard work Alex,

Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Mon 06 Sep 2010 09:22

Hello,

Please send me the tnsnames.ora file (alexp*devart*com).
Please run the 'tnsping' utility and send me the result
(type in the command line 'tnsping localhost' and press enter)
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Tue 07 Sep 2010 14:20

Greetings Alex,

As per your request, I have emailed my tnsnames.ora file to you. (The 'XE' entry was also provided in my second post, above.)

Below, you will find the results of tnsping.exe execution:

===================================
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Owner>tnsping localhost

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 07-SEP-2
010 10:05:28

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)

C:\Users\Owner>
==========================

Thank you for your time Alex,
Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Wed 08 Sep 2010 12:32

Hello,


Please run the 'tnsping' utility and send me the result
(type in the command line 'tnsping XE' and press Enter)

Check that you have not installed any other Oracle Clients.
In PL/SQL Developer check the Oracle Home property (Tools->Preferences->Oracle->Connection->Oracle Home), if there are several of them,
try to connect using each of them and send me the result.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Wed 08 Sep 2010 16:32

Greetings Alex,

Here is the result of running 'tnsping XE'

=======================================
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Owner>tnsping XE

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-SEP-2
010 11:36:55

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Owner-PC
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (20 msec)

C:\Users\Owner>
=======================================

In my Delphi app, using the 'DevartOracleDirect' DriverName, I tried to
connect with "DEDICATED:1521:XE" and then "Owner:1521:XE" and then
"OWNER:1521:XE". All three times the execption thrown was same Windows socket error ('requested address is not
valid in its context (10049)').




Here is the result of changing the Oracle Connection Home:

Summary of what happened:
The first time I opened PL/SQL Developer, the Oracle/Connection/Home dropdown
box was empty and there was one and only one entry in its list: "XE". I
changed Home dropdown box to 'XE' and it opened the same way.


Here are my exact steps:
PL/SQL Developer:

Tools>Preferences>Oracle>Connection>Oracle Home (empty is autodetected):
At first Connect: The dropdown box was empty.
Therefore it must have connected by autodetection.

There was one and only one entry in the dropdown box: "XE"
I changed the dropdown box from empty to "XE".
On making this change, a notification dialogue said it would take affect
the next time I opened PL/SQL Developer.
I then shut down both PO/SQL Developer and the machine, itself.

Second opening of PL/SQL Developer:
It opened the same as the first time
Tools>Preferences>Oracle>Connection>Oracle Home (empty is autodetected):
The dropdown box value is now "XE", when SQL/Developer was opened.



Additional note: whenever I open PL/SQL Developer, I receive a warning of a
character set mismatch between database (AL32UTF8) and client (WEBMSWIN1252).
This has always happened, ever since I installed PL/SQL Developer. I do not
believe this will affect your decision-making process, but I thought I should
bring it to your attention, just in case it does matter.

Thank you for helping me Alex
Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Fri 10 Sep 2010 11:06

Hello,


>In my Delphi app, using the 'DevartOracleDirect' DriverName, I tried to
>connect with "DEDICATED:1521:XE" and then "Owner:1521:XE" and then >"OWNER:1521:XE".

In your case the DataBase property should be "Owner-PC:1521:XE", or "127.0.0.1:1521:XE", or "localhost:1521:XE". Try to use these values.


>Additional note: whenever I open PL/SQL Developer, I receive a warning of a
>character set mismatch between database (AL32UTF8) and client >(WEBMSWIN1252).
>This has always happened, ever since I installed PL/SQL Developer. I do not
>believe this will affect your decision-making process, but I thought I should
>bring it to your attention, just in case it does matter.

It doesn't affect connection to the to server.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Fri 10 Sep 2010 16:07

Greetings Alex,

As per your instructions, in my Delphi app, using the 'DevartOracleDirect' DriverName, I tried to connect with: "Owner-PC:1521:XE" and then "127.0.0.1:1521:XE" and then "localhost:1521:XE"

In all cases I received the same Windows socket error ('requested address is not valid...').

Alex, I have an idea that I would like to bring to your attention. I have noticed that when I connect SQL/Developer to Oracle, it requires 4 parameters. In addition to the usual Username, Password, and Database, it also requires a 'Connect as' parameter. In the 'Connect as' dropdown box, three values are listed: 1) Normal, 2) SYSDBA, and 2) SYSOPER. I can connect PL/SQL Developer with both 'Normal' and 'SYSDBA' values. However, when I connect with 'SYSOPER' I am denied because of lack of privileges. This may have nothing to do with our problem here, but I thought I would bring it to your attention.

I would also like to add that dbExperss connects to InterBase and FireBird without any problems at all.

Thank you for your work Alex.

I hope you can see that I am working hard too.

Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Postby AlexP » Mon 13 Sep 2010 13:18

Hello,

This error also may occur when using firewalls, please disable firewalls and try to connect again.

Please try to reinstall your Oracle client or install another Oracle client.
AlexP
Devart Team
 
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Postby Keith » Tue 14 Sep 2010 06:11

Greetings Alex,

The Windows 7 firewall was already off. I checked it when starting windows with the Admin profile and the Owner profile. In both cases the Windows 7 firewall was off.

However, I will check if HP (the comp mfg) has installed one.

I will try to reinstall the Oracle client tomorrow.

Thanks,

Keith
Keith
 
Posts: 12
Joined: Tue 31 Aug 2010 18:55

Next

Return to dbExpress driver for Oracle