Is it possible to open a recordset in exclusive mode so others cannot open it ?
Regards
Antonio H Ferreira
Antonio H Ferreira
FiveTech Support Forums
FiveWin / Harbour / xBase community
Board index FiveWin for Harbour/xHarbour ADO Exclusive open
Hi AHF,
I don't think it is possible, because the exclusive locking is against usual database principles, and ADO is just an abstraction layer, so that kind of behavoir is depending on the data engine itself, not ADO.
May be if you are using some particular data drivers you can state in the string connection that you won't share any data, but it's up to the driver, not ADO.
Probably it would be easier to use some "dirty trick" like a locking semaphore or sth like that, adapted to the engine.
Regards
Saludos
Carlos Mora
http://harbouradvisor.blogspot.com/
StackOverflow http://stackoverflow.com/users/549761/carlos-mora
“If you think education is expensive, try ignorance"
Carlos Mora
http://harbouradvisor.blogspot.com/
StackOverflow http://stackoverflow.com/users/549761/carlos-mora
“If you think education is expensive, try ignorance"
Carlos,
I remember to read somewere that if you opend recorset and issue : begintrans immediatly after might be the same as USE .. EXCLUSIVE if you get an error USE fails if not the state of table would be protected untill you commit or abort trans.
Do you have any experience on this?
Regards
Antonio H Ferreira
Antonio H Ferreira
As regards the first question:
Please read more on SELECT ... FOR UPDATE available in Oracle, MySql and some others
Please note that these are very rarely used. I strongly advise we need to unlearn DBF habits and re-orient our thinking.
We can also open recordset with pessimistic locking and if we update a record, it is locked till unlocked.
Concept of TRANSACTIONS is different.
On quite a few occasions we need to update two or more tables for recording a single transaction. We need to either alter all the tables or none of the tables but can not leave some tables altered and some unaltered.
For this purpose almost all SQL databases offer a construct like this.
BEGIN TRANSACTION
update table1
update table2, ........... etc
if there is some problem
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
The programmer can decide either to COMMIT or ROLLBACK.
Even hardware / power failures also perform an automatic ROLLBACK.
ADO provides the same facility with
oCn:BeginTrans()
oCn:CommitTrans()
oCn:RollBackTrans()
Please note TDataRow class automatically implements this transactions feature if we use more than one table and enable oRec:lUseTrans is set to .T.
Regards
G. N. Rao.
Hyderabad, India
G. N. Rao.
Hyderabad, India
nageswaragunupudi wrote:
Please read more on SELECT ... FOR UPDATE available in Oracle, MySql and some others
Please note that these are very rarely used. I strongly advise we need to unlearn DBF habits and re-orient our thinking.
I agree completly these are two completly diferent worlds.
The question is that I m trying to buid an adordd emulating the dbfrdd to have it working with minor code changes in a huge an old application.
We can also open recordset with pessimistic locking and if we update a record, it is locked till unlocked.
Wont do for me.
Concept of TRANSACTIONS is different.
On quite a few occasions we need to update two or more tables for recording a single transaction. We need to either alter all the tables or none of the tables but can not leave some tables altered and some unaltered.
Unfortunatly this application uses a lot of updates to several tables in a single TS.
For this purpose almost all SQL databases offer a construct like this.
BEGIN TRANSACTION
update table1
update table2, ........... etc
if there is some problem
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
The programmer can decide either to COMMIT or ROLLBACK.
Even hardware / power failures also perform an automatic ROLLBACK.
ADO provides the same facility with
oCn:BeginTrans()
oCn:CommitTrans()
oCn:RollBackTrans()
This is what I want. But how do I know that the recordsets Im opening are all within same connection ?
Please note TDataRow class automatically implements this transactions feature if we use more than one table and enable oRec:lUseTrans is set to .T.
I know but it doesnt solve my problem. Can I see the source to understand how recordsets are opened in the same connection?
I take the opportunity to ask if you would help as SQL expert to translate fieldsizes and fielddec from sql o dbf in adordd.
Regards
Antonio H Ferreira
Antonio H Ferreira
Antonio,
oRs:Fields( n ):DefinedSize
Anyhow Mr. Rao is the real expert on ADO/SQL 
Antonio,
This code is from FWH\source\function\adofunc.prg and it has been developed by Mr. Rao and surely will help you very much:
This code is from FWH\source\function\adofunc.prg and it has been developed by Mr. Rao and surely will help you very much:
function FWAdoFieldStruct( oRs, n ) // ( oRs, nFld ) where nFld is 1 based
// ( oRs, oField ) or ( oRs, cFldName )
// ( oField )
local oField, nType, uval
local cType := 'C', nLen := 10, nDec := 0, lRW := .t. // default
if n == nil
oField := oRs
oRs := nil
elseif ValType( n ) == 'O'
oField := n
else
if ValType( n ) == 'N'
n--
endif
TRY
oField := oRs:Fields( n )
CATCH
END
endif
if oField == nil
return nil
endif
nType := oField:Type
if nType == adBoolean
cType := 'L'
nLen := 1
elseif AScan( { adDate, adDBDate, adDBTime, adDBTimeStamp }, nType ) > 0
cType := 'D'
nLen := 8
if oRs != nil .and. ! oRs:Eof() .and. ValType( uVal := oField:Value ) == 'T' .and. ;
FW_TIMEPART( uVal ) >= 1.0
cType := 'T'
endif
elseif AScan( { adTinyInt, adSmallInt, adInteger, adBigInt, ;
adUnsignedTinyInt, adUnsignedSmallInt, adUnsignedInt, ;
adUnsignedBigInt }, nType ) > 0
cType := 'N'
nLen := oField:Precision + 1 // added 1 for - symbol
if oField:Properties( "ISAUTOINCREMENT" ):Value == .t.
cType := '+'
lRW := .f.
endif
elseif AScan( { adSingle, adDouble }, nType ) > 0
cType := 'N'
nLen := Max( 19, oField:Precision + 2 )
nDec := 2
elseif nType == adCurrency
cType := 'N' // 'Y'
nLen := 19
nDec := 2
elseif AScan( { adDecimal, adNumeric, adVarNumeric }, nType ) > 0
cType := 'N'
nLen := Max( 19, oField:Precision + 2 )
if oField:NumericScale > 0 .and. oField:NumericScale < nLen
nDec := oField:NumericScale
endif
elseif AScan( { adBSTR, adChar, adVarChar, adLongVarChar, adWChar, adVarWChar, adLongVarWChar }, nType ) > 0
nLen := oField:DefinedSize
if nType != adChar .and. nType != adWChar .and. nLen > nFWAdoMemoSizeThreshold
cType := 'M'
nLen := 10
endif
elseif AScan( { adBinary, adVarBinary, adLongVarBinary }, nType ) > 0
nLen := oField:DefinedSize
if nType != adBinary .and. nLen > nFWAdoMemoSizeThreshold
cType := 'm'
nLen := 10
endif
elseif AScan( { adChapter, adPropVariant }, nType ) > 0
cType := 'O'
lRW := .f.
else
lRW := .f.
endif
if lAnd( oField:Attributes, 0x72100 ) .or. ! lAnd( oField:Attributes, 8 )
lRW := .f.
endif
return { oField:Name, cType, nLen, nDec, nType, lRW }