ADO Exclusive open

[AHF]

Is it possible to open a recordset in exclusive mode so others cannot open it ?

[Carlos Mora]

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

[AHF]

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?

[nageswaragunupudi]

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.

[AHF]

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.

[Antonio Linares]

Antonio,

oRs:Fields( n ):DefinedSize

Anyhow Mr. Rao is the real expert on ADO/SQL

[Antonio Linares]

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:

Code: Select all  Expand view

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 }