Thread: accessing postgres from c++

On Tue, 2022-06-21 at 10:56 +0800, Rino Mardo wrote:
> hi. i found odbc.postgresql.org when i was looking for a way to hook up my c++ project to postgresql server.
> installed it and tested to be successful in connection. this was when i had postgresql
> running from a virtualbox vm in Ubuntu. 
> 
> i installed a postgresql container using Docker for Windows in my laptop. from a Windows terminal i can
> connect to the postgresql container and from there use psql client to create database.
> 
> when postgresql was running in an Ubuntu vm in virtualbox, i wasn't able to test the c++ connection.
> now i want to but can't find a way how. either from python or any programming language.
> 
> i guess my problem is more of the programming than db server side but i thought to try asking here
> in case someone can help. thanks.

I am surprised that you choose to be fettered by the constraints of a generic API like ODBC.
For me, that only makes sense if you want to be portable to different databases.

I would use the powerful C API of libpq, or, if you want some C++ boilerplate around it,
use libpqxx.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Tue, Jun 21, 2022 at 7:59 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> I am surprised that you choose to be fettered by the constraints of a generic API like ODBC.
> For me, that only makes sense if you want to be portable to different databases.

Ditto.

> I would use ... C API of libpq, or, .. C++ boilerplate around it, use libpqxx.

There's also this modern C++ libpq wrapper, announced on this list
earlier this year:
https://github.com/dmitigr/pgfe

PS: Haven't used it. but followed it's development (to inform my own
non-OSS C++ wrapper dev), and it looked solid from a distance.
PPS: I think we tried libpqxx in the past, and I kinda remember it
forced you into non-binary mode, which was a non-started for
performance.

We are developing a huge Library Management System with some 400 tables
and which is/was running on many UNIX derivates (SINIX, HP-UX, AIX,
SunOS, Linux) and all kind of databases one can imagine (INFORMIX,
Oracle, Sybase and now PostgreSQL). The system is written in C, C++ and
Perl and the C/C++ dblayer uses ESQL/C which made it highly portable between
the above mentioned DBSes. The PostgreSQL ESQL/C has an unbeatable
logging feature which logs all ESQL/C operations with their arguments
and results, like this:

[20746] [20.06.2022 11:57:19:817]: ECPGconnect: opening database test01 on bvbzflltdb1 port 5432 with options
application_name=SunRiseDBCALL V7.2 (pid=20746) for user sisis
 
[20746] [20.06.2022 11:57:19:821]: ecpg_execute on line 822: query: select current_date; with 0 parameter(s) on
connectiontest01
 
[20746] [20.06.2022 11:57:19:821]: ecpg_execute on line 822: using PQexec
[20746] [20.06.2022 11:57:19:821]: ecpg_process_output on line 822: correctly got 1 tuples with 1 fields
[20746] [20.06.2022 11:57:19:821]: ecpg_get_data on line 822: RESULT: 20.06.2022 offset: 80; array: no
[20746] [20.06.2022 11:57:19:822]: prepare_common on line 936: name sid_sisisinst; query: "SELECT ctid, * from
sisisinstWHERE version  = $1"
 
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1174: query: declare sisisinst_seq cursor with hold for SELECT
ctid,* from sisisinst WHERE version  = $1; with 1 parameter(s) on connection test01
 
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1174: using PQexecParams
[20746] [20.06.2022 11:57:19:823]: ecpg_free_params on line 1174: parameter 1 = V7.2
[20746] [20.06.2022 11:57:19:823]: ecpg_process_output on line 1174: OK: DECLARE CURSOR
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1585: query: fetch sisisinst_seq; with 0 parameter(s) on
connectiontest01
 
[20746] [20.06.2022 11:57:19:823]: ecpg_execute on line 1585: using PQexec
[20746] [20.06.2022 11:57:19:823]: ecpg_process_output on line 1585: correctly got 1 tuples with 7 fields
[20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: (0,35) offset: 19; array: no
[20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: 22.09.2021 offset: 137; array: no
[20746] [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616       offset: 137; array:
no
...

The only missing thing in this logging was the PID and exact timestamp
of the operation. The latter makes performance analysing very easy.
We added this (PID and timestamp) to the sources of the ecpglib:

postgresql-14.1/src/interfaces/ecpg/ecpglib/misc.c

I could share the diff for maybe to be included in the original
sources.

    matthias
-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!