Publish date: 2010-04-19

Changes in SQL-parser. Support for SQLSTATE and NativeError

SQL-parser of IBProvider v3 was improved

  • Support of end symbol «;» in sql-queries
  • IBProvider raise error if many sql-queries appeared in one command text
  • IBProvider raise error if empty query appeared in one command text. For example: «select * from RDB$DATABASE;/*empty stmt*/;»
  • Following queries now supported by IBProvider core:
    • «GRANT …
    • «REVOKE …»
    • «SET GENERATOR …»
    • «SET STATISTICS …»

New rowset properties was added in IBProvider v3

SQLSTATE and NativeError now supported (v3)

  • When IBProvider handle Firebird/InterBase server errors it create special object CustomErrorObject, which contains 2 parameters: SQLSTATE (5-symbols string with standart error code) and NativeError (ISC Error Code).
  • This feature allows get custom error info for any(!) Firebird, InterBase or Yaffil server and didn’t depends on a client library
  • New algorithm for SQLSTATE is based on Firebird 2.5 code.
  • IBProvider supports isc_arg_sqlstate — new tag of Firebird 2.5 status vector.

If you are using ADODB for access to Firebird or InterBase, you can get SQLSTATE and NativeError via Errors object of ADODB.Connection:

  • ADODB.Connection->Errors->Error->SQLState
  • ADODB.Connection->Errors->Error->NativeError

For example:

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"
cn.Properties("auto_commit")=true

call cn.Open()

on error resume next

call cn.Execute("DROP TABLE ABRA_KADABRA")

on error goto 0

dim i

for i=0 to cn.Errors.Count-1
 wscript.echo "--- ERROR: "&cstr(i+1)
 wscript.echo "SQLState    : "&cn.Errors.Item(i).SQLSTATE
 wscript.echo "NativeError : "&cn.Errors.Item(i).NativeError
next

Result:

--- ERROR: 1
SQLState    : 42S02
NativeError : 336397206 <-- ISC CODE: isc_dsql_table_not_found

Publish date: 2010-04-19. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link http://www.ibprovider.com/eng required.