PostgreSQL

Supported data types
Stored procedure & function
SQL syntax
DataWindow
Transaction
Known issues

Supported data types

The ADO.NET for PostgreSQL database interface supports the following data types:

bigint

bigserial

bit

bit varying

boolean

bytea

char

character

character varying

citext

date

double precision

integer

interval

money

name

numeric

real

serial

smallint

smallserial

text

time with time zone

time without time zone

timestamp with time zone

timestamp without time zone

uuid

varchar

xml

Here are more details about the support for certain data types:

bpchar and Varchar

In PowerBuilder, bpchar and Varchar types are displayed as Character types.
Decimal

When creating a table, the decimal type will be displayed as numeric type after saving.
citext and name

In DataWindow, the default mapping length for citext and name types is 32766.
Bit

bit varying and bit varying(n) are both essentially the PostgreSQL bit varying type. The bit varying type is split into two types because PB IDE table/column definition requires to distinguish them.

(1) For bit varying type where no length can be specified, it will be mapped to 32766 (the maximum length) in DataWindow. The bit varying type cannot have a length specified and is used to represent the state of bit varying with unspecified length.

(2) In PowerBuilder IDE, bit varying(n) type is added for users to specify the length.
Character

(1) For character varying type where no length can be specified, it will be mapped to 32766 (the maximum length) in DataWindow.

(2) In PowerBuilder IDE, character varying(n) type is added to handle cases where character varying type needs a length specification.
Numeric

(1) The numeric type supports a precision of up to 30 digits (including integer digits and decimal points). Queries exceeding this precision will fail.

(2) Columns of serial type are not recognized as identity columns.
Boolean

When a DataWindow is created and includes boolean type columns, the boolean columns are mapped as char(6).
time with zone and timestamp with zone

time/timestamp with zone type is currently displayed after localization. However, since PowerBuilder only supports datetime/time types, updates involving time zones are not supported. The valid time range is 0001 to 9999; times outside this range are unsupported by ADO.NET, and drivers might return incorrect times.
interval datetime

In DataWindow, interval datetime type is mapped as char(64). When the interval type contains a month value, some scenarios may return error messages (see known issues). The solution is to convert month values into days for storage.
DataWindow boolean, interval, and XML have no mapping types and are handled as the String type for the time being.

Below picture shows the supported data types under the ADO.NET connection and the ODBC connection.

Stored procedure & function

General support status
Support level comparison between ADO.NET and ODBC

General support status

Here are the general description about the support for stored procedure & function under the ADO.NET for PostgreSQL connection:

IN parameters, OUT parameters, and return values currently only support data types listed in the previous section. Arrays, triggers, and other types are not supported at this time.

When the DataWindow data source is a function, we only support syntax formats where the prototype definition includes a specific return type.

Example 1:

  CREATE OR REPLACE FUNCTION public.f_get_alltypedata(
      )
    RETURNS TABLE(id bigint, c_char "char",…) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

  AS $BODY$
  Begin

    RETURN Query
    Select t.id,t.c_char,t.c_bigint…
    from t_unit_dw_ado_alltype_pb t;

  End;
$BODY$;

Example 2:

  CREATE OR REPLACE FUNCTION public. f_get_alltypedata (
      )
    RETURNS SETOF t_unit_dw_ado_alltype_pb //table name
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

  AS $BODY$

    SELECT * FROM t_unit_dw_ado_alltype_pb t；

$BODY$;

Example 3:

CREATE OR REPLACE FUNCTION public. f_get_alltypedata (
   INOUT inout_id bigint,
   OUT c_char "char",
   OUT c_bigint bigint,
   …)
  RETURNS SETOF record 
  LANGUAGE 'plpgsql'
  COST 100
  VOLATILE PARALLEL UNSAFE
  ROWS 1000

AS $BODY$
Begin
 FOR inout_id, c_char, c_bigint, … IN
  SELECT t.id,t.c_char,t.c_bigint,…
  FROM t_unit_dw_ado_alltype_pb t where t.id = inout_id
 LOOP
  RETURN NEXT;
 END LOOP;

End;
$BODY$;

Overloaded functions and procedures: In the IDE, overloading with different numbers and names of parameters is supported. At runtime, overloading with different numbers of parameters is supported. Note: The overload list in the right-click syntax wizard currently does not support selecting overloaded functions; it can only generate the syntax for one of the overloaded functions. Users need to manually modify the scripts.

ESQL function & procedure support both positional mapping syntax and named parameter mapping syntax.

Syntax example:

Longlong  lll_ID, lll_bigint
String    ls_character
Date      ld_Date
Double    ldb_double
Decimal   ldec_Numeric10
lll_ID = 0
lll_bigint = -32768
ls_character = 'R'
ld_Date = Today()
ldb_double = 12345.12345
ldec_Numeric10 = 12345.12345
DECLARE PRO_Insert PROCEDURE FOR p_insert_simpletypedata_6parm  
         in_c_id = :lll_ID,   
         in_c_bigint = :lll_bigint,   
         in_c_character = :ls_character,   
         in_c_date = :ld_Date,   
         in_c_doubleprecision = :ldb_double,   
         in_c_numeric10 = :ldec_Numeric10 Using itr_PG ;
Execute PRO_Insert;

Longlong lll_ID
 
lll_ID = 1

DECLARE func_GetData PROCEDURE FOR public.f_get_simpletypedata_id  ( :lll_ID ) ;

Execute func_ GetData;

When calling PostgreSQL functions in ESQL, you only need to provide input parameters. When calling PostgreSQL procedures in ESQL, both input and output parameters need to be provided. The parameters should be provided according to the calling syntax conventions specified in pgAdmin.

Support level comparison between ADO.NET and ODBC

Compared to ODBC, ADO.NET interface provides better support to stored procedures and functions in the following areas.

(In this section "procedure" refers to stored procedure only, "function" refers to stored function only, and "procedure/function" refers to either stored procedure or stored function.)

Previewing and updating data

In the ADO.NET for PostgreSQL database painter, you can preview the return data of a procedure, by right-clicking the procedure and then selecting "Edit Data" from the pop-up menu; But in the ODBC for PostgreSQL database painter, you cannot preview the return data of a procedure, as there is no "Edit Data" menu at all for the procedure. (Stored functions can be previewed in both of these database painters.)

Note: ADO.NET driver’s procedure/function data preview only supports previewing return values of data types that Appeon supports.

In the following picture, please also note that the procedure in ADO.NET database painter uses a different icon from the function.
In the DataWindow painter or at runtime, you can update the data for DataWindows using procedure/function as the data source under the ADO.NET connection, but you cannot under the ODBC connection (there will be an error indicating that procedure xxx or function xxx does not exist).

Retrieval arguments

When you create a DataWindow with the stored procedure/function which contains query parameters (retrieval arguments), the DataWindw will be created successfully under the ADO.NET connection, but will fail under the ODBC connection.

For example, function f_get_simpletypedata_id which contains the following syntax will produce an error under ODBC indicating that column "in_id" does not exist, as shown in the following figure:

Create Function f_get_simpletypedata_id
(inout in_id bigint, out in_c_char "char", out c_bigint bigint, 
 out c_bitvarying4 bit varying(4), out c_character character(10),out c_date date, 
 out c_doubleprecision double precision,  out c_numeric10 numeric(10,5),
 out c_timestampwithzone timestamp with time zone, out c_createClient character varying)
 Returns SETOF RECORD
AS $$
Begin
 FOR in_id, in_c_char, c_bigint,c_bitvarying4,
  c_character,c_date,c_doubleprecision, c_numeric10, 
  c_timestampwithzone,c_createClient  IN
  SELECT t.id,t.c_char,t.c_bigint,t.c_bitvarying4,
  t.c_character,t.c_date,t.c_doubleprecision, t.c_numeric10, 
  t.c_timestampwithzone,t."c_createClient"
  FROM t_unit_dw_ado_alltype_pb t 
  WHERE t.id = in_id 
 LOOP
  RETURN NEXT;
 END LOOP;

End;
$$ LANGUAGE plpgsql;

In the ADO.NET database painter, when you preview the return data of a stored function (by selecting "Data manipulation" from the toolbar), and if the function contains query parameters, the Specify Retrieval Arguments dialog will display for you to specify the parameter value (the same dialog displays when you preview the return data of a stored procedure which contains query parameters). But in the ODBC database painter, when you select "Data manipulation" to preview the return data of a function which contains query parameters, an error displays indicating column xxx does not exist.

Overloaded procedure/function

In the ADO.NET database painter, you can view the definition of every overloaded procedure/function (having different number of parameters, different parameter names, or partial parameters that have different IN or OUT mode). But in the ODBC database painter, there will be only one definition shown for all overloaded procedures/functions in the same group.
During the process of creating a DataWindow with an overloaded procedure/function, when using the ADO.NET connection, you can select the appropriate overloaded procedure/function; and the DataWindow syntax will be generated according to the definition of the selected procedure/function; but when using the ODBC connection, there is only one definition for all overloaded procedures/functions in the same group, and the DataWindow syntax will be generated according to this definition no matter which overloaded procedure/function is used, which means you will have to manually modify the syntax after the DataWindow is created.

ESQL

ESQL that uses the overloaded function executes successfully under the ADO.NET connection, but fails under the ODBC connection.
ESQL that uses stored procedures executes successfully under the ADO.NET connection, but fails under the ODBC connection.
ADO.NET supports both parameter name mapping and parameter position mapping in ESQL procedures/functions, while ODBC only supports parameter position mapping.

RPC

If the procedure/function has IN parameters, the RPC syntax generated under the ADO.NET connection executes correctly, while the RPC syntax generated under the ODBC connection will cause IDE to crash.

Note that there are some data types not supported in the ADO.NET connection, such as the array parameter type.
The RPC function that uses a parameterized procedure executes successfully under the ADO.NET connection, but fails under the ODBC connection.

SQL syntax

Here are details about the support for SQL syntax:

PostgreSQL only supports ANSI-standard outer join syntax.
In ESQL SELECT statements, the money type cannot be directly assigned a numeric value in PostgreSQL. Users need to use the CAST function to explicitly convert the type, or use a string type variable (PostgreSQL supports implicit conversion from strings).
As there is no one-to-one mapping type for bit, time with time zone, timestamp with time zone, xml, and real types in PowerBuilder, the default syntax might not support parameter binding as WHERE conditions in PostgreSQL. If necessary, you can use explicit conversion functions to convert the parameter types to the specified types.

For example: CAST(:ldec_money AS money)
When using parameter binding, the boolean type parameter values can only be true, false, 1, or 0.

DataWindow

Here are details about the support for DataWindow:

In DataWindow, for fields with the char data type, the maximum length is extended to 32766. Data exceeding this length may not be accurate, except for the text type.
DataWindow Update conditions (updatewhereclause):

In the PowerBuilder environment, XML, money, timestamp with zone, and time with zone types do not have a unique mapping type. This may lead to incorrect PostgreSQL syntax generation or improper data value conversion. Therefore, to ensure normal update and query functionality, the updatewhereclause property for columns of these types is set to NO by default when generating DataWindow.
DataWindow Query conditions:

In the DataWindow source design, if a money type column is used as a query condition, users need to explicitly convert the parameter type to money or use a string type parameter (PostgreSQL supports implicit conversion from string).
DataWindow using stored procedure as data source:

When the program cannot read the type length of the returned columns, a default length value will be used. Users can modify the column length according to their data requirements.
In DataWindow, when the money type is mapped to decimal, the default number of decimal places is 4, which may differ from PostgreSQL's default of 2.
Memory management for data operations:

Due to driver differences, to support DataWindow features related to rows or properties, the driver will pre-cache all data to be queried in memory before implementing Row or Retrieve as needed functions. Therefore, 1) ADO.NET For PostgreSQL will use more memory when querying data but will release this memory when closing the window or application; 2) Querying tens of thousands of rows may result in some delay.

Transaction

The following transaction error might occur:

current transaction is aborted, commands ignored until end of transaction block

Cause: The error is triggered by exceptions in the preceding code, such as:

1) ESQL parameter type mismatch

2) Parameter length exceeding the limit (e.g., 22001: value too long for type character(lO))

3) DataWindow Retrieve parameter type mismatch.

These issues can lead to transaction blocking errors in subsequent data exchange requests.

Solution:

#1. Set Autocommit to True, or

#2. After capturing exceptions in the script, roll back or commit transactions to avoid blocking issues in subsequent data requests.

Known issues

The ADO.NET for PostgreSQL database interface has the following known issues:

In DB Painter, if an error occurs when executing certain functions, users need to manually reconnect to the database. Otherwise subsequent operations may not be executed properly.
When the Interval type contains a month value, some scenarios may cause queries to return the following error message (Error 1). If this error occurs, subsequent data requests and transactions may return additional error messages (Error 2 or 3) or other exceptions. In such cases, it is necessary to reconnect to the database to continue requesting and interacting with data. This issue is caused by lack of driver support. The solution is to convert month values into days for storage.

Error 1:
```
Select Error: Cannot read interval values with non-zero months as TimeSpan, since that type doesn't support months. Consider using NodaTime Period which better corresponds to PostgreSQL interval, 
or read the value as NpgsqlInterval, or transform the interval to not contain months or years in PostgreSQL before reading it.
```
Error 2:
```
Select Error: Internal Npgsql bug: unexpected value 255 of enum BackendMessageCode. Please file a bug.
```
Error 3:
```
Select Error: Connection is not open.
```

Prev	Up	Next
Sharing ADO.NET Database Connections	Home	Working with Native Database Interfaces

Connecting to Your DatabaseUsing the ADO.NET Interface