How to connect database in dos program
lazarus / 16-Databases

----------------------------------------------------------------------
Gebony · 2019-08-04 07:41

I need to connect a database in my program without GUI .

----------------------------------------------------------------------
wp · 2019-08-04 09:07

Which kind of database?

----------------------------------------------------------------------
Gebony · 2019-08-04 09:11

sqlserver

----------------------------------------------------------------------
wp · 2019-08-04 10:23

Sorry, never used sqlserver. There's a wiki page for it:
https://wiki.freepascal.org/mssqlconn - no idea whether it is still
valid.

As for connecting to databases without GUI (you mean: in a console
program?) you coud study the code in (fpc-source)/packages/fcl-
db/examples. Or if you need more words, try
https://wiki.freepascal.org/SqlDBHowto or
https://wiki.lazarus.freepascal.org/Lazarus_Database_Overview and
links therein.

----------------------------------------------------------------------
bee · 2019-08-04 14:57

You may want to read my article about connecting a console program to
a database. You could read it here: https://paklebah.github.io/fpc-
sqldb-dan-sqlite.html

It's written in Indonesian, but google translate would help you
translating it into any languages you prefer. HTH. 😊

----------------------------------------------------------------------
valdir.marcos · 2019-08-06 04:42

Quote from: bee on August 04, 2019, 04:57:02 pmYou may want to read my
article about connecting a console program to a database. You could
read it here: https://paklebah.github.io/fpc-sqldb-dan-sqlite.html
It's written in Indonesian, but google translate would help you
translating it into any languages you prefer. HTH.
Quote from: willbprog9933 on August 06, 2019, 03:11:25 amThank you for
this how-to, Bee! It is a very well-written article!

----------------------------------------------------------------------
valdir.marcos · 2019-08-06 04:48

Quote from: valdir.marcos on August 06, 2019, 06:42:43 amQuote from:
bee on August 04, 2019, 04:57:02 pmYou may want to read my article
about connecting a console program to a database. You could read it
here: https://paklebah.github.io/fpc-sqldb-dan-sqlite.html
It's written in Indonesian, but google translate would help you
translating it into any languages you prefer. HTH.
Quote from: willbprog9933 on August 06, 2019, 03:11:25 amThank you for
this how-to, Bee! It is a very well-written article! Sorry for my
interference.

Cannot post reply to forum
https://forum.lazarus.freepascal.org/index.php/topic,46303.0.html

----------------------------------------------------------------------
willbprog9933 · 2019-08-06 06:02

Sorry for all of the technical forums drama.  Bee, I just wanted to
let you know I appreciated the article you wrote.  Thank you!

----------------------------------------------------------------------
af0815 · 2019-08-06 06:24

Such an artikel should be translated and posted in the fpc wiki. I am
not able to read it direct, but the samples alone are very
informative.

Very well done Mr Bee.

About connection to mssql. The article in the wiki is ok with one
exception, avoid the use of the very,very old ntwdblib.dll. You must
have a look for the correct bitness. The mssqlserver must use tcp over
port 1433. And it is very similar to the democode of MrBee.
SqlDb and Zeos are ok. Both are running on windows, linux and RasPi.

---------- Edit ----------
If you have problems with the posting here: Copy the article in a
plain normal editor and check if not a special char is in the text,
remove the smilies and other artwork. Look for quotes and doublequotes
and if possible remove it. After thi8s copy it back to the (cleared)
postwindows.
The reason behind is, all text is normally stored in a database and to
make this secure, a lot of char combinations and words are forbidden
or treated in a special manner. This can sometimes make it impossible
to post without a clear reason. But the security have to block this.

----------------------------------------------------------------------
bee · 2019-08-06 15:29

Wow… glad to see my article is actually helpful to someone here. I
wrote it to explain database programming to Indonesian Pascal
community on Facebook, hence it's written in Indonesian (Bahasa
Indonesia). Fortunately we now have google translate so my article can
be read in any languages. I know the translation won't be perfect, but
you should get the idea. You're welcome, guys. 😊

----------------------------------------------------------------------
Tz · 2019-08-09 16:30

Hi Gebony,  for general connection you can use TSqlConnector

