Locking Problem and ODBC drivers.

Kedar Kulkarni -- kedarpk@trddc.ernet.in
Tuesday, January 28, 1997

Environment: VC++ 1.51, Win 3.1/ Windows-95 16-bit
-------------------------------------------

Hello,
	I am facing following problem.
	We are using ODBC in our application.

	The problem is related to Locking Strategy.

	We are using SELECT FOR UPDATE clause to lock records before modification
	for Concurrency purpose, which is suggested by MicroSoft.
	
	Steps are as follows..
	
	a.> First we get list of  domain objects present in Database, to show it
	on the UI, using CRecordset::Open() function. [ We are using a global pDb 	
	database pointer for all the recordsets. ]

	b.>  While modifying a single object we call 
		pDb -> BeginTrans();
	
	c.>  LockRecord();
		// In this function we are firing SELECT FOR UPDATE query.
		// Like this ' SELECT * FROM CUSTOMER WHERE COMPANY_ID = 'XYZ' FOR 	
		//UPDATE OF'  and used ExecuteSQL() function.
	
	d.>  After this we are again firing the query for the perticular record for 	
	modification by using CRecordSet::Open() function.

	e.> Modify the object.

	f.> pDb->CommitTrans();

	This is how we are doing. 
	Now the problem we are facing are ..
	
	When we tested the application on ORACLE 7.0 , it worked perfectly.
	
	While testing on Microsoft SQL Server 6.5 ,  it is giving errors .
	For SQL Server first we used Microsoft's ODBC driver( 2.00.1912 ) 
	It is not allowing us to use SELECT FOR UPDATE clause.
	ExecuteSQL() function returns '-1' error value.

	Then we tried with Intersolve ODBC driver( 2.11.0000) . This is executing 	
	ExecuteSQL()  correctly . But giving problem in step < c > ( as above.)
	The error given was " Can not execute 2 active statements simultaneously when
	AUTO_COMMIT is TRUE. "
	
	CRecordSet  :: Open () function is called with default arguments [i.e.in write 	
	mode only]  in Step  < a, d >
	
	How to avoid above errors with SQL Server ?	
	
	Thanks.
	Kedar.

---

Senthil .P -- senthilp@rsi.ramco.com
Wednesday, January 29, 1997

SELECT FOR UPDATE is an ODBC Level 2 SQL Grammar. So this will not be
supported by drivers which do not conform to ODBC Level 2
specifications. I think the SQL Server 6.5 driver which comes with ODBC
2.5 has full compliance to ODBC level 2.

Generally, using Level 2 functions or grammars might restrict the
portability of the application to different RDBMS. Therefore, it has to
be religiously avoided. There are a lot of other strategies for
implementing locking of records than using SELECT FOR UPDATE.

>----------
>From: 	Kedar Kulkarni[SMTP:kedarpk@trddc.ernet.in]
>Sent: 	Tuesday, January 28, 1997 4:28 PM
>To: 	mfc-l@netcom.com
>Subject: 	Locking Problem and  ODBC drivers.
>
>Environment: VC++ 1.51, Win 3.1/ Windows-95 16-bit
>-------------------------------------------
>
>Hello,
>	I am facing following problem.
>	We are using ODBC in our application.
>
>	The problem is related to Locking Strategy.
>
>	We are using SELECT FOR UPDATE clause to lock records before modification
>	for Concurrency purpose, which is suggested by MicroSoft.
>	
>	Steps are as follows..
>	
>	a.> First we get list of  domain objects present in Database, to show it
>	on the UI, using CRecordset::Open() function. [ We are using a global pDb 	
>	database pointer for all the recordsets. ]
>
>	b.>  While modifying a single object we call 
>		pDb -> BeginTrans();
>	
>	c.>  LockRecord();
>		// In this function we are firing SELECT FOR UPDATE query.
>		// Like this ' SELECT * FROM CUSTOMER WHERE COMPANY_ID = 'XYZ' FOR 	
>		//UPDATE OF'  and used ExecuteSQL() function.
>	
>	d.>  After this we are again firing the query for the perticular record for
>	
>	modification by using CRecordSet::Open() function.
>
>	e.> Modify the object.
>
>	f.> pDb->CommitTrans();
>
>	This is how we are doing. 
>	Now the problem we are facing are ..
>	
>	When we tested the application on ORACLE 7.0 , it worked perfectly.
>	
>	While testing on Microsoft SQL Server 6.5 ,  it is giving errors .
>	For SQL Server first we used Microsoft's ODBC driver( 2.00.1912 ) 
>	It is not allowing us to use SELECT FOR UPDATE clause.
>	ExecuteSQL() function returns '-1' error value.
>
>	Then we tried with Intersolve ODBC driver( 2.11.0000) . This is executing 	
>	ExecuteSQL()  correctly . But giving problem in step < c > ( as above.)
>	The error given was " Can not execute 2 active statements simultaneously
>when
>	AUTO_COMMIT is TRUE. "
>	
>	CRecordSet  :: Open () function is called with default arguments [i.e.in
>write 	
>	mode only]  in Step  < a, d >
>	
>	How to avoid above errors with SQL Server ?	
>	
>	Thanks.
>	Kedar.
>
>

---

| Вернуться в корень Архива |