?
Solved

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

Posted on 2004-11-22
2
Medium Priority
?
1,692 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:Modellium
2 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 1500 total points
ID: 12651914
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
 

Expert Comment

by:VeluN
ID: 12653526
Hi,

Search to Metalink to get latest patch set.

Rgds,

Velu N
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question