this is minimal example, I can get

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  unit
zSql;{    usage :     sql := TzSql.Create;     ConnectorType :=
Firebird | MSSQLServer | MySQL 5.7 | Oracle | PostgreSQL | SQLite3 |
Sybase     uri := 'MSSQLServer://username:password@localhost//database
?AutoCommit=True';    uri :=
'PostgreSQL://username:password@localhost//database';    uri :=
'SQLite3://username:password@localhost//database.db';    uri :=
'MySQL://username:password@localhost//database';     // oracle and
firebird not yet tested     sql.Query('select version()').Run;    
account_id := 1; // Int32    sql.Query('exec dbo.get_account')     
 .Param('account_id', account_id)       .Exec;     sql.Free;}{$mode
objfpc}{$h+}interface uses  Classes, SysUtils, StrUtils, URIParser,
SqlDb, Db,      Sqlite3conn, MySql57conn, IBConnection,     
OracleConnection, MSSqlConn, PqConnection; type   TzSql = class
(TObject)  private    FSqlConnector   :TSqlConnector;   
FSqlTransaction :TSqlTransaction;    FSqlQuery       :TSqlQuery;   
FMessage        :String;    function    AsTable :String;  public   
constructor Create;    destructor  Destroy; override;    function  
 Connect(const AUri :String) :Boolean;    function    Query(const
AQuery :String) :TzSql;    function    Param(const AName  :String;
const AValue :Boolean  ) :TzSql; overload;    function    Param(const
AName  :String; const AValue :Int16    ) :TzSql; overload;    function
   Param(const AName  :String; const AValue :Int32    ) :TzSql;
overload;    function    Param(const AName  :String; const AValue
:Int64    ) :TzSql; overload;    function    Param(const AName
 :String; const AValue :Double   ) :TzSql; overload;    function  
 Param(const AName  :String; const AValue :Currency ) :TzSql;
overload;    function    Param(const AName  :String; const AValue
:TDateTime) :TzSql; overload;    function    Param(const AName
 :String; const AValue :TGuid    ) :TzSql; overload;    function  
 Param(const AName  :String; const AValue :String   ) :TzSql;
overload;    function    Param(const AName  :String; const AValue
:TBytes   ) :TzSql; overload;    function    Exec :Int64;    function
  Run  :String;    property    Message :String read FMessage; 
end; implementation constructor TzSql.Create;  begin    FSqlConnector
 := TSqlConnector.Create(nil);    FSqlTransaction :=
TSqlTransaction.Create(nil);    FSqlQuery       :=
TSqlQuery.Create(nil);  end; destructor TzSql.Destroy;  begin    if
Assigned(FSqlQuery)       then FreeAndNil(FSqlQuery);    if
Assigned(FSqlTransaction) then FreeAndNil(FSqlTransaction);    if
Assigned(FSqlConnector)   then FreeAndNil(FSqlConnector); 
end; function    TzSql.Connect(const AUri :String) :Boolean;  var uri
:TUri;  begin    try      uri := ParseUri(AUri, True);       if
uri.Protocol = 'MySQL' then uri.Protocol += ' 5.7';  // uri cannot
accept ' '       FSqlConnector.ConnectorType     := uri.Protocol;    
FSqlConnector.Hostname          := uri.Host;     
FSqlConnector.DatabaseName      := uri.Document;     
FSqlConnector.Username          := uri.Username;     
FSqlConnector.Password          := uri.Password;     
FSqlConnector.Params.Text       := uri.Params;     
FSqlConnector.KeepConnection    := True;       if
FSqlConnector.ConnectorType = 'MSSQLServer' then        begin        
FSqlConnector.Params.Text   := 'AutoCommit=True'; // somehow
SqlTransaction error when free          FSqlTransaction.Options     :=
[stoUseImplicit];  // use implicit        end;      
FSqlTransaction.Database        := FSqlConnector;      
FSqlQuery.PacketRecords         := -1;      FSqlQuery.UniDirectional 
     := True;      FSqlQuery.ReadOnly              := True;     
FSqlQuery.Transaction           := FSqlTransaction;      
FSqlConnector.Open;      FMessage := '';      result   :=
FSqlConnector.Connected    except      on E: Exception do       
begin          FMessage := E.Message;          result   := false;    
  end;    end;  end; function TzSql.Query(const AQuery :String)
