Subject RE: [IBO] Strange behaviour with blobs and InsertSQL
Author Jason Wharton
If you assign your KeyLinks property this problem will go away.
The problem is you are using a blob and the way Firebird/IB uses them you
have to refetch the record inserted to get the permanent BLOB_ID from the
server. When I go to refetch the record I need the KeyLinks. A blank record
is coming back.

There probably should be an error of some sort rather than just incorrect
behavior. I'm still working on this...

Jason Wharton

-----Original Message-----
From: Daniel Albuschat [mailto:daniel@...]
Sent: Tuesday, August 17, 2004 11:39 PM
Subject: [IBO] Strange behaviour with blobs and InsertSQL


I've noticed a very strange (and broken) behaviour when
using TIB_Query's InsertSQL property.
I've put together a test-case that should reproduce
the problem (with version 4.2.I at least):

First, here's the SQL script for the test data

create table test_base
id integer not null,
title varchar(100),
constraint pk_test_base primary key(id)

create table test_info
id_base integer not null,
text blob sub_type 1,
constraint fk_test_info_1 foreign key(id_base) references test_base(id) on
update cascade on delete cascade

create procedure insert_test(
i_id integer,
i_title varchar(100),
i_text blob sub_type 1
) as
insert into test_base(id, title) values ( :i_id, :i_title );
insert into test_info(id_base, text) values ( :i_id, :i_text );

/* End of SQL statements */

Then, you need to create a new form, put a TIB_Query on it,
a TIB_DataSource, a TIB_Grid and a TIB_Memo. Optionally,
you can use a TIB_NavigationBar to post and append a dataset.
The Memo's DataField property is set to "Text". All other
Dataset and Datasource properties are assigned accordingly.

TIB_Query.SQL.Text =

test_base b
left join
test_info i

TIB_Query.InsertSQL.Text =

execute procedure insert_test( :ID, :Title, :Text )

That's the whole scenario. Now start up the application and insert
a datarow. First, only assign the ID and type in something at
the TITLE's column of the grid. If you post the datarow, everything
works correctly.
Now the second try: Append a second row, assign the ID, type something
in the TITLE field, *and* assign a text in the TIB_Memo.
If you post the dataset now, you'll notice that something is going
wrong. All fields of just posted row are empty.
Anyways, if you close the application in this state, or simply
reopen the query, the data seems to be Ok. Only the display/buffering
is broken in some way.
Btw, this problem occures if you assign proper editsql and
deletesql, too. But for the test case, I wanted to keep it as
simple as possible.

I'd be very grateful if somebody can tell me a way to fix his.

Thank you for your time,
Daniel Albuschat