BorlandTalk.com

pcerdaz · Guest

I have a TIBDataSet with a SelectSQL that work’s very well,
however, when I add a lookup field to retrieve data from
another table, the answer of the SelectSQL is very slow.

The lookup field is working well but it is affecting the speed
of SelectSQL; I need this lookup field to show the information
on a Grid.

Is there another method to do it without affecting the speed of
SelectSQL?

Thanks,
pcerdaz

Giuliano · Guest

Il 28 Jan 2007 14:06:01 -0800, "pcerdaz" <pcerdaz (AT) manquehue (DOT) net> ha scritto:

pcerdaz · Guest

Hi Giuliano,

Very good idea; I just joined the tables and now I have a
runtime query that give me those fields that I need, however
the IBDataSet where I made the SelectSQL with my query don’t
have those joined fields as part of its persistent fields from
where I can use it to show over a DBGrid. How can I do that?

Regards,
pcerdaz

Giuliano <> wrote:

Wayne Niddery [TeamB] · Guest

pcerdaz wrote:

pcerdaz · Guest

The fields that appear in the fields editor does not include
those that I create on runtime. This is because the SelectSQL in desing time is not equal to the SelectSQL in runtime. I'm looking for a method to add a field on runtime.

Any idea?

Thank you,
pcerdaz

"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote:

Wayne Niddery [TeamB] · Guest

pcerdaz wrote:

Giuliano · Guest

Il 4 Feb 2007 10:49:02 -0800, "pcerdaz" <pcerdaz (AT) manquehue (DOT) net> ha scritto:

pcerdaz · Guest

At desing time I use the SQL Editor that create automatically somthing like "select * from MI_TABLE" but at runtime I need some filters like "select * from MI_TABLE where A = B". Thinking again, probably it is posible to add the joined fields no matter the filters that I applied later.

Regards,
pcerdaz

Giuliano <> wrote:

Giuliano · Guest

Il 6 Feb 2007 05:57:05 -0800, "pcerdaz" <pcerdaz (AT) manquehue (DOT) net> ha scritto:

pcerdaz · Guest

Giuliano,

Your comments are being very helpful to me, however, the new
query with joined tables is not showing the field values of
new records that were inserted and populated, this happen
until exit and start again of my application.

To avoid this problem I’m trying with Commit of my database
and making a Refresh of the table, but it doesn’t work. Any
idea would be estimated.

Thank you,
pcerdaz

Giuliano <> wrote:

Giuliano · Guest

Il 16 Feb 2007 10:12:37 -0800, "pcerdaz" <pcerdaz (AT) manquehue (DOT) net> ha scritto:

Hi pcerdaz,

As far as I know, if you have modified the joined table in the context
of the same transaction object, you should be able to see the changes to the
recorset without commit the current transaction.

Hence, the obvious question is: have you refreshed the recordset?
That is, do you have closed then reopened again the DataSet component?
Keep in mind that the TIBDataSet's Refresh method, if I recall correctly,
is used primarily in order to refresh a single row, not the entire recordset.
In fact, you have to furnish a "refresh query" which includes a clause that
refetch the selected row in the opened recorset.
If you insert a record into a table using the TIBDataSet's Insert method,
fill the fields with sensible values, then you posts the new row using the
TIBDataSet's Post method, you can see immediately the changes you made.
But if you have added the new row with an another component (e.g. with
a TIBSQL componente), even if you are using the same TIBTransaction component
and you are in the context of the same transaction, alas, you can't see the
new row, e.g. in a grid, until you refresh the TIBDataSet component
connected with the aforesaid grid.
You could need to call the Commit method (or the CommitRetaining method) of
the Transaction Component, only if the row was added from another application
or, in the same application, if the row was added by others components linked
with a different transaction component (or in the context of an another
transaction).

Usually, it suffice to call the TIBDataSet::Close() and TIBDataSet::Open()
methods in sequence. That is:

IBDataSet->Close();
IBDataSet->Open();

However, it's possible that you lose the current cursor position. In order to
keep the current cursor position, you can use bookmarks. The following snippet
could help:

TBookmark const Bookmark = IBDataSet1->GetBookmark();
IBDataSet->Close();
IBDataSet->Open();
IBDataSet->GotoBookmark( Bookmark );
IBDataSet->FreeBookmark( Bookmark );

But, the aforesaid code isn't too elegant. Surely is not "exception safe"
code. Using the RAAI idiom is possible to have a better code. Just declare a
class like the following:

template<typename DS>
class BookmarkManagerType {
public:
explicit BookmarkManagerType( DS& DataSet )
: ds_( DataSet ), bm_( ds_.GetBookmark() ) {}
~BookmarkManagerType() throw() {
try {
ds_.GotoBookmark( bm_ ); // can throw?
}
catch ( Exception& E ) {}
ds_.FreeBookmark( bm_ );
}
private:
BookmarkManagerType( BookmarkManagerType const & );
BookmarkManagerType& operator=( BookmarkManagerType const & );

DS& ds_;
TBookmark bm_;
};

and use it in this manner:

void RefreshDataSet( TIBDataSet& DataSet )
{
BookmarkManagerType<TIBDataSet> BookmarkManager( DataSet );
DataSet.Close();
DataSet.Open();
}

Just call

RefreshDataSet( *IBDataSet );

in order to refresh the dataset IBDataSet without losing the cursor position.

Uhm, maybe we could try to apply again the RAII idiom even for the
open/close sequence. We add the following class:

template<typename DS>
class DataSetRefreshManagerType {
public:
explicit DataSetRefreshManagerType( DS& DataSet )
: ds_( DataSet ) { ds_.Close(); }
~DataSetRefreshManagerType() throw() { ds_.Open(); }
private:
DataSetRefreshManagerType( DataSetRefreshManagerType const & );
DataSetRefreshManagerType& operator=( DataSetRefreshManagerType const & );

DS& ds_;
};

and we transform the RefreshDataSet function in this fashion:

void RefreshDataSet( TIBDataSet& DataSet )
{
BookmarkManagerType<TIBDataSet> BookmarkManager( DataSet );
DataSetRefreshManagerType<TIBDataSet> DataSetRefreshManager( DataSet );
}

Good luck.

Giuliano