Quick Tutorial - Using JDBC in Scripts

Description

This tutorial will detail how to connect, query and update a database via JDBC through examples, allowing the flexibility of scripts to access data in any JDBC compliant database. This tutorial presents the power of server side scripts whatever its purpose may be. Server side scripts allow the creation of powerful data integration mechanisms for extending any type of business data to remote field users.

This tutorial is not meant to be a tutorial on JDBC, but rather it is meant to be demonstrating how one might use the power of the JDBC on the server side. For further details on how to use the JDBC framework, refer to Java documentation on the topic.

Resources

Establishing a connection

JDBC connections are handled by the java.sql.DriverManager class.

Using this class, JDBC may be accessed by specifying a connection string, username and password, with the getConnection() method.

//------------------------------------------------------------------------------

/**

* Connects to the SQL server.

//------------------------------------------------------------------------------

importClass (java.sql.DriverManager) ;

function getConnection()

{

// Script constants

var sqlConnectionString= "jdbc:sqlserver://..." ; // TODO set connection string with your database server details

var sqlUsername = "..." ; // TODO : Set user name

var sqlPassword = "..."; // TODO : Set password

if (ScriptSession.isDebugLogEnabled()) { ScriptSession.logDebug("Connecting to server...") ; }

if (ScriptSession.isDebugLogEnabled()) { ScriptSession.logDebug("Connecting to: " + sqlConnectionString) ; }

var jdbcConnection = DriverManager.getConnection(sqlConnectionString, sqlUsername, sqlPassword);

jdbcConnection.setAutoCommit(false);

if (ScriptSession.isDebugLogEnabled()) { ScriptSession.logDebug("Connected successfully.") ; }

return jdbcConnection;

}

Once established, the connection's settings may be changed via methods. For example, the above sample code turns off auto commit for the JDBC connection, thereby necessitating commit() and rollback() calls when making changes to the database. An example of other methods one could provide as follows:

function commit(connection)

{

if (ScriptSession.isDebugLogEnabled()) { ScriptSession.logDebug("Committing transactions...") ; }

if (connection != null) connection.commit();

if (ScriptSession.isDebugLogEnabled()) { Session.logDebug("Successfully committed transactions.") ; }

}

function rollBack(connection)

{

if (ScriptSession.isInfoLogEnabled()) { ScriptSession.logInfo("Rolling back transactions...") ; }

if (connection != null) connection.rollback();

if (ScriptSession.isInfoLogEnabled()) { ScriptSession.logInfo("Successfully rolled back.") ; }

}

It is also suggested to create and inherit off a common class containing all these features across multiple scripts, if the same database and credentials are to be used.

connection = getConnection();

var sqlUpdate = "...";

var ps = connection.prepareStatement(sqlUpdate);

ps.executeUpdate();

For more information using this external class, please refer to the java.sql.DriverManager documentation of the Java platform.

Querying the database

Queries are handled once an expression is established by SQL queries and prepared statements.

// update the record given inputs pCustName and pCustCode.

// note, executeUpdate() is used
var sqlUpdate = "UPDATE BS_CUSTOMER SET CUST_NAME=? WHERE CUST_CODE=?";

var ps = connection.prepareStatement(sqlUpdate);

ps.setObject(1,pCustName)

ps.setObject(2,pCustCode)

ps.executeUpdate();

commit(connection);

// execute a select statement, and create a record to add to rs.

// note, executeQuery() is used to attain query data

sqlQuery = "SELECT CUST_CODE, CUST_NAME FROM BS_CUSTOMER";

ps = connection.prepareStatement(sqlQuery);

rsQueryData = ps.executeQuery();

while (rsQueryData.next())

{

record = new Record();

record.add(new Integer(rsQueryData.getInt("CUST_CODE")));

record.add(rsQueryData.getString("CUST_NAME"));

rs.addRecord(record);

}

For more information using this external class, please refer to the java.sql.Connection documentation of the Java platform.

Exercise

Provided is an application which operates on the same default RPC script from the server, but for all 3 of its total database operations.

It is designed to work entirely with stored procedures, using RPC online queries on BrightServer. Only one RPC, 'ScriptCustomerOp' is used, but passed different function names. These called are mapped to each button, performing the following operations:

The Get button will retrieve all the records in the BS_CUSTOMER table from the server.
The Add button will create a new record on the server, with the name specified in the larger edit control. The ID is automatically populated by the server, and so the Name is the only field read.
The Update button will update the record with the specified ID with the name. A number must always be specified, and is retrieved from the listview.

After each Add and Update rule execution, the list is retrieved by a Get, and the listview updated.

The BSP is complete. Create the necessary server table with ScriptWithJDBCCreate_tbl.sql. Then, proceed to complete the BEP's 'ScriptCustomerOp' script to:

Correctly connect to the database instance with the ScriptConnection script, providing username/password and connection string details.
Correctly implement update/insert statements to the BS_CUSTOMER table as required by application.
Populate the record set returned to the device with all records in the BS_CUSTOMER table. Do this by executing a SELECT statement, then using the returned query data to populate the results.
Return error code 1 if error is caught, else 0 in the executeRPC statement

Placeholders are provided in 'ScriptCustomerOp' where necessary changes should be made. Proceed to test the solution by executing or deploying the BEP.

A possible solution may be referenced in ScriptWithJDBCFinished.bep