Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id C246A2009F3 for ; Fri, 6 May 2016 01:39:53 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id C0F64160A07; Thu, 5 May 2016 23:39:53 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 6C340160A05 for ; Fri, 6 May 2016 01:39:51 +0200 (CEST) Received: (qmail 75742 invoked by uid 500); 5 May 2016 23:39:50 -0000 Mailing-List: contact commits-help@trafodion.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: commits@trafodion.apache.org Delivered-To: mailing list commits@trafodion.apache.org Received: (qmail 75733 invoked by uid 99); 5 May 2016 23:39:50 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd4-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 05 May 2016 23:39:50 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd4-us-west.apache.org (ASF Mail Server at spamd4-us-west.apache.org) with ESMTP id 1DB4EC0176 for ; Thu, 5 May 2016 23:39:50 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd4-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -3.221 X-Spam-Level: X-Spam-Status: No, score=-3.221 tagged_above=-999 required=6.31 tests=[KAM_ASCII_DIVIDERS=0.8, KAM_LAZY_DOMAIN_SECURITY=1, RCVD_IN_DNSWL_HI=-5, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RP_MATCHES_RCVD=-0.001] autolearn=disabled Received: from mx2-lw-us.apache.org ([10.40.0.8]) by localhost (spamd4-us-west.apache.org [10.40.0.11]) (amavisd-new, port 10024) with ESMTP id zdZNUUlI0sRW for ; Thu, 5 May 2016 23:39:36 +0000 (UTC) Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx2-lw-us.apache.org (ASF Mail Server at mx2-lw-us.apache.org) with SMTP id 222F360DB2 for ; Thu, 5 May 2016 23:39:33 +0000 (UTC) Received: (qmail 74469 invoked by uid 99); 5 May 2016 23:39:32 -0000 Received: from git1-us-west.apache.org (HELO git1-us-west.apache.org) (140.211.11.23) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 05 May 2016 23:39:32 +0000 Received: by git1-us-west.apache.org (ASF Mail Server at git1-us-west.apache.org, from userid 33) id 691BFDF9C4; Thu, 5 May 2016 23:39:32 +0000 (UTC) Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: dbirdsall@apache.org To: commits@trafodion.incubator.apache.org Date: Thu, 05 May 2016 23:39:34 -0000 Message-Id: In-Reply-To: References: X-Mailer: ASF-Git Admin Mailer Subject: [03/22] incubator-trafodion git commit: JDBCT$ Programmer's Reference Guide archived-at: Thu, 05 May 2016 23:39:53 -0000 JDBCT$ Programmer's Reference Guide New guide. Added missing SPJ Guide file. Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/c2116c2b Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/c2116c2b Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/c2116c2b Branch: refs/heads/master Commit: c2116c2b3dd857be5cd28160e0f18d2daca73140 Parents: e5dcfc1 Author: Gunnar Tapper Authored: Fri Apr 29 00:13:43 2016 -0600 Committer: Gunnar Tapper Committed: Fri Apr 29 00:13:43 2016 -0600 ---------------------------------------------------------------------- docs/jdbct4ref_guide/pom.xml | 297 +++ .../src/asciidoc/_chapters/about.adoc | 174 ++ .../src/asciidoc/_chapters/accessing.adoc | 910 ++++++++ .../asciidoc/_chapters/avoiding_mismatch.adoc | 106 + .../src/asciidoc/_chapters/code_examples.adoc | 56 + .../src/asciidoc/_chapters/compliance.adoc | 386 ++++ .../src/asciidoc/_chapters/introduction.adoc | 53 + .../src/asciidoc/_chapters/lob_data.adoc | 553 +++++ .../src/asciidoc/_chapters/lob_management.adoc | 288 +++ .../src/asciidoc/_chapters/messages.adoc | 1943 ++++++++++++++++++ .../asciidoc/_chapters/properties_detail.adoc | 959 +++++++++ .../asciidoc/_chapters/properties_overview.adoc | 245 +++ .../src/asciidoc/_chapters/tracing_logging.adoc | 195 ++ docs/jdbct4ref_guide/src/asciidoc/index.adoc | 74 + docs/jdbct4ref_guide/src/images/lob_tables.jpg | Bin 0 -> 16241 bytes .../src/resources/acknowledgements.txt | 38 + .../src/resources/source/blob_example.java | 202 ++ .../src/resources/source/clob_example.java | 200 ++ .../src/asciidoc/_chapters/sample_spjs.adoc | 6 +- docs/spj_guide/src/asciidoc/index.adoc | 3 +- .../src/resources/source/create_demo.sql | 14 + 21 files changed, 6698 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/pom.xml ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/pom.xml b/docs/jdbct4ref_guide/pom.xml new file mode 100644 index 0000000..413710e --- /dev/null +++ b/docs/jdbct4ref_guide/pom.xml @@ -0,0 +1,297 @@ + + + + 4.0.0 + org.apache.trafodion + jdbct4-ref-guide + ${env.TRAFODION_VER} + pom + Trafodion JDBC Type 4 Programmer's Reference Guide + This document describes how to use the JDBC Type 4 Driver. + http://trafodion.incubator.apache.org + 2015 + + + org.apache.trafodion + trafodion + 1.3.0 + ../../pom.xml + + + + + + The Apache Software License, Version 2.0 + http://www.apache.org/licenses/LICENSE-2.0.txt + repo + A business-friendly OSS license + + + + + Apache Software Foundation + http://www.apache.org + + + + JIRA + http://issues.apache.org/jira/browse/TRAFODION + + + + scm:git:http://git-wip-us.apache.org/repos/asf/incubator-trafodion.git + scm:git:https://git-wip-us.apache.org/repos/asf/incubator-trafodion.git + https://git-wip-us.apache.org/repos/asf?p=incubator-trafodion.git + HEAD + + + + Jenkins + https://jenkins.esgyn.com + + + + UTF-8 + 1.5.2.1 + 1.5.0-alpha.11 + 1.5.4 + 2.0.2 + 9.0.4.0 + + + + + rubygems-proxy-releases + RubyGems.org Proxy (Releases) + http://rubygems-proxy.torquebox.org/releases + + true + + + false + + + + + + + rubygems + prawn + ${rubygems.prawn.version} + gem + provided + + + org.jruby + jruby-complete + ${jruby.version} + + + org.asciidoctor + asciidoctorj + ${asciidoctorj.version} + + + + + + + de.saumya.mojo + gem-maven-plugin + 1.0.10 + + + ${jruby.version} + ${project.build.directory}/gems + ${project.build.directory}/gems + + + + + initialize + + + + + + org.apache.maven.plugins + maven-resources-plugin + 2.7 + + UTF-8 + + false + + + + + org.asciidoctor + asciidoctor-maven-plugin + ${asciidoctor.maven.plugin.version} + + + org.asciidoctor + asciidoctorj-pdf + ${asciidoctorj.pdf.version} + + + org.asciidoctor + asciidoctorj + ${asciidoctorj.version} + + + + ${basedir}/src + + + + generate-html-doc + + process-asciidoc + + site + + book + html5 + coderay + ${basedir}/target/site + + ${basedir}/../shared/google-analytics-postprocessor.rb + + + + ${basedir}/../shared/trafodion-manuals.css + ${env.TRAFODION_VER} + Trafodion + ${basedir}/../shared/trafodion-logo.jpg + user@trafodion.incubator.apache.org + http://trafodion.incubator.apache.org/docs + ${maven.build.timestamp} + UA-72491210-1 + + + + + generate-pdf-doc + site + + process-asciidoc + + + book + pdf + coderay + ${basedir}/target + + ${basedir}/../shared + trafodion + font + + + + - + ${env.TRAFODION_VER} + Trafodion + ${basedir}/../shared/trafodion-logo.jpg + user@trafodion.incubator.apache.org + http://trafodion.incubator.apache.org/docs + ${maven.build.timestamp} + + + + + + + + org.apache.maven.plugins + maven-antrun-plugin + 1.8 + false + + + populate-release-directories + post-site + + + + + + + + + + + + + + + + + + + + + run + + + + + + + + + + true + + + org.apache.maven.plugins + maven-project-info-reports-plugin + 2.8 + + + + + + + + + + + + + trafodion.incubator.apache.org + Trafodion Website at incubator.apache.org + + file:///tmp + + + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/src/asciidoc/_chapters/about.adoc ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/src/asciidoc/_chapters/about.adoc b/docs/jdbct4ref_guide/src/asciidoc/_chapters/about.adoc new file mode 100644 index 0000000..69bf91d --- /dev/null +++ b/docs/jdbct4ref_guide/src/asciidoc/_chapters/about.adoc @@ -0,0 +1,174 @@ +//// +/** +* @@@ START COPYRIGHT @@@ +* +* Licensed to the Apache Software Foundation (ASF) under one +* or more contributor license agreements. See the NOTICE file +* distributed with this work for additional information +* regarding copyright ownership. The ASF licenses this file +* to you under the Apache License, Version 2.0 (the +* "License"); you may not use this file except in compliance +* with the License. You may obtain a copy of the License at +* +* http://www.apache.org/licenses/LICENSE-2.0 +* +* Unless required by applicable law or agreed to in writing, +* software distributed under the License is distributed on an +* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +* KIND, either express or implied. See the License for the +* specific language governing permissions and limitations +* under the License. +* +* @@@ END COPYRIGHT @@@ + */ +//// + += About This Document + +This document describes how to use the {project-name} JDBC Type 4 Driver (subsequently called the Type 4 driver). +This driver provides Java applications running on a foreign platform with JDBC access to {project-name}. + + +== Intended Audience +This {project-name} JDBC Type 4 Driver Programmer's Reference Guide is for +experienced Java programmers who want to access {project-name} SQL +databases. + +This document assumes you are already familiar with the Java +documentation, which is located at http://docs.oracle.com/en/java/. + +== New and Changed Information +This is a new manual. + +== Notation Conventions +This list summarizes the notation conventions for syntax presentation in this manual. + +* UPPERCASE LETTERS ++ +Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. ++ +``` +SELECT +``` + +* lowercase letters ++ +Lowercase letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. ++ +``` +file-name +``` + +<<< +* [ ] Brackets ++ +Brackets enclose optional syntax items. ++ +``` +DATETIME [start-field TO] end-field +``` ++ +A group of items enclosed in brackets is a list from which you can choose one item or none. ++ +The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines. ++ +For example: ++ +``` +DROP SCHEMA schema [CASCADE] +DROP SCHEMA schema [ CASCADE | RESTRICT ] +``` + +* { } Braces ++ +Braces enclose required syntax items. ++ +``` +FROM { grantee [, grantee ] ... } +``` ++ +A group of items enclosed in braces is a list from which you are required to choose one item. ++ +The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. ++ +For example: ++ +``` +INTERVAL { start-field TO end-field } +{ single-field } +INTERVAL { start-field TO end-field | single-field } +``` +* | Vertical Line ++ +A vertical line separates alternatives in a horizontal list that is enclosed in brackets or braces. +``` +{expression | NULL} +``` + +* … Ellipsis ++ +An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. ++ +``` +ATTRIBUTE[S] attribute [, attribute] ... +{, sql-expression } ... +``` ++ +An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. ++ +For example: ++ +``` +expression-n ... +``` + +* Punctuation ++ +Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. ++ +``` +DAY (datetime-expression) +@script-file +``` ++ +Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. ++ +For example: ++ +``` +"{" module-name [, module-name] ... "}" +``` + +<<< +* Item Spacing ++ +Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. ++ +``` +DAY (datetime-expression) DAY(datetime-expression) +``` ++ +If there is no space between two items, spaces are not permitted. In this example, no spaces are permitted between the period and any other items: ++ +``` +myfile.sh +``` + +* Line Spacing ++ +If the syntax of a command is too long to fit on a single line, each continuation line is indented three spaces and is separated from the preceding line by a blank line. ++ +This spacing distinguishes items in a continuation line from items in a vertical list of selections. ++ +``` +match-value [NOT] LIKE _pattern + [ESCAPE esc-char-expression] +``` + +<<< +== Comments Encouraged +We encourage your comments concerning this document. We are committed to providing documentation that meets your +needs. Send any errors found, suggestions for improvement, or compliments to {project-support}. + +Include the document title and any comment, error found, or suggestion for improvement you have concerning this document. + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/src/asciidoc/_chapters/accessing.adoc ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/src/asciidoc/_chapters/accessing.adoc b/docs/jdbct4ref_guide/src/asciidoc/_chapters/accessing.adoc new file mode 100644 index 0000000..7a6a647 --- /dev/null +++ b/docs/jdbct4ref_guide/src/asciidoc/_chapters/accessing.adoc @@ -0,0 +1,910 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[accessing-project-name-sql-databases]] += Accessing {project-name} SQL Databases + +[[data-sources]] +== Data Sources + +The term *data source* logically refers to a database or other data +storage entity. A JDBC (client) data source is physically a Java object that +contains properties such as the URL of the physical database, the +catalog to use when connecting to this database, and the schema to use +when connecting to this database. The JDBC data source also contains +methods for obtaining a JDBC connection to the underlying database. + +[[jdbc-data-source-client-side]] +=== JDBC Data Source (client-side) + +All JDBC data source classes implement either the `javax.sql.DataSource` +interface or the `javax.sql.ConnectionPoolDataSource` interface. The Type +4 driver data source classes are `org.trafodion.t4jdbc.HPT4DataSource` and +`org.trafodion.t4jdbc.HPT4ConnectionPoolDataSource`. (These classes are +defined by the JDBC 3.0 specification.) + +Typically, a user or system administrator uses a tool to create a data +source, and then registers the data source by using a JNDI service +provider. At run time, a user application typically retrieves the data +source through JNDI, and uses the data source's methods to establish a +connection to the underlying database. + +A DataSource object maps to an instance of a database. In the Type 4 +driver product, the DataSource object acts as an interface between the +application code and the database and enables connection with an DCS +data source. + +[[security]] +== Security + +Clients connect to the {project-name} platform with a valid user name +and ID, using standard JDBC 3.0 APIs. An application can make multiple +connections using different user IDs, and creating different Connection +objects. + +The Type 4 driver provides for user name and password authentication. +The password is encrypted with a proprietary algorithm provided by DCS. + +NOTE: There is no secure wire communication such as SSL provided for the +communication between Type 4 driver and the {project-name} platform. + +<<< +[[connection-by-using-the-datasource-interface]] +== Connection by Using the DataSource Interface + +The `javax.sql.DataSource` interface is the preferred way to establish a +connection to the database because this interface enhances the application +portability. Portability is achieved by allowing the application to use a +logical name for a data source instead of providing driver-specific information +in the application. A logical name is mapped to a `javax.sql.DataSource` +object through a naming service that uses the Java Naming and Directory +Interface (JNDI). Using this DataSource method is particularly recommended +for application servers. + +When an application requests a connection by using the `getConnection` method +in the `DataSource`, then the method returns a `Connection` object. + +A `DataSource` object is a factory for `Connection` objects. An object that +implements the `DataSource` interface is typically registered with a JNDI +service provider. + +[[overview-of-tasks-to-deploy-datasource-objects]] +=== Overview of Tasks to Deploy DataSource Objects + +Before an application can connect to a `DataSource` object, typically +the system administrator deploys the `DataSource` object so that +the application programmers can start using it. + +Data source properties are usually set by a system administrator using +a GUI tool as part of the installation of the data source. Users to +the data source do not get or set properties. Management tools can get +at properties by using introspection. + +Tasks involved in creating and registering a database object are: + +1. Creating an instance of the `DataSource` class. +2. Setting the properties of the `DataSource` object. +3. Registering the `DataSource` object with a naming service that uses +the Java Naming and Directory Interface (JNDI) API. + +An instance of the `DataSource` class and the `DataSource` object +properties are usually set by an application developer or system +administrator using a GUI tool as part of the installation of the +data source. If you are using an installed data source, then see +<>. + +The subsequent topics show an example of performing these tasks programmatically. + +For more information about using data sources, see https://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html[Connecting with DataSource Objects] +in the https://docs.oracle.com/javase/tutorial/jdbc/TOC.html[JDBC(TM) Database Access: Table of Contents] documentation +or other information available in the field. + +<<< +[[datasource-object-properties]] +=== DataSource Object Properties + +A `DataSource` object has properties that identify and describe the actual +data source that the object represents. These properties include such +information as the URL (the primary IP address or host name of the database), +the database schema and catalog names, the location of the database server, +the name of the database, and so forth. + +For details about Type 4 driver properties that you can use with the `DataSource` object, see <>. + +[[programmatically-creating-an-instance-of-the-datasource-class]] +=== Programmatically Creating an Instance of the DataSource Class + +A JDBC application can set `DataSource` properties programmatically and +register with a DataSource object. To get or set `DataSource` object properties programmatically, use the +appropriate getter or setter methods on the `HPT4DataSource` object or +the `HPT4ConnectionPoolDataSource` object. + +*Example* + +[source, java] +---- +HPT4DataSource temp = new HPT4DataSource() ; +temp.setCatalog( "Seabase" ) ; +---- + +In the following example, the code fragment illustrates the methods that a +`DataSource` object `ds` needs to include if the object supports the +`serverDataSource` property `ds.setServerDataSource( "my_server_datasource" )`. +In this example, the code shows setting properties for the `HPT4DataSource` object +to use the Type 4 driver to access a {project-name} database: + +[source, java] +---- +HPT4DataSource ds = new HPT4DataSource() ; + +ds.setUrl( "jdbc:hpt4jdbc://:18650/" ); +ds.setCatalog( "Seabase" ) ; +ds.setSchema( "myschema" ) ; +ds.setUser( "gunnar" ) ; +ds.setPassword( "my_userpassword" ) ; + +// Properties relevant for Type 4 connection pooling. +// Set ds.setMaxPoolSize(-1) for turning OFF connection pooling +ds.setMaxPoolSize( "10000" ) ; +ds.setMinPoolSize( "1000" ) ; + +// Properties relevant for Type 4 statement pooling. +// Set ds.setMaxStatement(0) for turning statement pooling OFF +// Statement pooling is enabled only when connection pooling is +// enabled. +ds.setMaxStatements( "7000" ) ; +---- + +This technique essentially builds a properties file. For more information, +see <>. + +[[programmatically-registering-the-datasource-object]] +=== Programmatically Registering the DataSource Object + +In the following example, the code shows how to register, programmatically, +the `HPT4DataSource` object `ds` that was created using the preceding code with JNDI. + +[source, java] +---- +java.util.Hashtable env = new java.util.Hashtable() ; +env.put( Context.INITIAL_CONTEXT_FACTORY, "Factory class name here" ) ; + +javax.naming.Context ctx = new javax.naming.InitialContext( env ) ; +ctx.rebind( "myDataSource", ds ) ; +---- + +[[retrieving-a-datasource-instance-by-using-jndi-and-connecting-to-the-data-source]] +=== Retrieving a DataSource Instance by Using JNDI and Connecting to the Data Source +Typically, the JDBC application looks up the data source JNDI name from a +context object. Once the application has the `DataSource` object, then the application +does a `getConnection()` call on the data source and gets a connection. + +The steps that JDBC application does to connect to and use the data source associated +with the database are listed below together with the application code to perform the +operation. + +1. Import the packages. ++ +[source, java] +---- +import javax.naming.* ; +import java.sql.* ; +import javax.sql.DataSource ; +---- + +2. Create the initial context. ++ +[source, java] +---- +Hashtable env = new Hashtable() ; +env.put( Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory" ) ; +try +{ + Context ctx = new InitialContext( env ) ; +} +catch( ... ) +{ +... +} +---- ++ +<<< +3. Look up the JNDI name associated with the data source `myDataSource`, where `myDataSource` +is the logical name that will be associated with the real-world data source - server. ++ +[source, java] +---- +DataSource ds = (DataSource)ctx.lookup( "myDataSource" ) ; +---- + +4. Create the connection using the data source. ++ +[source, java] +---- +con = ds.getConnection() ; +---- + +5. Do work with the connection. The following statements are just a simple example. ++ +[source, java] +---- +stmt = con.createStatement() ; +try +{ + stmt.executeUpdate( "drop table tdata" ) ; +} +catch ( SQLException e ) {} +---- + +[[specifying-the-properties-file-that-configures-the-data-source]] +=== Specifying the Properties File that Configures the Data Source + +To use the properties file method to configure a `DataSource` object, the properties +file must exist on disk and contain the `property_name=property_value` pairs that +configure the data source. +See <> +for more information about creating this file. + +When the JDBC application makes the connection, then the application should +pass the properties file as a command-line parameter: + +``` +java -Dhpt4jdbc.properties= +``` + +[[connection-by-using-the-drivermanager-class]] +== Connection by Using the DriverManager Class + +The `java.sql.DriverManager` class is widely used to get a connection, but +is less portable than the `DataSource` class. The `DriverManager` class +works with the Driver interface to manage the set of drivers loaded. +When an application issues a request for a connection using the +`DriverManager.getConnection` method and provides a URL, the `DriverManager` +finds a suitable driver that recognizes this URL and obtains a database +connection using that driver. + +`org.trafodion.t4jdbc.HPT4Driver` is the Type 4 driver class that +implements the `java.sql.Driver` interface. + +<<< +[[loading-and-registering-the-driver]] +=== Loading and Registering the Driver + +Before connecting to the database, the application loads the Driver +class and registers the Type 4 driver with the DriverManager class in +one of the following ways: + +* Specifies the Type 4 driver class in the `-Djdbc.drivers` option in the +command line of the Java program: ++ +``` +-Djdbc.drivers=org.trafodion.t4jdbc.HPT4Driver +``` + +* Uses the `Class.forName` method programmatically within the application: ++ +[source, java] +---- +Class.forName("org.trafodion.t4jdbc.HPT4Driver") +---- + +* Adds the Type 4 driver class to the `java.lang.System` property +`jdbc.drivers` property within the application: ++ +``` +jdbc.drivers=org.trafodion.t4jdbc.HPT4Driver +``` + +<<< +[[establishing-the-connection]] +=== Establishing the Connection + +The `DriverManager.getConnection` method accepts a string containing a +Type 4 driver URL. The JDBC URL for the Type 4 driver is + +``` +jdbc:hpt4jdbc://:3700/[:][property=value[;property2=value2]...] +``` + +[cols="40%,60%", options="header"] +|=== +| Parameter | Usage +| `` | The primary IP address or host name for the {project-name} database. +| `37800` | The port number for the {project-name} SQL database. +| `property = value` and `property2=value2` | Specifies a Type 4 driver property name-property value pair. The pairs must be separated by a +semicolon (`;`). For example, `T4LogLevel=ALL;T4LogFile=temp1.log`. +|=== + +For information about the properties file, see <>. + +To establish a connection, the JDBC application can use this code: + +[source, java] +---- +Class.forName( "org.trafodion.t4jdbc.HPT4Driver" ) ; //loads the driver + +String url = "jdbc:hpt4jdbc://:37800/" + +Connection con = DriverManager.getConnection( url, "userID", "Passwd" ) ; +---- + +The variable con represents a connection to the data source that can be +used to create and execute SQL statements. + +[[guidelines-for-connections-using-the-driver-manager]] +=== Guidelines for Connections Using the Driver Manager + +* The Type 4 driver defines a set of properties that you can use to +configure the driver. For detailed information about these properties, +see <>. +* Java applications can specify the properties in these ways (listed in +the order of precedence): ++ +1. Using the `java.util.Properties` parameter in the `getConnection` method of DriverManager class. + +2. Using the database URL in the `DriverManager.getconnection` method, where the URL is: ++ +``` +jdbc:hpt4jdbc://:37800/:property=value +``` ++ +`` is the primary IP address or host name for the {project-name} database. ++ +<<< +3. Using a properties file for the JDBC driver. The properties file is +passed as a command-line parameter. The format to enter the properties +file in the command line is: ++ +``` +-Dhpt4jdbc.properties= +``` ++ +For example, `-Dhpt4jdbc.properties=C:\temp\t4props` ++ +For information about the properties file, see <>. +4. Using JDBC properties with the `-D` option in the command line. If +used, this option applies to all JDBC connections using the +`DriverManager` within the Java application. The format in the command +line is: ++ +``` +-Dhpt4jdbc.property_name= +``` ++ +For example, `-Dhpt4jdbc.maxStatements=1024` + +<<< +[[connection-pooling]] +== Connection Pooling + +The Type 4 driver provides an implementation of connection pooling, +where a cache of physical database connections are assigned to a client +session and reused for the database activity. If connection pooling is +active, connections are not physically closed. The connection is +returned to its connection pool when the `Connection.close()` method is +called. The next time a connection is requested by the client, the +driver will return the pooled connection, and not a new physical +connection. + +* The connection pooling feature is available when the JDBC application +uses either the `DriverManager` class or `DataSource` interface to obtain a +JDBC connection. The connection pool size is determined by the +`maxPoolSize` property value and `minPoolSize` property value. + +* By default, connection pooling is disabled. To enable connection +pooling, set the maxPoolSize property to an integer value greater than 0 +(zero). + +* Manage connection pooling by using these Type 4 driver properties: + +** `maxPoolSize` under <> +** `minPoolSize` under <> +** `initialPoolSize` under <> +** `maxStatements` under <> + +* When used with the DriverManager class, the Type 4 driver has a +connection-pool manager that determines which connections are pooled +together by a unique value for these combination of properties: ++ +``` +url +catalog +schema +username +password +serverDataSource +``` ++ +Therefore, connections that have the same values for the combination of +a set of properties are pooled together. ++ +NOTE: The connection-pooling property values used at the first +connection of a given combination are effective throughout the life of +the process. An application cannot change any of these property values +after the first connection for a given combination. + +<<< +[[statement-pooling]] +== Statement Pooling + +The statement pooling feature allows applications to reuse the +PreparedStatement object in the same way that they can reuse a +connection in the connection pooling environment. Statement pooling is +completely transparent to the application. + +[[guidelines-for-statement-pooling]] +=== Guidelines for Statement Pooling + +* To enable statement pooling, set the `maxStatements` property to an +integer value greater than 0 and enable connection pooling. For more +information, see <> and +<>. + +* Enabling statement pooling for your JDBC applications might +dramatically improve the performance. + +* Explicitly close a prepared statement by using the `Statement.close` +method because `PreparedStatement` objects that are not in scope are also +not reused unless the application explicitly closes them. + +* To ensure that your application reuses a `PreparedStatement`, call +either of these methods: + +** `Statement.close method`: called by the application. +** `Connection.close method`: called by the application. All the +`PreparedStatement` objects that were in use are ready to be reused when +the connection is reused. + +[[troubleshooting-statement-pooling]] +=== Troubleshooting Statement Pooling + +Note the following Type 4 driver implementation details if you are +troubleshooting statement pooling: + +* The Type 4 driver looks for a matching `PreparedStatement` object in the +statement pool and reuses the `PreparedStatement`. The matching criteria +include the SQL string, catalog, current schema, current transaction +isolation, and result set holdability. ++ +If the Type 4 driver finds the matching `PreparedStatement` object, then the +driver returns the same `PreparedStatement` object to the application for reuse +and marks the `PreparedStatement` object as in use. + +* The algorithm, _earlier used are the first to go_, is used to make +room for caching subsequently generated `PreparedStatement` objects when +the number of statements reaches the `maxStatements` limit. + +* The Type 4 driver assumes that any SQL CONTROL statements in effect at +the time of execution or reuse are the same as those in effect at the time +of SQL compilation. ++ +If this condition is not true, then reuse of a `PreparedStatement` object might +result in unexpected behavior. + +* Avoid recompiling to yield performance improvements from statement +pooling. The SQL executor automatically recompiles queries when certain conditions are met. +Some of these conditions are: + +** A run-time version of a table has a different redefinition timestamp +than the compile-time version of the same table. + +** An existing open operation on a table was eliminated by a DDL or SQL +utility operation. + +** The transaction isolation level and access mode at execution time is +different from that at the compile time. + +* When a query is recompiled, then the SQL executor stores the recompiled query; +therefore, the query is recompiled only once until any of the previous conditions +are met again. + +* The Type 4 driver does not cache `Statement` objects. + +[[thread-safe-database-access]] +== Thread-Safe Database Access + +In the Type 4 driver, API layer classes are implemented as +instance-specific objects to ensure thread safety: + +* `HPT4DataSource.getConnection()` is implemented as a synchronized method +to ensure thread safety in getting a connection. + +* Once a connection is made, the `Connection` object is instance-specific. + +* If multiple statements are run on different threads in a single +connection, then statement objects are serialized to prevent data corruption. + +[[update-where-current-of-operations]] +== "Update . . . Where Current of" Operations + +The fetch size on a `ResultSet` must be 1 when performing an +`update . . . where current of` _cursor_ SQL statement. + +If the value of the fetch size is greater than 1, the result of the +`update . . . where current` of operation might be one of the following: + +* An incorrect row might be updated based on the actual cursor position. + +* An SQLException might occur because the cursor being updated might +have already been closed. + +The following is an example of setting a result set's fetch size to 1 +and executing an `update . . . where current` of _cursor_ SQL statement. + +[source, java] +---- +ResultSet rs ; + ... + + rs.setFetchSize( 1 ) ; + String st1 = rs.getCursorName() ; + + Statement stmt2 = + connection.createStatement( ResultSet.TYPE_FORWARD_ONLY + , ResultSet.CONCUR_UPDATABLE + ) ; + stmt2.executeUpdate( "UPDATE cat2.sch2.table1 + SET j = 'update row' WHERE CURRENT OF " + + st1 + ) ; +---- + +[[infostats-command-for-obtaining-query-costs]] +== INFOSTATS Command for Obtaining Query Costs + +The INFOSTATS command reports the roll-up costs of a particular query. +INFOSTATS is a pass-through command that collects statistics for a +prepared statement. Statistics are returned to the JDBC application as a +result set as soon as the prepare is finished. The result set has these +columns: + +[cols="30%,70%",options="header" ] +|=== +| Column | Description +| `Query ID (SQL_CHAR)` | The unique identifier for the query. +| `CPUTime (SQL_DOUBLE)` | An estimate of the number of seconds of processor time it might take to execute the instructions for this query. A value of 1.0 is 1 second. +| `IOTime (SQL_DOUBLE)` | An estimate of the number of seconds of I/O time (seeks plus data transfer) to perform the I/O for this query. +| `MsgTime (SQL_DOUBLE)` | An estimate of the number of seconds it takes for the messaging for this query. The estimate includes the time for the number of local and remote +messages and the amount of data sent. +| `IdleTime (SQL_DOUBLE)` | An estimate of the maximum number of seconds to wait for an event to happen for this query. The estimate includes the amount of time to open +a table or start an ESP process. +| `TotalTime (SQL_DOUBLE)` | Estimated cost associated to execute the query. +| `Cardinality (SQL_DOUBLE)` | Estimated number of rows that will be returned. +|=== + +<<< +[[use-of-the-infostats-command]] +=== Use of the INFOSTATS Command + +The INFOSTATS command can only be used with PreparedStatement objects. +The syntax is: + +``` +INFOSTATS cursor_name +``` + +where `cursor_name` is the name of the prepared statement. If the cursor name is case-sensitive, +then enclose it in single quotes. + +To get the cursor name, use the `getStatementLabel()` method that is +defined for the {project-name} JDBC Type 4 driver with class: + +[source, java] +---- +org.trafodion.t4jdbc.HPT4PreparedStatement: public String +getStatementLabel() ; +---- + +*Considerations* + +* You can use INFOSTATS in these methods only: ++ +[source, java] +---- +java.sql.Statement.executeQuery(String sql) +java.sql.Statement.execute(String sql) +---- + +* `setCursorName` is not supported with INFOSTATS. + +* If you invoke INFOSTATS incorrectly, the Type 4 driver issues this error: ++ +``` +Message: INFOSTATS command can only be executed + by calling execute(String sql) method. + Sqlstate HY000 + Sqlcode 29180 +``` + +<<< +*Example of INFOSTATS* + +[source, java] +---- +Statement s = conn.createStatement( ) ; + +HPT4PreparedStatement p = + (HPT4PreparedStatement)conn.prepareStatement( + "SELECT * FROM t WHERE i = ?" ) ; + +boolean results = s.execute( "INFOSTATS " + p.getStatementLabel() ) ; + +if ( results ) +{ + ResultSet rs = s.getResultSet( ) ; + + while ( rs.next( ) ) + { + //process data + } +} +---- + +*Sample Output* + +``` +QueryID: MXID001001128212016369912348191_16_SQL_CUR_9829657 +CPUTime: 0.09975778464794362 +IOTime: 0.10584000146627659 +MsgTime: 0.09800000134418951 +IdleTime: 0.09800000134418951 +TotalTime: 0.10584000146627659 +Cardinality: 100.0 +``` + +<<< +[[internationalization-support]] +== Internationalization Support + +[[when-string-literals-are-used-in-applications]] +=== When String Literals Are Used in Applications + +Internationalization support in the driver affects the handling of +string literals. The Type 4 driver handles string literals in two +situations. + +1. When the driver processes an SQL statement. For example, ++ +[source, java] +---- +Statement stmt = connection.getStatement() ; + +stmt.execute( "SELECT * FROM table1 WHERE col1 = 'abcd'" ) ; +---- + +2. When the driver processes JDBC parameters. For example, ++ +[source, java] +---- +PreparedStatement pStmt = connection.prepareStatement( + "SELECT * FROM table1 WHERE col1 = ?" ) ; +pStmt.setString( 1, "abcd" ) ; +---- + +To convert a string literal from the Java to an array of bytes for +processing by the {project-name}, the Type 4 driver uses +the column type in the database. + +[[controlling-string-literal-conversion-by-using-the-character-set-properties]] +=== Controlling String Literal Conversion by Using the Character-Set Properties + +The Type 4 driver provides character-set mapping properties. These +properties allow you to explicitly define the translation of internal +SQL character-set formats to and from the Java string Unicode (`UnicodeBigUnmarked`) +encoding. + +The Type 4 driver provides character-set mapping properties through key +values as shown in the following table. + +[cols="50%,50%",options="header" ] +|=== +| Key | Default Value +| `ISO88591` | `ISO88591_1` +| `KANJI` | `SJIS` +| `KSC5601` | `EUC_KR` +|=== + +<<< +A description of these character sets appears in table below, which +summarizes the character sets supported by {project-name}. + +[cols="25%,35%,40%",options="header" ] +|=== +| {project-name} Character Set | Corresponding Java Encoding Set^1^ | Description +| ISO88591 | ISO88591_1 | Single-character, 8-bit character-data type ISO88591 supports English and other Western European languages. +|=== + +^1^ Canonical Name for `java.io` and `java.lang` API. + +For detailed information, see <>. + +[[using-the-character-set-properties]] +==== Using the Character-Set Properties + +The `java.sql.PreparedStatement` class contains the methods `setString()` +and `setCharacterStream()`. These methods take a String and Reader +parameter, respectively. + +The `java.sql.ResultSet` class contains the methods `getString()` and +`getCharacterStream()`. These methods return a String and Reader, respectively. + +[[retrieving-a-column]] +===== Retrieving a Column + +When you retrieve a column as a string (for example, call the +`getString()` or `getCharacterStream` methods), the Type 4 driver uses the +character-set mapping property key to instantiate a String object (where +that key corresponds to the character set of the column). + +*Example* + +The following `SQL CREATE TABLE` statement creates a table that has an +`ISO88591` column. + +[source, sql] +---- +CREATE TABLE t1 ( c1 CHAR(20) CHARACTER SET ISO88591 ) ; +---- + +The JDBC program uses the following java command to set the ISO88591 +property and issues the `getString()` method. + +[source, java] +---- +java -Dhpt4jdbc.ISO88591=SJIS test1.java + +// The following method invocation returns a String object, which +// was created using the "SJIS" Java canonical name as the charset +// parameter to the String constructor. +String s1 = rs.getString( 1 ) ; // get column 1 as a String +---- + +[[setting-a-parameter]] +===== Setting a Parameter + +When you set a parameter by using a String (for example, call the +`setString()` method), the Type 4 driver uses the key's value when +generating the internal representation of the String (where that +key corresponds to the character set of the column). The +character-set parameter to the String `getBytes` method is the Java +Canonical name that corresponds to the column's character set. + +*Example* + +The following `SQL CREATE TABLE` statement creates a table +that has an ISO88591 column: + +``` +CREATE TABLE t1 ( c1 CHAR(20) CHARACTER SET ISO88591) ; +> java -DISO88591=SJIS test1.java +``` + +The following method invocation sets column one of `stmt` to the String +"abcd" where "abcd" is encoded as SJIS. The charset parameter to the +String `getBytes` method is SJIS `stmt.setString( 1, "abcd" ) ;`. + +[[controlling-what-happens-on-an-exception]] +==== Controlling What Happens on an Exception + +You can use the `translationVerification` property to explicitly define +the behavior of the driver if the driver cannot translate all or part of +an SQL parameter. The value portion of the property can be `TRUE` or +`FALSE`. (The default value is `FALSE`). + +If the `translationVerification` property's value is `FALSE` and the driver +cannot translate all or part of an SQL statement, then the translation is +unspecified. In most cases, the characters that are untranslatable are +encoded as ISO88591 single-byte question marks (`'?'` or `0x3F`). No +exception or warning is thrown. + +If the `translationVerification` property's value is TRUE and the driver +cannot translate all or part of an SQL statement, then the driver throws an +`SQLException` with the following text: + +``` +Translation of parameter to {0} failed. Cause: {1} +``` + +where `{0}` is replaced with the target character set and `{1}` is +replaced with the cause of the translation failure. + +For more information, see +<>. + +<<< +[[localizing-error-messages-and-status-messages]] +=== Localizing Error Messages and Status Messages + +The Type 4 driver supports Internationalization through resource bundles +for localized error messages and status messages. The driver uses a set +of static strings from a property file to map error messages and status +messages to their textual representation. + +[[file-name-format-for-the-localized-messages-file]] +==== File-Name Format for the Localized-Messages File + +The property file that has the messages must have a file name in the +form: + +``` +HPT4Messages_xx.properties +``` + +where `xx` is the locale name. The locale name is defined by the current +default locale or by the language property. + +The Type 4 driver is shipped with an error messages and status messages +property file that contains the textual representation of errors and +status messages for the English locale. The file is named +`HPT4Messages_en.properties`. + +[[localized-message-string-format]] +==== Localized-Message String Format + +A localized message file contains strings in the form: + +``` +message=message_text +``` + +*Example* + +``` +driver_err_error_from_server_msg=An error was returned from the server. +Error: {0} Error detail: {1} +``` + +where the `message` is `driver_err_error_from_server_msg`. The +`message_text` is: `An error was returned from the server. Error: {0} Error detail: {1}` + +The pattern `{n}` in `message_text`, where `n` equals 1, 2, 3, and +so forth, is a placeholder that is filled in at run time by the Type 4 +driver. Any translation must include these placeholders. + +<<< +[[procedure-to-create-a-localized-message-file]] +==== Procedure to Create a Localized-Message File + +1. Extract the `HPT4Messages_en.properties file`, which is in the +`hpt4jdbc.jar file`. ++ +*Example* ++ +From a UNIX prompt, use the jar Java tool: `jar -x HPT4Messages_en.properties < hpt4jdbc.jar` + +2. Copy the file. + +3. Edit the file and replace the English text with the text for your locale. + +4. Save the file, giving it a file name that meets the naming +requirements described under +<>. + +5. Put the file in a directory anywhere in the class path for running the JDBC application. + +The new messages file can be anywhere in the class path for running the +user application. + +At run time, if driver cannot read the messages property file, the +driver uses the `message` portion of the property as the text of the +message. For a description of the message portion, see the +<>. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/src/asciidoc/_chapters/avoiding_mismatch.adoc ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/src/asciidoc/_chapters/avoiding_mismatch.adoc b/docs/jdbct4ref_guide/src/asciidoc/_chapters/avoiding_mismatch.adoc new file mode 100644 index 0000000..8d40e10 --- /dev/null +++ b/docs/jdbct4ref_guide/src/asciidoc/_chapters/avoiding_mismatch.adoc @@ -0,0 +1,106 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[avoiding-driver-server-version-mismatch]] += Avoiding Driver-Server Version Mismatch + +The {project-name} JDBC type 4 driver described in this +document can connect only with an version-equivalent platform +(server). It cannot connect with an earlier version platform. + +To make a connection with the {project-name} platform, JDBC clients, +through the driver, connect with the {project-name} database +connectivity service (DCS) on the {project-name} platform. In some +situations, {project-name} JDBC clients need to make connections to +older-version platforms (servers) from the same client boxes. +To make a connection, the driver version +must be compatible with the {project-name} platform version. + +NOTE: The DCS release version and {project-name} platform release +version are always the same. + + +[[compatible-versions]] +== Compatible Versions + +Ensure that you install the driver version that is compatible with the +{project-name} platform version. + + +[cols=",",options="header" ] +|=== +| Driver version | Compatible versions of the {project-name} platform +| {project-name} Release 2.0 driver | All versions up to, but not including, {project-name} Release 2.2 +| {project-name} Release 2.1 driver | All versions up to, but not including, {project-name} Release 2.2 +| {project-name} Release 2.2 driver | {project-name} Release 2.2 and later versions +|=== + +If a compatible version is not installed, you can obtain the software to +download from the {project-name} download site. + +[[considerations-for-mixed-version-jdbc-clients-connecting-to-project-name-platforms]] +== Considerations for Mixed-Version JDBC Clients Connecting to {project-name} Platforms + +On the client platform, you can install multiple versions of the +{project-name} JDBC type 4 driver to connect to {project-name} +platforms of different platform versions. + +* Assuming you have installed the Release 2.2 {project-name} JDBC type +4 driver on your workstation and set up the client environment, the 2.2 +driver's classes are set your java CLASSPATH. +* To connect to a Release 2.1 or 2.0 server ({project-name} platform) +from the same client machine, you must load the 2.1 driver by making +sure that it is in your java CLASSPATH. +* Connecting to both a 2.1 and 2.2 server from the same application at +the same time is not possible. +* A given application must use either the 2.2 driver or the 2.1 driver +when launched. The only way to switch is to reload the application when +pointing to a new CLASSPATH that contains a different driver. + +[[version-mismatch-error-message]] +== Version Mismatch Error Message + +If an {project-name} JDBC client attempts to connect to an invalid DCS +version, the driver returns the error: + +``` +SQLCODE: 29162 +SQLSTATE S1000 + +Error text: + +Unexpected programming exception has been found: . Check +the server event log on node for details. +``` + +* is the error text from the server. +* is the location of the log file. + +Additional error text returned only by a Release 2.2 server, but displayed by any version driver: + +``` +Version Mismatch: Client Version is: Pre R2.2.0 Server Version is: +R2.2.0 Server vproc is: + +T7969N25_10AUG07_N25_AS_0613 ERROR +``` + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/src/asciidoc/_chapters/code_examples.adoc ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/src/asciidoc/_chapters/code_examples.adoc b/docs/jdbct4ref_guide/src/asciidoc/_chapters/code_examples.adoc new file mode 100644 index 0000000..b8471e3 --- /dev/null +++ b/docs/jdbct4ref_guide/src/asciidoc/_chapters/code_examples.adoc @@ -0,0 +1,56 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[sample-programs-accessing-clob-and-blob-data]] += Sample Programs Accessing CLOB and BLOB Data + +This appendix shows two working programs. + +[[sample-program-accessing-clob-data]] +== Sample Program Accessing CLOB Data + +This sample program shows operations that can be performed through the +Clob interface or through the PreparedStatement interface. The sample +program shows examples of both interfaces taking a variable and putting +the variable's value into a base table that has a CLOB column. + +You can access the source at: http://trafodion.apache.org/docs/jdbct4ref_guide/resources/source/clob_example.java + +[source, java] +---- +include::{sourcedir}/clob_example.java[CLOB Example] +---- + +[[sample-program-accessing-blob-data]] +== Sample Program Accessing BLOB Data + +This sample program shows the use of both the Blob interface and the +PreparedStatement interface to take a byte variable and put the +variable's value into a base table that has a BLOB column. + +You can access the source at: http://trafodion.apache.org/docs/jdbct4ref_guide/resources/source/blob_example.java + +[source, java] +---- +include::{sourcedir}/blob_example.java[BLOB Example] +---- + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/src/asciidoc/_chapters/compliance.adoc ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/src/asciidoc/_chapters/compliance.adoc b/docs/jdbct4ref_guide/src/asciidoc/_chapters/compliance.adoc new file mode 100644 index 0000000..25eedf1 --- /dev/null +++ b/docs/jdbct4ref_guide/src/asciidoc/_chapters/compliance.adoc @@ -0,0 +1,386 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[type-4-driver-compliance]] += Type 4 Driver Compliance + +[[compliance-overview]] +== Compliance Overview + +The Type 4 driver conforms where applicable to the JDBC +3.0 API specification. However, this driver differs from the JDBC +standard in some ways. This subsection describes the JDBC methods that +are not supported, the methods and features that deviate from the +specification, and features that are {project-name} extensions to the JDBC standard. +JDBC features that conform to the specification are not described in this subsection. + +In addition, this chapter lists features of {project-name} SQL that are not supported by the {project-name} JDBC Type 4 driver, other +unsupported features, and restrictions. + +[[unsupported-features]] +== Unsupported Features + +These methods in the java.sql package throw an SQLException with the +message `Unsupported feature - `: + +[cols="65%,35%", options="header"] +|=== +| Method | Comments +| `CallableStatement.getArray(int parameterIndex)` + +`CallableStatement.getArray(String parameterName)` + +`CallableStatement.getBlob(int parameterIndex)` + +`CallableStatement.getBlob(String parameterName)` + +`CallableStatement.getClob(int parameterIndex)` + +`CallableStatement.getClob(String parameterName)` + +`CallableStatement.getObject(int parameterIndex, Map map)` + +`CallableStatement.getObject(String parameterName, Map map)` + +`CallableStatement.getRef(int parameterIndex)` + +`CallableStatement.getRef(String parameterName)` + +`CallableStatement.getURL(int parameterIndex)` + +`CallableStatement.getURL(String parameterName)` + +`CallableStatement.executeBatch()` | The particular `CallableStatement` method is not supported. +| `Connection.releaseSavepoint(Savepoint savepoint)` + +`Connection.rollback(Savepoint savepoint)` + +`Connection.setSavepoint()` + +`Connection.setSavepoint(String name)` | The particular `Connection` methods are not supported. +| `PreparedStatement.setArray(int parameterIndex, Array x)` + +`PreparedStatement.setRef(int parameterIndex, Ref x)` + +`PreparedStatement.setURL(int parameterIndex, URL x)` | The particular `PreparedStatement` methods are not supported. +| `ResultSet.getArray(int columnIndex)` + +`ResultSet.getArray(String columnName)` + +`ResultSet.getObject(int columnIndex, Map map)` + +`ResultSet.getObject(String columnName, Map map)` + +`ResultSet.getRef(int columnIndex)ResultSet.getRef(String columnName)` + +`ResultSet.getURL(int columnIndex)` + +`ResultSet.getURL(String columnName)` + +`ResultSet.updateArray(int columnIndex)` + +`ResultSet.updateArray(String columnName)` + +`ResultSet.updateRef(int columnIndex)` + +`ResultSet.updateRef(String columnName) | The particular `ResultSet` methods are not supported. +| `Statement.getQueryTimeout()` + +`Statement.setQueryTimeout()` | The particular `Statement` methods are not supported. +|=== + +The following methods in the java.sql package throw an SQLException with +the message `Auto generated keys not supported`: + +[cols="65%,35%", options="header" ] +|=== +| Method | Comments +| `Connection.prepareStatement(String sql, int autoGeneratedKeys)` + +`Connection.prepareStatement(String sql, int[] columnIndexes)` + +`Connection.prepareStatement(String sql, String[] columnNames)` | Automatically generated keys are not supported. +| `Statement.executeUpdate(String sql, int autoGeneratedKeys)` + +`Statement.executeUpdate(String sql, int[] columnIndexes)` + +`Statement.executeUpdate(String sql, String[] columnNames)` + +`Statement.getGeneratedKeys()` | Automatically generated keys are not supported. +|=== + +The following methods in the java.sql package throw an SQLException with +the message `Data type not supported`: + +[cols="65%,35%", options="header" ] +|=== +| Method | Comments +| `CallableStatement.getBytes(int parameterIndex)` + +`CallableStatement.setBytes(String parameterIndex, bytes[] x)` | The particular data type is not supported. +|=== + +The following interfaces in the `java.sql` package are not implemented in +the Type 4 driver: + +[cols="65%,35%", options="header" ] +|=== +| Method | Comments +| `java.sql.Array` + +`java.sql.Ref` + +`java.sql.Savepoint` + +`java.sql.SQLData` + +`java.sql.SQLInput` + +`java.sql.SQLOutput` + +`java.sql.Struct` | The underlying data types are not supported by {project-name}. +|=== + +<<<< +The following interfaces in the `javax.sql` package are not implemented in the Type 4 driver: + +[cols="65%,35%", options="header" ] +|=== +| Method | Comments +| `javax.sql.XAConnection` + +`javax.sql.XADataSource` | Distributed Transactions, as described in the JDBC 3.0 API specification, are not yet implemented. +|=== + +For additional information about deviations for some methods, see <>. + +[[deviations]] +== Deviations + +The following table lists methods that differ in execution from the JDBC +specification. When an argument in a method is ignored, the Type 4 +driver does not throw an SQLException,thus allowing the application to +continue processing. The application might not obtain the expected +results, however. Other methods listed do not necessarily throw an +SQLException, unless otherwise stated, although they differ from the +specification. + +NOTE: The `java.sql.DatabaseMetaData.getVersionColumns()` method mimics the +`java.sql.DatabaseMetaData.getBestRowIdentifier()` method because +{project-name} SQL does not support `SQL_ROWVER` (a columns function that +returns the column or columns in the specified table, if any, that are +automatically updated by the data source when any value in the row is +updated by any transaction). + +[cols="50%,50%", options="header" ] +|=== +| Method | Comments +| `java.sql.DatabaseMetaData.getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)` | +The column is added to the column data, but its value is set to NULL because {project-name} SQL does not support the column type for these types: + + + +`SCOPE_CATALOG,` + +`SCOPE_SCHEMA,` + +`SCOPE_TABLE,` + +and `SOURCE_DATA_TYPE` +| `java.sql.DatabaseMetaData.getTables(String catalog, String schemaPattern, String[] types)` | +The column is added to the column data, but its value is set to NULL because {project-name} SQL does not support the column type for these types: + + + +`TYPE_CAT,` + +`TYPE_SCHEMA,` + +`TYPE_NAME,` + +`SELF_REFERENCING_COL_NAME,` + +and `REF_GENERATION.` +| `java.sql.DatabaseMetaData.getUDTs(String catalog, String schemaPattern, String tableNamePattern, int[] types)` | +BASE_TYPE is added to the column data, but its value is set to NULL because {project-name} SQL does not support the base type. +| `java.sql.DatabaseMetaData.getVersionColumns()` | +Mimics the `DatabaseMetaData.getBestRowIdentifier()` method because {project-name} SQL does not support `SQL_ROWVER` (a columns function that returns the +column or columns in the specified table, if any, that are automatically updated by the data source when any value in the row is updated by any transaction). +| `java.sql.Connection.createStatement(. . .)` + +`java.sql.Connection.prepareStatement(. . .)` | +The Type 4 driver does not support the scroll-sensitive result set type, so an SQL Warning is issued if an application requests that type. The result set is changed to +a scroll-insensitive type. +| `java.sql.ResultSet.setFetchDirection(. . .)` | The fetch direction attribute is ignored. +| `java.sql.Statement.cancel()` | +In some instances, drops the connection to the server instead of just canceling the query. You must then reconnect to the server. +Note that the connection is dropped if `cancel()` is issued for a statement that is being processed. Otherwise the connection is maintained. +| `java.sql.Statement.setEscapeProcessing(. . .)` | Because {project-name} SQL parses the escape syntax, disabling escape processing has no effect. +| `java.sql.Statement.setFetchDirection(. . .)` | The fetch direction attribute is ignored. +|=== + +<<< +[[project-name-extensions]] +== {project-name} Extensions + +The {project-name} extensions to the JDBC standard implemented in the Type 4 driver are as follows. + +[[internationalization-of-messages]] +=== Internationalization of Messages + +The Type 4 driver is designed so that Java messages can be adopted for +various languages. The error messages are stored outside the source code +in a separate property file and retrieved dynamically based on the +locale setting. The error messages in different languages are stored in +separate property files based on the language and country. This +extension does not apply to all messages that can occur when running +JDBC applications. + +For details, see <>. + +[[additional-databasemetadata-apis]] +=== Additional DatabaseMetaData APIs + +APIs added to the `HPT4DatabaseMetaData` class provide these capabilities: + +* Get a description of a table's synonyms. ++ +[source, java] +---- +public java.sql.ResultSet getSynonymInfo(String catalog, String schema, String table) throws SQLException +---- + +//// +* Get a description of a table's materialized views. ++ +[source, java] +---- +public java.sql.ResultSet getMaterializedViewInfo(String catalog, String schema, String table) throws SQLException +---- +//// + +[[additional-connection-apis]] +=== Additional Connection APIs + +APIs added to the `HPT4Connection` class provide these capabilities: + +* Sets the current service name for this Connection object. ++ +[source, java] +---- +public void setServiceName(String serviceName) throws SQLException +---- + +* Gets (retrieves) the current service name of this Connection object. Default return value: HP_DEFAULT_SERVICE. ++ +[source, java] +---- +public String getServiceName() throws SQLException +---- + +[[conformance-of-databasemetadata-methods-handling-of-null-parameters]] +== Conformance of DatabaseMetaData Methods' Handling of Null Parameters + +This topic describes how the Type 4 driver determines the value of null +parameters passed as a parameter value on DatabaseMetaData methods. +Since other vendors might implement the JDBC specification differently, +this information explains the Type 4 driver results on the affected +queries. + +This implementation applies to methods that take parameters that can +represent a pattern. The names of these parameters have the format: + +``` +attributePattern +``` + +The many methods of the java.sql.DatabaseMetaData class are affected; +for example, the `getColumns()` method. + +For another example, schema is the attribute in the parameter +`schemaPattern`, which is a parameter to the `java.sql.ResultSet.getAttributes` method. + +[source, java] +---- +public ResultSet getAttributes( String catalog + , String schemaPattern + , String typeNamePattern + , String attributeNamePattern + ) throws SQLException +---- + +If the application passes a null value, the null is treated as follows: + +* If a parameter name contains the suffix Pattern, the null is interpreted as a `%` wild card. +* If the parameter name does not contain the suffix `Pattern`, nulls are interpreted as the default value for that parameter. + +Using this example, null parameters are interpreted as follows: + +|=== +| `catalog` | The default catalog name. +| `schemaPattern` | A `%` wild card retrieves data for all schemas of the specified catalog +|=== + +<<< +[[type-4-driver-conformance-to-sql-data-types]] +== Type 4 Driver Conformance to SQL Data Types + +[[jdbc-data-types]] +=== JDBC Data Types + +The following table shows the JDBC data types that are supported by Type +4 driver and their corresponding {project-name} SQL data types: + +[cols="30%,40%,30%", options="header"] +|=== +| JDBC Data Type | Support by JDBC Driver for {project-name} SQL | {project-name} SQL Data Type +| `Types.Array` | No | Not applicable. +| `Types.BIGINT` | Yes | `LARGEINT` +| `Types.BINARY` | Data type is mapped by {project-name} SQL. Data type varies from that used for table creation. | `CHAR(n)`^1^ +| `Types.BIT` | Data type is mapped by {project-name} SQL. Data type varies from that used for table creation. | `CHAR(1)` +| `Types.BLOB` | Yes | `LARGEINT` +| `Types.CHAR` | Yes | `CHAR(n)` +| `Types.CLOB` | Yes | `LARGEINT` +| `Types.DATE` | Yes | `DATE` +| `Types.DECIMAL` | Yes | `DECIMAL(p,s)` +| `Types.DISTINCT` | No | Not applicable. +| `Types.DOUBLE` | Yes | `DOUBLE PRECISION` +| `Types.FLOAT` | Yes | `FLOAT(p)` +| `Types.INTEGER` | Yes | `INTEGER` +| `Types.JAVA_OBJECT` | No | Not applicable. +| `Types.LONGVARBINARY` | Data type is mapped by {project-name} SQL. Data type varies from that used for table creation. | `VARCHAR(n)`^1^ +| `Types.LONGVARCHAR` | Yes. Maximum length is 4018. | `VARCHAR[(n)]` +| `Types.NULL` | No | Not applicable. +| `Types.NUMERIC` | Yes | `NUMERIC(p,s)` +| `Types.REAL` | Yes | `FLOAT(p)` +| `Types.REF` | No | Not applicable. +| `Types.SMALLINT` | Yes | `SMALLINT` +| `Types.STRUCT` | No | Not applicable. +| `Types.TIME` | Yes | `TIME` +| `Types.TIMESTAMP` | Yes | `TIMESTAMP` +| `Types.TINYINT` | Data type is mapped by {project-name} SQL. Data type varies from that used for table creation. | `SMALLINT` +| `Types.VARBINARY` | Data type is mapped by {project-name} SQL. Data type varies from that used for table creation. | `VARCHAR(n)`^1^ +| `Types.VARCHAR` | Yes | `VARCHAR(n)` +| `Types.BOOLEAN` | Data type is mapped by {project-name} SQL. Data type varies from that used for table creation. | `CHAR(1)` +| `Types.DATALINK` | No | Not applicable. +|=== + +^1^ Because of mapping provided by {project-name}, a `ResultSet.getObject()` method returns a string object instead of an array of bytes. + +The Type 4 driver maps the following data types to the JDBC data type `Types.OTHER`: + +``` +INTERVAL YEAR(p) +INTERVAL YEAR(p) TO MONTH +INTERVAL MONTH(p) +INTERVAL DAY(p) +INTERVAL DAY(p) TO HOUR +INTERVAL DAY(p) TO MINUTE +INTERVAL DAY(p) TO SECOND +INTERVAL HOUR(p) +INTERVAL HOUR(p) TO MINUTE +INTERVAL HOUR(p) TO SECOND +INTERVAL MINUTE(p) +INTERVAL MINUTE(p) TO SECOND +INTERVAL SECOND(p) +``` + +[[floating-point-support]] +== Floating-Point Support + +The Type 4 driver supports only IEEE floating-point data to be passed between the application client and the Type 4 driver. + +[[sqlj-support]] +== SQLJ Support + +The Type 4 driver supports non-customized SQLJ applications, but does not support customized SQLJ applications. + +<<< +[[jdbc-3-0-features-not-supported-by-the-type-4-driver]] +== JDBC 3.0 Features Not Supported by the Type 4 Driver + +These features are not required for JDBC 3.0 compliance, and they are not supported by the {project-name} JDBC Type 4 driver. + +* Multiple result sets returned by batch statements. +* Database savepoint support. (Not provided in {project-name} SQL ) +* Retrieval of auto generated keys. +* Transform group and type mapping. +* Relationship between connector architecture and JDBC 3.0 SPI. +* Secured socket communication or encryption for the interaction between the Type 4 driver and DCS. +* Security context (user name and password) implicit propagation from AppServer to the Type 4 driver. +* IPV6 protocol stack. (IPV6 addressing is emulated over IPV4 on the {project-name} platform - server side) +* Distributed transactions. + +[[restrictions]] +== Restrictions + +* The Type 4 driver supports only database features that are supported by {project-name} SQL and SPJ. +Therefore, the Type 4 driver is not fully compliant with JDBC 3.0 specifications. + +* The Type 4 driver depends on the HP connectivity service for all server side manageability related features. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c2116c2b/docs/jdbct4ref_guide/src/asciidoc/_chapters/introduction.adoc ---------------------------------------------------------------------- diff --git a/docs/jdbct4ref_guide/src/asciidoc/_chapters/introduction.adoc b/docs/jdbct4ref_guide/src/asciidoc/_chapters/introduction.adoc new file mode 100644 index 0000000..a0f8219 --- /dev/null +++ b/docs/jdbct4ref_guide/src/asciidoc/_chapters/introduction.adoc @@ -0,0 +1,53 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[introduction]] += Introduction + +This document describes how to use the {project-name} JDBC Type 4 +Driver. This driver provides Java applications running on a foreign +platform with JDBC access to {project-name} SQL databases on the +{project-name}. + +*Supported Java Releases:* The Type 4 driver requires Java enabled +platforms that support JDK 1.4.1 or higher. + + +[[type-4-driver-api-package]] +== Type 4 Driver API Package + +The Type 4 driver package, `org.trafodion.t4jdbc`, is shipped with the +driver software. For class and method descriptions, see the +_{project-name} JDBC Type 4 Driver API Reference_. + +The {project-name} JDBC Type 4 Driver (hereafter, Type 4 driver) +implements JDBC technology that conforms to the standard JDBC 3.0 Data +Access API. + +To obtain detailed information on the standard JDBC API, download the +JDBC API documentation: http://docs.oracle.com/en/java/. + +[[installation]] +== Installation + +Refer to the {docs-url}/client_install/index.html[Trafodion Client Installation Guide]. +