Connecting to Oracle base

Hi

I've been using Delphi for some time, but I have never used any of the database capabilities. I would like to use Delphi5 to extract some data from an Oracle (8i) database, which is located at a local NT server. I havent really tried anything yet, but before I do, I would like to know what is required to do this. Have in mind that I know very little about the subject!

So, what is needed? I need to create an alias, right? And are the drivers provided with Delphi enough? And what if I would like to run the application on a clean machine? Are the 'Data access' components all I need?

My goal is to connect and extract some data (whatever) from a table. Thats it :-)

Regards,
Pede
LVL 1
pedeAsked:
Who is Participating?
 
mhervaisConnect With a Mentor Commented:
thanks pede :=)
0
 
mhervaisCommented:
Pede, I am afraid to tell you that Delphi's database capabilities are awkward.

Howether, they exist and are usable. and in D5 you even have some possibilities that implement special features of Oracle 8 that you will probably not use every day like sub tables.

How you access Oracle depends on the version of Delphi that you have :

The answer I give you begins with Delphi 3.

if you have a standard version, in theory you cannot connect. practically it is still possible to find database components,

with a professional version you may connect with (BDE) ODBC,

With a C/S version you vill use direct links.

Let's say that you have a professional version, What you will first need is a created database and running, SQLNET interfaces on your client, and an ODBC alias for your database.

when this is done you can see your database from Delphi. Try for instance to drop a TDatabase component from the database access folder on a form, to select the good alias name, and connect it. It should work.

For the rest I recommend you the complete reading of the sections of the book that come with delphi, because many things are far from SQL programming.

Regads Marc

When all these conditions a

0
 
pedeAuthor Commented:
I use Delphi5 Enterprise, and I do not want to use ODBC, it has to be as fast as possible. I wanted to use Delphi because ProC (which I've used so far) is very bad when working with strings. Are you telling me that Im better off with ProC?? I can see here at EE, that a lot of people use Delphi with databases, so I thought it was ok...

I reject your answer, since Im more likely to get more comments that way. That doesnt mean you wont get the points, but I need to know more about this than you have told me so far.

/Pede
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
mhervaisCommented:
No problem Pede.

Delphi is very bad with relational database for structural reasons rather than for a matter of connection:

It was first written to be working well with indexed files.

so for instance, when you use a ttable on an indexed file, you issue just a request, that positions a cursor on a starting position, and retrieves a limited amount of data. the quantity of transeferred data is small.

when you use an sql database with a TTable component, you begin to query the whole table without a where clause. Of cours in TTable you have a filter option, but this option is a post reading option.

You are better off with queries (TQuery components) but they are not optimized at least with ODBC (they do not make usage of the caching options of the database engines)

Stored procedures seem to work well, but you loose portability.

speaking about database access (because it is the main critic against BDE, you can find BDE replacement components on the site : www.kylecordes.com

On an other hand, though I don't know PRO C, I doubt that you would be better off with it or relational database access.

regards, Marc
0
 
kretzschmarCommented:
hi pede,

first to marc,
i must disagree with:
>Delphi is very bad with relational
>database for structural reasons
i work with oracle and delphi (d3-d5)
with millions of records and  thousands of tables
and never had a problem

i agree with:
>You are better off with queries (TQuery components)

well pede,
how to establish an oracle connection:
- first you must install and configure the oracle client software
- then create an alias with the bde-admin with the oracle driver
- and place in the entry server name your properly configured tns-name (oracle-alias)

thats all
maybe there are some additional changes in the other entrys

if you will develop only for oracle then i would recommend to use doa-components like this comercial
http://www.allroundautomations.nl/doa.html

be free to ask, if something unclear

meikl
0
 
mhervaisCommented:
kretzchmar ... you never tried Powerbuilder.

I do not say that you cannot do it with delphi.. I say that it is not optimized,
that when you are developping you have to reload delphi every two minutes because you get an out of memory error from BDE, that with TQuery you get bumped off when you use tables with a too large number of columns (I have some tables with 300 columns) and I am obliged to proceed like this because in 3dr normal form I get dreadful response times ... and so on.

Delphi is a wonderful tools for many things but for database it is a wreck even if you can use it. :=(

for the rest, your comment adds nothing to my first rejected answer..

regards, Marc
0
 
pedeAuthor Commented:
Hi

For now its Oracle only. A few hours ago, when I asked this question, I was really lost, but now Im catching up a little :o)

With the link mhervais sent me, I found the component you mention, meikl. I am using it now, and it just seem soooo easy compared to BDE. I will continue to use this, and maybe (just maybe..) I will return to BDE some day.

Im still a little confused, but at least Im satisfied for now.

If you propose an answer, mhervais, you will get the points.

Regards,
Pede

0
 
kretzschmarCommented:
hi marc,

well, i never used powerbuilder, but your explained problems i never had.

the bde comes only out of memory if you run your app in the ide and you select the ide function reset program for terminating the app.

about to many fields, i've also tables with such field amount, i've also no problem with it. maybe you should enable the schema cache and adjust the correspondending entrys in the bde.

alternative take a look to the given link above

meikl
0
 
karouriCommented:
dear pede,
I am currently in the same problem as yours,but i have found out the following points,(this in Delphi 4):
To access an Oracle database (ver 8.0 ) from the BDE:
1. The BDE must be at least version 5.10
2. You must install the oracle 8.0 SQL link driver from borland.
Both are found at http://www.borland.com/devsupport/bde/bdeupdate.html
3. After replacing the oracle 8.0 dll (or installing it if it is not there),change in BDE Administrator/Configuration/Drivers/Native/ORACLE the dll32 value to SQLORA8.dll, and vendor init to OCI.DLL.
This is explained in the text file accompanying the oracle 8.0 driver.
4. Then you make a new alias.In Server name you choose your server.

Try this and tell us about success or failue. I don't know the limits of the capabilities of the oracle 8.0 driver, but it did conect me to the database, wiht access to various objects.
0
 
pedeAuthor Commented:
Hi

As mentioned above I have decided to use DOA (Direct Oracle Access). It works perfectly, and was very easy to get running. Im just waiting for mhervais to propose an answer.

Thank you for your suggestions!

/Pede
0
 
jeffdyerCommented:
mhervais is clearly a twat
0
All Courses

From novice to tech pro — start learning today.