7.1 JDBC Connector

Overview of GridDB AE JDBC driver

An interface that can access GridDB data using SQL is provided in the GridDB Advanced Edition (GridDB AE). This chapter provides an overview and specifications of the Java API (JBDC) used to access databases supported by the GridDB AE.

This chapter also consists of a description of the specified format and data types that can be used in a program using JDBC parameters, and the points to note during use.

• A container created by GridDB SE (Standard Edition) client can be referenced as a table by the GridDB AE JDBC driver but it cannot be updated. Besides updating the rows, changes in the schema and index of a container are also included in an update. Conversely, a table created with the GridDB AE JDBC driver can neither be referenced nor updated from a GridDB SE client.

• If a TimeSeries container created by a GridDB client is searched with a SQL command from the GridDB AE, the results will not be in chronological order if no ORDER BY phrase is specified for the main key. Specify an ORDER BY against the main key if a chronological series of the SQL results is required.
• Regarding consistency during a search, the way the results are viewed may differ between a search conducted from a GridDB SE client and a search conducted from a GridDB AE JDBC driver. As GridDB supports READ COMMITTED as an isolation level of the trasnaction, data committed at the point search is started will be read in a search. If a search is conducted from a GridDB SE client, since each search request is read once in the prcoess, only data committed at the point the search is started will be searched as per READ COMMMITTED. On the other hand, if a search is conducted with a SQL command from a GridDB AE JDBC driver, the requested SQL may be read and processed several times. In this case, the respective readings will become READ COMMITTED. As a result, when a transaction of another client is committed, in-between the readings the update details are ready by the next reading and the entire SQL may not become READ COMMITTED.
• When the number of SQL hits is large, the error "memory limit exceed" may occur. In this case, add the descriptions of parameter transaction:totalMemoryLimit and dataStore:resultSetMemoryLimit in the gs_node.json file as shown below, and expand the upper limit value of the memory used in the SQL process. However, the names of these parameters may be changed or deleted in future versions.

Example: when specifying the upper limit value

"transaction":{
    "totalMemoryLimit":"2048MB"
}

"dataStore":{
    "resultSetMemoryLimit":"20480MB"
}

*Default value is 1024MB and 10240 MB respectively if there is no description.

The specifications of the GridDB AE JDBC Driver are shown in this section. This section explains mainly the support range of the driver as well as the differences with the JDBC standard. See the JDK API reference for the API specifications that conform to the JDBC standard unless otherwise stated.

Please note that the following could be revised in the future versions.

• Actions not conforming to the JDBC standard
• Support status of unsupported functions
• Error messages

Common Items

Supported JDBC Version

The following functions corresponding to some of the functions of JDBC 4.1 are not supported.

• Transaction control
• Stored procedure
• Error processing

Use of Unsupported Functions

• Standard Functions

  A SQL FeatureNotSupportedException occurs if a function that ought to be but is currently not supported by a driver conforming to the JDBC specifications is used. This action differs from the original SQLFeatureNotSupportException specifications. Error name (to be described later) is JDBC_NOT_SUPPORTED.

• Optional Functions

  If a function not supported by the driver that is positioned as an optional function in the JDBC specifications and for which a SQLFeatureNotSupportedException may occur is used, a SQLFeatureNotSupportedException will occur as per the JDBC specifications. Error name is JDBC_OPTIONAL_FEATURE_NOT_SUPPORTED.

Invoke A Method Against A Closed Object

Invalid Null Argument

If There Are Multiple Error Causes

Description Of Exception

Use of the following method to get the exception details.

• getErrorCode()
  For errors detected by GridDB in either the server or client, an error number will be returned. See the list of error codes for the specifc number and cause.
• getSQLState()
  Corresponding SQLSTATE is returned if an error is detected in the SQL process or JDBC driver. null is returned otherwise.
• getMessage()
  Return an error message containing the error number and error description as a set. The format is as follows.

[(Error number): (Error code name)] (Supplementary message)

import java.sql*

public class SampleJDBC{
    public static void main (String[] args) throws SQLException {
        String url = null, user = "", password = "";
        
        if (args.length != 3) {
            System.err.println (
                "usage: java SAmple JDBC (multicastAddress)(port)(clusterName)");
            System.exit(1)
        }
        // url is "jdbc:gs:// (multicastAddress): (portNo)/(clusterName)" format
        url = "jdbc:gs://" + args[0] + ":" + args[1] + "/" + args[2];
        user = "system";
        password = "manager";
        
        System.out.println("DB Connection Start");
        
        // Connection with GridDB Cluster
        Connection con = DriverManager.getConnection (url, user, password);
        try{
            System.out.println("Start");
            Statement st = con.createStatement();
            ResultSet rs = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM table01");
            ResultSetMetaData md = rs.getMetaData();
            while (rs.next()){
                for (int 1 = 0; i < md.getColumnCount(); i++) {
                    System.out.print(rs.getStrng(i + 1) + "|");
                }
                System.out.println("");
            }
            rs.close();
            System.out.println("End");
            st.close ();
        }
        finally {
            System.out.println("DB Connection Close");
            con.close();
        }
    }
}