Recent

Author Topic: How to connect database in dos program  (Read 663 times)

Gebony

  • New Member
  • *
  • Posts: 16
How to connect database in dos program
« on: August 04, 2019, 09:41:51 am »
I need to connect a database in my program without GUI .

wp

  • Hero Member
  • *****
  • Posts: 6153
Re: How to connect database in dos program
« Reply #1 on: August 04, 2019, 11:07:38 am »
Which kind of database?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Gebony

  • New Member
  • *
  • Posts: 16
Re: How to connect database in dos program
« Reply #2 on: August 04, 2019, 11:11:11 am »
sqlserver

wp

  • Hero Member
  • *****
  • Posts: 6153
Re: How to connect database in dos program
« Reply #3 on: August 04, 2019, 12:23:13 pm »
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

bee

  • Sr. Member
  • ****
  • Posts: 359
Re: How to connect database in dos program
« Reply #4 on: August 04, 2019, 04:57:02 pm »
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. 😊
-Bee-

A long time pascal lover.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 801
Re: How to connect database in dos program
« Reply #5 on: August 06, 2019, 06:42:43 am »
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.

Thank you for this how-to, Bee! It is a very well-written article! :D

valdir.marcos

  • Hero Member
  • *****
  • Posts: 801
Re: How to connect database in dos program
« Reply #6 on: August 06, 2019, 06:48:53 am »
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.

Thank you for this how-to, Bee! It is a very well-written article! :D
Sorry for my interference.

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

willbprog9933

  • New Member
  • *
  • Posts: 28
    • BrainOut!
Re: How to connect database in dos program
« Reply #7 on: August 06, 2019, 08:02:43 am »
Sorry for all of the technical forums drama.  Bee, I just wanted to let you know I appreciated the article you wrote.  Thank you! :D
Blessed and forgiven! :-D

af0815

  • Sr. Member
  • ****
  • Posts: 345
Re: How to connect database in dos program
« Reply #8 on: August 06, 2019, 08:24:17 am »
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.
 
« Last Edit: August 06, 2019, 09:02:36 am by af0815 »
regards
Andreas

bee

  • Sr. Member
  • ****
  • Posts: 359
Re: How to connect database in dos program
« Reply #9 on: August 06, 2019, 05:29:58 pm »
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. 😊
-Bee-

A long time pascal lover.

Tz

  • New Member
  • *
  • Posts: 40
  • Tz with FPC Pen Cil
Re: How to connect database in dos program
« Reply #10 on: August 09, 2019, 06:30:39 pm »
Hi Gebony,  for general connection you can use TSqlConnector

this is minimal example, I can get

