Firebird selecting from system tables
lazarus / 16-Databases

----------------------------------------------------------------------
TRNelson · 2016-02-21 06:05

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLquery1.Close;SQLquery1.sql.text := 'select current_time from
rdb$database';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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLquery1.Close;SQLquery1.sql.text := 'select * from newtable';  // a
small table in my databaseSQLquery1.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 · 2016-02-21 08:19

Try this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLquery1.Close;SQLquery1.sql.text := 'select cast('now' as TimeStamp)
as now from rdb$database';SQLquery1.open;
To get the time, date part:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  T:
TTime;  D: Date;begin  T := Frac(FieldByName('now').AsDateTime);   D
:= Trunc(FieldByName('now').AsDateTime);end;
Alternatively use the FormatDateTime function.

----------------------------------------------------------------------
TRNelson · 2016-02-21 13:55

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 · 2016-02-21 15:37

@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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var 
IBConnect: TIBConnection;  SQLTransaction: TSQLTransaction; function
ConnectToDb(const AHostName, ADatabaseName, AUserName, APassword:
string): Boolean;begin  Result := True;  with IBConnect do  begin   
HostName := AHostName;    DatabaseName := ADatabaseName;    UserName
:= AUserName;    Password := APassword;    CharSet := 'UTF8'; //or
something else    Transaction := SQLTransaction;  end;  try   
IBConnect.Connected := True;  except    on E: Exception do    begin  
  ShowMessage('Cannot connect to database: ' + E.Message);      Result
:= False;    end;  end;end; function GetDateTime: TDateTime;var 
SQLQuery: TSQLQuery;begin  Result := 0;  SQLQuery :=
TSQLQuery.Create(nil);  try    SQLQuery.SQL.Text := 'select
cast(''now'' as TimeStamp) as now from rdb$database';   
SQLQuery.DataBase := IBConnect;    SQLQuery.SQLTransaction :=
SQLTransaction;    SQLQuery.Open;    if SQLQuery.RecordCount > 0 then 
    Result := SQLQuery.FieldByName('now').AsDateTime;   
SQLQuery.Close;  finally    SQLQuery.Free;  end;end; procedure
TForm1.Button1Click(Sender: TObject);var  Dt: TDateTime;begin 
IBConnect := TIBConnection.Create(nil);  SQLTransaction :=
TSQLTransaction.Create(nil);  try    if ConnectToDb('localhost',
'c:\Database\Test.fdb', 'sysdba', 'masterkey') then //change values  
begin      Dt := GetDateTime;;      if Dt > 0 then       
ShowMessage('Server datetime:  ' + FormatDateTime('YYYY.MM.DD
hh:mm:ss', Dt));    end;  finally    IBConnect.Free;   
SQLTransaction.Free;  end;end; 
Please change the credentials, db name with the appropriate values.

----------------------------------------------------------------------
TRNelson · 2016-02-21 16:06

@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 · 2016-02-21 16:19

Quote did make one minor change in Line 37Good 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.

QuoteThank you so much for your time and effort.  I really appreciate
it.You're welcome! I'm glad is working.

----------------------------------------------------------------------
TRNelson · 2016-02-21 17:16

@GetMem

One other thing, I was unaware that you could return the result of a
function in this manner.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
GetDateTime: TDateTime;......  Result := 0;......  Result :=
SQLQuery.FieldByName('now').AsDateTime;end;
Never encountered that before.  Nice.