ADO question?

[richard-service]

All

This is my first test ADO connect to MS-SQL and ORACLE

oRS:UpdateBatch() => not save any data. any idea or solution?

if I use Do while a record update save no problem.

oCn:=TOLEAuto():New('ADODB.Connection')
if oCn == nil
MsgInfo( "無法建立 ADO 連線" )
else
xConnect:= 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';User Id='+xUSERID+';Password='+xPASSWORD+";"
oCn:ConnectionString:=xConnect
oCn:Open(xConnect)
SELE WCHAAAM1
DO WHILE !EOF()
cSql:="SELECT * FROM WEBEMR.EMR_TEMP_OPDDRUG WHERE erop_patno='"+alltrim(MNUM)+"' AND erop_drorderno='"+WAP011M1->SERNO+"' AND erop_drugcode='"+alltrim(WHINV->ITEMN12)+"' AND erop_dgfrequency='"+MPA+"'"
oRs:=FW_OpenRecordSet( oCn, cSql, 4 , 3, ,1 )
if oRs:Eof
IF FWAdoAppendBlank( oRs )
oRs:Fields("erop_drorderno"):Value:=MSERNO &&2 EREP_REFERNO
oRs:Fields("erop_patname"):Value:=HB_TRANSLATE(MNAME,'BIG5','UTF8') &&3 EREP_PATNAME
oRs:Fields("erop_sex"):Value:=MSEX &&4 EREP_PATIDTYPE
ENDIF
ENDIF
SELE WCHAAAM1
SKIP
ENDDO
TRY
oRS:UpdateBatch()
CATCH
oErr := FW_GetAdoError( oRs:ActiveConnection )
if oErr != nil
logfile("WRISRUNERR",{oErr:Description})
else
MsgStop( "!!未知錯誤" )
endif
oRS:CancelUpdate()
END
ENDIF

[Antonio Linares]

Dear Richard,

The problem is two things:

1. LockType must be adLockBatchOptimistic (4), not adLockOptimistic (3)

   UpdateBatch() requires lock type 4. You're passing 3:
   oRs := FW_OpenRecordSet( oCn, cSql, 4, 3, , 1 )
   // CursorType^ LockType (should be 4)

2. CursorLocation must be adUseClient (3)

   Set this on the connection BEFORE opening recordsets:
   oCn:CursorLocation := 3 // adUseClient

3. You're creating a new recordset each iteration — UpdateBatch() only applies to the last one opened.

   Here's the corrected approach:

Code: Select all | Expand

  oCn := TOLEAuto():New('ADODB.Connection')
  oCn:CursorLocation := 3   // adUseClient — REQUIRED for UpdateBatch
  oCn:ConnectionString := xConnect
  oCn:Open( xConnect )

  // Open ONE recordset for batch inserts
  cSql := "SELECT * FROM WEBEMR.EMR_TEMP_OPDDRUG WHERE 1=0"
  oRs := FW_OpenRecordSet( oCn, cSql, 3, 4, , 1 )
  //                         adOpenStatic^ ^adLockBatchOptimistic

  SELE WCHAAAM1
  DO WHILE !EOF()
     IF FWAdoAppendBlank( oRs )
        oRs:Fields("erop_drorderno"):Value := MSERNO
        oRs:Fields("erop_patname"):Value   := HB_TRANSLATE(MNAME,'BIG5','UTF8')
        oRs:Fields("erop_sex"):Value       := MSEX
     ENDIF
     SELE WCHAAAM1
     SKIP
  ENDDO

  TRY
     oRs:UpdateBatch()
  CATCH
     oErr := FW_GetAdoError( oRs:ActiveConnection )
     IF oErr != nil
        LogFile("WRISRUNERR", { oErr:Description })
     ENDIF
     oRs:CancelBatch()   // CancelBatch, not CancelUpdate
  END

  oRs:Close()
  oCn:Close()

Summary of fixes:

• CursorLocation := 3 on the connection (client-side cursor)

• LockType 4 (adLockBatchOptimistic) instead of 3

• Open one recordset outside the loop, append all records, then call UpdateBatch() once

• Use CancelBatch() instead of CancelUpdate() for batch operations

• If you only need to insert (not check existence first), use WHERE 1=0 to get an empty recordset with the right
  schema

  If you need the existence check per record, you can either keep individual recordsets with adLockOptimistic (3) and
  use oRs:Update() (not UpdateBatch) inside the loop, or do the check with a separate query and batch the inserts
  separately.

[richard-service]

Dear Richard,

The problem is two things:

1. LockType must be adLockBatchOptimistic (4), not adLockOptimistic (3)

   UpdateBatch() requires lock type 4. You're passing 3:
   oRs := FW_OpenRecordSet( oCn, cSql, 4, 3, , 1 )
   // CursorType^ LockType (should be 4)

2. CursorLocation must be adUseClient (3)

   Set this on the connection BEFORE opening recordsets:
   oCn:CursorLocation := 3 // adUseClient

3. You're creating a new recordset each iteration — UpdateBatch() only applies to the last one opened.

   Here's the corrected approach:

Code: Select all | Expand

  oCn := TOLEAuto():New('ADODB.Connection')
  oCn:CursorLocation := 3   // adUseClient — REQUIRED for UpdateBatch
  oCn:ConnectionString := xConnect
  oCn:Open( xConnect )

  // Open ONE recordset for batch inserts
  cSql := "SELECT * FROM WEBEMR.EMR_TEMP_OPDDRUG WHERE 1=0"
  oRs := FW_OpenRecordSet( oCn, cSql, 3, 4, , 1 )
  //                         adOpenStatic^ ^adLockBatchOptimistic

  SELE WCHAAAM1
  DO WHILE !EOF()
     IF FWAdoAppendBlank( oRs )
        oRs:Fields("erop_drorderno"):Value := MSERNO
        oRs:Fields("erop_patname"):Value   := HB_TRANSLATE(MNAME,'BIG5','UTF8')
        oRs:Fields("erop_sex"):Value       := MSEX
     ENDIF
     SELE WCHAAAM1
     SKIP
  ENDDO

  TRY
     oRs:UpdateBatch()
  CATCH
     oErr := FW_GetAdoError( oRs:ActiveConnection )
     IF oErr != nil
        LogFile("WRISRUNERR", { oErr:Description })
     ENDIF
     oRs:CancelBatch()   // CancelBatch, not CancelUpdate
  END

  oRs:Close()
  oCn:Close()

Summary of fixes:

• CursorLocation := 3 on the connection (client-side cursor)

• LockType 4 (adLockBatchOptimistic) instead of 3

• Open one recordset outside the loop, append all records, then call UpdateBatch() once

• Use CancelBatch() instead of CancelUpdate() for batch operations

• If you only need to insert (not check existence first), use WHERE 1=0 to get an empty recordset with the right
  schema

  If you need the existence check per record, you can either keep individual recordsets with adLockOptimistic (3) and
  use oRs:Update() (not UpdateBatch) inside the loop, or do the check with a separate query and batch the inserts
  separately.

Dear Antonio,

I will try it. Thanks a lot.