TDataRow and ADO questions

[elvira]

Thanks a lot.

One more question please. What is the equivalent for rlock()?.

Code: Select all  Expand view

IF RLOCK()
           Delete
           UNLOCK
        ELSE
           ? "Record update failed"
           return .f.
        ENDIF

 

I need to lock the record before deleting it.

Thanks.

[Rick Lipkin]

Elvira

I am not familiar with how TDataRow or any of the FW_ado methods work .. typically when you create your recordset you define the object like this .. there is no need for record locking the OLEDB provider handles all that for you if you define with opportunistic locking ..

Rick Lipkin

Code: Select all  Expand view

oRsGs := TOleAuto():New( "ADODB.Recordset" )
oRsGs:CursorType     := 1        // opendkeyset
oRsGs:CursorLocation := 3        // local cache
oRsGs:LockType       := 3        // lockoportunistic

cSql := "Select * From [GroomService] "
cSql += "Where [BreedType] = 'Packages' "
cSql += "Order by [Breed]"

TRY
   oRsGs:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening GROOMSERVICE table" )
   RETURN(.F.)
END TRY

If oRsGs:eof
Else
   oRsGs:MoveFirst()
   // delete the first row
   oRsGs:Delete()
   oRsGs:Update()
Endif

oRsGs:Close()
oRsGs := nil
 

[Enrico Maria Giordano]

Rick,

Code: Select all  Expand view

   oRsGs:Delete()
   oRsGs:Update()
Endif

oRsGs:Close()
oRsGs := nil
 

oRsGs:Update() and oRsGs := nil are not needed.

EMG

[elvira]

Hello,

I did not explain myself properly.

In our software, we have a Browse with buttons with the options. We have a button called "Delete".

REDEFINE BUTTON.... ACTION (Delete(oRs), oLbx:Refresh() )


This is the function Delete:

Code: Select all  Expand view

 

FUNCTION Delete( oRs )
   LOCAL n



    if MsgYesNo( "¿ Desea BORRAR este Registro ?."+CRLF+CRLF+"Si tiene dudas, seleccione No.", " B O R R A R   R E G I S T R O" )

       if oRs:RecordCount() = 0
          MsgAlert("ERROR: No hay ningún registro en la tabla."+CRLF+CRLF+"No hay nada que BORRAR.", " E R R O R ")
          RETURN NIL
       endif

       n := oRs:AbsolutePosition

       oRs:Delete()

       oRs:Update()

       if !oRs:RecordCount() = 0
            oRs:AbsolutePosition := Min( n, oRs:RecordCount() )
        endif


       MsgInfo("El Registro ha sido BORRADO correctamente.", " A V I S O ")


    else
       MsgInfo("El USUARIO ha cancelado la operación de Borrar.", " A V I S O ")

    endif



RETURN NIL
//----------------------------------------------------------------------------//
 

The problem happens ina network enviroment and both users want to delete the same record.

As one press before the Yes button, when the second user press the Yes button the application crashes. That is what I want to control with locks.

Thanks.

[Enrico Maria Giordano]

Elvira,

Code: Select all  Expand view

       oRs:Delete()

       oRs:Update()

oRs:Update is only needed when you change fields values. I don't know what it could do when used after a Delete(), nothing good I suppose.

That is what I want to control with locks.

You can't, as far as I know. You have to trap the error.

EMG

[Rick Lipkin]

Enrico and Elvira

You are correct on the Delete followed by update .. I went back and looked at some of my code and I do not use update and I do try to trap a possible error with try\catch\end try.

Here is some code where I delete a row presented through xBrowse..

Code: Select all  Expand view

//-----------------
Static FUNCTION _RateDel( oRsTaxRate,oLbxB )

LOCAL SAYING, cSQL, oERR,  cNAME

IF xSUPER = 'Y'
ELSE
   SAYING := "Sorry ... You have READ only Rights"
   Msginfo( SAYING )
   RETURN(.F.)
ENDIF

IF oRsTaxRate:EOF
   SAYING := "Sorry ... Before you can Delete a "
   SAYING += "record, you have to Add one first"
   Msginfo( SAYING )
   RETURN(.F.)
ENDIF

cNAME := oRsTaxRate:Fields("Description"):Value

SAYING := "Are you SURE you want to Delete this record for "+CHR(10)
SAYING += alltrim(cNAME)+CHR(10)

IF MsgYesNO( SAYING )
ELSE
   RETURN(.F.)
ENDIF

Try
  oRsTaxRate:Delete()
Catch
   MsgInfo( "Deletion failed" )
   Return(.f.)
End Try

TRY
  oRsTaxRate:MoveNext()
CATCH
END TRY

IF oRsTaxRate:eof .and. .not. oRsTaxRate:bof
   TRY
      oRsTaxRate:MoveFirst()
   CATCH
   END TRY
ENDIF

oLbxB:ReFresh()

RETURN(NIL)
 

Elvira

Recordsets are a 'fetch' of rows based on the time you did the query. Workstation update visibility is something you have to consider in a networked environment ..

1) To Test for two workstations that are trying to modify the same record .. I create a field called UPDATED "N" and when the row is created I set UPDATED to 1 and when changed it gets incremented with UPDATED++. I allow both workstations to buffer their fields to memory variables and before I allow the the UPDATE() .. I have a function that goes out and creates another recordset based on the value of the rows primary key and I return the CURRENT value of UPDATED. I then compare the workstation value with the current returned value and if they are different .. then workstation B got to the update before workstation A making Workstation A's records 'stale' and in that case I do not allow workstation A to Update the same row... and you can then apply a Requery() to refresh Workstation A or close and re-open the recordset as I suggest in the next example.

