Oracle Provider For OLE DB 9.2.0.4.0 can't update the recordset when it contains an INNER JOIN clause

Hello!
I'm trying to edit a recordset obtained from an Oracle 9.2.0.5 database.

When the SQL query is made with an inner join in it, I can't edit fields.
"SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP inner join DEPT  on EMP.DEPTNO = DEPT.DEPTNO "
But when the join is made within the "Where" section (and not with an inner join in the "from" section), it works fine.
"SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP, DEPT Where EMP.DEPTNO = DEPT.DEPTNO "

I just change the provider, from MS provider for Oracle to Oracle Ole DB provider to resolve an other problem with the clob datatype. All my queries in my code are made with "inner join" SQL.

Is there a way to resolve this issue without changing all my queries?




Here is the way I use to obtain the recordset :

Public Function ShowForm(strUserName As String, strPassword As String, strBD As String) As Boolean
 ' Set up Command and Connection objects
  Dim rs As ADODB.Recordset, Cmd As ADODB.Command
  Set rs = New ADODB.Recordset
  Set Cmd = New ADODB.Command

  'query with an inner join - not working to edit field
  strSQL = "SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP inner join DEPT  on EMP.DEPTNO = DEPT.DEPTNO "
 
  'query whithout an inner join - working well to edit field
  'strSQL = "SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP, DEPT Where EMP.DEPTNO = DEPT.DEPTNO "
 

  'Run the procedure
  Cmd.ActiveConnection = "Provider=OraOLEDB.Oracle.1;Password=" & strPassword & ";Persist Security Info=True;User ID=" & strUserName & ";Data Source=" & strBD

  Cmd.CommandText = strSQL
  Cmd.CommandType = adCmdText
  Cmd.CommandTimeout = 0
 
 
  rs.CursorLocation = adUseClient
  rs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
 
  ' Disconnect the recordset
  Set rs.ActiveConnection = Nothing
  Cmd.ActiveConnection.Close
 
End Function
ModelliumAsked:
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.

Helena Markováprogrammer-analystCommented:
I think that it is this BUG 3866119 - Oracle Provider for OLE DB - Updated 22-NOV-2004 - MOST JOINS NOT UPDATEABLE WHEN USING SQL 1999 SYNTAX

** 08/31/04 05:12 pm ***

< TAR>: 3928670.994
=========================

< PROBLEM>:

1. Clear description of the problem encountered:

When using the Oracle OLE DB Provider to execute queries containing JOINs and then update these queries, it fails with multiple errors as described below:

.

DOES NOT WORK WITH FOLLOWING ERROR MESSAGES

- ERROR: Insufficient key column information for updating or refreshing
.

CROSS JOIN

SELECT e.ename, d.deptno

FROM emp e

CROSS JOIN dept d

- ERROR: ORA-00942: table or view does not exist

.

CROSS JOIN

.

SELECT e.ename, e.deptno

FROM emp e

CROSS JOIN dept d

- ERROR: Multiple-step operation generated errors

.

(INNER) JOIN WITH ON - (NOTE: INNER keyword is optional)

.

SELECT empno, ename

FROM emp e

JOIN dept d ON (d.deptno = e.deptno)

.

SELECT empno, ename

FROM emp e

INNER JOIN dept d ON (d.deptno = e.deptno)

.

SELECT e.ename

FROM emp e

JOIN dept d ON (e.deptno = d.deptno)

.

SELECT e.ename

FROM emp e

INNER JOIN dept d ON (e.deptno = d.deptno)

.

(INNER) JOIN WITH USING - (NOTE: INNER keyword is optional)

.

SELECT e.ename

FROM emp e

JOIN dept d USING (deptno)

.

SELECT e.ename

FROM emp e

INNER JOIN dept d USING (deptno)

.

LEFT OUTER JOIN

SELECT e.ename, d.deptno

FROM dept d LEFT OUTER JOIN emp e ON e.deptno = d.deptno

ORDER BY d.deptno

.

RIGHT OUTER JOIN

.

SELECT e.ename, d.deptno

FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno

ORDER BY d.deptno

.

FULL OUTER JOIN

.

SELECT e.ename, d.deptno

FROM dept d FULL OUTER JOIN emp e ON e.deptno = d.deptno

ORDER BY d.deptno

.

SQL 1999 JOIN SYNTAX THAT ***WORKS***

NATURAL JOIN

SELECT ename, deptno

FROM emp NATURAL JOIN dept

.

CROSS JOIN (not qualifying select list with table name)

SELECT ename

FROM emp CROSS JOIN dept

.

ALL ORACLE JOIN SYNTAX WORKED

.

.

Both types work fine from SQL*Plus!

.

2. Pertinent configuration information (MTS/OPS/distributed/etc)

.

Client

Windows XP Sp1

Oracle 10.1.0.3.0 Client

Oracle OLE DB Provider 10.1.0.2.0

.

Server

Windows XP Sp1

Oracle 10.1.0.3.0 Database

.

3. Indication of the frequency and predictability of the problem

.

Consistently

4. Sequence of events leading to the problem

.

Execute these joins using the Oracle OLE DB Provider and errors as documented above occur.

5. Technical impact on the customer. Include persistent after effects.

.

Cust must rewrite SQL statements to use Oracle JOIN syntax. This is a major rewrite for the customer and prevents same application from working with non-Oracle database such as SQL Server.

.
=========================

< DIAGNOSTIC ANALYSIS>:

.

- ALL OF THE QUERIES DOCUMENTED ABOVE WORK FROM SQL*PLUS

.

Verified that the following is being done as documented:

.

Rowsets created using queries with joins are updatable by OraOLEDB only with the Client Cursor Engine enabled. C/C++ OLE DB consumers must enable this service to make these rowsets updatable. ADO consumers must specify the CursorLocation as adUseClient to make these rowsets updatable.

.

Guess: Possible base table information not being properly provided to CCE for these types of JOINS.

.

=========================
< WORKAROUND>:

.

(1) Use the Microsoft OLE DB Provider for Oracle.

.

(2) Use the Oracle ODBC Driver.

.

(3) Use the Microsoft ODBC Driver.

.

(4) Rewrite your queries using the Oracle JOIN Syntax rather than SQL 1999 syntax.

*** 08/31/04 05:12 pm ***

=========================

< RELATED BUGS>:

Bug 3628047.

=========================

< REPRODUCIBILITY>:

1. State if the problem is reproducible; indicate where and predictability

.

Yes

2. List the versions in which the problem has reproduced

Oracle OLEDB Provider

10.1.0.2.0

      9.2.0.4.1

3. List any versions in which the problem has not reproduced
.....................................................................................................

So you have to use one of the workarounds or wait for a patch.
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
VeluNCommented:
Hi,

Search to Metalink to get latest patch set.

Rgds,

Velu N
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
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.