BuildUpdate

(ODBC Manager Library)

Description:	Builds SQL UPDATE statements using supplied field names and values. Made to be called as a subroutine, but will function as a called module.
Returns:	Text
Usage:	Script Only.
Related to:	AddConnection | BuildDelete | BuildInsert | BuildSelect | ConvertToDbDate | ConvertToDbTime | ConvertToDbTimeStamp | ConvertToVTSDate | ConvertToVTSTime | ConvertToVTSTimeStamp | CopyRecords | DBDropList | DBGridList | ErrMessage | ExecuteQuery | ExecuteQueryCached | FlushCache | FormatBatchQuery | GetConnList | ResultFormat | StatsWin | TableSynch | Transaction | TransactionCached
Format:	\ODBCManager.BuildUpdate(  TableName,  UpdateFields,  UpdateValues,  SQLDataTypes,  WhereFields,  WhereOperators,  WhereValues,  WhereSQLDataTypes,  WhereAND,  dbType)
Parameters:

TableName

Required. Any expression for the name of the table to be updated.

UpdateFields

Required. May be a simple value or a one-dimensional array. Provides the field names to be updated

UpdateValues

Required. Any SQL data type. May be a simple value or a one-dimensional array. Provides the new values for the fields

SQLDataTypes

Required. Values indicating the data type of the update values. Should be a simple value or an array matching the UpdateFields parameter. Refer to Data Type Codes used in the ODBC Manager for a list of the numeric codes.

WhereFields

Required. Any expression or array of the field names for WHERE clause

WhereOperators

Required. May be a simple value or a one-dimensional array. Operators for WHERE clause

WhereValues

Required. May be a simple text value or a one-dimensional array of text. Values for WHERE clause

WhereSQLDataTypes

Required. Values indicating the data type of the insert values. Should be a simple value or an array matching the WhereFields parameter. Refer to Data Type Codes used in the ODBC Manager for a list of the numeric codes.

WhereAND

Required. Can be any expression that evaluates to a Boolean true or false. If set to true (non-zero) then the components of the WHERE clause are to be ANDed together. If false (0) an OR is used between the sub clauses.

dbType

Required numeric value, indicating the type of this DB connection. DBType Meaning 0 MS SQL 1 MS Access 2 Oracle 3 MySQL 4 SyBase

Comments:

This module is a member of the ODBCManager Library, and must therefore be prefaced by \ODBCManager, as shown in "Format" above. Returns the SQL UPDATE statement as a text string unless binary parameters are involved, in which case an ODBCQuery structure is returned. The format of the structure is as follows:

  ODBCQuery STRUCT [
    QueryString;
    Parameters;
  ];

Example:

UpdateRecord [
  If 1 GetUpdatedRecord;
  [
    SubNames  = New(2);
    SubValues = New(2);
    SubTypes  = New(2);

    SubNames[0] = "TextVal";
    SubNames[1] = "NumberVal";
    SubTypes[0] = \ODBCManager\#SQL_VARCHAR;
    SubTypes[1] = \ODBCManager\#SQL_NUMERIC;
    SubValues[0] = "changed";
    SubValues[1] = 110;
    Query = \ODBCManager.BuildUpdate("TestTable", SubNames, SubValues, SubTypes,
                                     "RecordName", "=", "rec1", \ODBCManager\#SQL_VARCHAR, 1,
                                     \ODBCManager\#dbType_MSAccess);
    Err    = Invalid;
    Result = Invalid;
    Attrib = Invalid;
    ErrorMsg = Invalid;
    SQLState = Invalid;
    ErrorCode = Invalid;
    \ODBCManager.ExecuteQuery(&Err, Query, Conn1DSN, Invalid, Invalid, &Result, &Attrib, &ErrorMsg, &SQLState,
                              &ErrorCode, 0, Invalid, 0, \ODBCManager\#dbType_MSAccess);
  ]
]

GetUpdatedRecord [...