Domino connection SQL access DB2 (lcconnection)

Update Method for lcconnection
This method updates selected records in the connection metadata.

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 into SS 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 encoded 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, please tively 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 defined as lcfield

'Set the appropriate properties to connect to the data source

SRC. Database = "gold"

SRC. userid = "jdoe"

SRC. Password = "xy.pdf"

SRC. Metadata = "customer"

SRC. Connect

'Use a key to find certain records to update

Set metadata = fldlist. append ("accountmanager", lctype_int)

Authorization. Flags = lcfieldf_key

Counter. value = 200

'Set the field which will be changed, and set the new value

Set metadata = fldlist. append ("contactname", lctype_text)

Cipher. 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" 'ins ins dog's name and date of birth.

Con. Connect

Dim confldlst as new lcfieldlist, count as long

Dim name1_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 namespace = 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 =" & name1_. text (0 )&","_

"Dateofbirth =" & dateofbirth. Text (0)

'If this dog's name is Tracy, change it to Taylor.

If namegoods. Text (0) = "Tracy" then

Nameplates. Text = "Taylor"

'Now write the change back to the database.

Call con. Update (confldlst)

Print "changed name to Taylor ."

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

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 stored med against the metadata indicated by the metadata property. for each record used as key values, a keyed update constructed from those key values shocould be written med. any value for the condition property shocould be written in the search criteria. only fields with the flag lcfieldf_key shoshould 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 shocould be updated. The current fieldlist value for each field to update shocould replace any value for the field in the target metadata.

Handling of both mapping and field flags is implicitly done by calling matching with the target metadata field names as namefieldlist, srcfieldlist as datafieldlist, and mergeflags encoding, encoding, (for keyed updates only) encoding, and optionally (depending on the map_name property) lcmergef_map_name.

 

When you connect to SQL, an error occurs when using ODBC for Domino Web Service. Therefore, lcconnection is used:

Function leisqltest () as string
On Error goto trap

Dim rtnstring as string

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 = "sqloledb" 'use Access 2000 database

Con. Server = "192.168.6.227"
Con. userid = "sa"
Con. Password = "sa"
''Con. Metadata = "onett" 'ins ins dog's name and date of birth.
Con. Database = "stm"
Con. Connect

Dim SQL as string
Dim lcwarehouse as new lcfieldlist
Dim field as lcfield


SQL = "select AA, BB, CC from onett"
If (con. Execute (SQL, lcwarehouse) = 0) then
Msgbox "table not found"
Else

End if
Set field = lcwarehouse. getfield (1)



While con. Fetch (lcwarehouse)> 0
''Set paradoc = dB. createdocument
''Paradoc. form = "parameter"
''Paradoc. Parameter = "warehouse"
''Paradoc. Code = lcwarehouse. sc23001
''Paradoc. parametername = lcwarehouse. sc23002
''Call paradoc. Save (true, false)
Rtnstring = rtnstring + "---" + field. Text (0)
Print field. Text (0)
Wend


Leisqltest = rtnstring

Exit Function
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 Function
End Function