SELECT ... FOR UPDATE does not work with ODBC
Karg Michael
I use the ODBC ver 2.0 (32 Bit) (english) driver and
SQL-Server 6.0 (german) under NT 4.0 beta 1.
If I try to use
SELECT x FROM y FOR UPDATE
The driver responds with an error that the FOR UPDATE
clause could only be used with a DECLARE CURSOR
statement. If i try to use
DECLARE C1 CURSOR FOR ....
SQLPrepare works, but you could not bind any colums
nor execute works...
So I think it's a bug, but comes the bug from
NT 4.0 beta or from the ODBC-Driver?
Has anybody encountered this problem?
--
----------------------------------------------------------
IMSoft - Dipl.-Ing. Michael Karg
Graf Starhemberggasse 32/2/3
1040 Wien
Austria
----------------------------------------------------------
e-mail : ka...@uranus.tuwien.ac.at
Tel & Fax: ++43/1/5056190
WWW : http://uranus.tuwien.ac.at/~karg/imsoft.html
Rick Morris
>If I try to use
>
>SELECT x FROM y FOR UPDATE
>
>The driver responds with an error that the FOR UPDATE
>clause could only be used with a DECLARE CURSOR
>statement. If i try to use
>DECLARE C1 CURSOR FOR ....
>SQLPrepare works, but you could not bind any colums
>nor execute works...
>So I think it's a bug, but comes the bug from
>NT 4.0 beta or from the ODBC-Driver?
>
That's pretty funny, isn't it? I found the same thing.
I got my idea from the SQL Books On-line. They have
an example that does a SELECT x FROM y FOR UPDATE!
I have no idea what you can do about it. I finally gave up
and tried a whole new tactic!
-Rick.
Lynne Warner
Karg Michael wrote:
> I use the ODBC ver 2.0 (32 Bit) (english) driver and
SQL-Server 6.0 (german) under NT 4.0 beta 1.
SELECT x FROM y FOR UPDATE
The driver responds with an error that the FOR UPDATE
clause could only be used with a DECLARE CURSOR
statement. If i try to use
DECLARE C1 CURSOR FOR ....
SQLPrepare works, but you could not bind any colums
nor execute works...
> So I think it's a bug, but comes the bug from
> NT 4.0 beta or from the ODBC-Driver?
I'd guess it is the ODBC driver. Have you posted this to the new MS
newsgroups? In particular, try "microsoft.public.sqlserver.odbc". MS's
NNTP server is "msnews.microsoft.com"
Karg Michael
Rick Morris wrote:
>
> >If I try to use
> >
> >SELECT x FROM y FOR UPDATE
> >
> >The driver responds with an error that the FOR UPDATE
> >clause could only be used with a DECLARE CURSOR
> >statement. If i try to use
> >DECLARE C1 CURSOR FOR ....
> >SQLPrepare works, but you could not bind any colums
> >nor execute works...
> >So I think it's a bug, but comes the bug from
> >NT 4.0 beta or from the ODBC-Driver?
> >
>
> I got my idea from the SQL Books On-line. They have
> an example that does a SELECT x FROM y FOR UPDATE!
>
> I have no idea what you can do about it. I finally gave up
> and tried a whole new tactic!
>
> -Rick.
I've found somthing in the MSDN:
2.50.0121 6.00
WINDOWS
----------------------------------------------------------------------
The information in this article applies to:
- Microsoft SQL Server, version 6.0
- Microsoft Open Data Base Connectivity, version 2.5
----------------------------------------------------------------------
BUG# NT: 11019 (SQLBUG 6.00)
SYMPTOMS
========
When you use the ODBC SQL Server driver in conjunction with Microsoft SQL
Server version 6.0 server-side cursors to attempt the creation of an
updateable rowset via SQLPrepare(), the FOR UPDATE portion of the SELECT
statement returns an incorrect error message.
WORKAROUND
==========
If it you do not need to use SQLPrepare() to create your updateable rowset,
then you should use SQLExecDirect() to achieve this functionality.
Otherwise, the same functionality can be accomplished by excluding the FOR
UPDATE portion of the SELECT statement from the string passed to
SQLPrepare(). When this query has been prepared, the rowset may still be
operated on with modification operations using SQLExtendedFetch() and
SQLSetPos().
STATUS
======
Microsoft has confirmed this to be a problem in the ODBC SQL Server Driver
version 2.50.0121. We are researching this problem and will post new
information here in the Microsoft Knowledge Base as it becomes available.
MORE INFORMATION
================
For example, with the SQLSetStmtOption() settings made to invoke server
side cursor usage (as defined in SQL Books Online under the ODBC SQL Server
Driver section headings "Using ODBC Cursors" and "Creating Cursors") the
statement:
Select * from <table_name> for update
returns the following incorrect error message:
Return Code = SQL_ERROR = -1
szSqlState = "37000"
*pfNativeError = 1003
*pcbErrorMsg = 105
szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
FOR UPDATE clause allowed only for DECLARE CURSOR."
Additional reference words: 2.50.0121 6.00 sql6
KBCategory: kbprg kbbug2.50.0121 kbbug6.00
KBSubcategory: SSrvProg
=============================================================================
Copyright Microsoft Corporation 1995.
So Microsoft ist working on it...
Steve Tilson
FOR UPDATE is valid in ODBC when used with cursors only.
Try:
UPDATE TABLE1
SET TABLE1.col1 = TABLE2.col2
FROM TABLE1, TABLE2
WHERE TABLE1.keycol = TABLE2.fkeycol
something along that line will work although the syntax be be off a tad.
good luck...
Arnd Liman
In article <318F59...@uranus.tuwien.ac.at>, ka...@uranus.tuwien.ac.at
says...
>
>I use the ODBC ver 2.0 (32 Bit) (english) driver and
>SQL-Server 6.0 (german) under NT 4.0 beta 1.
>
>SELECT x FROM y FOR UPDATE
>
>The driver responds with an error that the FOR UPDATE
>clause could only be used with a DECLARE CURSOR
>statement. If i try to use
>DECLARE C1 CURSOR FOR ....
>SQLPrepare works, but you could not bind any colums
>nor execute works...
>So I think it's a bug, but comes the bug from
>NT 4.0 beta or from the ODBC-Driver?
>
As far as I know ODBC is the problem. Version 2.0
doesn't support server side cursors. A newer version
(I think it's 2.65) does!
Arnd
_________________________________________________________________________
___
/ ) _//
li...@willow.ruhr.de
/--/ /
/ (_ rnd /___ iman D-44227 Dortmund