Board index » delphi » dbExpress - the glass is half empty - or is the glass half full :-) ?

dbExpress - the glass is half empty - or is the glass half full :-) ?

[Please let me know if another place (beside a
trash can :-) is more appropriate for my posting.]

I performed a simple(!!) test to connect a Delphi 7 app
to my Oracle 9 database.  It was somewhat successful,
and somewhat not successful.

Server environment:
        Oracle 9
        Windows (NT or XP, I'm not sure)
Client environment:
        Oracle 8 network client
        Oracle 9 network client
        Windows XP
        Delphi 7 Enterprise
No middle tiers, at least for now.
Net environment: No Internet, intranet only.

I created a new app, dropped these components onto my form:
* TSQLConnection, TSQLQuery, TDataSetProvider,
         TClientDataSet, TSQLMonitor
* TDataSource, TDBNavigator, TDBGrid,
         TDBEdit, TDBText (3 of them)
* TButton, TMemo

Quick description of how I connected the db components to each other:
SQLConnection <-- SQLQuery (SQLConnection) <-- DataSetProvider (DataSet)
<-- DataSetClient (ProviderName) <-- DataSource (DataSet)
<-- Data Aware Controls, e.g. DBGrid (DataSource)
Also,
SQLConnection <-- SQLMonitor (SQLConnection)

I changed the SQLConnection DriverName property to Oracle.
Delphi 7 IDE automatically filled in 3 other properties when
I did so.  I opened the editor for the SQLConnection Params
property and added my username, password, and server name
(the same information I provide when I start Oracle 9
client's SQL*Plus).  At some point, I verified the connection
by changing SQLConnection Active property to true (then I
changed it back to false).

I changed SQLQuery1 SQL property, adding a statement such as
         select
         col1, col2, col3, rpad(rtrim(col4),30) calcfield1
         from mytable order by col2

I updated the data aware components (DBGrid, DBEdit, etc) to use
col1, col2, col3, calcfield1 field names.

I added an OnClick event handler to the TButton so that I could
toggle between opening and closing my query (SQLQuery.Open,
SQLQuery.Close).

THE GOOD NEWS FIRST
===================
I ran the app.  I was able to navigate both forward and
backward through the rows of my DataSet.  I haven't yet
attempted to update the data.  (i.e. call ApplyUpdates
to make my changes permanent on the Oracle 9 server)

FIRST ISSUE: DBEXPRESS IS USING ORACLE 8 NOT ORACLE 9
=====================================================

I exited my app.  I added an event handler (OnTrace) for
the SQLMonitor component and used Memo1.Lines.Append to
add the SQLMonitor's messages to the form at run time.  I
also set SQLMonitor Active to True (at runtime, just
before SQLQuery.Open)

I ran the app again.  When I viewed the SQL monitor's
messages, it appeared that the dbExpress SQLConnection
was using the ___ORACLE 8___ client to connect to
the ___ORACLE 9___ server.

QUESTION #1: how do I configure Delphi 7 dbExpress so
that I use the Oracle 9 client?  I know there's 2 .ini
files for dbExpress, but which .ini file, what do I use
to update (notepad?) and what do I change?

Question #1A: At the moment I don't know why I'd need
to write Delphi 7 code to connect to the old Oracle 8
server.  But I might need to.  How do I set up two
different dbExpress Oracle connections, so that 1
connection is using the Oracle 9 client to the Oracle 9
  server and the other is using the Oracle 8 client to
the Oracle 8 server?  What limitations are there, if any?

SECOND ISSUE: SQL BUILDER IN DBEXPRESS
======================================

In the IDE, when I right-clicked on SQLQuery component on
my form, I didn't see SQL Builder under 'Execute' (which is
where it is for the BDE TQuery component).

QUESTION #2 - Where is SQL Builder for dbExpress's TSQLQuery?

If we need or want to use SQL Builder, which we do, and the
answer is, "SQLBuilder is only available from a BDE TQuery
component at design-time", then that implies that we MUST
use a design-time BDE TQuery component to connect to our
Oracle 9 server.  After working out the SQL statement in
SQL builder, I'd then copy it to the dbExpress SQLQuery.SQL
property; I'd ignore BDE and TQuery at runtime.  (A little
awkward during development, but I think I can live with it).

This leads me to my next question (only need an answer if
BDE is the only way to access SQL Builder).

QUESTION #2A - How do we set up BDE Oracle driver and
alias for Oracle 9 so that BDE TQuery component will
operate properly at design time in Delphi 7 IDE?

