Github user dyozie commented on a diff in the pull request: https://github.com/apache/incubator-hawq-docs/pull/83#discussion_r95686500 --- Diff: markdown/plext/using_pljava.html.md.erb --- @@ -299,411 +331,770 @@ Scalar types are mapped in a straightforward way. This table lists the current m | complex | java.sql.ResultSet | | setof complex | java.sql.ResultSet | -All other types are mapped to `java.lang.String` and will utilize the standard textin/textout routines registered for respective type. +All other types are mapped to `java.lang.String` and will utilize the standard textin/textout routines registered for the respective type. ### <a id="nullhandling"></a>NULL Handling -The scalar types that map to Java primitives can not be passed as NULL values. To pass NULL values, those types can have an alternative mapping. You enable this mapping by explicitly denoting it in the method reference. +The scalar types that map to Java primitives can not be passed as NULL values to Java methods. To pass NULL values, those types should be mapped to the Java object wrapper class that corresponds with the primitive, and must be explicitly denoted in the method reference. For example, the object wrapper class for the `integer` primitive type is `java.lang.Integer`. -```sql -=> CREATE FUNCTION trueIfEvenOrNull(integer) - RETURNS bool - AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)' - LANGUAGE java; -``` +Example: Handling Null Inputs -The Java code would be similar to this: - -```java -package foo.fee; -public class Fum -{ - static boolean trueIfEvenOrNull(Integer value) - { - return (value == null) - ? true - : (value.intValue() % 1) == 0; - } -} -``` +1. Create a work area for the example: -The following two statements both yield true: + ``` shell + $ mkdir pljava_work + $ cd pljava_work + $ export PLJAVAWORK=`pwd` + $ mkdir -p pljex/foo/fee + $ cd pljex/foo/fee + ``` -```sql -=> SELECT trueIfEvenOrNull(NULL); -=> SELECT trueIfEvenOrNull(4); -``` +2. Create a new file named `Fum.java`, adding the following text to create a class named `Fum` with a single method named `trueIfEvenOrNull()`. This method takes an integer as input and returns true if the integer is even or NULL, false otherwise: -In order to return NULL values from a Java method, you use the object type that corresponds to the primitive (for example, you return `java.lang.Integer` instead of `int`). The PL/Java resolve mechanism finds the method regardless. Since Java cannot have different return types for methods with the same name, this does not introduce any ambiguity. + ``` shell + $ vi Fum.java + ``` + + ``` java + package foo.fee; + public class Fum + { + static boolean trueIfEvenOrNull(Integer value) + { + return (value == null) + ? true + : (value.intValue() % 1) == 0; + } + } + ``` + +3. Compile the `Fum` class and create a JAR file for this class named `pljex.jar`: + + ``` shell + $ javac Fum.java + $ cd ../.. + $ jar cf pljex.jar foo + ``` + +4. Copy the JAR file to the default PL/Java classpath directory: + + ``` shell + $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/ + ``` + +5. Start the `psql` subsystem: + + ``` shell + $ psql -d testdb + ``` + +6. Add the JAR file to the session-level classpath: + + ``` sql + => SET pljava_classpath='pljex.jar'; + ``` + +7. Create a trusted PL/Java UDF that invokes the `Fum` class `trueIfEvenOrNull()` method: + + ``` sql + => CREATE FUNCTION isEvenOrNull(integer) + RETURNS bool + AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)' + LANGUAGE java; + ``` + + Notice that the UDF input type is `integer`, while the `trueIfEvenOrNull()` Java method input is a `java.lang.Integer` object. + +8. Execute the UDF twice, once with a NULL and once with an even input: + + ``` sql + => SELECT isEvenOrNull(NULL); + => SELECT isEvenOrNull(4); + ``` + + Both statements should return true. ### <a id="complextypes"></a>Complex Types -A complex type will always be passed as a read-only `java.sql.ResultSet` with exactly one row. The `ResultSet` is positioned on its row so a call to `next()` should not be made. The values of the complex type are retrieved using the standard getter methods of the `ResultSet`. +PL/Java supports complex types. Use the `CREATE TYPE` SQL command to create the complex type. Use the `CREATE FUNCTION` SQL command to define a PL/Java UDF whose input argument is the new (complex) type. -Example: +A complex type is always passed to a Java method as a read-only `java.sql.ResultSet` with exactly one row. The values of the specific fields in the complex type are retrieved using the standard getter method associated with the data type of each field present in the `ResultSet`. -```sql -=> CREATE TYPE complexTest - AS(base integer, incbase integer, ctime timestamptz); -=> CREATE FUNCTION useComplexTest(complexTest) - RETURNS VARCHAR - AS 'foo.fee.Fum.useComplexTest' - IMMUTABLE LANGUAGE java; -``` +Example: Complex Input Types -In the Java class `Fum`, we add the following static method: - -```java -public static String useComplexTest(ResultSet complexTest) -throws SQLException -{ - int base = complexTest.getInt(1); - int incbase = complexTest.getInt(2); - Timestamp ctime = complexTest.getTimestamp(3); - return "Base = \"" + base + - "\", incbase = \"" + incbase + - "\", ctime = \"" + ctime + "\""; -} -``` +1. Add the following definitions and static method to the Java `Fum` class you created in an earlier exercise. This method outputs the components of the complex type comprised of two integer fields and a timestamp field: + + ``` shell + $ cd $PLJAVAWORK/pljex/foo/fee + $ vi Fum.java + ``` + + ``` java + import java.sql.ResultSet; + import java.sql.SQLException; + import java.sql.Timestamp; + ``` + + ``` java + public static String useComplexTest(ResultSet complexTest) + throws SQLException + { + int base = complexTest.getInt(1); + int incbase = complexTest.getInt(2); + Timestamp ctime = complexTest.getTimestamp(3); + return "Base = \"" + base + + "\", incbase = \"" + incbase + + "\", ctime = \"" + ctime + "\""; + } + ``` + + Add the `imports` under the `package` definition. And be sure to include the `useComplexTypes()` method within the `Fum` class `{}`s. + + Notice that the `ResultSet` object is immediately referenced; a call to `next()` is not required. + +2. Compile the `Fum` class, create the JAR file, and copy the JAR file to the default PL/Java classpath directory: + + ``` shell + $ javac Fum.java + $ cd ../.. + $ jar cf pljex.jar foo + $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/ + ``` + +5. Start the `psql` subsystem: + + ``` shell + $ psql -d testdb + ``` + +6. Add the JAR file to the session-level classpath: + + ``` sql + => SET pljava_classpath='pljex.jar'; + ``` + +7. Create a custom complex type with 2 integers and a single timestamp component: + + ``` sql + => CREATE TYPE complexTestType + AS(base integer, incbase integer, ctime timestamptz); + ``` + +7. Create a PL/Java UDF that invokes the `Fum` class `useComplexTest()` method, specifying a `complexTestType` as input: + + ```sql + => CREATE FUNCTION useComplexTest(complexTestType) + RETURNS VARCHAR + AS 'foo.fee.Fum.useComplexTest' + IMMUTABLE LANGUAGE java; + ``` + +8. Execute the `useComplexTest()` UDF, providing 2 integers and a timestamp as input: + + ```sql + => SELECT useComplexTest( '(1,2,20170101010203)' ); + ``` + + ``` + usecomplextest + ------------------------------------------------------------ + Base = "1", incbase = "2", ctime = "2017-01-01 01:02:03.0" + (1 row) + ``` + + Running the UDF displays the fields and values comprising the complex type. ### <a id="returningcomplextypes"></a>Returning Complex Types -Java does not stipulate any way to create a `ResultSet`. Hence, returning a ResultSet is not an option. The SQL-2003 draft suggests that a complex return value should be handled as an IN/OUT parameter. PL/Java implements a `ResultSet` that way. If you declare a function that returns a complex type, you will need to use a Java method with boolean return type with a last parameter of type `java.sql.ResultSet`. The parameter will be initialized to an empty updateable ResultSet that contains exactly one row. +As Java does not define any way to create a `ResultSet`, returning a `ResultSet` from a Java method is not an option. The SQL-2003 draft suggests that a complex return value should be handled as an input/output argument. Conveniently, PL/Java implements a `ResultSet` that way. To declare a function that returns a complex type, you must implement a Java method with a `boolean` return type and a last input argument of type `java.sql.ResultSet`. This input/output argument will be initialized to an empty updateable `ResultSet` that contains exactly one row. -Assume that the complexTest type in previous section has been created. +Example: Complex Return Types -```sql -=> CREATE FUNCTION createComplexTest(int, int) - RETURNS complexTest - AS 'foo.fee.Fum.createComplexTest' - IMMUTABLE LANGUAGE java; -``` +1. Create the `complexTestType` type definition if you did not yet create it: -The PL/Java method resolve will now find the following method in the `Fum` class: - -```java -public static boolean complexReturn(int base, int increment, - ResultSet receiver) -throws SQLException -{ - receiver.updateInt(1, base); - receiver.updateInt(2, base + increment); - receiver.updateTimestamp(3, new - Timestamp(System.currentTimeMillis())); - return true; -} -``` + ``` sql + => CREATE TYPE complexTestType + AS(base integer, incbase integer, ctime timestamptz); + ``` + +2. Add the following static method to the Java class `Fum` you created in an earlier exercise. This method takes two integers as input, returning a complex type consisting of the first input integer, first input integer added to the second input integer, and a current timestamp: + + ``` shell + $ cd $PLJAVAWORK/pljex/foo/fee + $ vi Fum.java + ``` + + ``` java + public static boolean complexReturn(int base, int increment, + ResultSet receiver) + throws SQLException + { + receiver.updateInt(1, base); + receiver.updateInt(2, base + increment); + receiver.updateTimestamp(3, new + Timestamp(System.currentTimeMillis())); + return true; + } + ``` + + The return value denotes if the `receiver` should be considered as a valid tuple (true) or NULL (false). + +3. Compile the `Fum` class, create the JAR file, and copy the JAR file to the default PL/Java classpath directory: + + ``` shell + $ javac Fum.java + $ cd ../.. + $ jar cf pljex.jar foo + $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/ + ``` + +5. Start the `psql` subsystem: + + ``` shell + $ psql -d testdb + ``` + +6. Add the JAR file to the session-level classpath: -The return value denotes if the receiver should be considered as a valid tuple (true) or NULL (false). + ``` sql + => SET pljava_classpath='pljex.jar'; + ``` + +7. Create a PL/Java UDF that invokes the `Fum` class `createComplexType()` method, taking two integers as input: + + ```sql + => CREATE FUNCTION createComplexTest(int, int) + RETURNS complexTestType + AS 'foo.fee.Fum.complexReturn' + IMMUTABLE LANGUAGE java; + ``` + +8. Execute the `createComplexTest()` UDF, providing 2 integers as input: + + ```sql + => SELECT createComplexTest(11,22); + ``` + + ``` + createcomplextest + -------------------------------------- + (11,33,"2016-12-31 23:04:09.388-08") + (1 row) + ``` + + As described, the UDF, when executed, returns the first input integer, the sum of the first and second input integers, and the current timestamp. + +### <a id="functionreturnsets"></a>Functions that Return Sets -### <a id="functionreturnsets"></a>Functions that Return Sets +PL/Java supports user-defined functions that return sets of both scalar and complex types. -When returning result set, you should not build a result set before returning it, because building a large result set would consume a large amount of resources. It is better to produce one row at a time. Incidentally, that is what the HAWQ backend expects a function with SETOF return to do. You can return a SETOF a scalar type such as an int, float or varchar, or you can return a SETOF a complex type. +HAWQ backend a function that `RETURNS` a `SETOF` to return one row at a time. ### <a id="returnsetofscalar"></a>Returning a SETOF \<scalar type\> -In order to return a set of a scalar type, you need create a Java method that returns something that implements the `java.util.Iterator` interface. Here is an example of a method that returns a SETOF varchar: +To return a set of a scalar type, the Java method must return an object that implements the `java.util.Iterator` interface. -```sql -=> CREATE FUNCTION javatest.getSystemProperties() - RETURNS SETOF varchar - AS 'foo.fee.Bar.getNames' - IMMUTABLE LANGUAGE java; -``` +Example: Function that returns a SETOF varchar: -This simple Java method returns an iterator: +1. Create a new file named `Bar.java`, adding the following text to create a class named `Bar` with a single method named `getNames()`. This method uses an `Iterator` to collect and return a list of string names: -```java -package foo.fee; -import java.util.Iterator; + ``` shell + $ cd $PLJAVAWORK/pljex/foo/fee + $ vi Bar.java + ``` -public class Bar -{ - public static Iterator getNames() + ``` java + package foo.fee; + import java.util.ArrayList; + import java.util.Iterator; + + public class Bar { - ArrayList names = new ArrayList(); - names.add("Lisa"); - names.add("Bob"); - names.add("Bill"); - names.add("Sally"); - return names.iterator(); + public static Iterator getNames() + { + ArrayList names = new ArrayList(); + names.add("Lisa"); + names.add("Bob"); + names.add("Bill"); + names.add("Sally"); + return names.iterator(); + } } -} -``` + ``` + + +3. Compile the `Bar` class (ignore warnings), create the JAR file, and copy the JAR file to the default PL/Java classpath directory: + + ``` shell + $ javac *.java + $ cd ../.. + $ jar cf pljex.jar foo + $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/ + ``` + +5. Start the `psql` subsystem: + + ``` shell + $ psql -d testdb + ``` + +6. Add the JAR file to the session-level classpath: + + ``` sql + => SET pljava_classpath='pljex.jar'; + ``` + +7. Create a PL/Java UDF that invokes the `Bar` class `getNames()` method: + + ``` sql + => CREATE FUNCTION getListOfNames() + RETURNS SETOF varchar + AS 'foo.fee.Bar.getNames' + IMMUTABLE LANGUAGE java; + ``` + +8. Execute the UDF: + + ``` sql + => SELECT getListOfNames(); + ``` + + ``` + getlistofnames + ---------------- + Lisa + Bob + Bill + Sally + (4 rows) + ``` + + The UDF returns a list of four string names. ### <a id="returnsetofcomplex"></a>Returning a SETOF \<complex type\> -A method returning a SETOF <complex type> must use either the interface `org.postgresql.pljava.ResultSetProvider` or `org.postgresql.pljava.ResultSetHandle`. The reason for having two interfaces is that they cater for optimal handling of two distinct use cases. The former is for cases when you want to dynamically create each row that is to be returned from the SETOF function. The latter makes is in cases where you want to return the result of an executed query. +A method returning a set of \<complex type\> must implement either the `org.postgresql.pljava.ResultSetProvider` interface or the `org.postgresql.pljava.ResultSetHandle` interface. The interfaces provide optimal handling for distinct use cases. Use `org.postgresql.pljava.ResultSetProvider` when you want to dynamically create each row the function returns. Use `org.postgresql.pljava.ResultSetHandle` in cases where you want to return the result of an executed query. #### Using the ResultSetProvider Interface -This interface has two methods. The boolean `assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)` and the `void close()` method. The HAWQ query evaluator will call the `assignRowValues` repeatedly until it returns false or until the evaluator decides that it does not need any more rows. Then it calls close. +The `ResultSetProvider` interface has two methods: -You can use this interface the following way: +- `boolean assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)` +- `void close()` -```sql -=> CREATE FUNCTION javatest.listComplexTests(int, int) - RETURNS SETOF complexTest - AS 'foo.fee.Fum.listComplexTest' - IMMUTABLE LANGUAGE java; -``` +The HAWQ query evaluator calls `assignRowValues()` repeatedly until it returns false or until the evaluator decides that it does not need any more rows. At that point, it will call `close()`. + +Example: Using the `ResultSetProvider` Interface + +1. Create a new file named `FumSetOfComplex.java`, adding the following text to create a class named `FumSetOfComplex` that implements the `ResultSetProvider` interface: + + ``` shell + $ cd $PLJAVAWORK/pljex/foo/fee + $ vi FumSetOfComplex.java + ``` + + ``` java + package foo.fee; + import java.sql.ResultSet; + import java.sql.SQLException; + import java.sql.Timestamp; + import org.postgresql.pljava.ResultSetProvider; -The function maps to a static java method that returns an instance that implements the `ResultSetProvider` interface. - -```java -public class Fum implements ResultSetProvider -{ - private final int m_base; - private final int m_increment; - public Fum(int base, int increment) - { - m_base = base; - m_increment = increment; - } - public boolean assignRowValues(ResultSet receiver, int + public class FumSetOfComplex implements ResultSetProvider + { + private final int m_base; + private final int m_increment; + public FumSetOfComplex(int base, int increment) + { + m_base = base; + m_increment = increment; + } + public boolean assignRowValues(ResultSet receiver, int currentRow) - throws SQLException - { - // Stop when we reach 12 rows. - // - if(currentRow >= 12) - return false; - receiver.updateInt(1, m_base); - receiver.updateInt(2, m_base + m_increment * currentRow); - receiver.updateTimestamp(3, new + throws SQLException + { + if(currentRow >= 12) + return false; + receiver.updateInt(1, m_base); + receiver.updateInt(2, m_base + m_increment * currentRow); + receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis())); - return true; - } - public void close() - { - // Nothing needed in this example - } - public static ResultSetProvider listComplexTests(int base, + return true; + } + public void close() + { + /* Nothing needed in this example */ + } + public static ResultSetProvider listComplex(int base, int increment) - throws SQLException - { - return new Fum(base, increment); - } -} -``` + throws SQLException + { + return new FumSetOfComplex(base, increment); + } + } + ``` + + The `listComplex()` method is called once. It will return NULL if no results are available or an instance of the `ResultSetProvider` interface. The Java class `FumSetOfComplex` implements this interface to return an instance of itself. `assignRowValues()` is called repeatedly until it returns false. At that time, `close()` is called. + +3. Compile the `FumSetOfComplex` class, create the JAR file, and copy the JAR file to the default PL/Java classpath directory: + + ``` shell + $ javac -classpath /usr/local/hawq/lib/postgresql/pljava.jar FumSetOfComplex.java + $ cd ../.. + $ jar cf pljex.jar foo + $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/ + ``` + +5. Start the `psql` subsystem: + + ``` shell + $ psql -d testdb + ``` + +6. Add the JAR file to the session-level classpath: + + ``` sql + => SET pljava_classpath='pljex.jar'; + ``` + +7. Create a PL/Java UDF that invokes the `FumSetOfComplex` class `listComplexTest()` method: + + ```sql + => CREATE FUNCTION listComplexTest(int, int) + RETURNS SETOF complexTestType + AS 'foo.fee.FumSetOfComplex.listComplex' + IMMUTABLE LANGUAGE java; + ``` + +8. Execute the UDF: + + ``` sql + => SELECT listComplexTest(1,2); + ``` + + ``` + testdb=# SELECT listComplexTest(1,2); + listcomplextest + ------------------------------------- + (1,1,"2017-01-01 01:20:32.888-08") + (1,3,"2017-01-01 01:20:32.888-08") + (1,5,"2017-01-01 01:20:32.888-08") + (1,7,"2017-01-01 01:20:32.888-08") + (1,9,"2017-01-01 01:20:32.888-08") + ... + (12 rows) + ``` -The `listComplextTests` method is called once. It may return NULL if no results are available or an instance of the `ResultSetProvider`. Here the Java class `Fum` implements this interface so it returns an instance of itself. The method `assignRowValues` will then be called repeatedly until it returns false. At that time, close will be called. #### Using the ResultSetHandle Interface -This interface is similar to the `ResultSetProvider` interface in that it has a `close()` method that will be called at the end. But instead of having the evaluator call a method that builds one row at a time, this method has a method that returns a `ResultSet`. The query evaluator will iterate over this set and deliver the `ResultSet` contents, one tuple at a time, to the caller until a call to `next()` returns false or the evaluator decides that no more rows are needed. +Classes implementing the `ResultSetHandle` interface will include a method to return a `ResultSet` named `getResultSet()`. The query evaluator will iterate over this set and deliver the `ResultSet` contents, one tuple at a time, to the caller until a call to `next()` returns false or the evaluator decides that no more rows are needed. -Here is an example that executes a query using a statement that it obtained using the default connection. The SQL suitable for the deployment descriptor looks like this: +Example: Using the `ResultSetHandle` Interface -```sql -=> CREATE FUNCTION javatest.listSupers() - RETURNS SETOF pg_user - AS 'org.postgresql.pljava.example.Users.listSupers' - LANGUAGE java; -=> CREATE FUNCTION javatest.listNonSupers() - RETURNS SETOF pg_user - AS 'org.postgresql.pljava.example.Users.listNonSupers' - LANGUAGE java; -``` +1. A `Users` class is defined in the Java example package `org.postgresql.example` (refer to [`example`](https://github.com/apache/incubator-hawq/blob/master/src/pl/pljava/src/java/examples/org/postgresql/example) for example source files): -And in the Java package `org.postgresql.pljava.example` a class `Users` is added: - -```java -public class Users implements ResultSetHandle -{ - private final String m_filter; - private Statement m_statement; - public Users(String filter) - { - m_filter = filter; - } - public ResultSet getResultSet() - throws SQLException - { - m_statement = - DriverManager.getConnection("jdbc:default:connection").cr -eateStatement(); - return m_statement.executeQuery("SELECT * FROM pg_user - WHERE " + m_filter); - } - - public void close() - throws SQLException - { - m_statement.close(); - } - - public static ResultSetHandle listSupers() - { - return new Users("usesuper = true"); - } - - public static ResultSetHandle listNonSupers() - { - return new Users("usesuper = false"); - } -} -``` + ``` java + package org.postgresql.example; + + import java.sql.DriverManager; + import java.sql.ResultSet; + import java.sql.SQLException; + import java.sql.Statement; + + import org.postgresql.pljava.ResultSetHandle; + + public class Users implements ResultSetHandle + { + private final String m_filter; + private Statement m_statement; + public Users(String filter) + { + m_filter = filter; + } + public ResultSet getResultSet() + throws SQLException + { + m_statement = + DriverManager.getConnection("jdbc:default:connection").createStatement(); + return m_statement.executeQuery("SELECT * FROM pg_user + WHERE " + m_filter); + } + + public void close() + throws SQLException + { + m_statement.close(); + } + + public static ResultSetHandle listSupers() + { + return new Users("usesuper = true"); + } + + public static ResultSetHandle listNonSupers() + { + return new Users("usesuper = false"); + } + } + ``` + + The `listSupers()` and `listNonSupers()` methods each execute a query to return a `ResultSetHandle` containing a list of those users with and without superuser privileges, respectively. + +2. Copy the `examples.jar` file to the default PL/Java classpath directory: + + ``` shell + $ cp /usr/local/hawq/share/postgresql/pljava/examples.jar /usr/local/hawq/lib/postgresql/java/ + ``` + +2. Start the `psql` subsystem: + + ``` shell + $ psql -d testdb + ``` + +6. Add the `examples.jar` JAR file to the session-level classpath: + + ``` sql + => SET pljava_classpath='examples.jar'; + ``` + +7. Create PL/Java UDFs that execute the `listSupers()` and `listNonSupers()` methods in the `org.postgresql.example.Users` class: + + ``` sql + => CREATE FUNCTION plistSupers() + RETURNS SETOF pg_user + AS 'org.postgresql.example.Users.listSupers' + LANGUAGE java; + => CREATE FUNCTION plistNonSupers() + RETURNS SETOF pg_user + AS 'org.postgresql.example.Users.listNonSupers' + LANGUAGE java; + ``` + +8. Execute the UDFs: + + ``` sql + => SELECT plistSupers(); + ``` + + ``` + plistsupers + ------------------------------- + (gpadmin,10,t,t,t,********,,) + (1 row) + ``` + + ``` sql + => SELECT plistNonSupers(); + ``` + + ``` + plistnonsupers + --------------- + (0 rows) + ``` + + ## <a id="usingjdbc"></a>Using JDBC -PL/Java contains a JDBC driver that maps to the PostgreSQL SPI functions. A connection that maps to the current transaction can be obtained using the following statement: +PL/Java includes a JDBC driver. This driver invokes HAWQ internal SPI routines. The driver is essential; it is common for functions to make calls back to the database to fetch data. When PL/Java user-defined functions fetch data, they must use the same transactional boundaries that are used by the main function that entered the PL/Java execution context. -```java +You can obtain a PL/Java JDBC driver connection mapping to the current transaction with the following Java statement: + +``` java Connection conn = DriverManager.getConnection("jdbc:default:connection"); ``` -After obtaining a connection, you can prepare and execute statements similar to other JDBC connections. These are limitations for the PL/Java JDBC driver: +After obtaining a connection, you can prepare and execute statements similar to other JDBC connections. Refer to the `Users` class Java source code from the example above. + +Limitations of the PL/Java JDBC driver include the following: -- The transaction cannot be managed in any way. Thus, you cannot use methods on the connection such as: +- A transaction cannot be managed in any way. As such, you cannot use the following methods on the JDBC connection: - `commit()` - `rollback()` - `setAutoCommit()` - `setTransactionIsolation()` -- Savepoints are available with some restrictions. A savepoint cannot outlive the function in which it was set and it must be rolled back or released by that same function. -- A ResultSet returned from `executeQuery()` are always `FETCH_FORWARD` and `CONCUR_READ_ONLY`. -- Meta-data is only available in PL/Java 1.1 or higher. +- Savepoints are available, with some restrictions. A savepoint cannot outlive the function in which it was set, and it must be rolled back or released by that same function. +- A `ResultSet` returned from `executeQuery()` is always `FETCH_FORWARD` and `CONCUR_READ_ONLY`. - `CallableStatement` (for stored procedures) is not implemented. -- The types `Clob` or `Blob` are not completely implemented, they need more work. The types `byte[]` and `String` can be used for `bytea` and `text` respectively. +- The types `Clob` or `Blob` are not completely implemented. Use the types `byte[]` and `String` for `bytea` and `text`, respectively. --- End diff -- "or" -> "and"
--- If your project is set up for it, you can reply to this email and have your reply appear on GitHub as well. If your project does not have this feature enabled and wishes so, or if the feature is enabled but not working, please contact infrastructure at infrastruct...@apache.org or file a JIRA ticket with INFRA. ---