REST JDBC README

Introduction

The REST JDBC driver is a stateless Type 3 JDBC driver, providing a common interface for RESTful interaction with the database. It allows JDBC applications to communicate with cloud databases using the standard JDBC API without the need of raw REST calls or a separate SDK.

Overview

At a high-level, an application communicates with the database using the REST JDBC driver. The driver uses the REST Enabled SQL feature in Oracle Rest Data Services (ORDS) to send and receive information from the Database.

Overview

Development

ORDS Version Requirement

The REST JDBC driver requires ORDS 17.3.0 or later.

Follow these instructions to download and install the latest version of ORDS.

Getting Started

Setup

  1. Start ORDS with REST Enabled SQL enabled.
  2. Rest enable an Oracle Database Schema.
  3. Note the schema username, password and Rest Enabled URL.
  4. Add the REST JDBC driver to your classpath.

JDBC Connection String Example

Here is a sample Connection string and Driver string that could be used with the REST JDBC driver.



							String DRIVER = "oracle.dbtools.jdbc.Driver"; 
							String DB_URL = "http://localhost:8080/ords/hr/"; 
							 String USER   = "HR";     String PASS   = "debjani";

Note: The schema name has to be specified in the URL.

A similar sample that would be used with the Oracle thin driver is below.



							  String DRIVER = "oracle.jdbc.driver.OracleDriver"; 
							  String DB_URL = "jdbc:oracle:thin:@example.us.oracle.com:1521/orcl";
							  String USER   = "HR";     String PASS   = "debjani";

Therefore, a java application intending to use the REST JDBC driver only needs to swap the driver name and the database URL.

Hello World!

Here's an example with the connection string URL and the REST JDBC Driver String inside of a java application.

```java public class Example { public static void main(String[] args) {




String USER = "HR", PASS ="debjani" 
String driver = "oracle.dbtools.jdbc.Driver" 
String DB_URL = "http://slc04qag.us.oracle.com:8082/ords/hr/"
Properties cred = new Properties();  cred.put("user", USER); 
 cred.put("password", PASS);     try {      Connection conn = DriverManager.getConnection(DB_URL, cred); 
Statement stmt  = conn.createStatement();
 ResultSet rs    = stmt.executeQuery("select 'Hello World' from dual"); 
while(rs.next()) {     System.out.println(rs.getString(1));
      }      stmt.close(); 
 }    catch(SQLException e) {    e.printStackTrace();
 }

} } ```

Quickstart - Using the Example Project with the REST JDBC Driver

  1. Download the JDBC REST Example zip file. This is an eclipse project which includes several sample programs that describe how to use various features in the driver.

  2. Download SQLcl sqlcl-17.2.0.184.1230 from the OTN page.

$ unzip RESTJDBC_source_beta.zip $ unzip sqlcl-17.2.0.184.1230-no-jre.zip $ cp sqlcl examples/src $ ant

After the project is built, you can run each example by right clicking on the java file and choosing:

"Run as -> Java Application"

Java Commandline with REST JDBC

You can also do this from the command line. Explode the zip file and go to the examples directory. From here you can run

$ant compile This will build the examples and put them into the built directory. You can run each one from the command line like this

java -cp built/classes:lib oracle.dbtools.jdbc.examples.BasicExample

You will need to change the connection string in the examples to connection to your REST service. See below for details.

Using SQLcl with REST JDBC

Follow these steps to use SQLcl with the REST JDBC driver:

  1. Download SQLcl
  2. Download Driver
  3. Drop the rest jdbc driver jar into sqlcl/lib

To use SQLcl with the REST JDBC driver, you can start as follows:

sql USER/PASS@http://<host>:<port>/ords/user/

Building the driver from Source

You can download the source for the REST JDBC Driver from the opensource page. This source is for the driver only. To build the driver code, you will need:

  • Java JDK 1.8.X
  • Ant 1.9.X
  • SQLcl sqlcl-17.2.0.184.1230 which can be downloaded from the OTN page.

When you have downloaded the source and exploded the zip file do the following:

$ unzip [RESTJDBC_source_beta.zip](http://download.oracle.com/otn/java/ords/REST_JDBC_SOURCE.zip) $ cp sqlcl-17.2.0.184.1230-no-jre.zip source $ cd source
Follow [these instructions on authenticating a third party application against ORDS] (http://docs.oracle.com/cd/E56351_01/doc.30/e87809/REST-development-tutorial.htm#AELIG90157) to generate a clientID and clientSecret and add this information to oracle/dbtools/util/JDBCSessionType.java.

Then run:

$ ant The output from the build is summarised below

``` Buildfile: /source/build.xml

clean:

unzip_sqlcl: [echo] Download SQLcl Beta [sqlcl-17.3.0.248.1158-no-jre.zip] from OTN to this directory [unzip] Expanding: /source/sqlcl-17.3.0.248.1158-no-jre.zip into /source

compile: [mkdir] Created dir: source/built/classes [javac] Compiling 41 source files to /source/built/classes [javac] /source/src/oracle/dbtools/jdbc/BLOB.java

jar: [copy] Copying 1 file to /source/built/classes [jar] Building jar: /source/built/oracle.dbtools.jdbcrest.jar

BUILD SUCCESSFUL Total time: 2 seconds

```

Unsupported Datatypes

The following datatypes are not yet supported by the REST JDBC driver:

  1. ARRAY
  2. BLOB
  3. REF
  4. STRUCT
  5. SQLXML
  6. NCHAR
  7. NVARCHAR
  8. NCLOB
  9. NBLOB
  10. VARBINARY
  11. LONGNVARCHAR
  12. LONGVARBINARY
  13. DISTINCT

Connection State

Since the driver is RESTful, each new query to the database is a new connection and each request to the database is auto-committed. Consequently, the REST JDBC driver does not support rollbacks either. Stateful transactions can be implemented with PL/SQL but the state is not preserved across statements.

Pagination

For large ResultSets, ORDS supports ResultSet pagination. The REST JDBC Driver automatically requests more pages as required by the JDBC API.

The setFetchSize methods in the JDBC API can be used to alter pagination in the REST JDBC Driver. In the REST JDBC driver, the default number of rows fetched from the database at a time is 25. This value is configurable upto the max number of rows that can be set in ORDS (500), using the setFetchSize methods in Statement and ResultSet.

The total number of rows to be fetched from the database is set by setMaxRows in Statement. The REST JDBC driver issues the query against ORDS after the end of each fetch and until all the rows specified by maxRows has been obtained.

Since the driver does not support transactions, the state of the database may change in between fetches. This means that if there are DDLs or DMLs in between fetches, results from the driver will be indicative of the latest state of the database, regardless of which state the query was started on.

Other Limitations

The REST JDBC driver is based on the REST web standards and is affected by constraints of the REST architectural style. Additionally, since the driver communicates with ORDS in JSON, there are a few limitations from the JSON standard.

  1. Transactions are not supported by the driver - it is stateless.
  2. Cursor expressions are not supported.
  3. Each request to the database is auto-committed.
  4. Duplicate column names are not supported.
  5. DateTime objects are limited to a millisecond precision.
  6. When getString methods in ResultSet are called on a datetime object, the result is returned in the ISO 8601 format. This is because ORDS converts datetime values in the Oracle Database to ISO 8601 strings.

LICENSE

This Driver release is covered by the Oracle Technology Network Free Use Developer License.