Updated: 2023-10-19
OLE DB Property of Rowset and Command — «insert_sql»
Name:
insert_sql
Type:
VT_BSTR
R/W:
R/W
Default:
EMPTY
IBProvider:
2, 3, 5
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:
See also
- The rowset property update_sql
- The rowset property delete_sql
- The rowset property refresh_sql
- The rowset property named_param_prefix
- The rowset property auto_insert_field_rule
- The rowset property auto_update_field_rule
- The rowset property Server Data on Insert
- The initialization property server_data_back_read