Connect to Salesforce from AWS Lambda using JDBC driver

1. Set up AWS Toolkit in Eclipse
2. Set up AWS Credentials for managing AWS Lambda

AWS Lambda is an event-driven, serverless computing platform provided by Amazon as a part of the Amazon Web Services. Although AWS has out of the box solutions for connecting to RDS and Redshift, there would be scenarios where you would find yourselves needing to connect to a different datasource, which might be a SaaS data or Big data or No SQL data or the good old relational databases. One of the ways that you can connect to these data sources is by using Progress DataDirect JDBC drivers.

In this tutorial, we will walk you through how you can connect to Salesforce from AWS Lambda using Progress DataDirect JDBC driver.

1. Download DataDirect Salesforce JDBC driver from here.
2. To install the driver, you would have to execute the .jar package and you can do it by running the following command in terminal or just by double clicking on the jar package.
   java -jar PROGRESS_DATADIRECT_JDBC_SF_ALL.jar
3. This will launch an interactive java installer using which you can install the Salesforce JDBC driver to your desired location as either a licensed or evaluation installation.
4. Note that this will install Salesforce JDBC driver and bunch of other drivers too for your trial purposes in the same folder.

1. Create a new AWS Lambda Java Project in Eclipse, by following this tutorial from AWS.
2. After you have created the Project, add Progress DataDirect Salesforce driver (sforce.jar) to the build path. The driver would be present in the install location you have chosen previous section.

    

   For example: C:\Program Files (x86)\Progress\DataDirect

   

    

3. Replace the code in the method handleRequest with the following code.

   
   

   public String handleRequest(Object input, Context context) {

           context.getLogger().log("Input: " + input);

            

           String username = "<username>";

           String password = "<password>";

                

            

           try {

               Class.forName("com.ddtek.jdbc.sforce.SForceDriver");

           } catch (ClassNotFoundException ex) {

               context.getLogger().log("Error: class not found");

           }

            

    

           Connection connection = null;

            

           try {

               connection = DriverManager.getConnection("jdbc:datadirect:sforce://login.salesforce.com;DatabaseName=/tmp/db2;SecurityToken=<SecurityToken>", username, password);

           } catch (SQLException ex) {

               context.getLogger().log("Error getting connection: " + ex.getMessage());

               

           } catch (Exception ex) {

               context.getLogger().log("Error: " + ex.getMessage());

    

           }

            

            

           if(connection != null)

           {

               context.getLogger().log("Connected Successfully!");

           }

            

            

           String query = "SELECT * FROM " + input;

            

           ResultSet resultSet = null;

           try

           {

               //executing query

               Statement stmt = connection.createStatement();

               resultSet = stmt.executeQuery(query);

    

    

               ResultSetMetaData metaData = resultSet.getMetaData();

               int columnsNumber = metaData.getColumnCount();

    

    

               //Printing the results

               while(resultSet.next())

               {

    

                   for(int i = 1; i <= columnsNumber; i++)

                   {

                       System.out.printf("%-25s", (resultSet.getObject(i) != null)?resultSet.getObject(i).toString().replaceAll("\n", ""): null );

                        

                   }

                   System.out.print("\n");

               }

           }

           catch (SQLException ex)

           {

               System.out.println("Exception while executing statement. Terminating program... " + ex.getMessage());

           }

           catch (Exception ex)

           {

               System.out.println("General exception while executing query. Terminating the program..." + ex.getMessage());

           }

    

            

           String output = "Hello!";

           return output;

       }

   
   

4. The above code connects to Salesforce, queries a table -- which you can provide as an input to AWS Lambda function.
5. Fill in the Username, Password and Security Token in the above code. Build the code.
6. Upload the code to AWS Lambda as shown below:

   
   
   

7. Now, run the function on AWS Lambda, by using the same context menu in the above screenshot. As an input for the AWS Lambda function, provide the table name as shown below.

   
    

8. Your function should now start running and you should see the data from your table in the logs as shown below.

Although we have just logged the data, you can use this to write your own custom functions for your own business use cases or take the data from Salesforce to any RDS database. You can use any of the other Progress DataDirect JDBC drivers to connect to Oracle Eloqua, Oracle Sales Cloud, Oracle Service Cloud, SQL Server, DB2, Mongo DB etc., from AWS Lambda. If you are using Node.js, you can use the jdbc module to connect to these data sources via DataDirect JDBC driver. Feel free to try the Progress DataDirect JDBC drivers with AWS Lambda for a hassle-free connectivity.