Code: Pascal  [Select]
  1.  
  2. unit zSql;
  3. {
  4.     usage :
  5.  
  6.     sql := TzSql.Create;
  7.  
  8.     ConnectorType := Firebird | MSSQLServer | MySQL 5.7 | Oracle | PostgreSQL | SQLite3 | Sybase
  9.  
  10.     uri := 'MSSQLServer://username:password@localhost//database?AutoCommit=True';
  11.     uri := 'PostgreSQL://username:password@localhost//database';
  12.     uri := 'SQLite3://username:password@localhost//database.db';
  13.     uri := 'MySQL://username:password@localhost//database';
  14.  
  15.     // oracle and firebird not yet tested
  16.  
  17.     sql.Query('select version()').Run;
  18.  
  19.     account_id := 1; // Int32
  20.     sql.Query('exec dbo.get_account')
  21.        .Param('account_id', account_id)
  22.        .Exec;
  23.  
  24.     sql.Free;
  25. }
  26. {$mode objfpc}
  27. {$h+}
  28. interface
  29.  
  30. uses  Classes, SysUtils, StrUtils, URIParser, SqlDb, Db,
  31.       Sqlite3conn, MySql57conn, IBConnection,
  32.       OracleConnection, MSSqlConn, PqConnection;
  33.  
  34. type
  35.  
  36.   TzSql = class (TObject)
  37.   private
  38.     FSqlConnector   :TSqlConnector;
  39.     FSqlTransaction :TSqlTransaction;
  40.     FSqlQuery       :TSqlQuery;
  41.     FMessage        :String;
  42.     function    AsTable :String;
  43.   public
  44.     constructor Create;
  45.     destructor  Destroy; override;
  46.     function    Connect(const AUri :String) :Boolean;
  47.     function    Query(const AQuery :String) :TzSql;
  48.     function    Param(const AName  :String; const AValue :Boolean  ) :TzSql; overload;
  49.     function    Param(const AName  :String; const AValue :Int16    ) :TzSql; overload;
  50.     function    Param(const AName  :String; const AValue :Int32    ) :TzSql; overload;
  51.     function    Param(const AName  :String; const AValue :Int64    ) :TzSql; overload;
  52.     function    Param(const AName  :String; const AValue :Double   ) :TzSql; overload;
  53.     function    Param(const AName  :String; const AValue :Currency ) :TzSql; overload;
  54.     function    Param(const AName  :String; const AValue :TDateTime) :TzSql; overload;
  55.     function    Param(const AName  :String; const AValue :TGuid    ) :TzSql; overload;
  56.     function    Param(const AName  :String; const AValue :String   ) :TzSql; overload;
  57.     function    Param(const AName  :String; const AValue :TBytes   ) :TzSql; overload;
  58.     function    Exec :Int64;
  59.     function    Run  :String;
  60.     property    Message :String read FMessage;
  61.   end;
  62.  
  63. implementation
  64.  
  65. constructor TzSql.Create;
  66.   begin
  67.     FSqlConnector   := TSqlConnector.Create(nil);
  68.     FSqlTransaction := TSqlTransaction.Create(nil);
  69.     FSqlQuery       := TSqlQuery.Create(nil);
  70.   end;
  71.  
  72. destructor TzSql.Destroy;
  73.   begin
  74.     if Assigned(FSqlQuery)       then FreeAndNil(FSqlQuery);
  75.     if Assigned(FSqlTransaction) then FreeAndNil(FSqlTransaction);
  76.     if Assigned(FSqlConnector)   then FreeAndNil(FSqlConnector);
  77.   end;
  78.  
  79. function    TzSql.Connect(const AUri :String) :Boolean;
  80.   var uri :TUri;
  81.   begin
  82.     try
  83.       uri := ParseUri(AUri, True);
  84.  
  85.       if uri.Protocol = 'MySQL' then uri.Protocol += ' 5.7';  // uri cannot accept ' '
  86.  
  87.       FSqlConnector.ConnectorType     := uri.Protocol;
  88.       FSqlConnector.Hostname          := uri.Host;
  89.       FSqlConnector.DatabaseName      := uri.Document;
  90.       FSqlConnector.Username          := uri.Username;
  91.       FSqlConnector.Password          := uri.Password;
  92.       FSqlConnector.Params.Text       := uri.Params;
  93.       FSqlConnector.KeepConnection    := True;
  94.  
  95.       if FSqlConnector.ConnectorType = 'MSSQLServer' then
  96.         begin
  97.           FSqlConnector.Params.Text   := 'AutoCommit=True'; // somehow SqlTransaction error when free
  98.           FSqlTransaction.Options     := [stoUseImplicit];  // use implicit
  99.         end;
  100.  
  101.       FSqlTransaction.Database        := FSqlConnector;
  102.  
  103.       FSqlQuery.PacketRecords         := -1;
  104.       FSqlQuery.UniDirectional        := True;
  105.       FSqlQuery.ReadOnly              := True;
  106.       FSqlQuery.Transaction           := FSqlTransaction;
  107.  
  108.       FSqlConnector.Open;
  109.       FMessage := '';
  110.       result   := FSqlConnector.Connected
  111.     except
  112.       on E: Exception do
  113.         begin
  114.           FMessage := E.Message;
  115.           result   := false;
  116.         end;
  117.     end;
  118.   end;
  119.  
  120. function TzSql.Query(const AQuery :String) :TzSql;
  121.   begin
  122.     FSqlQuery.Params.Clear;
  123.     FSqlQuery.SQL.Text := AQuery;
  124.     result := self;
  125.   end;
  126.  
  127. function TzSql.Param(const AName :String; const AValue :Boolean) :TzSql; overload;
  128.   begin
  129.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  130.     FSqlQuery.Params.ParamByName(AName).AsBoolean := AValue;
  131.     result := self;
  132.   end;
  133.  
  134. function TzSql.Param(const AName :String; const AValue :Int16) :TzSql; overload;
  135.   begin
  136.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  137.     FSqlQuery.Params.ParamByName(AName).AsSmallInt := AValue;
  138.     result := self;
  139.   end;
  140.  
  141. function TzSql.Param(const AName :String; const AValue :Int32) :TzSql; overload;
  142.   begin
  143.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  144.     FSqlQuery.Params.ParamByName(AName).AsInteger := AValue;
  145.     result := self;
  146.   end;
  147.  
  148. function TzSql.Param(const AName :String; const AValue :Int64) :TzSql; overload;
  149.   begin
  150.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  151.     FSqlQuery.Params.ParamByName(AName).AsLargeInt := AValue;
  152.     result := self;
  153.   end;
  154.  
  155. function TzSql.Param(const AName :String; const AValue :Double) :TzSql; overload;
  156.   begin
  157.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  158.     FSqlQuery.Params.ParamByName(AName).AsFloat := AValue;
  159.     result := self;
  160.   end;
  161.  
  162. function TzSql.Param(const AName :String; const AValue :Currency) :TzSql; overload;
  163.   begin
  164.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  165.     FSqlQuery.Params.ParamByName(AName).AsCurrency := AValue;
  166.     result := self;
  167.   end;
  168.  
  169. function TzSql.Param(const AName :String; const AValue :TDateTime) :TzSql; overload;
  170.   begin
  171.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  172.     FSqlQuery.Params.ParamByName(AName).AsDateTime := AValue;
  173.     result := self;
  174.   end;
  175.  
  176. function TzSql.Param(const AName :String; const AValue :TGuid) :TzSql; overload;
  177.   begin
  178.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  179.     FSqlQuery.Params.ParamByName(AName).AsString := AValue.ToString;
  180.     result := self;
  181.   end;
  182.  
  183. function TzSql.Param(const AName :String; const AValue :String) :TzSql; overload;
  184.   begin
  185.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  186.     FSqlQuery.Params.ParamByName(AName).AsString := AValue;
  187.     result := self;
  188.   end;
  189.  
  190. function TzSql.Param(const AName :String; const AValue :TBytes) :TzSql; overload;
  191.   begin
  192.     FSqlQuery.SQL.Text := FSqlQuery.SQL.Text + ' :' + AName;
  193.     FSqlQuery.Params.ParamByName(AName).AsBytes := AValue;
  194.     result := self;
  195.   end;
  196.  
  197. function TzSql.Exec :Int64;
  198.   begin
  199.     try
  200.       if not FSqlConnector.Connected then FSqlConnector.Open;
  201.       if not FSqlTransaction.Active  then FSqlTransaction.StartTransaction;
  202.       FSqlQuery.Prepare;
  203.       FSqlQuery.ExecSql;
  204.       if FSqlTransaction.Active then FSqlTransaction.Commit;
  205.       FMessage := '';
  206.       result   := 0; // := FSqlQuery.RowsAffected;   // seems not working always -1
  207.     except
  208.       on E: Exception do
  209.         begin
  210.           if FSqlTransaction.Active then FSqlTransaction.Rollback;
  211.           FMessage := E.Message;
  212.           result   := -1;
  213.         end;
  214.     end;
  215.   end;
  216.  
  217. function TzSql.Run :String;
  218.   begin
  219.     result := '';
  220.     try
  221.       FSqlQuery.Open;
  222.       // begin tran
  223.       if not FSqlTransaction.Active then FSqlTransaction.StartTransaction;
  224.       if not FSqlQuery.Eof then result := AsTable;
  225.       // commit tran
  226.       if FSqlTransaction.Active then FSqlTransaction.Commit;
  227.       FSqlQuery.Close;
  228.       FMessage := '';
  229.     except
  230.       on E :Exception do
  231.         begin
  232.           // rollback tran
  233.           if FSqlTransaction.Active then FSqlTransaction.Rollback;
  234.           FSqlQuery.Close;
  235.           FMessage := E.Message;
  236.         end;
  237.     end;
  238.   end;
  239.  
  240. function TzSql.AsTable :String;
  241.     var ncol, nrow, col :Int64;
  242.     var header, lines, first :String;
  243.   begin
  244.     result := '';
  245.     header := '';
  246.     lines  := '';
  247.     first  := '';
  248.     ncol   := FSqlQuery.FieldCount;
  249.     nrow   := 1;
  250.  
  251.     // note: problemo with sql server display width
  252.  
  253.     for col:=0 to ncol-1 do
  254.       with FSqlQuery.Fields[col] do
  255.         begin
  256.           if Alignment = TAlignment.taLeftJustify then
  257.             begin
  258.               header += PadRight(DisplayLabel, DisplayWidth) + ' ';
  259.               first  += PadRight(AsString,     DisplayWidth) + ' ';
  260.             end
  261.           else
  262.             begin
  263.               header += PadLeft (DisplayLabel, DisplayWidth) + ' ';
  264.               first  += PadLeft (AsString,     DisplayWidth) + ' ';
  265.             end;
  266.           lines  += StringOfChar('-', DisplayWidth) + ' ';
  267.         end;
  268.  
  269.     result := header + LineEnding
  270.             + lines  + LineEnding
  271.             + first  + LineEnding;
  272.  
  273.     FSqlQuery.Next;
  274.     while not FSqlQuery.Eof do
  275.       begin
  276.         Inc(nrow);
  277.  
  278.         for col:=0 to ncol-1 do
  279.           with FSqlQuery.Fields[col] do
  280.             begin
  281.               if Alignment = TAlignment.taLeftJustify then
  282.                 result += PadRight(AsString, DisplayWidth) + ' '
  283.               else
  284.                 result += PadLeft (AsString, DisplayWidth) + ' ';
  285.             end;
  286.         result += LineEnding;
  287.  
  288.         FSqlQuery.Next;
  289.       end;
  290.     result += LineEnding + 'rows: ' + IntToStr(nrow) + LineEnding;
  291.   end;
  292.  
  293. end.
  294.  
  295.  

