Sample Database Insert Version 1

This handler leverages Java Database Connectivity (JDBC) technology to execute SQL queries against a database server. JDBC provides an abstract inferface for querying against different database systems. In order to configure a connection, a JDBC Driver must be made available, typically by including a .jar file somewhere on the classpath. In order to initializing the JDBC connection, a JDBC driver, or adapter class, associated to the connection must be pre-loaded. Once the JDBC adapter class is loaded, a simple connection string, which specifies the location of the server, name of the database to connect to and a list of parameters can be used to initialize a connection.

Practically, this means that a JDBC .jar file specific to the target database server will need to be added to the Kinetic Task web application WEB-INF/lib directory. Additionally, the adapter class and connection string info values will need to be configured specific to the JDBC adapter that is being used to connect. Below are some example configurations for commonly used JDBC adapters.

DB2 Adapter Class: com.ibm.db2.jcc.DB2Driver Connection String: jdbc:db2://[SERVER][:PORT]/[DATABASE]

Microsoft SQL Server Adapter Class: com.microsoft.sqlserver.jdbc.SQLServerDriver Connection String: jdbc:sqlserver://[SERVER][:PORT];databaseName=[DATABASE]

Oracle Adapter Class: oracle.jdbc.OracleDriver Connection String: jdbc:oracle:thin:@//[SERVER][:PORT]/[SERVICENAME]

MySQL Adapter Class: com.mysql.jdbc.Driver Connection String: jdbc:mysql://[SERVER][:PORT]/[DATABASE]

Once you have JDBC configured, you will also need to configure the handler to execute your specific query. In the node.xml file, insert your query in the node, using curly braces for the parameters that you wish to be dynamic. Below is an example of a query were the username and password will be dynamic values.

INSERT INTO Users (Id,Name,Username,Password) VALUES ({id},{name},{username},{password})

Any value that you used inside of the curly braces will now need to be added as a parameter. The parameter id must be the same as the value in the curly braces. Later, when defining the parameters nodes that is inside the node (the second set of parameter nodes in node.xml) make sure that the type is equal to the Java class that cooresponds to the parameter type in the database (Integer, String, Date, and Timestamp are supported in this sample). If you want to add a type that is not supported in this sample, you just need to add the type to the logic in init.rb (currently found between lines 76-87).

Once all this is configured, the handler will now be able to insert a value for you to your desired database!

Testing the Handler

NOTE: Testing this handler requires the Kinetic Task 3.0 testing harness.

In order to test this handler, the JDBC .jar file must be made available to the java virtual machine. In order to do this, java must be called directly (rather than executing the kinetic task jar file using java -jar kinetic-task.jar ...). See below for a sample execution (which assumes that the current working directory is the root of the handler):

java -classpath [PATH]\kinetic-task.jar;[PATH][JDBCJARFILE].jar com.kineticdata.task.Main -test-handler=.

The simple test case shows the configuration for executing this handler against a Kinetic Matrix instance, which uses an Oracle database. Included in the test directory is a lib/ojdbc6.jar file, which is a JDBC jar file for Oracle (which requires Java 6 or higher). If the test harness is being run from Java 6 and connecting to a Matrix instance, the following command line can be used to execute the test process:

java -classpath [PATH]\kinetic-task.jar;test\lib\ojdbc6.jar com.kineticdata.task.Main -test-handler=.

Parameters

Sample Configuration

Results

This handler does not return any results.

Changelog

sampledatabaseinsert_v1 (2013-09-25)

• Initial version. See README for details.

---

Related Handlers

Sample Command Line Hello World

Executes 'echo' from a command line, passing a "hello" string argument, and returns the standard out and standard error results. If the 'name' parameter is present, that person will be greeted. Otherwise the world will be greeted.

Sample Database Count

This is a sample handler that shows you how to count the amount of records are returned in a query to your database using JDBC. The handler will return an xml string containing the amount of records that were counted.

Sample Database Delete

This is a sample handler that shows you how to delete a record from your database using JDBC.

Sample Database Retrieve Multiple Records

This is a sample handler that shows you how to retrieve records from a database using JDBC.

Sample Database Retrieve

This is a sample handler that shows you how to retrieve a record from your database using JDBC. The handler will return an xml string containing data from all of the columns that the record returned.

Sample HTML to PDF

Converts an HTML string to a PDF.

Sample Java Hello World

Calls into a simple Java "application" via an exposed Class and class method. The application returns a 'Message' result that represents a simple greeting.

Sample REST Hello World

Calls the 'EchoService' RESTful web service using, passing a "hello" string argument, and returns the result of the call. If the 'name' parameter is present, that person will be greeted. Otherwise, the world will be greeted.

Sample SOAP Hello World

Calls the SOAP based 'EchoService' web service, passing the desired greeting and target and returns the resulting message. If the 'name' parameter is present, that person will be greeted. Otherwise, the world will be greeted.