Query Firebird without drop in components
lazarus / 16-Databases

----------------------------------------------------------------------
super.noob.user · 2016-01-14 06:54

Hi,
I've looked everywhere for an example, but cant find one.
I need a function that can do a simple query for me - I pass it three
integers, and it must return a name for me. The query looks like this:

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
countries.countryname || ' - ' || cities.citynameFROM citiesINNER JOIN
countriesON countries.countryindex = cities.countryindexWHERE
cities.countryindex = : country_IdxAND cities.regionindex = :
region_IdxAND cities.cityindex = : city_Idx * This is exactly how I
use it in IB Expert

I have a commandline app that helps me manage files coming in from
different sites, and right now its only writing the index numbers to a
log file - I'd like to modify this to simply add a site name as well.

Can anybody please show me how I would go about doing this without
using drop in/data aware components from the IDE?

----------------------------------------------------------------------
balazsszekely · 2016-01-14 07:37

Try something like this, you may need more fine tuning:
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 program
FBTest; {$mode objfpc}{$H+} uses  {$IFDEF UNIX}{$IFDEF UseCThreads} 
cthreads,  {$ENDIF}{$ENDIF}  Classes, SysUtils, sqldb, db,
IBConnection  { you can add units after this }; 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  
  Writeln('Cannot connect to database: ' + E.Message);      Result :=
False;    end;  end;end; function GetName(const ACountry_Idx,
ARegion_Idx, ACity_Idx: Integer): String;var  SQLQuery:
TSQLQuery;begin  Result := '';  SQLQuery := TSQLQuery.Create(nil); 
try    SQLQuery.SQL.Text := 'SELECT countries.countryname || '' - ''
|| cities.cityname as FullName ' +                         'FROM
cities ' +                         'INNER JOIN countries ' +         
              'ON countries.countryindex = cities.countryindex ' +   
                    'WHERE cities.countryindex = :country_Idx ' +    
                   'AND cities.regionindex = :region_Idx ' +         
              'AND cities.cityindex = :city_Idx';    SQLQuery.DataBase
:= IBConnect;    SQLQuery.SQLTransaction := SQLTransaction;   
SQLQuery.Params.BeginUpdate;   
SQLQuery.ParamByName('country_Idx').AsInteger := ACountry_Idx;   
SQLQuery.ParamByName('region_Idx').AsInteger := ARegion_Idx;   
SQLQuery.ParamByName('city_Idx').AsInteger := ACity_Idx;   
SQLQuery.Params.EndUpdate;    SQLQuery.Open;    if
SQLQuery.RecordCount > 0 then      Result :=
SQLQuery.FieldByName('FullName').AsString;    SQLQuery.Close;  
finally    SQLQuery.Free;  end;  //Readln;end; var  Str: String;begin 
IBConnect := TIBConnection.Create(nil);  SQLTransaction :=
TSQLTransaction.Create(nil);  try    if ConnectToDb('localhost',
'c:\test.fdb', 'sysdba', 'masterkey') then //change values    begin  
  Str := GetName(14, 3, 122); //change values      if Str <> '' then 
     Writeln('Name: ', Str)      else        Writeln('Cannot get the
name');    end;  finally    IBConnect.Free;    SQLTransaction.Free; 
end;  Readln;end. 
PS: Remove Readln from function GetName.

----------------------------------------------------------------------
super.noob.user · 2016-01-14 08:20

I'll give it a try, but I'm confident it will work - thank you!