IBProvider v3.6. New interfaces for rowset and new metadata schema

New interfaces for rowset: IRowsetRefresh, IRowsetResynch

• New interfaces are used to reload the values of columns in the rowset.
• By default, support of IRowsetRefresh and IRowsetResynch interfaces disabled. For activation of these interfaces, need to assign «true» to the properties IRowsetRefresh, IRowsetResynch.
• For configuration of work of these interfaces, defined new properties: refresh_sql, refresh_trans_type, refresh_trans_level.
• Note that ADODB.Field.UndelyingValue can’t correctly work with IRowsetRefresh interface. Our common recommendations for ADODB users — activate and use the IRowsetResynch interface only.

option explicit

'--------------------------------------------------------------------------
dim cn
set cn=createobject("ADODB.Connection")

cn.Provider="LCPI.IBProvider.3"

cn.Properties("location")="localhost:d:\database\employee.fdb"
cn.Properties("user id") ="SYSDBA"
cn.Properties("password")="masterkey"

call cn.Open()

call cn.BeginTrans()

dim cmd
set cmd=createobject("ADODB.Command")

cmd.ActiveConnection=cn

cmd.Properties("IRowsetResynch")=true

'Robert Nelson
cmd.CommandText="select * from employee where EMP_NO=2"

dim rs
set rs=cmd.execute()

wscript.echo "[CurrentValue]    FIRST_NAME : "&print_value(rs("first_name").value)
wscript.echo "[UnderlyingValue] FIRST_NAME : "&print_value(rs("first_name").UnderlyingValue)

wscript.echo "--- [update]"

call cn.Execute("update employee set first_name=upper(first_name) where emp_no=2")

wscript.echo "[CurrentValue]    FIRST_NAME : "&print_value(rs("first_name").value)
wscript.echo "[UnderlyingValue] FIRST_NAME : "&print_value(rs("first_name").UnderlyingValue)

wscript.echo "--- [resync]"

call rs.Resync()

wscript.echo "[CurrentValue]    FIRST_NAME : "&print_value(rs("first_name").value)
wscript.echo "[UnderlyingValue] FIRST_NAME : "&print_value(rs("first_name").UnderlyingValue)

call wscript.quit(0)

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
private function print_value(v)
 dim s

 if(IsNull(v))then
  s=s&"#NULL"
 else
  s=s&cstr(v)
 end if

 print_value=s
end function 'print_value

Output:

New scheme of metadata: DBSCHEMA_IBP_FIELD_DIMENSIONS

• A new scheme provides the information about dimension of column or SP parameter with array data type.
• Schema identifier: {769A128E-04BF-11D8-AE8B-00A0C907DB93}
• Schema columns:
  • RELATION_CATALOG [WSTR]
  • RELATION_SCHEMA [WSTR]
  • RELATION_NAME [WSTR]
  • FIELD_NAME [WSTR]
  • FIELD_GUID [GUID]
  • FIELD_PROPID [UI4]
  • DIMENSION [UI4]. Numbering begins at one
  • LOWER_BOUND [I4]
  • UPPER_BOUND [I4]
• Restriction columns: RELATION_CATALOG, RELATION_SCHEMA, RELATION_NAME, FIELD_NAME.
• Default sort order: RELATION_CATALOG, RELATION_SCHEMA, RELATION_NAME, FIELD_NAME, DIMENSION.

option explicit

'--------------------------------------------------------------------------
private const adSchemaProviderSpecific=-1

private const guid_DBSCHEMA_IBP_FIELD_DIMENSIONS="{769A128E-04BF-11D8-AE8B-00A0C907DB93}"

'--------------------------------------------------------------------------
dim cn
set cn=createobject("ADODB.Connection")

cn.Provider="LCPI.IBProvider.3"

cn.Properties("location")="localhost:d:\database\employee.fdb"
cn.Properties("user id") ="SYSDBA"
cn.Properties("password")="masterkey"

call cn.Open()

dim restrictions
restrictions=Array(empty,empty,"JOB","LANGUAGE_REQ")

dim rs
set rs=cn.OpenSchema(adSchemaProviderSpecific, _
                     restrictions, _
                     guid_DBSCHEMA_IBP_FIELD_DIMENSIONS)
dim nRec
nRec=0

while not rs.eof
 nRec=nRec+1

 wscript.echo "---------------- ["&nRec&"]"
 wscript.echo "RELATION_CATALOG: "&print_value(rs("RELATION_CATALOG").value)
 wscript.echo "RELATION_SCHEMA : "&print_value(rs("RELATION_SCHEMA").value)
 wscript.echo "RELATION_NAME   : "&print_value(rs("RELATION_NAME").value)
 wscript.echo "FIELD_NAME      : "&print_value(rs("FIELD_NAME").value)
 wscript.echo "FIELD_GUID      : "&print_value(rs("FIELD_GUID").value)
 wscript.echo "FIELD_PROPID    : "&print_value(rs("FIELD_PROPID").value)
 wscript.echo "DIMENSION       : "&print_value(rs("DIMENSION").value)
 wscript.echo "LOWER_BOUND     : "&print_value(rs("LOWER_BOUND").value)
 wscript.echo "UPPER_BOUND     : "&print_value(rs("UPPER_BOUND").value)

 call rs.MoveNext()
wend

wscript.echo ""
wscript.echo "total record count: "&cstr(nRec)

call wscript.quit(0)

'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
private function print_value(v)
 dim s

 if(IsNull(v))then
  s=s&"#NULL"
 else
  s=s&cstr(v)
 end if

 print_value=s
end function 'print_value

Output: