sql3types.h does not include "BigInt"

From: Niko Ware <nikowareinc(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: sql3types.h does not include "BigInt"
Date: 2021-03-15 19:34:16
Message-ID: CACHGMtRaMpp=0nuvBi0C4m7MSRpNqvDFHSoEbUNoAFNcF9VCkg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm using "dynamic sql" to process generic queries (see code fragment
below). The routine works correctly for most data types, but it does not
work for functions (e.g., NextVal()). The data TYPE returned for NextVal()
sequence is zero (see line 24 of the code fragment). The documented data
type for NextVal() is "bigint" (see
https://www.postgresql.org/docs/current/functions-sequence.html) The query
being executed is "SELECT NEXTVAL('my_sequence')".

The data types defined in sql3types.h (see below) do not include "zero" or
"bigint" (the enumerated values start at "1"). My function defaults the
data type to "String" which returns a NULL terminated character array with
the expected value. I'm able to coerce this to an integer at the
application level, but there are other "unknown" cases where my application
is requesting the result of a function. The application code does not
always coerce the value to the correct type (it assumes the type based on
the query) which results in unexpected operations.

How do I identify these "out of bound" data types and properly coerce them
to the expected type? At a minimum I would like to handle the results from
sequence manipulation functions. Is there an alternate descriptor field
which can be used to identify the function return type (bigint) so that it
can be properly cast?

-------------------------------------------------
SQL3TYPES.H

/* SQL3 dynamic type codes */
/* chapter 13.1 table 2: Codes used for SQL data types in Dynamic SQL */

enum
{
SQL3_CHARACTER = 1,
SQL3_NUMERIC,
SQL3_DECIMAL,
SQL3_INTEGER,
SQL3_SMALLINT,
SQL3_FLOAT,
SQL3_REAL,
SQL3_DOUBLE_PRECISION,
SQL3_DATE_TIME_TIMESTAMP,
SQL3_INTERVAL, /* 10 */
SQL3_CHARACTER_VARYING = 12,
SQL3_ENUMERATED,
SQL3_BIT,
SQL3_BIT_VARYING,
SQL3_BOOLEAN,
SQL3_abstract
/* the rest is xLOB stuff */
};

-------------------------------------------------
CODE FRAGMENT:

1 EXEC SQL WHENEVER SQLERROR GOTO myErrorHandler;
2 EXEC SQL WHENEVER NOT FOUND DO break;
3
4 EXEC SQL ALLOCATE DESCRIPTOR my_desc;
5
6 EXEC SQL PREPARE my_prepare FROM :my_sql_query;
7 EXEC SQL DECLARE my_cursor CURSOR FOR my_prepare;
8 EXEC SQL OPEN my_cursor;
9
10 while (TRUE)
11 {
12 EXEC SQL FETCH NEXT FROM my_cursor INTO SQL DESCRIPTOR my_desc;
13
14 if (gdat->cols == NULL)
15 {
16 EXEC SQL GET DESCRIPTOR my_desc :my_num_cols = COUNT;
17
18 if ((gdat->num_cols = my_num_cols) > 0)
19 {
20 if ((gdat->cols = (ColDat*) calloc(gdat->num_cols,
sizeof(ColDat))) != NULL)
21 {
22 for (my_col = 1; my_col <= gdat->num_cols; my_col++)
23 {
24 EXEC SQL GET DESCRIPTOR my_desc VALUE :my_col
:my_name = NAME, :my_type = TYPE;
25 OAStrCpySize(gdat->cols[my_col-1].name, my_name.arr);
26 gdat->cols[my_col-1].type =
GDconvertSQL13Type(my_type);
27 OAprintf("----- %d = %d\n",
gdat->cols[my_col-1].type, my_type); //~~
28 }
29 }
30 }
31 }
32 }

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-03-15 21:10:24 Re: Allowing John to Drop Triggers On Chad's Tables
Previous Message David G. Johnston 2021-03-15 19:33:39 Re: Allowing John to Drop Triggers On Chad's Tables