what is the purpose of <UDF> in formula builder?

[modelpoint]

Documentation doesn't say much about what is the purpose or how the <UDF> function in Formula Builder can be used. Could you please give any examples?

[Ilan]

Actually, we will probably remove that at some stage. It was used to call stored procedures, however, everyone uses a DBLookup to do that.

Thanks for reminding us.
Ilan.

[modelpoint]

Speaking of DBLookup for calling stored procedures. I encountered an interesting problem today.

We have following two stored procedures (both MS SQL Server)

Code: Select all

procedure [dbo].[sequence_currval](
   @sequence_name varchar(30)
)
as
   declare @result int;
   declare @msg varchar(255);
begin
   -- set nocount on added to prevent extra result sets from
   -- interfering with select statements.
   set nocount on;

   select @result = current_value from sequence where sequence_name = @sequence_name;

   if @result is null
    begin
      raiserror ('sequence "%s" does not exist', 10, 1, @sequence_name);
      set @result = -1;
   end

   select @result as result;
   return;
end

and

Code: Select all

procedure [dbo].[sequence_nextval](
   @sequence_name varchar(50)
)
as
   declare @result int;
    declare @msg varchar(255);
begin
   set nocount on;
   set transaction isolation level repeatable read;

   begin transaction

   update sequence set current_value = current_value + 1 where sequence_name = @sequence_name;
   select @result = current_value from sequence where sequence_name = @sequence_name;

   if @result IS NULL
   begin
      rollback transaction;
      raiserror ('Sequence "%s" does not exist', 10, 1, @sequence_name);
      return;
   end

   -- Return the result of the function
   select @result as result;
   commit transaction;
end

The first one simply returns the current sequence value in a recordset, the second one increments sequence value by one and returns the result in a recordset.

We tried to call them in transformation in following manner

Code: Select all

DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_currval ('etl_batch')}")

DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_nextval ('etl_batch')}")


The first one works just fine. The second DBLookup returns <EMPTY>. Both procedures work fine when called outside Jitterbit (using any SQL query tool). The interesting thing is that second DBLookup actualy updates the value in the table, it just doesn't return anything (even though both procedures use exactly same method to return data to the calling app).

I also found that if update stament in the second stored proc was commented out, DBLookup would work. We currently have a workaround , we simply call it like this when we need a new value from the sequence

Code: Select all

Set(
   "batchID",
   If(  IsNull( DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_nextval ('etl_batch')}" ) ),
          DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_currval ('etl_batch')}"),
          DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_currval ('etl_batch')}")   )
)

but I'm just curious why DBlookup doesn't work for the second stored proc? Does it have something to do with implementation of SQL Server JDBC driver or is it something else?

Dmitriy Li
Model Point US

[Ilan]

The reason why it returns <Empty> is bacuase the SQL statement returns no data, but the stored procedure would still increment your sequence.

Your workaround is correct. Alternatively, you should be able to do the following to return the sequence value to your target field

<trans>
Set("NextVal",DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_currval ('etl_batch')}"));

DBLookup("<TAG>catalog-data-extractor/Targets/db-modelpoint-etl</TAG>","{call sequence_nextval ('etl_batch')}");

Get("NextVal")
</trans>

This way, the last thing you are doing in your formula is returning your value from a variable and that is what the target receives.

Thanks.
Ilan.

[modelpoint]

The reason why it returns <Empty> is bacuase the SQL statement returns no data

It actually does. Both procedures do

Code: Select all

select @result as result

in MS SQL Server that returns value of @result to the caller of the procedure in form of rowset. As I said, when called from outside Jitterbit both procedures return values but only one does so in Jitterbit