Define a JDBC Connection
The JDBC Client must include iijdbc.jar and run with a JRE version described in
Requirements.
On the Gateway machine the Vector Client Runtime and Apache Knox must be installed. The important connection target of the VectorH cluster is the VectorH master node through the Gateway.
The following are required for a successful JDBC connection:
• A configured LDAP authentication using PAM on the VectorH master node (see previous section)
• LDAP users defined in the master database, iidbdb. The users in iidbdb do not need passwords or special privileges.
To define a JDBC connection, perform the following steps.
1. On Gateway machine define a global vnode pointing to the VectorH master node (only the connection details are needed):
a. Start Vector Client Runtime and Data Access Server (DAS):
ingstart
ingstart -iigcd
b. Create a vnode using the netutil utility:
a. Issue the netutil command at the command prompt. The netutil utility starts.
b. Select "Create".
c. Enter a name for the vnode (for example, test), and then select OK.
d. Select "Global".
e. Skip adding login/password credentials by pressing Cancel.
f. For "Network Address" enter the hostname or IP address of the VectorH master node.
g. Leave the protocol as it is.
h. For "Listen Address" enter the VectorH server instance ID (default is "VH").
i. Select "Save", and then quit netutil by pressing F10.
j. Set ii.hostname.gcn.remote_vnode to the vnode name:
iisetres ii.$(iipmhost).gcn.remote_vnode <vnode name>
You can check the new setting:
iigetres ii.$(iipmhost).gcn.remote_vnode
k. Restart Name Server:
ingstop -iigcn; ingstart -iigcn
2. On the JDBC Client, use the following connection string:
jdbc:ingres://<gatewayHost>:<gatewayPort>/<vnode name>::<dbname>;user=<gatewayUser>;password=<gatewayPassword>
A vnode (virtual node) stores all the information required to connect to a remote VectorH server. For more information about vnode usage in JDBC, see JDBC Driver Properties in the Connectivity Guide.
CountryList.java
The CountryList.java example shows how the JDBC connection works.
Change package name to your appropriate java project path.
package jdbc.src;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class CountryList {
/* Connection to VectorH Gateway - "Creation of a vnode at Gateway and provide the vnode in the JDBC connection string"
* connection string template: "jdbc:ingres://<gatewayHost>:<gatewayPort>/<VectorHdbname>;<gatewayUser>;<gatewayPassword>" */
private static String gatewayHost = "ussf-user01-c3m"; // Gateway machine where Knox and Ranger are installed
private static String gatewayPort = "CL7"; // DAS Port on Gateway machine
private static String gatewayUser = "user=tom"; // credentials for LDAP authentication which happen on VectorH Master
private static String gatewayPassword = "password=tom-password";// credentials for LDAP authentication which happen on VectorH Master
private static String VectorHdbname = "testvnode::testdb"; // "<vnode name>::<dbname>"
private static String connectionStr = "jdbc:ingres://" +
gatewayHost + ":" + gatewayPort + "/" +
VectorHdbname + ";" +
gatewayUser + ";" + gatewayPassword;
private static Connection conn = null;
private static Statement stmt = null;
private static String tableName = "country";
/**
* @param args
*/
public static void main(String[] args) {
System.out.println("create connection to" + connectionStr);
createConnection();
System.out.println("drop and create country table...");
dropCreateCountryTable();
System.out.println("retrieve countries...");
retrieveCountries();
System.out.println("inserting country data...");
insertCountry(1, "CH", "Switzerland");
insertCountry(2, "GER", "Germany");
insertCountry(3, "P", "Poland");
System.out.println("retrieve countries...");
retrieveCountries();
}
/**
* Create a connection to Ingres using the Ingres JDBC driver
* and the private variable connectionStr
*/
private static void createConnection()
{
try
{
Class.forName("com.ingres.jdbc.IngresDriver").newInstance();
conn = DriverManager.getConnection(connectionStr);
System.out.println("");
}
catch (Exception except)
{
except.printStackTrace();
}
}
/**
* Drop and create country table.
*/
private static void dropCreateCountryTable()
{
try
{
stmt = conn.createStatement();
stmt.execute("drop table IF EXISTS " + tableName);
stmt.execute("create table " + tableName +" (ct_id integer, ct_code varchar(32), ct_name varchar(32))");
stmt.close();
System.out.println("");
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
/**
* Insert a country.
*
* @param countryID
* @param countryCode
* @param countryName
*/
private static void insertCountry(Integer countryID, String countryCode, String countryName)
{
try
{
stmt = conn.createStatement();
stmt.execute("insert into " + tableName +
" values(" +
countryID + ","+ "'" +
countryCode + "','" +
countryName +"')");
stmt.close();
System.out.println("");
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
/**
* Retrieve and print all countries in the database.
*/
private static void retrieveCountries()
{
try
{
stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("SELECT ct_id, ct_code, ct_name" + " FROM " + tableName);
ResultSetMetaData rsmd = results.getMetaData();
int numberCols = rsmd.getColumnCount();
for (int i=1; i<=numberCols; i++)
{
//print Column Names
System.out.print(rsmd.getColumnLabel(i)+"\t");
}
System.out.println("");
String line = "";
for (int i = 0; i < numberCols; i++) {
line += "=====\t";
}
System.out.println(line);
while(results.next())
{
String colValue = "";
for (int i = 1; i <= numberCols; i++) {
colValue += results.getString(i) + "\t";
}
System.out.println(colValue);
}
results.close();
stmt.close();
System.out.println("");
}
catch (SQLException sqlExcept)
{
sqlExcept.printStackTrace();
}
}
}