Updated: 2023-09-20

OLE DB Property of Rowset and Command — «update_sql»

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

Defines SQL queries executed when updating a row.

Several SQL are enumerated through semicolon.

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

Examples

Variant of property value

update_sql = «UPDATE MY_TABLE SET ID=:NEW.ID, NAME=:NEW.NAME WHERE ID=:OLD.ID RETURNING ID INTO :COL.MY_TABLE.ID»

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("update_sql")= _
  "update COUNTRY "&_
  "SET CURRENCY='#ANY' "& _
  "WHERE COUNTRY=:OLD.COUNTRY "& _
  "returning COUNTRY, CURRENCY "& _
  "into :COL.COUNTRY,:COL.COUNTRY.CURRENCY"

'LockType =adLockPessimistic (2)
'CommandType=adCmdText (1)
call rs.Open("select * from COUNTRY where COUNTRY='Russia'",,,2,1)

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

wscript.echo "--"

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

call rs.Update(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