BuildInsert

(ODBC Manager Library)

Description:	Builds SQL Insert statements based on arrays of field names and values. Made to be called as a subroutine only.
Returns:	Text or ODBCQuery structure.
Usage:	Script Only.
Related to:	AddConnection | BuildDelete | BuildSelect | BuildUpdate | ConvertToDbDate | ConvertToDbTime | ConvertToDbTimeStamp | ConvertToVTSDate | ConvertToVTSTime | ConvertToVTSTimeStamp | CopyRecords | DBDropList | DBGridList | ErrMessage | ExecuteQuery | ExecuteQueryCached | FlushCache | FormatBatchQuery | GetConnList | ResultFormat | StatsWin | TableSynch | Transaction | TransactionCached
Format:	\ODBCManager.BuildInsert(  TableName,  InsertFields,  InsertValues,  SQLDataTypes,  dbType)
Parameters:

TableName

Required. The name of the table into which data will be inserted.

InsertFields

Required. May be a simple value or a one-dimensional array. Field names matching the InsertValues array of data to be inserted

InsertValues

Required. Any SQL data type. May be a simple value or a one-dimensional array. Provides the new value(s) for the fields in the matching InsertFields parameter.

SQLDataTypes

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

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.

The only way to ensure that a long running (or faulty) query does terminate is to set appropriate values for both the parameters ExecutionTimeout and UseDriverTimeout. Modules executing queries are designed to be run as launched modules and will remain active until complete.

Returns the SQL INSERT statement as a text string, unless long binary data is involved, in which case an ODBCQuery structure is returned. The format of the structure is as follows:

  ODBCQuery STRUCT [
    QueryString;
    Parameters;
  ];

Example:

InsertRecord [
  If 1 NextRecord;
  [
    Names  = New(4);
    Types  = New(4);
    Values = New(4);
    Nnames[0] = "RecordName";
    Names[1] = "TextVal";
    Names[2] = "NumberVal";
    Names[3] = "BoolVal";
    Types[0] = \ODBCManager\#SQL_VARCHAR;
    Types[1] = \ODBCManager\#SQL_VARCHAR;
    Types[2] = \ODBCManager\#SQL_NUMERIC;
    Types[3] = \ODBCManager\#SQL_BINARY;
    Values[0] = "rec1";
    Values[1] = "string1";
    Values[2] = 11;
    Values[3] = 1;
    Query = \ODBCManager.BuildInsert("TestTable", Names, Values, Types,
                                     \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);
  ]
]
NextRecord [ ...