Thanks.
Linda Murphy

 

Re:dbExpress - the glass is half empty - or is the glass half full :-) ?


Hello !

Quote
> THE GOOD NEWS FIRST
> ===================
> I ran the app.  I was able to navigate both forward and
> backward through the rows of my DataSet.  I haven't yet

I am happy for you ! <g>

Quote
> QUESTION #1: how do I configure Delphi 7 dbExpress so
> that I use the Oracle 9 client?  I know there's 2 .ini
> files for dbExpress, but which .ini file, what do I use
> to update (notepad?) and what do I change?

Look at dbxdrivers.ini file, section Oracle. It has line
VendorLib=oci.dll. This line talks to DbExpress driver
which Oracle client library (OCI) it should use. If you
have 2 or more Oracle clients installed, they are in
different "Oracle homes", and just one of them is "active".
Active is one which have <Oralce home>\Bin first in
the PATH environment variable. In two words, DbExp
driver will load Oracle client library, which is first in
PATH.

Quote
> How do I set up two different dbExpress Oracle connections,
> so that 1 connection is using the Oracle 9 client to the Oracle 9
>   server and the other is using the Oracle 8 client to
> the Oracle 8 server?  

Easiest way will be register 2 DbExp drivers in
dbxdrivers.ini. Example:

[Installed Drivers]
Oracle8=1
Oracle9=1

[Oracle8]
LibraryName=dbexpora.dll
VendorLib=c:\Oracle\Ora81\bin\oci.dll
<all other lines from Oracle section>

[Oracle9]
LibraryName=dbexpora.dll
VendorLib=c:\Oracle\Ora91\bin\oci.dll
<all other lines from Oracle section>

Now you can connect to Oracle explicitly using
any installed Oracle client. Or you can perform
similar setup directly with TSQLConnection.

Quote
> What limitations are there, if any?

No limitations.

Quote
> QUESTION #2A - How do we set up BDE Oracle driver and
> alias for Oracle 9 so that BDE TQuery component will
> operate properly at design time in Delphi 7 IDE?

Sorry, this is a question for BDE newsgroup.

Regards,
Dmitry

<IMHO and not 100% True>

===========================================
Dmitry L. Arefiev, director of gs-soft.ru ltd.
Solutions for successful companies

Author of NCOCI8 - Freeware Delphi to Oracle8i direct access

ICQ: 50741007
EMail: daref...@gs-soft.ru
Company: http://www.gs-soft.ru
NCOCI8: http://www.da-soft.com

Re:dbExpress - the glass is half empty - or is the glass half full :-) ?


Quote
Dmitry Arefiev [gs-soft.ru] wrote:

>>How do I set up two different dbExpress Oracle connections,
>>so that 1 connection is using the Oracle 9 client to the Oracle 9
>>  server and the other is using the Oracle 8 client to
>>the Oracle 8 server?  

>Easiest way will be register 2 DbExp drivers in
>dbxdrivers.ini. Example:

>[Installed Drivers]
>Oracle8=1
>Oracle9=1

>[Oracle8]
>LibraryName=dbexpora.dll
>VendorLib=c:\Oracle\Ora81\bin\oci.dll
><all other lines from Oracle section>

>[Oracle9]
>LibraryName=dbexpora.dll
>VendorLib=c:\Oracle\Ora91\bin\oci.dll
><all other lines from Oracle section>

>Now you can connect to Oracle explicitly using
>any installed Oracle client. Or you can perform
>similar setup directly with TSQLConnection.

>>What limitations are there, if any?

>No limitations.

>Regards,
>Dmitry

><IMHO and not 100% True>

>===========================================
>Dmitry L. Arefiev, director of gs-soft.ru ltd.
>Solutions for successful companies

>Author of NCOCI8 - Freeware Delphi to Oracle8i direct access

>ICQ: 50741007
>EMail: daref...@gs-soft.ru
>Company: http://www.gs-soft.ru
>NCOCI8: http://www.da-soft.com

Thank you.  It worked. :-)
Linda

Re:dbExpress - the glass is half empty - or is the glass half full :-) ?


Quote
"Linda Murphy" wrote:
> Thank you.  It worked. :-)

Please dont quote the entire message when just adding "thank you".

Refer to item 1 of:

http://info.borland.com/newsgroups/netiquette.html

Thank you <g>

--
Dave Nottage (TeamB)

Other Threads