HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Martin Pettit

Delphi Developer

Tue, 13 Jan 2004 16:40:27 GMT

HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Hi, I have an existing app which attaches to either an Access or
Oracle 8 database using the BDE v5.01 and the Oracle 8.00.4 ODBC
driver. The connection works fine when using the Access ODBC
driver, but wehen I switch to the Oracle version,which can be
doen at runtime, I get a problem.
I can access tables in readonly mode, but when I try to set the
RequestLive proprty of a TQuery accessing one of the Oracle
tables, I get an 'Invalid Table name' error.
When I dig deeper, it seems that the problem occurs when trying
to process the OpenCursor function in the BDEDataset, which
happens when the dataset is first opened. I have
logged on with full permissions, set the database readonly to
False, I have even tried a new app with just a TQuery, a DBGrid
and a TDatasource component on a form and still get the same
error.
I know from reading other messages that using this BDE-ODBC config to access an Oracle database ids not recommended, but I
have picked up this project after 180,000 lines of code were
already written, and a trial version with a customer that works.
I must also retain the ability to switch between Access and Oracle databases within the same source code.
Have I set up something incorrectly in the BDE or the ODBC
System DSN?
Any help would bve greatly appreciated.

Martin

John Pierc

Delphi Developer

Wed, 14 Jan 2004 00:31:16 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Are you using UPPER CASE for all of your table names? The BDE puts
table names in quotes in RequestLive mode, and this makes them
case-sensitive to Oracle. As a general rule, it is easiest to put all of
your Oracle SQL statements in UC. I doubt that this will make a
difference to Access, but I don't use it.

John PIerce

John Pierc

Delphi Developer

Wed, 14 Jan 2004 02:12:18 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Looking back at your previous query, this is precisely what Nicholas
Bronke said.

Martin Petti

Delphi Developer

Fri, 16 Jan 2004 17:17:09 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Quote

>Are you using UPPER CASE for all of your table names?

Yes, I've checked this - all tables are in upper case and all
SQL statements are in Upper case as well.
Thanks for the advice, is there anything else I could check. I'm at a loss...

Martin

Martin Petti

Delphi Developer

Fri, 16 Jan 2004 17:56:13 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Quote

John Pierce <jpie...@healthplanning.com> wrote:
>Are you using UPPER CASE for all of your table names? The BDE puts
>table names in quotes in RequestLive mode, and this makes them
>case-sensitive to Oracle. As a general rule, it is easiest to put all of
>your Oracle SQL statements in UC. I doubt that this will make a
>difference to Access, but I don't use it.

>John PIerce

Martin Petti

Delphi Developer

Fri, 16 Jan 2004 17:59:56 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

I think I have found a common problem. If I try to access a
table whose name contains a '_' character, the response
'Invalid Table name' is returned. If I try to access a table
with a simple ASCII text name, ti works ok.
So my 'ACCOUNT_ACTIVITY' table cannot be opened in RequestLive
mode, but 'OUTLET' and 'EXCEPTIONS' can.
Is there any rule regarding allowable table names, or the
maximum length of table names?

Thanks

Martin

John Pierc

Delphi Developer

Sat, 17 Jan 2004 00:32:56 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Quote

Martin Pettit wrote:
> I think I have found a common problem. If I try to access a
> table whose name contains a '_' character, the response
> 'Invalid Table name' is returned.

There's something else going on. There's no problem accessing tables
containing an underscore character. Table names can be a maximum of 30
characters long, and there are some restrictions, which you can look up,
but they're not tricky. If you've got Enterprise, look at the query in
SQL Monitor and see what's being sent to the database. Also, try your
queries in Sql*plus, to see if you get an error. Do you have quote
marks, or anything like that?

John PIerce

Oliver Townshen

Delphi Developer

Sun, 18 Jan 2004 18:03:15 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Quote

> Hi, I have an existing app which attaches to either an Access or
> Oracle 8 database using the BDE v5.01 and the Oracle 8.00.4 ODBC
> driver.

As I understand it, the BDE is certified for Oracle 8.0.5.

Oliver

Martin Petti

Delphi Developer

Sun, 18 Jan 2004 18:09:23 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

I think I may have found it. I am using Oracle ODBC driver 8.04.00 and I have just read on Oracle's technical site that there is a bug with Oracle ODBC drivers 8.04.00 and 8.04.01 where tables with an underscore are sometimes not accessed properly. I have just updated my MDAC settings to get the latest Microsoft ODBCX drivers and using Microsoft Oracle ODBC driver v2.51, it connects ok!
I have written to Oracle to get 8.04.04 driver - I wait with baited breath to see if they send it!

Thanks for all your help,

Martin

John Pierc

Delphi Developer

Mon, 19 Jan 2004 01:00:53 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

Quote

Martin Pettit wrote:
> I think I may have found it. I am using Oracle ODBC driver 8.04.00 and I have just read on Oracle's technical site that there is a bug with Oracle ODBC drivers 8.04.00 and 8.04.01 where tables with an underscore are sometimes not accessed properly. I have just updated my MDAC settings to get the latest Microsoft ODBCX drivers and using Microsoft Oracle ODBC driver v2.51, it connects ok!
> I have written to Oracle to get 8.04.04 driver - I wait with baited breath to see if they send it!

Glad you solved it! Take my advice and get Direct Oracle Access and all these little, nagging problems will go away. You'll also get a great speed improvement. ODBC is not the method of choice for accessing Oracle.

John Pierce

June Waldbaue

Delphi Developer

Sun, 25 Jan 2004 04:17:46 GMT

Re:HELP!- D4-BDE-ODBC-Oracle 8 RequestLive connection problem

I had a problem when doing "RequestLive" with an Oracle query. I found on
the Borland site that when RequestLive is false, Oracle automatically
handles any case changes necessary in the table and field names. When
RequestLive is true, the BDE passes the table and column names in quotes and
they do not get shifted to upper case. Changing the table names and field
names to upper case in my queries allowed me to set RequestLive = True.

Quote

"Oliver Townshend" <oli...@zip.com.au> wrote in message

news:3b67d4bf_2@dnews...

Quote

> > Hi, I have an existing app which attaches to either an Access or
> > Oracle 8 database using the BDE v5.01 and the Oracle 8.00.4 ODBC
> > driver.

> As I understand it, the BDE is certified for Oracle 8.0.5.

> Oliver

Other Threads

1. problem selecting BLOBs using RequestLive, Oracle 7.3.3, BDE 3.51, D2

2. d4: tuning bde, odbc, oracle, blobs

3. beginning connection help with Oracle and D4

4. ODBC connection to Oracle 8, please help

5. Help with ODBC Connection To Personal Oracle 8

6. SQL7 + ODBC + RequestLive => BDE Crash

7. Delphi 5+Oracle 8i +BDE 5.11 Two query master-Detail (Requestlive=true)

8. An Oracle-BDE connection problem

9. Problem of connection BDE/ORACLE/WindowsXP

10. An Oracle-BDE connection problem