Firebird selecting from system tables

[TRNelson]

I want to get the current time from the server.  I run the following code generates the error shown in the attachment.  I have the exact same code in another program and it appears to work just fine.

Code: Pascal  [Select][+][-]

1. SQLquery1.Close;
2. SQLquery1.sql.text := 'select current_time from rdb$database';
3. SQLquery1.open;  

I thought perhaps there was an error in setup and changed the code as follows and all works as expected with no errors.

Code: Pascal  [Select][+][-]

1. SQLquery1.Close;
2. SQLquery1.sql.text := 'select * from newtable';  // a small table in my database
3. SQLquery1.open;  

Further testing shows that I can not select any information from the system tables without generating a similar error.  However, in another totally separate application, it works fine.

Any ideas?

[balazsszekely]

Try this:

Code: Pascal  [Select][+][-]

1. SQLquery1.Close;
2. SQLquery1.sql.text := 'select cast('now' as TimeStamp) as now from rdb$database';
3. SQLquery1.open;

To get the time, date part:

Code: Pascal  [Select][+][-]

1. var
2.   T: TTime;
3.   D: Date;
4. begin
5.   T := Frac(FieldByName('now').AsDateTime);
6.   D := Trunc(FieldByName('now').AsDateTime);
7. end;

Alternatively use the FormatDateTime function.

[TRNelson]

Thanks for that GetMem.  However, it gives me the same result.

This is very odd behavior because anytime I try and access the system tables in this application I get the same out of bounds error.  The same code works properly in a small application that I was testing.  I took the code that worked there and put it in a new unit in my bigger application and it fails with this error.  In fact if I take some of the code (those 3 lines) and put it any unit in the big app, it fails.

[balazsszekely]

@TRNelson
To identify the problem, use a separate connection/transaction. Don't reuse any of the component, in fact you should create everything dynamically. For example this works, in the big application?

Code: Pascal  [Select][+][-]

1. var
2.   IBConnect: TIBConnection;
3.   SQLTransaction: TSQLTransaction;
4.  
5. function ConnectToDb(const AHostName, ADatabaseName, AUserName, APassword: string): Boolean;
6. begin
7.   Result := True;
8.   with IBConnect do
9.   begin
10.     HostName := AHostName;
11.     DatabaseName := ADatabaseName;
12.     UserName := AUserName;
13.     Password := APassword;
14.     CharSet := 'UTF8'; //or something else
15.     Transaction := SQLTransaction;
16.   end;
17.   try
18.     IBConnect.Connected := True;
19.   except
20.     on E: Exception do
21.     begin
22.       ShowMessage('Cannot connect to database: ' + E.Message);
23.       Result := False;
24.     end;
25.   end;
26. end;
27.  
28. function GetDateTime: TDateTime;
29. var
30.   SQLQuery: TSQLQuery;
31. begin
32.   Result := 0;
33.   SQLQuery := TSQLQuery.Create(nil);
34.   try
35.     SQLQuery.SQL.Text := 'select cast(''now'' as TimeStamp) as now from rdb$database';
36.     SQLQuery.DataBase := IBConnect;
37.     SQLQuery.SQLTransaction := SQLTransaction;
38.     SQLQuery.Open;
39.     if SQLQuery.RecordCount > 0 then
40.       Result := SQLQuery.FieldByName('now').AsDateTime;
41.     SQLQuery.Close;
42.   finally
43.     SQLQuery.Free;
44.   end;
45. end;
46.  
47. procedure TForm1.Button1Click(Sender: TObject);
48. var
49.   Dt: TDateTime;
50. begin
51.   IBConnect := TIBConnection.Create(nil);
52.   SQLTransaction := TSQLTransaction.Create(nil);
53.   try
54.     if ConnectToDb('localhost', 'c:\Database\Test.fdb', 'sysdba', 'masterkey') then //change values
55.     begin
56.       Dt := GetDateTime;;
57.       if Dt > 0 then
58.         ShowMessage('Server datetime:  ' + FormatDateTime('YYYY.MM.DD hh:mm:ss', Dt));
59.     end;
60.   finally
61.     IBConnect.Free;
62.     SQLTransaction.Free;
63.   end;
64. end;
65.  

Please change the credentials, db name with the appropriate values.

[TRNelson]

@GetMem

That worked brilliantly!  I did make one minor change in Line 37.  I was reusing components and I guess that is where I tripped up.  Not sure why but it doesn't matter.  This is much more elegant.

Thank you so much for your time and effort.  I really appreciate it.  Some days it is hard to get the old neurons firing like they used to.  This stuff is quite new to me but I love it.

 

[balazsszekely]

did make one minor change in Line 37

Good catch! One more thing...try to reuse your current connection/transaction and only create the sql dynamically, I believe one of your sql causing the error.

Thank you so much for your time and effort.  I really appreciate it.

You're welcome! I'm glad is working.

[TRNelson]

@GetMem

One other thing, I was unaware that you could return the result of a function in this manner.

Code: Pascal  [Select][+][-]

1. function GetDateTime: TDateTime;
2. ......
3.   Result := 0;
4. ......
5.   Result := SQLQuery.FieldByName('now').AsDateTime;
6. end;

Never encountered that before.  Nice.