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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
mhervaisCommented:
thanks pede :=)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jeffdyerCommented:
mhervais is clearly a twat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.