PostgreSQL Data Source

PostgreSQL Data Source
lac42
This article includes information about how to install the PostgreSQL JDBC driver, how to connect to a PostgreSQL database, and a list of the PostgreSQL data types that 
CA Live API Creator
 supports. With the JDBC driver installed, the PostgreSQL data source is available as an option in the connection wizard when creating an API.
In this article:
2
Install the PostgreSQL JDBC Driver
The self-contained, single-user version of 
CA Live API Creator
 that is based on Jetty includes the PostgreSQL JDBC driver. The JDBC driver is located in the
%JETTY_HOME%/caliveapicreator/lib/ext
 directory. If you have installed this version of
CA Live API Creator
, no additional steps are required to install the JDBC driver.
If you have installed 
CA Live API Creator
 on another Java container, such as Apache Tomcat, copy the license file and the 
postgresql-9.xxx.xxx-jdbc41.jar
 JAR file (the JDBC driver) into the directory that is based on the Java container on which you have installed 
CA Live API Creator
:
Apache Tomcat
Copy the JDBC driver into the
%{CATALINA_HOME}/lib
 directory
Oracle WebLogic
Copy the JDBC driver to the 
<Your WebLogic Domain Directory>/lib
 directory.
JBoss/WildFly
For more information, see Install on JBoss.
IBM WebSphere
Add the JDBC driver to your Web application server.
Using the WebSphere administrative console, create a JDBC provider for the driver in the server.
For more information about how to create a JDBC provider, see the IBM WebSphere documentation.
For more information about the JDBC driver versions that
CA Live API Creator
 supports, see Installation Requirements and Supported Platforms.
Connect to a PostgreSQL Database
Connect to your database by creating your API.
Prerequisites: 
You know your JDBC connection information.
For more information about how to create an API using API Creator, Creating APIs.
Follow these steps:
  1. In API Creator, on the APIs page, click 
    Create New API
    .
    The connection wizard opens.
  2. Click 
    Database First
    .
  3. Select
    PostgreSQL
     as the data source.
  4. Complete the
    Host
    ,
    Port
    , and
    Password
    fields, including the following fields, and then click
    Continue
    :
    Database
    This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) database value.
    Schema
    This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) schema name.
    Example:
     "MySchema".
    Username
    This database value accepts double quotes. Use double quotes to accept the value of mixed-case (non-standard) username.
You are now connected to the PostgreSQL database.
PostgreSQL Supported Data Types
When you are setting up a connection to an external data source, 
CA Live API Creator
 must map the data types in the data source to data types in 
CA Live API Creator
. The following table lists the different data type mappings applicable when working with a PostgreSQL database.
CA Live API Creator
 supports a subset of the full range of PostgreSQL data types.
For more information about the PostgreSQL data types, see the PostgreSQL documentation.
CA Live API Creator
 supports read and write of the following PostgreSQL data types:
Data type
Modeled as...
Notes
smallint
number
integer
number
bigint
number
decimal
number
real
number
double precision
number
smallserial
number
serial
number
bigserial
number
numeric
number in JSON plus NaN value
money
number in JSON (BigDecimal)
PostgreSQL databases handle this data type according to its default monetary format.
For more information about how
CA Live API Creator
handles the
money
data type, see the "Handling of the money Data Type" section.
character varying
Alias:
varchar
string
character
Alias:
 char
string
text
string
json
string
jsonb
string
xml
string
uuid
string
bit
BitMask
bit varying
BitMask
boolean
boolean in JSON
bytea
bytes
timestamp
SimpleTimestamp
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
timestamp with time zone
SimpleTimestampOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
If you are using a timestamp with time zone, set a prepared statement threshold for connections created from this URL by including the following parameter to the JDBC URL connection (data source):
jdbc:postgresql://server:5432/database?
prepareThreshold=0
For more information about connecting to the database, including information about 
prepareThreshold
, see the PostgreSQL JDBC driver documentation.
date
SimpleDate
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
time
SimpleTime
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
time with time zone
SimpleTimeOffset
For more information about how to use this data type in JavaScript, see Date, Time, Timestamp, and TimestampOffset.
If you are using a time with time zone, set a prepared statement threshold for connections created from this URL by including the following parameter to the JDBC URL connection (data source):
jdbc:postgresql://server:5432/database?
prepareThreshold=0
interval
interval
Use Sequences in PostgreSQL Data Sources
PostgreSQL typically handles auto-generated primary keys using sequences, which are database objects that the sequence generator in the database uses to generate sequential values. When you connect to an PostgreSQL data source, 
CA Live API Creator
 detects the sequences that are defined in your schema.
You can have 
CA Live API Creator
 auto-generate primary keys using these sequences using the following methods:
Associate Entity Columns to Sequences
When you connect to a database that supports sequences, such as PostgreSQL, 
CA Live API Creator
 detects the sequences that are defined in your schema. 
CA Live API Creator
 cannot know which entity (table or view) uses which sequence. If your database includes sequences, you want it to generate unique primary keys during row inserts, and you want 
CA Live API Creator
 to auto-generate numbers for the entity's column using the sequence, associate the sequence to that entity's column.
For more information, see Associate Entities to Sequences.
Define Rules that Return a Unique Primary Key
You can have 
CA Live API Creator
 generate the following primary keys for the table using sequences and assign it to a column by way of event rules:
  • Alphanumeric unique primary key:
     Define a 
    pre-insert event rule
    . Within the code for the rule, define how 
    CA Live API Creator
     computes the key. Pre-insert event rules fire only on client inserts. Rules of this type automatically handle inserting a parent and a set of children in a single transmission (Cascade Add).
    The
    Oracle Sequence Example
    API sample illustrates an example of how you can have
    CA Live API Creator
    insert a parent and a set of children in the same request.
    For more information about this example, see Oracle Sequence Example API Sample.
  • Unique primary key:
     Define an 
    early event rule
    . Define a rule of this type for row inserts that are triggered from within your rule logic (for example, audit changes into child tables).
    The
    Oracle Sequence Example
    API sample illustrates an example of how you can have
    CA Live API Creator
    compute an alphanumeric primary key in an early event rule using sequences.
    For more information about this example, see Oracle Sequence Example API Sample.
For more information about how to define pre-insert and early event rule types, see Event Rule Types.
PostgreSQL Data Source Limitation
The PostgreSQL JDBC driver has limitations that prevent
CA Live API Creator
 from exposing PostgreSQL functions as REST endpoints. This PostgreSQL JDBC driver limitation exposes inadequate information on the parameter datatypes to 
CA Live API Creator
.