Thread: PostgreSQL && data types in ESQL/C

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

struct {

    long blobLength;
    char blobBytes[MAX_EXPECTED_BLOB];
    } blob;

For example Sybase handles 'date' and 'bytea' in another way: 'date' is
a struct of two long and for 'bytea' an additional host variable
for the length must be used in FETCH, INSERT, ...

The above is a bit by try and error. Is there any good manual which
describes the ESQL/C details for PostgreSQL.

Thanks

    matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen durchschaut"
"Believe little, scrutinise all, think by your own: How see through manipulations"
ISBN-10: 386489218X

On Wed, Nov 6, 2019 at 12:32 AM Matthias Apitz <guru@unixarea.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