Functions for changing the data - (en)

After publishing the library of external functions (UDF) of Database Link, added some new functions.
The first publication contained only functions for consultation.
Now, there are already available functions that allow you to change the data from a remote base, from a SQL statement, Procedure or Trigger, within the Firebird.
Separated into 2 sets of UDF, the first that does not need transactions, with only one function, and the second set, the more complex, with 5 functions.

No need for transactions:

-ExecSQL (: Conn,: SQL);

Runs the SQL data change.

Where: conn is a connection string with the name of the data source registered in ODBC. E: SQL is the string with the senteça to be performed (insert, delete or updade).

Returns 1 on success.

example:

Select execsql('DBNAME', 'Update table_name Set field2 = ''ONE STRING'' Where field1 = 322439 ') From RDB$Database

Functions that works within transactions:

-BEGINTRANS (: Conn);

Initiates the transaction.

Where: conn is the connection string.

Returns the number Session started.

-ADDBATCH (: IdSession,: sql);

Adds a SQL statement to the queue.

Where:: idSession is an integer and: sql is the string containing the sql sentence.

Returns 1 on success, 0 on error.

-EXECBATCH(:idSession)

Executes the SQL statements that were added to the queue for addBatch.

Returns 1 on success.

-COMMITTRANS(:idSession) 

or

-ROLLBACKTRANS(:idSession)

Make the Commit or Rollback the transaction.

When executed, the session will end.

Follows a procedure containing an example of using the UDF with transactions.

The procedure call:

Run Procedure DBLINKEXECSQL;

Dependencies beyond the UDF:

CREATE EXCEPTION X_STOP '';

Para criar:

CREATE PROCEDURE DBLINKEXECSQL 

AS/**/

declare idSessao Integer ;

declare qtdIns Integer ;

declare resp Integer ;

declare ERRMSG Varchar(1024) ;

declare CD_LGR Integer ;

declare DS_TPLGR varchar(100) ;

declare DS_LGR varchar(100) ;

declare NM_USR varchar(100) ;

declare DT_CDS timestamp ;

declare SG_ALT varchar(3) ;

declare NR_CEP varchar(15) ;

begin

  

  idSessao = begintrans('EMHA');

  if (idSessao = 0) then

  begin

    ERRMSG = getErr(:idSessao) ;

    exception x_stop 'idsessao=0. '|| ERRMSG ;

  end

  

  for Select CD_LGR,DS_TPLGR,DS_LGR,NM_USR,DT_CDS,SG_ALT,NR_CEP

      From T_Lgr

      Into CD_LGR,DS_TPLGR,DS_LGR,NM_USR,DT_CDS,SG_ALT,NR_CEP

      do

  begin

       DS_LGR = replace(DS_LGR,'''','´') ; 

       qtdIns = addbatch(:idSessao,

       'Insert Into T_Lgr (CD_LGR,DS_TPLGR,DS_LGR,NM_USR,DT_CDS,SG_ALT,NR_CEP) Values ('

       ||:CD_LGR ||','''|| :DS_TPLGR ||''','''|| :DS_LGR

       ||''','|| Coalesce(''''||:NM_USR||'''','NULL') ||','|| Coalesce(''''||:DT_CDS||'''','NULL') ||','''||:SG_ALT

       ||''','|| Coalesce(''''||:NR_CEP||'''','NULL')

       ||')'

       ) ;

       if (qtdIns = 0) then

       begin

         ERRMSG = getErr(:idSessao) ;

         exception x_stop 'qtdIns=0. '|| ERRMSG ;

       end

  end

  resp = execbatch(:idSessao) ;

  if (resp = 0) then

  begin

    resp = rollbacktrans(:idSessao) ;

    ERRMSG = getErr(:idSessao) ;

    exception x_stop 'execbatch. '|| ERRMSG ;

  end

  resp = committrans(:idSessao) ;

  if (resp = 0) then

  begin

    ERRMSG = getErr(:idSessao) ;

    exception x_stop 'committrans. '|| ERRMSG ;

  end

end