Re: PostgreSQL && data types in ESQL/C

On Wed, Nov 6, 2019 at 12:32 AM Matthias Apitz <guru(at)unixarea(dot)de> wrote:
> Hello,
>
> On our project roadmap to port our LMS (Library Management System) from
> Sybase/Oracle to PostgreSQL we are now in the phase of addressing the
> ESQL/C and C++ code parts (some million lines of code).
>
> I wrote a small ESQL/C test code to see how the various data types are
> handled.
>
> In general: Is there any good manual about ESQL/C in PostgreSQL?
> Because, even if there are standards any implementation has its details.
>
> In detail:
>
> I've created a table with the most used data types:
>
> $ cat mytypes.sql
>
> create table mytypes (
> myint integer,
> mychar char (4),
> mydate date,
> myvchar varchar(81),
> myblob bytea
> ) ;
>
> and have loaded a row with some data which is shown in pgsql as:
>
> $ printf "select * from mytypes;\n" | psql -Usisis -d newsisis
> myint | mychar | mydate | myvchar | myblob
> -------+--------+------------+--------------+------------------------------------------------------
> 1 | char | 08.05.1945 | освобождение | \xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b50a
> (1 Zeile)
>
> in the ESQL/C code the host variables are declared as:
>
> EXEC SQL BEGIN DECLARE SECTION;
> ...
> int myint;
> char mychar[8];
> char mydate[10+1];
> char myvchar[81];
> char myblob[1024];
> ...
> EXEC SQL END DECLARE SECTION;
>
> and the FETCH into these is done with:
>
> EXEC SQL FETCH IN c_statename INTO :myint, :mychar, :mydate, :myvchar, :myblob;
>
> which gives with an ESQL/C test pgm which prints the above host
> variables:
>
> $ /usr/local/sisis-pap/pgsql/bin/ecpg embedded.pgc
> $ gcc -m64 -o embedded embedded.c -I/usr/local/sisis-pap/pgsql/include -L/usr/local/sisis-pap/pgsql/lib/ -lpq -lecpg
>
> $ ./embedded
> stmt: SELECT myint, mychar, mydate, myvchar, myblob FROM mytypes;
> myint [1]
> mychar [char]
> mydate [08.05.1945]
> myvchar [освобождение]
> myblob [\xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b50a]
>
> It seems(!):
>
> - an int appears as binary integer
> - all others types (even the column type 'date') appear as C type char*
> - 'date', 'char' and 'varchar' are delivered as '\0' terminated strings
> - 'bytea' appears as '\0' terminated string coded in hex with "\x" in front
>
> Our DBCALL layer must convert these char strings in the data form the
> application layer is expecting, for example a BLOB ('bytea') into a C struct

https://www.postgresql.org/docs/9.1/ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING

It looks like there is a workaround for the null terminated strings
and dates. In practice through it looks like you may need to be
prepared to tweak either the ecpg library or your application to get
this to work.

merlin