:TzSql;  begin    FSqlQuery.Params.Clear;    FSqlQuery.SQL.Text :=
AQuery;    result := self;  end; function TzSql.Param(const AName
:String; const AValue :Boolean) :TzSql; overload;  begin   
FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsBoolean := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:Int16) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsSmallInt := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:Int32) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsInteger := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:Int64) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsLargeInt := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:Double) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsFloat := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:Currency) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsCurrency := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:TDateTime) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsDateTime := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:TGuid) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsString := AValue.ToString;   
result := self;  end; function TzSql.Param(const AName :String; const
AValue :String) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsString := AValue;    result :=
self;  end; function TzSql.Param(const AName :String; const AValue
:TBytes) :TzSql; overload;  begin    FSqlQuery.SQL.Text :=
FSqlQuery.SQL.Text + ' :' + AName;   
FSqlQuery.Params.ParamByName(AName).AsBytes := AValue;    result :=
self;  end; function TzSql.Exec :Int64;  begin    try      if not
FSqlConnector.Connected then FSqlConnector.Open;      if not
FSqlTransaction.Active  then FSqlTransaction.StartTransaction;     
FSqlQuery.Prepare;      FSqlQuery.ExecSql;      if
FSqlTransaction.Active then FSqlTransaction.Commit;      FMessage :=
'';      result   := 0; // := FSqlQuery.RowsAffected;   // seems not
working always -1    except      on E: Exception do        begin     
   if FSqlTransaction.Active then FSqlTransaction.Rollback;         
FMessage := E.Message;          result   := -1;        end;    end; 
end; function TzSql.Run :String;  begin    result := '';    try     
FSqlQuery.Open;      // begin tran      if not FSqlTransaction.Active
then FSqlTransaction.StartTransaction;      if not FSqlQuery.Eof then
result := AsTable;      // commit tran      if FSqlTransaction.Active
then FSqlTransaction.Commit;      FSqlQuery.Close;      FMessage :=
'';    except      on E :Exception do        begin          //
rollback tran          if FSqlTransaction.Active then
FSqlTransaction.Rollback;          FSqlQuery.Close;          FMessage
:= E.Message;        end;    end;  end; function TzSql.AsTable
:String;    var ncol, nrow, col :Int64;    var header, lines, first
:String;  begin    result := '';    header := '';    lines  := '';   
first  := '';    ncol   := FSqlQuery.FieldCount;    nrow   := 1;    
// note: problemo with sql server display width     for col:=0 to
ncol-1 do      with FSqlQuery.Fields[col] do        begin          if
Alignment = TAlignment.taLeftJustify then            begin           
 header += PadRight(DisplayLabel, DisplayWidth) + ' ';             
first  += PadRight(AsString,     DisplayWidth) + ' ';            end 
       else            begin              header += PadLeft
(DisplayLabel, DisplayWidth) + ' ';              first  += PadLeft
(AsString,     DisplayWidth) + ' ';            end;          lines  +=
StringOfChar('-', DisplayWidth) + ' ';        end;     result :=
header + LineEnding            + lines  + LineEnding            +
first  + LineEnding;     FSqlQuery.Next;    while not FSqlQuery.Eof
do      begin        Inc(nrow);         for col:=0 to ncol-1 do      
  with FSqlQuery.Fields[col] do            begin              if
Alignment = TAlignment.taLeftJustify then                result +=
PadRight(AsString, DisplayWidth) + ' '              else             
 result += PadLeft (AsString, DisplayWidth) + ' ';            end;   
   result += LineEnding;         FSqlQuery.Next;      end;    result
+= LineEnding + 'rows: ' + IntToStr(nrow) + LineEnding;  end; end.  
example program as interactive sql
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
isql; // interactive sql{$mode objfpc}{$h+}uses zSql, URIParser; var
sql :TzSql;    uri, qry :String; begin   uri := ParamStr(1);  sql :=
TzSql.Create;   if sql.Connect(uri) then    begin      Write('sql>
');      ReadLn(qry);       while Pos('quit', qry) = 0 do       
begin          // if first statement is select          if    
(Pos('select', qry) > 0)             and (Pos('select', qry) < 6) then
 // not good, but enough for example           
WriteLn(sql.Query(qry).Run)          else           
WriteLN(sql.Query(qry).Exec);{          // example param         
sql.Query('exec dbo.get_account')             .Param('account_id',
Int32(1))             .Run;}          if sql.Message <> '' then      
    WriteLn('msg: ', sql.Message + LineEnding);           Write('sql>
');          ReadLn(qry);        end    end  else    WriteLn('error:
', sql.Message + LineEnding);   sql.Free; end.   

not yet tested on firebird and oracle

Please Enjoy!