Zeoslib Portal

Why I can't read the updated values from FireBird database? With the same in ORACLE is none problems.
Next test read data from "CurData" (which are constantly updated) only first time. All is OK with uncommited "db.Reconnect" but this consump twice more time in cycle

Thanks.

[font=Courier New]procedure TstDBx;
var ok : Boolean; i : Integer; u, s : String; dt : TDateTime;
sql: TZReadOnlyQuery;
db : TZConnection;
begin
db := TZConnection.Create(nil);
db.User := 'ROML'; db.password := 'rkln'; db.hostName := '10.11.1.131'; db.Database := 'RCM'; db.Protocol := 'firebird-1.5';
db.Connect;

sql := TZReadOnlyQuery.Create(nil);
sql.Connection := db;

u := '''01.01.1888 00:00:00''';
for i := 0 to 500 do
begin
s := 'SELECT timIndex, timMark FROM CurData' +
' WHERE timMark > ' + u + ' ORDER BY timMark DESC';
sql.SQL.Clear;
sql.SQL.Add(s);
sql.Open;
ok := not sql.EOF;
if ok then
begin
dt := sql.Fields.FieldByName('timMark').AsDateTime;
u := '''' + DateTimeToStr(dt) + '''';
end;
// Sleep(1000);
sql.Close;
// db.Reconnect; // this statement is needed in FireBird ??
end;
end;[/font]

defining an sql string by means of DateTimeToStr() is tricky.
better is passing datetime as parameter
guido

Thank, "guidoaerts" ! I'll do that.
But what is the reason for this difference between FireBird and ORACLE?

no idea, don't know anything about oracle
(and almost nothing about firebird, to be honest)

leonrom,

I don't understand the problem completely.
Is the problem that the inserts are not seen by the same connection or by a second different connection?
When you see the problem from a second conection I may know the answer : firebird has something like a soft and a hard commit. Commits within the same zeoslib connection are soft commits. At the end of a connection (also at reconnect) there's tha hard commit. If I'm not mistaken second connections only 'see' hard committed data. (but ask this to a FB expert to be sure)

Are you using autocommit in both Oracle and firebird? When yes, this explains the difference between oracle and FB : oracle commits are immediately visible by other connections.

Mark

Thank, Mark!
I have two similar DBs,- in ORACLE and Firebird,- identical tables, fields. The tables "CurData" are filled (once per second) by "first" program,- another soft on another connections to both DBs. The result of filling (changed values) I can observe by IBExpert for FireBird (or by ORADeveloper Studio for ORACLE).

On "second",- another client program (procedure TstDBx) I read (with ReadOnly and without any COMMIT ) changed values. And here is question: why, when ORACLE client do such reading in cycle without any reconnections, the FireBird client reads values only first time? When I to need read next time - I can do that only with uncommited statement "db.Reconnect;"

>Is the problem that the inserts are not seen by the same connection or by a second different connection?
- by different connection

>When you see the problem from a second conection I may know the answer : firebird has something like a soft and a hard commit. Commits within the same zeoslib connection are soft commits. At the end of a connection (also at reconnect) there's tha hard commit.
- first pogram, which do write to DB, is written on C++ and use ODBC operator
SQLEndTran (SQL_HANDLE_DBC, hd, SQL_COMMIT); on each "write"

>If I'm not mistaken second connections only 'see' hard committed data. (but ask this to a FB expert to be sure)
- I CAN see (on second connection) results, that are NOT "hard committed". But I can do that only after reconnection on that "second connection"

>Are you using autocommit in both Oracle and firebird?
- both "first" programs are similar and use similar ODBC statements

>When yes, this explains the difference between oracle and FB : oracle commits are immediately visible by other connections.
- I think, it's must not be in such manner... Because I can see all results, but in case of FireBird I need do reconnection by "second" (clients, ReadOnly) program to see changed data.

Thanks once more. I'd adapted for that difficulties. And only the thought on the doubled expenses of milliseconds on each reading infuriates me...

Leon

Now, I'd found the solution!
With

db.TransactIsolationLevel:= tiReadCommitted ;

in DB's initialization FireBird client works fine without any reconnections (thank Mark for suggestions)!

Sorry, but I didn't find time between 4/5 and 5/5 to reply. Once became clear the reconnect was needed on the second connection I knew it was about the transaction isolation level.

Mark