[SOLVED] Windows CLI: how to connect to postgres?

[Xtra999]

Hi folks,

For some reason I recently switched from C# to Free Pascal / Lazarus. I have to write a CLI tool which has to do some PostgreSQL queries. While knowing the basic syntax of Free Pascal I've no clue how to connect to a Postgres db. I read somewhere that I need some dll files placed in the project directory to make it work. And a Postgres Client? What module(s) do I have to use? I can't find any helpful information on the net. All I found was how to write a GUI application using Lazarus. But again, it's a CLI tool.

Thanks in advance.

[mdalacu]

Hi, you could try this:
https://wiki.freepascal.org/ZeosDBO
EDIT: Or better read this and decide for the component:
https://wiki.freepascal.org/postgres

[Nimbus]

Have a look at examples in the FPC sources
https://gitlab.com/freepascal.org/fpc/source/-/blob/main/packages/postgres/examples/testpg1.pp

You will certainly need the client library (libpq.dll, possibly a few more) available on your system.
I expect these might work for Win64 (didn't test):
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows

[Xtra999]

Have a look at examples in the FPC sources
https://gitlab.com/freepascal.org/fpc/source/-/blob/main/packages/postgres/examples/testpg1.pp

You will certainly need the client library (libpq.dll, possibly a few more) available on your system.
I expect these might work for Win64 (didn't test):
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows

Hi and thanks for your answer. I tried the FPC example program but unfortunately FPC can't compile. I get this error (postgres_connect is the name I gave the example project):

postgres_connect.lpr(105,1) Error: Import library not found for pq
postgres_connect.lpr(105,1) Error: Import library not found for c

I copied libpq.dll into my project folder btw.

[TRon]

Try replacing unit postgres in the uses section with postgres3dyn in the example-code.

[Xtra999]

Try replacing unit postgres in the uses section with postgres3dyn in the example-code.

Oh, that's it. Thanks. Now, when I want to connect to a remote db, can postgres3dyn handle that?

Edit: and how to use username and password to login??

[TRon]

I would probably advise to use sqldb (wiki tutorial(s) ) instead of using low-level postgres stuff. If not mistaken then there is a login example (makes use of a form but that can be omitted/rewritten).

fyi: SQLDB does not require LCL (if you do not want to) or any visual component(s).

[Zvoni]

I would probably advise to use sqldb (wiki tutorial(s) ) instead of using low-level postgres stuff. If not mistaken then there is a login example (makes use of a form but that can be omitted/rewritten).

fyi: SQLDB does not require LCL (if you do not want to) or any visual component(s).

He did specify "CLI", so no LCL needed (at least for GUI-stuff)

[TRon]

He did specify "CLI", so no LCL needed (at least for GUI-stuff)

Yes, I've read that so makes me wonder if I perhaps messed up in what I wrote.

The wiki shows the use of sqldb pro-dominantly using GUI components and other LCL related routines, which could provide the impression that sqldb can only be used that way. The statement I made was intended to make clear that this is not the case and that you can use SQLDB using the command-line compiler or if so named CLI.

[Zvoni]

He did specify "CLI", so no LCL needed (at least for GUI-stuff)

Yes, I've read that so makes me wonder if I perhaps messed up in what I wrote.

The wiki shows the use of sqldb pro-dominantly using GUI components and other LCL related routines, which could provide the impression that sqldb can only be used that way. The statement I made was intended to make clear that this is not the case and that you can use SQLDB using the command-line compiler or if so named CLI.

You don't need to use the commandline-compiler to create Console-Programs (CLI).
I use Lazarus the whole time to create console (Non-GUI) code on Windows, and i use sqldb the whole time.

In a nutshell for TS:
He has to setup the "holy trinity" of sqldb: PQConnection, TSQLTransaction and TSQLQuery from code.
PQConnection has some Properties (look them up) like, Server, Port, Username, Password.

as how to connect to a PostGres-Server using his CLI-Program: He has to "parse" User-Input, usually done through "Options"
e.g. (from console)
mypqcli -s 192.168.0.10 -p 1234 -u MyUserName -w MyPassword

he has to parse the passed arguments, assign them to the corresponding Properties, and then open the connection, optionally reacting to wrong input

[TRon]

You don't need to use the commandline-compiler to create Console-Programs (CLI).

In case we're going that route I'll play along 

I would like for you to try and compile something with Lazarus without having any command-line compiler present.

I use Lazarus the whole time to create console (Non-GUI) code on Windows, and i use sqldb the whole time.

Yes, I know that. You are the one that pointed the attention to the acronym CLI in a context which was an affirmation of what I had already written but in such a way that it seem to be in disagreement with what I wrote.

Call me confused 


@Xtra999:
For a simplified example (using TSQLite3Connection) see f.e. this msg

[Xtra999]

Hi guys,

Thanks for all your answers and hints. I'm finally able to connect to a remote Postgres DB. Alas, I've no clue how to retrieve data from the connected database. Sure, I know SQL, but how to query the DB with Free Pascal? Do I need additional libraries (units) and how to program a query?
BTW, as much as I like Lazarus / Free Pascal, the documentation is somewhat frustrating sometimes. You find a piece of information here, another peace there and some code examples which are 10 yrs old or even older and when you try to Frankenstein everything together, it doesn't work because you used the wrong unit and there are almost no information about the right one. Generally the docs are quite useful, but in my case, using Free Pascal to query a Postgres DB ... OMG! Lots of stuff regarding Lazarus / GUI programming with Firebird, though.

[Nimbus]

Hi, this isn't really too complicated. Just as Zvoni suggested earlier, a combination of TPQConnection (PQConnection unit) + TSQLTransaction (SQLDB unit) + TSQLQuery (SQLDB again) can do the job pretty straightforward.

How about this:

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

1. program hellopg;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   SysUtils, PQConnection, SQLDB;
7.  
8. var
9.   Conn: TPQConnection;
10.   Transaction: TSQLTransaction;
11.   Query: TSQLQuery;
12.  
13. begin
14.   Conn := TPQConnection.Create(Nil);
15.   Transaction := TSQLTransaction.Create(Conn);
16.   Query := TSQLQuery.Create(Conn);
17.   try
18.     Conn.HostName := '127.0.0.1';
19.     Conn.UserName := 'postgres';
20.     Conn.Password := 'postgres';
21.     Conn.DatabaseName := 'hellopg';
22.     Conn.Params.AddPair('port', '5432');
23.  
24.     Writeln('Connecting...');
25.     Conn.Connected := True;
26.  
27.     Transaction.DataBase := Conn;
28.     Query.Transaction := Transaction;
29.  
30.     Query.SQL.Text := 'SELECT id, foo, bar FROM mytable';
31.  
32.     Writeln('Running the query...');
33.     Query.Open;
34.  
35.     // Loop over the result rows
36.     while not Query.EOF do
37.     begin
38.       Writeln(
39.         Format('%d | %s | %.2f',
40.           [
41.             Query.FieldByName('id').AsInteger,
42.             Query.FieldByName('foo').AsString,
43.             Query.FieldByName('bar').AsFloat
44.           ]));
45.       Query.Next; // Move to the next row
46.     end;
47.     Query.Close;
48.  
49.   finally
50.     FreeAndNil(Conn);
51.   end;
52.  
53.   Writeln('Bye.');
54. end.

Written on a Mac though it should work the very same way on Windows.

More info on SQLDB:
https://www.freepascal.org/docs-html/fcl/sqldb/usingsqldb.html

[Xtra999]

Thanks a million Nimbus, it works now! You really helped me a lot!