Xbasic

SQL::ConnectionGenerateInsertStatement Method

Syntax

Statement as C = GenerateInsertStatement as C (TableInfo as SQL::TableInfo [, ReplicateIdentity = .f. as L [, UseBatchQualifiers = .t. as L [,SourceTableInfo as SQL::TableInfo]]])

Arguments

TableInfoSQL::TableInfo: SQL::TableInfo
ReplicateIdentityLogical: Default = .F. If ReplicateIdentity is .T., the statement will include any identity column. When the insert statement is executed, identity values must be available.
UseBatchQualifiersLogical: Default = .T. Changes the syntax of SQL INSERT, DELETE, and UPDATE statements.
SourceTableInfoSQL::TableInfo: SQL::TableInfo

Returns

StatementCharacter: A SQL INSERT statement.

Description

Generate a SQL Insert statement for the table passed in.

Discussion

The GenerateInsertStatement() method generates a SQL INSERT statement for the table passed in. When UseBatchQualifiers is explicitly set to .T., the appropriate column values are generated in the format :old. or :new. (All :old. for DELETE or WHERE clauses, all :new. for INSERT and a mixture for UPDATE statements). Note: If UseBatchQualifiers is true then field values will be generated as arguments prefaced with 'new.' so a result set can be the source of the data.

Example

This statement is used when the values for insertion should come from the new data values in a result set.

INSERT INTO tablename (column1, column2) VALUES (:new.value1, :new.value2)

If UseBatchQualifiers is set to .F., these function will generate the value entries as simple arguments (with one exception) so you can build an arguments collection up and execute the statement.

INSERT INTO tablename (column1, column2) VALUES (:column1, :column2)

This script creates a new table and inserts the contents of a SQL::ResultSet into the table.

dim conn as SQL::Connection
dim ti as SQL::TableInfo
dim rs as SQL::ResultSet
dim connstring as C
dim tbl as P
dim insert as C
connstring = "{A5API=Access, FileName='C:\Program Files\A5V8\MDBFiles\Alphasports.mdb', UserName='Admin'}"
ti.Name = "AlphaSportsCustomerX"
if .not. conn.Open(connstring) then
    ui_msg_box("Error", conn.CallResult.text)
    end
end if
SQL_TableInfoOfDBF("c:\program files\a5v8\samples\alphasports\Customer.dbf", ti)
if .not. conn.CreateTable(ti) then
    ui_msg_box("Error", conn.CallResult.text)
    end
end if
insert = conn.GenerateInsertStatement(ti)
tbl = table.open("c:\program files\a5v8\samples\alphasports\Customer.dbf")
rs = conn.ResultSetFromDBF(tbl)
if .not. conn.ApplyData(insert, rs) then
    ui_msg_box("Error", conn.CallResult.text)
end if
tbl.close()
conn.close()