Subject Re: [IBO] Problem with a create database script
Author Geoff Worboys (TeamIBO)
> I am new on this list.

Hello :-)

> A. What can cause this incompatibility WISQL <-> TIB_Script ?

> B. How to call this procedure correctly in IBO? Is it possible?

It could be a difference in the transaction commit method used. You
may need an explicit commit call inside your script (before trying to
execute a procedure that was presumably just created), or if that does
not work try setting ServerAutoCommit to true (on the Transaction
component) - apparently this helps with some DDL scripts.

Then again you may be right, that IBO is having difficulty executing a
procedure that has return values inside IB_Script. I am not certain,
I've not tried it.

> C. The procedure has a _result value_ (the ID of the new
> Lookup-Entry) which is ignored in my script I tried to make a test
> scenario with no result declaration -> this seems to work in IBO
> script. If this is the real problem, how can I declare global
> variables in a script to receive result values?

IBO TIB_Script simply runs a series of commands it is not a full
embedded SQL application - which is what you would need to do what I
think you want. That is; I am guessing the procedure returns a key
value, that you then want to use in later inserts?

There are a number of ways of doing this sort of thing...

One way to do this is...

create a procedure that will perform the inserts of the data needing
the lookup key information. along the lines of...

create procedure xxxx( lookup_unique_name varchar(...),
<other insert values>
declare variable lookup_key <type>
select primary_key_field from lookup_table
where unique_name_field=:lookup_unique_name
into :lookup_key;
insert into some_table ( ... )
values ( ... )

This procedure can then be called by your script to insert values into
a table - after first looking up the necessary lookup key value for
the insert (from lookup records that were presumably inserted earlier
in the script).

Another way to do this...

Presuming the lookup key value you are wanting comes from a generator,
you can set the generator forward:
SET GENERATOR lookup_gen TO 1000;

and then use the "reserved" values (from 0..999 in example above) as
fixed lookup keys. Insert the lookup records using predefined lookup
keys from this value - and match them when inserting the main records
that need the lookup foreign key.

I hope the above makes sense.

Geoff Worboys - TeamIBO
Telesis Computing