accessing postgres from c++
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.
regards,
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:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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 ...
[20746]: [20.06.2022 11:57:19:823]: ecpg_get_data on line 1585: RESULT: srap34dxr1-20210616 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!
a db connector that will fit everyone? i think that's timely.
i have tried, once, libpqxx but i think it forces me to use the source.
couldn't find a binary that will just install and use like how python
works, i.e., "pip install psycopg2".
On Tue, 21 Jun 2022, 3:57 pm Matthias Apitz <guru@unixarea.de> wrote:
Show quoted text
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=SunRise DBCALL 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 connection test01
[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 sisisinst WHERE 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 connection test01
[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!