Updated: 2018-05-07

OLE DB Property of Rowset and Command — «insert_sql»

Name:
insert_sql
Type:
VT_BSTR
R/W:
R/W
Default:
EMPTY
IBProvider:
2, 3

Defines SQL queries executed when inserting a new row.

Several SQL are enumerated through semicolon.

Usage of parameters with «NEW», «GEN», «GEN_N», «GEN_NI», «COL» markers is allowed in the query text.

Examples

Variant of property value

insert_sql = «INSERT INTO MY_TABLE (ID,NAME) VALUES(:GEN.GEN_ID_MY_TABLE.ID,:NEW.NAME) RETURNING ID, NAME INTO :COL.ID, :COL.NAME»

Example for VBScript and ADODB

option explicit

dim cn,rs,v,f

set cn=createobject("ADODB.Connection")

cn.ConnectionString= _
 "provider=LCPI.IBProvider.3;"& _
 "location=inet4://localhost/d:\database\fb_03_0_0\employee.fdb;"& _
 "user id=SYSDBA;"& _
 "password=masterkey;"& _
 "dbclient_type=fb.direct"

call cn.Open()

call cn.BeginTrans()

set rs=createobject("ADODB.Recordset")

set rs.ActiveConnection=cn

rs.Properties("insert_sql")= _
  "insert into COUNTRY (COUNTRY,CURRENCY) "&_
  "values(:NEW.COUNTRY,'MarsCoin') "&_
  "returning COUNTRY, CURRENCY "&_
  "into :COL.COUNTRY,:COL.COUNTRY.CURRENCY"

'LockType =adLockPessimistic (2)
call rs.Open("COUNTRY",,,2)

'New row column values
f=array("COUNTRY","CURRENCY")
v=array("Mars"   ,"#DUMMY")

call rs.AddNew(f,v)

wscript.echo "COUNTRY  : "&rs("COUNTRY").value
wscript.echo "CURRENCY : "&rs("CURRENCY").value

call cn.RollbackTrans()

Output:

Sample output

See also

Other properties of group

Other sets of properties