2) As far as a DELETE that occurred from Workstation B .. Workstation A's visibility will not see any change in its buffered rows, hence your run-time error .. what you might consider is modifying my above example like this and if the Try\Catch errors .. you can either do a Requery() to refresh the current recordset or you can CLose() the current recordset and re-open it again which I have found to be more reliable than Requery() .. consider this change to the above delete function

Code: Select all  Expand view

Static FUNCTION _RateDel( oRsTaxRate,oLbxB )

LOCAL SAYING, cSQL, oERR,  cNAME

IF xSUPER = 'Y'
ELSE
   SAYING := "Sorry ... You have READ only Rights"
   Msginfo( SAYING )
   RETURN(.F.)
ENDIF

IF oRsTaxRate:EOF
   SAYING := "Sorry ... Before you can Delete a "
   SAYING += "record, you have to Add one first"
   Msginfo( SAYING )
   RETURN(.F.)
ENDIF

cNAME := oRsTaxRate:Fields("Description"):Value

SAYING := "Are you SURE you want to Delete this record for "+CHR(10)
SAYING += alltrim(cNAME)+CHR(10)

IF MsgYesNO( SAYING )
ELSE
   RETURN(.F.)
ENDIF

Try
  oRsTaxRate:Delete()
Catch
   MsgInfo( "Deletion failed" )

   // two possible options
   // option 1

 *  oRsTaxRate:Requery()

   // or close the recordset and reopen without re-initializing the
   // recordset variable

   oRsTaxRate:Close()
   cSQL := "SELECT * FROM [jTax Sets] order by NAME"   // original query

   TRY
       oRsTaxSet:Open( cSQL,xCONNECT )
   CATCH oErr
      // hope this never happens .. going to blow chunks
       MsgInfo( "Error in Opening jTax Sets table" )
      RETURN(.F.)
   END TRY

   IF oRsTaxRate:eof .and. .not. oRsTaxRate:bof
      TRY
         oRsTaxRate:MoveFirst()
      CATCH
      END TRY
   ENDIF

   oLbxB:ReFresh()
   Return(nil)

End Try

TRY
  oRsTaxRate:MoveNext()
CATCH
END TRY

IF oRsTaxRate:eof .and. .not. oRsTaxRate:bof
   TRY
      oRsTaxRate:MoveFirst()
   CATCH
   END TRY
ENDIF

oLbxB:ReFresh()

RETURN(NIL)

 

[elvira]

Thank you so much.

I am going to test with Requery() and share the results.

Also, how can I made two relations?

SET RELATION TO ADDRESS->REGISTRO INTO CUSTOMER, TO ADDRESS->REG_USUARI INTO ITEMS

So as I can do the following:

SELECT("ADDRESS")
DBGOTO(10)

?CUSTOMER->NAME
?ITEMS->NAME


Thank you very very much. You are helping me very much and very quick also, to learn and use Ado.

[Marc Vanzegbroeck]

Thank you so much.

I am going to test with Requery() and share the results.

Also, how can I made two relations?

SET RELATION TO ADDRESS->REGISTRO INTO CUSTOMER, TO ADDRESS->REG_USUARI INTO ITEMS

So as I can do the following:

SELECT("ADDRESS")
DBGOTO(10)

?CUSTOMER->NAME
?ITEMS->NAME


Thank you very very much. You are helping me very much and very quick also, to learn and use Ado.

Elvira,

For the deletion you can also execute the query 'DELETE FROM tabel WHERE ID = idnr'
Then you don't get an error if it is already deleted by someone else.

For the relation you have to use the 'JOIN' in your query.

[Enrico Maria Giordano]

Elvira,

Also, how can I made two relations?

Code: Select all  Expand view

SELECT * FROM Address, Customer WHERE Address.Id = Customer.Id

where Id is the relation field. Then you can use

Code: Select all  Expand view

oRs:Fields( "Name" ):Value

EMG

[Enrico Maria Giordano]

Marc,

For the deletion you can also execute the query 'DELETE FROM tabel WHERE ID = idnr'
Then you don't get an error if it is already deleted by someone else.

Right!

EMG

[elvira]

Thank you very much, great solution!.

Also, how do you make the bakcups to the database via SQL command?.

Thanks.

[nageswaragunupudi]

You are correct on the Delete followed by update .. I went back and looked at some of my code and I do not use update and I do try to trap a possible error with try\catch\end try.

Here is safe and simple code to delete row from recordset while using xbrowse

Code: Select all  Expand view

unction RsDelete( oBrw )
local oRs := oBrw:oRs
local tmp
local lDeleted := .f.

if oRs:RecordCount() > 0
   tmp := oRs:AbsolutePosition
   oRs:Delete()
   oRs:Update()
   oRs:AbsolutePosition := Max( 1, Min( tmp, oRs:RecordCount() ) )
   lDeleted := .t.  
   oBrw:Refresh()
endif

return lDeleted
 

From the next version of xBrowse you don't even to write this much code.

Just call oBrw:Delete() and that is enough.

It does not matter whether we are browsing dbf, recset, array, etc.

[elvira]

Thank you very much.

And which is the best way to back up tables via an SQL COMMAND?.

SOmething like EXPORT or APPEND TO.

Thannks .

[James Bott]

I am not clear on this: if you do a oRS:delete() does it just delete the record in the recordset or does it automatically delete the record in the table on the server too? Or, is the record in the table only deleted when the recordset is updated (oRS:update())?

James

[Rick Lipkin]

James

I am not clear on this: if you do a oRS:delete() does it just delete the record in the recordset or does it automatically delete the record in the table on the server too? Or, is the record in the table only deleted when the recordset is updated (oRS:update())?

James

When you oRs:Delete() it does delete the record in the recordset and the row in the table. I was incorrect when I followed oRs:Delete() with oRs:Update().

Rick Lipkin