example program as interactive sql
Code: Pascal  [Select]
  1.  
  2. program isql; // interactive sql
  3. {$mode objfpc}
  4. {$h+}
  5. uses zSql, URIParser;
  6.  
  7. var sql :TzSql;
  8.     uri, qry :String;
  9.  
  10. begin
  11.  
  12.   uri := ParamStr(1);
  13.   sql := TzSql.Create;
  14.  
  15.   if sql.Connect(uri) then
  16.     begin
  17.       Write('sql> ');
  18.       ReadLn(qry);
  19.  
  20.       while Pos('quit', qry) = 0 do
  21.         begin
  22.           // if first statement is select
  23.           if     (Pos('select', qry) > 0)
  24.              and (Pos('select', qry) < 6) then  // not good, but enough for example
  25.             WriteLn(sql.Query(qry).Run)
  26.           else
  27.             WriteLN(sql.Query(qry).Exec);
  28. {
  29.           // example param
  30.           sql.Query('exec dbo.get_account')
  31.              .Param('account_id', Int32(1))
  32.              .Run;
  33. }
  34.           if sql.Message <> '' then
  35.             WriteLn('msg: ', sql.Message + LineEnding);
  36.  
  37.           Write('sql> ');
  38.           ReadLn(qry);
  39.         end
  40.     end
  41.   else
  42.     WriteLn('error: ', sql.Message + LineEnding);
  43.  
  44.   sql.Free;
  45.  
  46. end.
  47.  
  48.  
  49.  


not yet tested on firebird and oracle

Please Enjoy!