Defined In

LCConnection

Syntax

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

Parameters

Parameter	Description
srcFieldlist	LCFieldlist. The fieldlist that contains the fields to be changed.
recordIndex	Long, >= 1. Optional. The starting record in the fieldlist from which to read data. The default is 1.
recordCount	Long, >= 1. Optional. The number of records in the fieldlist to use to perform the update. The default is 1.

Return Value

Value	Description
count	Long. Number of records successfully updated. This may be LCCOUNT_UNKNOWN.

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.

• 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

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 LEI 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.

Glossar