Update Method for LCConnection

Defined In

LCConnection

Syntax

count = lcConnection.Update (srcFieldlist, recordIndex, recordCount)

Parameters

Return Value

Usage Notes®

You can achieve optimal performance by using the same fieldlist across consecutive updates to the same target.

The property Writeback indicates whether to perform a writeback or keyed update:

• Writeback update: If the Writeback property is set True, this method updates fields in the most recently fetched record from the writeback result set (the result set produced by LCConnection.Execute, LCConnection.Select or LCConnection.Call with the Writeback property set). Fields that have the NO_UPDATE field flag set are not updated.
• Keyed update: Updates all records in the supplied metadata with field values matching all fields in the fieldlist that have the LCFIELDF_KEY field flag set. Fields with the NO_UPDATE field flags set are not affected. If a value is specified for the property Condition, this will be included in the key search criteria. This value must be in a syntax valid for the relevant connector. See the documentation for the connector for information about its supported syntax.

When using inequality key flags GT, LT, and NE, it is important to remember that the default of no flags is equal. The following combinations are valid for inequality flags:

• equal to LCFIELDF_KEY
• greater than or equal to LCFIELDF_KEY + LCFIELDF_KEY_GT
• less than or equal to LCFIELDF_KEY + LCFIELDF_KEY_LT
• not equal to LCFIELDF_KEY + LCFIELDF_KEY_NE
• greater than LCFIELDF_KEY + LCFIELDF_KEY_GT + LCFIELDF_KEY_NE
• less than LCFIELDF_KEY + LCFIELDF_KEY_LT + LCFIELDF_KEY_NE
• string pattern matching LCFIELDF_KEY + LCFIELDF_KEY_LIKE

If you supply more than one key field comparison in your keyFieldList, effectively you are "and"ing the tests -- LCConnect will update records that meet all the tests. Likewise if you supply one or more key tests and an additional test in the Condition property.

LCConnect does not directly support logical "or" operations of multiple conditions. You can, however, execute any query using LCConnection.Execute, or construct an "or" type query in the Condition field, using the query language supported by the connector.

The rules for pattern matching in a "like" comparison vary depending on the connector. Consult your database documentation for details.

Example 1

Option Public
Option Explicit
Uselsx "*lsxlc" 

Sub Initialize
  Dim src As New LCConnection ("db2")  
  Dim fldList As New LCFieldList
  Dim fld As LCField

  ' set the appropriate properties to connect to the data source
  src.Database = "Gold"
  src.Userid = "JDoe"
  src.Password = "xyzzy"
  src.Metadata = "customer"

  src.Connect

  ' use a key to find certain records to update
  Set fld =  fldList.Append ("ACCOUNTMANAGER", LCTYPE_INT)
  fld.Flags =  LCFIELDF_KEY
  fld.value = 200
  ' set the field which will be changed, and set the new value
  Set fld =  fldList.Append ("CONTACTNAME", LCTYPE_TEXT)
  fld.text = "Me"

  src.MapbyName = True

  ' The fieldlist contains ACCOUNTMANAGER=200 as a key field, and
  ' CONTACTNAME="Me", which is not a key field. Calling Update now 
  ' will find all records where ACCOUNTMANAGER=200 and change their
  ' CONTACTNAME to "Me".
 Print "The update affected " & Cstr (src.Update (fldList)) & " records"
End Sub

Example 1 Output

The update affected 2 records.

Example 2

  ' This example shows how to use the Writeback property 
  '   to update records from the result set.
Option Public
Option Explicit
Uselsx "*lsxlc"
Sub Initialize
  On Error Goto trap
  Dim ses As New Lcsession
     ' In an HEI Scripted Agent, use the following syntax instead:
     ' Dim session As New LCSession ("mySession")
  Dim con As New Lcconnection("oledb")
  con.provider = "Microsoft.Jet.OLEDB.4.0" ' use Access 2000 database
  con.server = "C:\AccessData\DogData.mdb"
  con.metadata = "DogDob" ' Contains dog's name and date of birth.
  con.Connect
  Dim confldlst As New Lcfieldlist, count As Long
  Dim nameFld As LCField, dateOfBirth As LCField
  
  con.writeback = True
  count = con.Select(Nothing, 1, confldlst)
  ' Get a "handle" to each of the fields in the list returned by Selection.
  Set nameFld = confldlst.GetField(1)   ' locate field by position
  Set dateOfBirth = confldlst.Lookup("DateOfBirth") ' locate field by name.
                                                     ' Either works.
  While (con.Fetch (conFldLst) > 0)
    Print "Name=" & nameFld.Text(0) & ", " _
    "DateOfBirth=" & dateOfBirth.Text(0)
    ' If this dog's name is Tracy, change it to Tyler.
    If nameFld.text(0) = "Tracy" Then
      nameFld.text = "Tyler"
      ' Now write the change back to the database.
      Call con.Update(confldlst)
      Print "Changed name to Tyler."
    End If
  Wend
  con.Action(LCACTION_COMMIT)
  Exit Sub
trap:
  Dim stat$, errcode As Long, msg$
  If ses.Status <> LCSUCCESS Then
    ses.Getstatus stat, errcode, msg
    If (ses.Status = LCFAIL_EXTERNAL)Then
      Messagebox "ODBC message: " & msg & "  code #" & Cstr(errcode), 0, _
      "error number " & Err & " line " & Erl
    Else
      Messagebox "Connector message: " & text, 0, "error number " & _ 
Err & " line " & Erl
    End If
  Else
    Messagebox Error, 0, "error number " & Err & " line " & Erl
  End If
  Exit Sub
End Sub

Example 2 Output

Name=Margot, DateOfBirth=12/02/1995
Name=Rex, DateOfBirth=06/12/2008
Name=Tracy, DateOfBirth=09/19/2001
Changed name to Tyler.
Name=Yaller, DateOfBirth=03/30/1987

Update Method for LCConnection for DB2® Connector

This method updates records in a writeback result set or a keyed search in the defined METADATA object in DB2®.

The LCXUpdate method operates in one of two modes, depending if the WRITEBACK property is set and there is a current result set that supports writeback.

Non-writeback Updates

One or more keyed updates are performed against the metadata indicated by the METADATA property. For each record used as key values, a keyed update constructed from those key values should be performed. Any value for the CONDITION property should be included in the search criteria. Only fields with the flag LCFIELDF_KEY should be used as key values. The property MAP_NAME indicates whether to map fields in KeyFieldlist and the target metadata by name or position.

Writeback Updates

There must be a current result set which supports writeback operations and a previous Fetch from that result set. The last document fetched by the most recent Fetch call is updated.

Only fields without the flag LCFIELDF_NO_UPDATE should be updated. The current fieldlist value for each field to update should replace any value for the field in the target metadata.

Handling of both mapping and field flags is implicitly done by calling LCFieldlistMerge with the target metadata field names as NameFieldlist, SrcFieldlist as DataFieldlist, and MergeFlags LCMERGEF_NAME_LOSS, LCMERGEF_UPDATE, (for keyed updates only) LCMERGEF_KEY, and optionally (depending on the MAP_NAME property) LCMERGEF_MAP_NAME.