IBProvider v3.5. Support for Firebird v2.5.1. Automatic generation of SQL queries has been improved. Support for parameters in SQL scripts

Support of parameters in SQL scripts

The new IBProvider version supports parameters in the SQL-scripts containing several SQL queries. When this option is used, the following implementation limitations should be taken into account:

• Only named parameters are supported.
• The user must adjust parameter descriptions by his own.

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

cmd.NamedParameters=true

cmd.ActiveConnection=cn

rem enable interface for the RecordCount support
cmd.Properties("IRowsetScroll").value=true

cmd.CommandText = _
 "select first_name,last_name" & vbCrLf & _
 " from employee" & vbCrLf & _
 " where emp_no=:emp_no;" & vbCrLf & _
 "select p.proj_id,p.proj_name,p.proj_desc" & vbCrLf & _
 " from project p join employee_project ep on p.proj_id=ep.proj_id" & vbCrLf & _
 " where ep.emp_no=:emp_no;"

rem Pete Fisher
call cmd.Parameters.Append _
 (cmd.CreateParameter("emp_no",adVariant,adParamInput,-1,24))

dim rs
set rs=cmd.Execute()

rem output: 1
wscript.echo "rs1 size:"&rs.RecordCount

set rs=rs.NextRecordset

rem output: 2
wscript.echo "rs2 size:"&rs.RecordCount

If a problem occurs, we recommend to study error messages for each script query. IBProvider forms detailed error text descriptions.

Automatic generation of the SQL queries INSERT, DELETE and UPDATE based on the command SELECT

This functionality was implemented to extend the possibilities of working with updatable rowsets and for automatic generation of commands.

In the previous releases, definition of primary key columns and generation of queries for recording changes back to the database were supported only for a trivial query of the type «select * from MY_TABLE». In the new version, this has been dramatically improved. Now the provider extensively supports SQL queries:

• With enumeration of columns: «select col1, col2 t_col2, col3 as t_col3, t.col4, t.col5 t_col5, t.col6 as t_col6 from my_table as t». Column name can be put in parentheses.
• With selection of all columns of the table: «select t.* from my_table t»
• With the section WHERE: «select <columns> from my_table t where <conditions>»
• With the sections PLAN, ORDER BY, FOR UPDATE, ROWS

Existing limitations:

• The query must return data from only one table.
• The list of returned columns of the set can only contain basic columns of the table and must not contain repetitions.
• The combining of queries «select t1.* from my_table t1 union select t2.* from my_table t2» is not supported. However, UNION can be used in a subquery of the section WHERE.
• Result set must contains all columns of primary key

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

cmd.NamedParameters=true

cmd.ActiveConnection=cn

rem enable interface for the updatable recordset
cmd.Properties("IRowsetUpdate").value=true

cmd.CommandText = _
 "select emp_no,first_name,last_name from employee where emp_no=:x"

rem Pete Fisher
cmd("x")=24

dim rs
set rs=cmd.Execute()

wscript.echo "FIRST_NAME :"&rs("first_name").value
wscript.echo "LAST_NAME  :"&rs("last_name").value

rs("first_name").value =ucase(rs("first_name").value)
rs("last_name").value  =ucase(rs("last_name").value)

call rs.UpdateBatch()

set rs=nothing

rem ----
wscript. echo "-------------------- [re-execute]"

set rs=cmd.execute()

wscript.echo "FIRST_NAME :"&rs("first_name").value
wscript.echo "LAST_NAME  :"&rs("last_name").value

rem OUTPUT: FIRST_NAME :Pete
rem OUTPUT: LAST_NAME  :Fisher
rem OUTPUT: -------------------- [re-execute]
rem OUTPUT: FIRST_NAME :PETE
rem OUTPUT: LAST_NAME  :FISHER

Introduction of support for Firebird 2.5.1

Now Firebird 2.5.1 is used as the main test server of the provider. In the new IBProvider version its changes are taken into account.

The new IBProvider version allows to write less code for working with database at the expense of batch execution of SQL queries and automatic command generation. In addition, the new IBProvider supports the latest versions of the databases InterBase XE (2010) and Firebird 2.5.1

Do you want to create FAST and compact 32-bit and 64-bit applications
using the CAPABILITIES of the latest Firebird and InterBase versions?

Choose IBProvider Professional ver.3.5

Download it right now!!!

Or purchase the time-unlimited version