Do you want to enable execution of SQL scripts in your programs and spend less time for exhausting coding?
Do you want to create a table, a generator and a trigger, to add data to the table, to make data selection and then to delete all this in one command? Now it is possible with new IBProvider!
The new IBProvider v.3.3.0.11117 allows to run a command with several SQL queries (SQL scripts). For example:
set autoddl on;
set transaction;
create generator GEN_ID_TEST_TABLE;
create table TEST_TABLE (ID INTEGER NOT NULL PRIMARY KEY,TEXT BLOB SUB_TYPE TEXT);
create trigger BI_TEST_TABLE for TEST_TABLE
before insert
as
begin
if(NEW.ID IS NULL) then NEW.ID=GEN_ID(GEN_ID_TEST_TABLE,1);
end;
insert into TEST_TABLE (text) values('record 1');
insert into TEST_TABLE (text) values('record 2');
set term !!;
select * from TEST_TABLE!!
set terminator ;!!
TEST_TABLE;
set autoddl off;
drop table TEST_TABLE;
drop generator GEN_ID_TEST_TABLE;
commit;
We added the support of special commands:
- SET {TERM|TERMINATOR} <new_stmt_terminator_string>. Added for compatibility with ISQL. The new terminator is effective only within the framework of the current script.
- SET AUTODDL {ON|OFF}. If enabled, the following DDL commands will be committed automatically. The set mode is effective only within the framework of the current script. If this instruction is absent in the script, the initial condition is defined by the value of auto_commit_ddl property.
Are queries returning data to the client supported?
If the script has a query that returns a rowset, IBProvider will download all rows (including BLOB-fields and arrays) to the client. If necessary, temporary files will be involved to store large rowsets. After the command execution has been finished, you can access the downloaded data.
What will happen if several data returning queries will occur in one script?
The provider allows to search the results of each executed query.
When working with Firebird/InterBase through ADODB, use Recordset.NextRecordset method for this. If a query was finished by an error, Recordset.NextRecordset will output an exclusion that can be intercepted and processed. If a query doesn’t return a rowset, the value of Recordset.State property will be null (adStateClosed). After all the results have been searched through, Recordset.NextRecordset will return Nothing.
When working with Firebird/InterBase through ADO.Net to download data from several rowsets, use OleDbDataAdater.Fill(dataset) method.
When working with Firebird/InterBase through OLE DB interfaces, you should request the command execution result as an object with IMultipleResults interface.
What restrictions does application of SQL scripts have?
Currently, you cannot set command parameters for SQL scripts in IBProvider. This opportunity will appear in the next releases.
Is it possible to continue script execution if there was an error in one of the queries?
Yes, it is possible trough the initialization property. On default, if an error occurs, IBProvider will stop command execution. This behavior can be changed by setting the new initialization and command property multi_stmts_exec_rules as 1. In this case, the provider will save the error description and continue execution of the queries.
3 Advantages of the new IBProvider!
1. You will be able to embed execution of SQL queries into all your programs
Now it is very simple to execute SQL scripts in a separate window of your application. Simply add a Memo field and «Execute» button to the form and transfer script text to IBProvider. Our driver will do the rest:
- Sort out DDL and DML queries
- Prepare and execute your commands
- Transfer message text to you in case of an error in a query or in the execution process
- Return resulting rowsets obtained as a result of command execution. All you will have to do is to input the downloaded data to the Grid (table)
This function is perfectly suited for making corrections in a working system when you have only a user computer and do not have a full-featured database editor. You can add this function to all of your programs. It will especially suit those who prefer to refine the system on an already working database.
2. You can write notably less code
Previously, you have to create and adjust a separate command for each SQL instruction. Now all logic of the function of working with a database can be implemented in one place by writing a script of several SQL commands. This will allow to reduce substantially application writing time.
3. You will be able to bring Firebird and InterBase database administration to the new level
The script packages for database administration (transfers, merge, replication) — everything became much more simple. You can execute SQL commands by batches right from VBScript scripts on any Windows operated computer.
Save a lot of your time — set scheduled execution of these scripts and receive for instance script execution reports to your email.
