speed & reliability

[sltfn]

The value of the batch routines is that you can communicate with multiple records on multiple tables in the same call. Using batch can turn 10k record transactions into a single call.

It may be of interest to know that the basic methods feed internally to the batch routines. Looping through each request should not be any less reliable than batching your calls into a single transaction. Looping your calls will be slower.

That said, you should see 100% accuracy using either method. Did you get an error message in your message box indicating there was a problem with the five records that failed? It would be helpful if you would elaborate on your testing so we can locate possible bugs.

Calls that fail to reach the cloud are automatically cached. The next attempt to make a cloud call will flush the cache for you. If you have time, please send us a test stack so we can reproduce the issue. Thanks.

Thank you mark_talluto.

Apologies for the delay in response. Other projects have required my attention. efrain.c's code above helps but I'm trying to wrap my head around arrays and using cdb_batchUpdate. If anyone can point me in the right direction, I'd appreciate it. My script takes a variable containing lines of tab-delimited data and updates records in a table. My data looks like this (see attachment). The top row in bold contains the names of the keys.

   

on writeRecordToDB
global myVar
set itemDelimiter to tab

put "thisCustID" & tab & "thisFullName" & tab & "thisLastName" & tab & "thisBalance" & tab & "thisCode" & tab & "thisStatus" & tab & "thisRecordID" into theLineItems

put "CUSTOMERS" into tTable
put "cloud" into tTarget
set itemDelimiter to tab

repeat for each line DBrecordLine of myVar
   
   repeat with itemCount = 1 to (the number of items of theLineItems)
      do "put item" && itemCount && "of DBrecordLine into" && (item itemCount of theLineItems)
   end repeat
  
   put thisRecordID into tRecordID
   put thisCustID into tDataA["CUSTOMER_ID"]
   put thisFullName into tDataA["FULL_NAME"]
   put thisLastName into tDataA["LAST_NAME"]
   put thisBalance into tDataA["BALANCE"]
   put thisCode into tDataA["CODE"]
   put thisStatus into tDataA["STATUS"]
      
   cdb_update tDataA,tTable,tRecordID,tTarget

end repeat -- looping through each line of myVar

end writeRecordToDB

How would I use cdb_batchUpdate to accomplish this without needing to update one record at a time? Thanks in advance.

[efrain.c]

Hi sltfn,

Here is one way to use cdb_batchUpdate in your code:


global myVar
set itemDelimiter to tab

put "thisCustID" & tab & "thisFullName" & tab & "thisLastName" & tab & "thisBalance" & tab & "thisCode" & tab & "thisStatus" & tab & "thisRecordID" into theLineItems

put cdb_tableID("CUSTOMERS") into tTableID
put "cloud" into tTarget
set itemDelimiter to tab

repeat for each line DBrecordLine of myVar

repeat with itemCount = 1 to (the number of items of theLineItems)
do "put item" && itemCount && "of DBrecordLine into" && (item itemCount of theLineItems)
end repeat

put thisRecordID into tRecordID
put thisCustID into tDataA[tTableID][tRecordID]["CUSTOMER_ID"]
put thisFullName into tDataA[tTableID][tRecordID]["FULL_NAME"]
put thisLastName into tDataA[tTableID][tRecordID]["LAST_NAME"]
put thisBalance into tDataA[tTableID][tRecordID]["BALANCE"]
put thisCode into tDataA[tTableID][tRecordID]["CODE"]
put thisStatus into tDataA[tTableID][tRecordID]["STATUS"]

end repeat -- looping through each line of myVar

cdb_batchUpdate tDataA, tTarget


There are a couple changes to note. cdb_batchUpdate uses tableID instead of table name. tDataA is structured differently to allow updates across multiple records across multiple tables. cdb_batchUpdate is placed after the repeat, the repeat structures the data and cdb_batchUpdate sends all the updates as a single request.

Hope this helps. Let us know if you have any questions.

[sltfn]

Hi sltfn,

Here is one way to use cdb_batchUpdate in your code:


global myVar
set itemDelimiter to tab

put "thisCustID" & tab & "thisFullName" & tab & "thisLastName" & tab & "thisBalance" & tab & "thisCode" & tab & "thisStatus" & tab & "thisRecordID" into theLineItems

put cdb_tableID("CUSTOMERS") into tTableID
put "cloud" into tTarget
set itemDelimiter to tab

repeat for each line DBrecordLine of myVar
 
  repeat with itemCount = 1 to (the number of items of theLineItems)
     do "put item" && itemCount && "of DBrecordLine into" && (item itemCount of theLineItems)
  end repeat
 
  put thisRecordID into tRecordID
  put thisCustID into tDataA[tTableID][tRecordID]["CUSTOMER_ID"]
  put thisFullName into tDataA[tTableID][tRecordID]["FULL_NAME"]
  put thisLastName into tDataA[tTableID][tRecordID]["LAST_NAME"]
  put thisBalance into tDataA[tTableID][tRecordID]["BALANCE"]
  put thisCode into tDataA[tTableID][tRecordID]["CODE"]
  put thisStatus into tDataA[tTableID][tRecordID]["STATUS"]
 
end repeat -- looping through each line of myVar

cdb_batchUpdate tDataA, tTarget


There are a couple changes to note. cdb_batchUpdate uses tableID instead of table name. tDataA is structured differently to allow updates across multiple records across multiple tables. cdb_batchUpdate is placed after the repeat, the repeat structures the data and cdb_batchUpdate sends all the updates as a single request.

Hope this helps. Let us know if you have any questions.

Thanks, efrain. This is very helpful. Now I just need to change some routines used earlier in my processing to work with batch updating and test. Much appreciated.