For up-to-date product documentation, see the IBM MobileFirst Foundation Developer Center.


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. We 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:

Parent topic: Developing JavaScript adapter code


Configuring the adapter.xml descriptor file


Procedure

  1. 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>

  2. 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 we use when implementing the procedure itself.

    <procedure name = "getAccountTransactions1" /> <procedure name = "getAccountTransactions2" />


JavaScript procedure implementation: SQL statement query


Procedure

  1. Assign your SQL query to a variable.
  2. 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;";

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

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

  4. 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" }] }