how to refresh a query

[Philippe Gingras]

Greetings,

I am trying to do a query refresh using the query close and open methods and
I don't get the result I am expecting. Here is what I have done exactly...

I started a new application in Delphi and dropped on the main form the
following components...
- 1 IBDatabase, 1 IBTransaction, 1 IBQuery, 1 DataSource, 1 DBGrid and 1
Button

I changed the properties of these components as follows...
- IBDatabase1.DatabaseName = 'C:\Program
Files\Borland\Interbase\Examples\Database\Employee.gdb'
- IBDatabase1.DefaultTransaction = IBTransaction1
- IBTransaction1.DefaultDatabase = IBDatabase1
- IBQuery1.Database = IBDatabase1
- IBQuery1.Transaction = IBTransaction1
- IBQuery1.SQL = 'select CUST_NO, CONTACT_FIRST, CONTACT_LAST, CITY from
CUSTOMER'
- IBDataSource1.DataSet = IBQuery1
- DBGrid1.DataSource = DataSource1

I have responded to the Form1.OnCreate, Form1.OnDestroy and Button1.OnClick
as follows...

procedure TForm1.FormCreate(Sender: TObject);
begin
ibdatabase1.open;
ibquery1.prepare;
ibquery1.open;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
ibquery1.close;
ibquery1.unprepare;
ibdatabase1.close;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
ibquery.close;
ibquery.open;
end;

I now run the application and enter the proper user-id and password to
connect to the database (sysdba, masterkey). At this point I can see that
the city for CUST_NO 1001 is 'San Diego' in the grid. Then I run the
following query through Interactive SQL...
- update customer set city = "New York" where cust_no = 1001;
and I commit the change.

At this point, if I do a select on the customer table through Interactive
SQL, I can see that 'San Diego' was changed to 'New York' for customer
number 1001. However, when I click on Button1 in my application, the city is
still 'San Diego' for customer 1001 in the grid although I think it should
now show 'New York'.

Why is this way of refreshing the query not working? What am I doing wrong?

Many thanks in advance for clarifying another newbie question.

--
Philippe

[Robert Schieck (TeamB)]

Philippe Gingras wrote:

You need to commit your current transaction (even though you have not
modified anything) before you re-open the query to see the changes....

or

Change the transaction isloation level to readcommitted.

hth

Rob

[Philippe Gingras]

Hi Rob,

Thank you for answering my question.

When I commit a transaction, it seems to close the query automatically. I
assume that if I have many nested master/detail queries associated to that
same transaction they will all be closed at the same time. I will then have
to reopen them each of them separately to complete the refresh. Is this
common practice with Interbase? Also, how do I change the isolation level to
readcommitted on a transaction.

Many thanks again for your time.
--
Philippe

[Robert Schieck (TeamB)]

Philippe Gingras wrote:

> When I commit a transaction, it seems to close the query
> automatically. I assume that if I have many nested master/detail
> queries associated to that same transaction they will all be closed
> at the same time. I will then have to reopen them each of them
> separately to complete the refresh. Is this common practice with
> Interbase? Also, how do I change the isolation level to readcommitted
> on a transaction.
>
> Many thanks again for your time.

I like to use two connections or transactions depending upon the
components I am using. One for reading and one for writing.

If you are using InterBase 7.x or higher, a read only transaction
isolation level is a really good thing to use for the read connection.

hth

Rob

[G. Allen Casteran]

In article <4149db93$1...@newsgroups.borland.com>, phil...@felitec.com
says...

This is common practice with SQL servers in general.

If you use separate transactions for each of your processes then you
will have better segregation and can commit/start one without affecting
the others.

One other point that may not have been made clear. When you made the
change in ISQL and reopened the query again in ISQL, you are seeing a
copy of the data that is specific to that connection until THAT
transaction is committed. Because the update transaction can be rolled
back other stations/connections do not usually see the changed data
until the commit, which is distinctly different from the execution of
the update command.

Allen.

[Philippe Gingras]

"Robert Schieck (TeamB)" <rsch...@mers.com> wrote in message
news:4149...@newsgroups.borland.com...

>
> I like to use two connections or transactions depending upon the
> components I am using. One for reading and one for writing.

Sounds like a good plan to me, I am going to try this.

>
> If you are using InterBase 7.x or higher, a read only transaction
> isolation level is a really good thing to use for the read connection.

I am actually experimenting with Interbase 6.5 and Firebird 1.5 but I will
certainly keep that in mind for the future.

I finally found how to change the isolation level of a transaction but I was
surprised to see that the transaction properties set through the transaction
editor (double-click) are hidden from the object inspector. You really have
to know how this works otherwise one can search for long time before finding
it out.

Many thanks again,
--
Philippe

[Philippe Gingras]

"G. Allen Casteran" <al...@nisinc.com> wrote in message
news:MPG.1bb3d4142...@newsgroups.borland.com...

>
> This is common practice with SQL servers in general.

I guess you're right, it has just been a long time since I played with an
SQL server and I am not used to strictly select queries that have to go
through a transaction.

>
> If you use separate transactions for each of your processes then you
> will have better segregation and can commit/start one without affecting
> the others.

That makes sense and I guess what confused me is that I am pretty sure that
I've seen somewhere in the documentation that normally you would not need
more than one transaction in an application. Maybe I just misinterpreted the
context of this statement??

>
> One other point that may not have been made clear. When you made the
> change in ISQL and reopened the query again in ISQL, you are seeing a
> copy of the data that is specific to that connection until THAT
> transaction is committed. Because the update transaction can be rolled
> back other stations/connections do not usually see the changed data
> until the commit, which is distinctly different from the execution of
> the update command.

Yes, I understand but my concern was to be unable to refresh a query
properly even after a commit was done.

Many thanks for your help Allen.
--
Philippe

[Van den Wouwer Danny]

Hi,

Rob, you sayed that you are normally use 2 transactions, one for reading and
one for writing, but how do you solve this in IBX, because there aren't
components that support this technique native.
Can you explain me how you will do that, im very interested in doing this

Thank you in advance,
Van den Wouwer Danny

"Robert Schieck (TeamB)" <rsch...@mers.com> schreef in bericht
news:4149...@newsgroups.borland.com...

[Bill Todd]

Note that the benefits you get from a read only transaction in IB 7.1
are not present in 6.5 or, AFAIK Firebird.

--
Bill (TeamB)
TeamB cannot answer questions received via email