Implementing JavaScript SQL adapters

Learn to develop a JavaScript SQL adapter.

Before you begin

The example in this page shows how to implement an adapter that connects with a MySQL database by using the connectivity facilities that are provided with MobileFirst Server. You can learn more about connectivity in The JavaScript adapter framework and SQL adapter connectionPolicy element.

To connect to an SQL database, JavaScript code needs a JDBC connector driver for the specific database type. You must download the appropriate JDBC connector driver and add it as a dependency in your Maven project. For more information about adding dependencies, see System Dependencies.

There are two parts to the implementation of a JavaScript adapter:

Configuring the adapter.xml descriptor file:
- In the connectionPolicy element, you declare the parameters of the SQL database to which the adapter connects. For more information, see SQL adapter connectionPolicy element.
- You declare each procedure that you implement in the JavaScript source files, by using a procedure element. For more information, see SQL adapter procedure element.
Implementing procedure logic in JavaScript source files.

Configuring the adapter.xml descriptor file

Procedure

In the adapter-descriptor file, inside the connectivityelement:, configure the following parameters:

JDBC driver class
Database URL
Username
Password

<?xml version="1.0" encoding="UTF-8"?>
<mfp:adapter name="JavaScriptSQL"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:mfp="http://www.ibm.com/mfp/integration"
    xmlns:sql="http://www.ibm.com/mfp/integration/sql">

    <displayName>JavaScriptSQL</displayName>
    <description>JavaScriptSQL</description>
    <connectivity>
        <connectionPolicy xsi:type="sql:SQLConnectionPolicy">
            <dataSourceDefinition>
                <driverClass>com.mysql.jdbc.Driver</driverClass>
                <url>jdbc:mysql://localhost:3306/mobilefirst_training</url>
                <user>mobilefirst</user>
                <password>mobilefirst</password>
            </dataSourceDefinition>
        </connectionPolicy>
    </connectivity>

</mfp:adapter>

Declare a procedure for connecting to the database with a plain SQL query and one for connecting with a stored procedure.
Note: The name of the procedure that you declare in the adapter-descriptor file must be identical to the name you use when implementing the procedure itself.
```
<procedure name = "getAccountTransactions1" />
<procedure name = "getAccountTransactions2" />
```

JavaScript procedure implementation: SQL statement query

Procedure

Assign your SQL query to a variable.

Add parameters, if necessary.

var getAccountsTransactionsStatement = "SELECT transactionId, fromAccount, toAccount, transactionDate, transactionAmount, transactionType " +
    "FROM accounttransactions " +
    "WHERE accounttransactions.fromAccount = ? OR accounttransactions.toAccount = ? " +
    "ORDER BY transactionDate DESC " +
    "LIMIT 20;";

Use the MFP.Server.invokeSQLStatement function to call prepared queries.

    function getAccountTransactions1(accountId){
        // MFP.Server.invokeSQLStatement calls prepared queries

Return the result to the application or to another procedure.

return MFP.Server.invokeSQLStatement({
    preparedStatement : getAccountsTransactionsStatement,
    parameters : [accountId, accountId]
});

JavaScript procedure implementation: SQL stored procedure

Procedure

Run an SQL stored procedure by using the MFP.Server.invokeSQLStoredProcedure function. Specify an SQL stored procedure name as an invocation parameter.

// Invoke stored SQL procedure and return invocation result 
function getAccountTransactions2 ( accountId ){ 
    // To run a SQL stored procedure, use the MFP.Server.invokeSQLStoredProcedure method 
    return MFP . Server . invokeSQLStoredProcedure ({
        procedure : "getAccountTransactions" , 
        parameters : [ accountId ] 
    }); 
}

Using multiple parameters

Procedure

When using multiple parameters in an SQL query, make sure to accept the variables in the function and pass them to the MFP.Server.invokeSQLStatement or MFP.Server.invokeSQLStoredProcedure parameters in an array.

var getAccountsTransactionsStatement = "SELECT transactionId, fromAccount, toAccount, transactionDate, transactionAmount, transactionType " +
    "FROM accounttransactions " +
    "WHERE accounttransactions.fromAccount = ? AND accounttransactions.toAccount = ? " +
    "ORDER BY transactionDate DESC " +
    "LIMIT 20;";

//Invoke prepared SQL query and return invocation result
function getAccountTransactions1(fromAccount, toAccount){
    return MFP.Server.invokeSQLStatement({
        preparedStatement : getAccountsTransactionsStatement,
        parameters : [fromAccount, toAccount]
    });
}

Output: JSON object

Results

Assuming that the following is the result set:

Table 1. Database entries
fromAccount	toAccount	transactionAmount	transactionDate	transactionId	transactionType
"12345"	"54321"	180.00	"2009-03-11T11:08:39.000Z"	"W06091500863"	"Funds Transfer"
"12345"	null	130.00	"2009-03-07T11:09:39.000Z"	"W214122\/5337"	"ATM Withdrawal"

Then the resulting JSON object is:

{
  "isSuccessful": true,
  "resultSet": [{
    "fromAccount": "12345",
    "toAccount": "54321",
    "transactionAmount": 180.00,
    "transactionDate": "2009-03-11T11:08:39.000Z",
    "transactionId": "W06091500863",
    "transactionType": "Funds Transfer"
  }, {
    "fromAccount": "12345",
    "toAccount": null,
    "transactionAmount": 130.00,
    "transactionDate": "2009-03-07T11:09:39.000Z",
    "transactionId": "W214122\/5337",
    "transactionType": "ATM Withdrawal"
  }]
}

The isSuccessful property defines whether the invocation was successful.
The resultSet object is an array of returned records.
- To access the resultSet object on the client-side, write:
```
result.invocationResult.resultSet
```
- To access the resultSet object on the server-side, write:
```
result.ResultSet
```