Object Reference

Classes
Command Interface
- xf_Command
External
- xf_External_VB6_32bit
- xf_External_VB7_32_64bit
General
System Core
System Library

xf_SchemaSQLConstructorSQL Constructor

Public Function SQLUpdateSETClause1_MoreThan127Cols_VPA (
IntoTableName As String,
PrefixTableName As String,
DefaultInsertUnlisted As Boolean,
IncludeIdentity As Boolean,
CoerceNullToEmptyString As Boolean,
SplitResultString As Boolean,
ByRef SecondResultString As String,
FieldValList As Variant
) As String

Create an UPDATE 'SET' clause from a (possibly multiple row) SQL source and passed values. The metatable is used to format the column data as expected by the back end table. FieldValList is in the format 'fieldname',fieldvalue, ...
INSERTS cope with 255 columns, however because UPDATES do a pass for the set row and value row they have an effective limit of 127 columns when updating column-from-column When there are more than 127 columns, we can split the insert into two parts using SplitResultString=true, and the second part of the result is passed back in SecondResultString For each column in the destination table, the following is applied :
- if not in the list of passed values,
- if DefaultInsertUnlisted=true, take column val from SQLFromSource with same column name (assume exists)
- if DefaultInsertUnlisted=false, skip column (if column not nullable and has no default value, then flag error)
- if passed, insert the value passed into SET assignment for that column
- if field name preceded with +, SET column val to column in SQLFromSource(same column name if passed value "", passed value otherwise)
- if field name preceded with -, skip column (if column not nullable, then flag error)

IntoTableName as String

The table name for the UPDATE statement

PrefixTableName as String

The table name to append to a column name in the SET value list

DefaultInsertUnlisted as Boolean

Automatically generate SQL for columns not defined in the passed name/value list

IncludeIdentity as Boolean

Generate column insertions for the identity column (otherwise it is omitted)

CoerceNullToEmptyString as Boolean

Automatically add nz() around inserted column names

SplitResultString as Boolean

If true, split the SET clause into two parts, and pass the second part back in SecondResultString

ByRef SecondResultString as String

The second SET clause, if SplitResultString=True

FieldValList as Variant

The paired column names and values ParamArray, passed as a Variant

Call Template:

Dim s As String

s = xf.DefaultSchema.SQLConstructor.SQLUpdateSETClause1_MoreThan127Cols_VPA(IntoTableName, PrefixTableName, DefaultInsertUnlisted, IncludeIdentity, CoerceNullToEmptyString, SplitResultString, SecondResultString, FieldValList)