Subject Re: Strange behaviour with blobs and InsertSQL
Author stephanmtb
Hi Daniel,
I had the exact same problem and could reproduce your experience
exactly!! I did find out why-through many hours of trial and error.
It seem keylinks needs to be defined or none of the IB_Querys or
IBOQuerys will work properly. You can not rely on the
KeyLinksAutoDefine for anything other than for a select * from
tablename type of select sql. I see you have a join in your sql and
IBO does not auto define any keylinks if you have any type of
aggragate or any thing other than select * from tablename. I just had
a select distinct and it messed everything up for me. You need to
manually define keylinks and it should work. Look at the code in
IB_Components about line 26128 for "function TIB_Dataset.GetKeyLinks:
TIB_StringList;" and you will see why it does not get defined
automatically. Look up in the help to see how to set KeyLinks
Stephan Anderson

--- In, Daniel Albuschat <daniel@v...>
> Hello,
> 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
> structure:
> 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
> begin
> insert into test_base(id, title) values ( :i_id, :i_title );
> insert into test_info(id_base, text) values ( :i_id, :i_text );
> end^
> /* 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 =
> select
> b.title,
> i.text
> from
> test_base b
> left join
> test_info i
> on
> 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
> 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
> 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
> --
> eat(this); // delicious suicide