Join condition
GERARD LIM -- GERARDLIM@ych.com
Thursday, January 23, 1997
Environment: NT 4.0 VC 4.2flat
I have a dialog box with an Remote Data Control and an Apex Data Bound Grid
bound to the RD Control. The Remote Data Control is accessing an Informix
Database on a UNIX server through ODBC. The Remote Data Control's SQL is a
long joint SELECT statement of 4 tables. When I try to update the grid,
the following message occurs:
MSRDC32
SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be
performed because result set was operated by joint condition.
Is there a way I can overcome this problem?
Any help would be greatly appreciated!
Andrew Borden -- borden@ms.com
Friday, January 24, 1997
[Mini-digest: 4 responses]
For most joined result sets or result sets generated by a stored proce
you have to perform an explicit update query to update the database with your
changes. This is because ODBC has no way of knowing how to perform the update
on the joined tables (e.g. you may have altered a key used in the join, in
which
case an insert or delete/insert might be appropriate.) Perhaps you can:
1.) trap the update condition
2.) read the new column values from the grid
3.) do whatever sql execs you need to do to perform the update
e.g mycdatabase.executesql("update table1 set field1=value1....");
4.) kill the bound-update event
5.) requery or refresh the grid to reflect the changes.
In situations such as yours, apps often pop up and edit dialog with the
fields from the current row in edit boxes etc. Then the user can edit and
select ok/cancel. You can validate the input and execute an update/insert
query as needed. Refresh the grid and the user's changes will show up.
I hope this info helps.
-Andrew
On Jan 23, 5:24pm, GERARD LIM wrote:
> Subject: Join condition
>
> Environment: NT 4.0 VC 4.2flat
>
> I have a dialog box with an Remote Data Control and an Apex Data Bound Grid
> bound to the RD Control. The Remote Data Control is accessing an Informix
> Database on a UNIX server through ODBC. The Remote Data Control's SQL is a
> long joint SELECT statement of 4 tables. When I try to update the grid,
> the following message occurs:
>
> MSRDC32
>
> SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be
> performed because result set was operated by joint condition.
>
> Is there a way I can overcome this problem?
>
> Any help would be greatly appreciated!
>-- End of excerpt from GERARD LIM
-----From: djs@sandyhook.com (David J. Straley)
The MFC Recordset documentation states that you cannot update a JOIN.
Sorry 'bout that. Presumably, then, RDO has a similar feature.
Regards,
Dave Straley
On Thu, 23 Jan 97 17:24:00 PST, you wrote:
>
>Environment: NT 4.0 VC 4.2flat
>
>I have a dialog box with an Remote Data Control and an Apex Data Bound =
Grid=20
>bound to the RD Control. The Remote Data Control is accessing an =
Informix=20
>Database on a UNIX server through ODBC. The Remote Data Control's SQL =
is a=20
>long joint SELECT statement of 4 tables. When I try to update the =
grid,=20
>the following message occurs:
>
>MSRDC32
>
>SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be=20
>performed because result set was operated by joint condition.
>
>Is there a way I can overcome this problem?
>
>Any help would be greatly appreciated!
>
--------------------------------------=20
David J. Straley * djs@sandyhook.com
Sandy Hook Software Corp. * http://www.sandyhook.com
-----------------------------------------=20
Software Development Outsourcing Services
Publishers of "Quik-Take Tech Strategies" Newsletter (Free)
-----------------------------------------=20
-----From: SCS.010@mch.scn.de
Hi,
U can refer to the following article "How to use Updatable Joins in an MFC
ODBC application" in MSDN. The article is also helpfull if you are using
direct ODBC calls.
Hope this helps.
Tarun Mehta.
-------------
Original Text
>From GERARD LIM , on 1/25/97 1:50 AM:
Environment: NT 4.0 VC 4.2flat
I have a dialog box with an Remote Data Control and an Apex Data Bound Grid
bound to the RD Control. The Remote Data Control is accessing an Informix
Database on a UNIX server through ODBC. The Remote Data Control's SQL is a
long joint SELECT statement of 4 tables. When I try to update the grid,
the following message occurs:
MSRDC32
SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be
performed because result set was operated by joint condition.
Is there a way I can overcome this problem?
Any help would be greatly appreciated!
0
-----From: Jim Leavitt
Gerald,
I'll bet the recordset is NOT updateable. Rdo resultsets that use joins =
are not updateable in SQL server ( I don't know about Informix ), that =
may be your problem. The rdoresultset object has an 'updateable' =
property. Check to see if your resultset is updateable.
If it is not, you may need to create another rdoresultset object that =
grabs the affected record(s) from a single table query and then update =
them. I'll agree, it is rather inconvenient.
Jim Leavitt
-----Original Message-----
From: GERARD LIM [SMTP:GERARDLIM@ych.com]
Sent: Thursday, January 23, 1997 5:24 PM
To: Mircosoft Foundation Class
Subject: Join condition
Environment: NT 4.0 VC 4.2flat
I have a dialog box with an Remote Data Control and an Apex Data Bound =
Grid=20
bound to the RD Control. The Remote Data Control is accessing an =
Informix=20
Database on a UNIX server through ODBC. The Remote Data Control's SQL =
is a=20
long joint SELECT statement of 4 tables. When I try to update the =
grid,=20
the following message occurs:
MSRDC32
SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be=20
performed because result set was operated by joint condition.
Is there a way I can overcome this problem?
Any help would be greatly appreciated!
Phil Reeves -- p.reeves@rbh.nthames.nhs.uk
Monday, January 27, 1997
At 17:24 23/01/97 PST, you wrote:
>
>Environment: NT 4.0 VC 4.2flat
>
>I have a dialog box with an Remote Data Control and an Apex Data Bound Grid
>bound to the RD Control. The Remote Data Control is accessing an Informix
>Database on a UNIX server through ODBC. The Remote Data Control's SQL is a
>long joint SELECT statement of 4 tables. When I try to update the grid,
>the following message occurs:
>
>MSRDC32
>
>SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be
>performed because result set was operated by joint condition.
>
>Is there a way I can overcome this problem?
>
>Any help would be greatly appreciated!
ODBC won't allow you to update a join. I don't know Informix, but with
Oracle you can get round this by defining a view and updating that.
Philip Reeves
Knowledge Engineer
*************************************
Royal Brompton Hospital
Sydney Street
London
SW3 6NP
UK
Tel: +44 (0)171 351 8702
Fax: +44 (0)171 351 8743
e-mail: p.reeves@rbh.nthames.nhs.uk
web: http://www.rbh.nthames.nhs.uk
*************************************
Ian Pepper -- Ian@flexicom.ie
Monday, January 27, 1997
Hi Gerard,
This is actually a SQL issue. In general, you cannot update a database
result set generated from a select join statement. This applies to both
inner and outer joins. There are some scenarios where it is possible to
do an update (but none that I remember offhand). Instead you must
update each table individually. Try binding the grid to the table with
most columns returned and then kick off the other updates as necessary.
Often, when one or more of the joined tables is a 'look up' table then
this is not usually a major coding problem.
HTH,
Ian
ian@flexicom.ie
>
P. Senthil -- senthilp@geocities.com
Tuesday, January 28, 1997
Phil Reeves wrote:
>
> ODBC won't allow you to update a join. I don't know Informix, but with
> Oracle you can get round this by defining a view and updating that.
Even in Oracle if the view contains a join of multiple tables, the view
cannot be updated. Views with single tables and with certain other
restrictive conditions only views are updatable. This is because only
one table can be updated by the RDBMS. So this is a question of general
RDBMS rather than any particular back-end.
Fernando Pereira -- morgan@cardume.com
Wednesday, January 29, 1997
[Mini-digest: 2 responses]
Sorry, but you are out of date. Oracle 7 allows updates/inserts on multi-table views.
Fernando
Phil Reeves wrote:
>
> ODBC won't allow you to update a join. I don't know Informix, but with
> Oracle you can get round this by defining a view and updating that.
Even in Oracle if the view contains a join of multiple tables, the view
cannot be updated. Views with single tables and with certain other
restrictive conditions only views are updatable. This is because only
one table can be updated by the RDBMS. So this is a question of general
RDBMS rather than any particular back-end.
-----From: jeremy@omsys.com (Jeremy H. Griffith)
On Mon, 27 Jan 1997 09:43:09 GMT, Phil Reeves
wrote:
>At 17:24 23/01/97 PST, you wrote:
>>
>>Environment: NT 4.0 VC 4.2flat
>>
>>I have a dialog box with an Remote Data Control and an Apex Data Bound Grid
>>bound to the RD Control. The Remote Data Control is accessing an Informix
>>Database on a UNIX server through ODBC. The Remote Data Control's SQL is a
>>long joint SELECT statement of 4 tables. When I try to update the grid,
>>the following message occurs:
>>
>>MSRDC32
>>
>>SL002: [Microsoft][ODBC Cursor Library] Positioned request cannot be
>>performed because result set was operated by joint condition.
>>
>>Is there a way I can overcome this problem?
>>
>>Any help would be greatly appreciated!
>
>ODBC won't allow you to update a join. I don't know Informix, but with
>Oracle you can get round this by defining a view and updating that.
With MS (and Sybase) SQL Server, there's a TSQL extension to the UPDATE
statement that allows a FROM clause, so that the WHERE can refer to
multiple tables. The table to be updated must be the first in the FROM.
You can still only update one table with the statement, but you can use
the join to specify the rows to update.
This is described in the docs as "similar to using a correlated subquery
as a search-condition in the WHERE clause of a standard UPDATE." This
suggests that you might be able to achieve the same effect by using such
a correleated subquery in Informix.
--Jeremy
| Вернуться в корень Архива
|