JDBC Adapter & Templates in webMethods

  JDBC Templates in WebMethods :

The JDBC adapter template is like a ready-made configuration that makes it easy for developers to connect to and work with databases. It simplifies the process by providing standard settings and commands, saving time and effort when integrating databases into applications.

Below is a list of JDBC adapter templates available in webMethods :

• CustomSQL : CustomSQL in the JDBC Adapter lets you create and use your own custom queries for working with a database. Instead of using the built-in actions provided by the adapter, you can write and run your own SQL statements to do specific things in your app’s database. 
• SelectSQL :  SelectSQL in JDBC Adapter refers to a feature that allows you to execute SQL queries by configuring it graphically and retrieve data from a database. It enables you to fetch specific information from the database tables and use that data in your webMethods Integration Server processes or services.
• UpdateSQL: It allows you to perform updates or modifications to data in a database by defining condition graphically. It simplifies the process of updating records in the database, making it more efficient and flexible for developers working with webMethods Integration Server and databases. reframe the sentence.
• InsertSQL :It allows you to insert data into a database table from your webMethods Integration Server application. It simplifies the process of adding new records to the database and is particularly useful for applications that need to store data in a structured way.
• DeleteSQL :It allows you to define SQL queries and condition graphically for deleting data from a database table. It is a part of webMethods Integration Server and provides a simple way to perform delete operations on records in a database table.
• DynamicSQL : It refers to the capability of constructing SQL queries on-the-fly at runtime, rather than having them predefined in the adapter configuration. This allows for more flexible and dynamic interactions with databases, as the SQL statements can be generated based on varying conditions or user inputs during the execution of the application. It provides greater adaptability when dealing with complex and changing data requirements in webMethods Integration Server.
• StoredProcedure : It refers to a pre-defined configuration that allows developers to easily call and execute stored procedures (a set of pre-written database operations) in a database. t simplifies the process of working with stored procedures, making it convenient to interact with databases in webMethods Integration Server without writing complex SQL queries manually.
• BatchInsertSQL : It is a feature that allows you to efficiently insert multiple rows of data into a database at once. Instead of inserting one row at a time, BatchInsertSQL groups several rows together and sends them to the database in a single operation, reducing the overhead and improving the performance of data insertion
• BatchUpdateSQL : This template allows you to efficiently perform multiple database update operations at once. Instead of sending individual updates, you can group them together as a batch, which can significantly improve performance and reduce network overhead when working with databases. 
• StoredProcedureWithSignature : This template refers to a configuration that enables the execution of stored procedures in a database with input and output parameters. It allows developers to call specific pre-defined procedures in the database, passing values to them, and retrieving results as needed, making it easier to work with complex database operations.

Note : Please Click below lick and download the SQL query to create and feed data to the table which will help us in the template implementation.

CustomSQL Implementation :

• • Create a folder with name ‘adapters’, where we will store our adapter services as part of the implementation plan.
  • Right click on ‘adapters’ folder and create a new adapter service with name ‘customSQLDemo’ and click next.
    
    

     

• Now you will be prompted to select the type of adapter you want to create. In our case, we need a JDBC adapter, so please select “webMethods Adapter for JDBC” and then click on the “Next” button.
  

   

• Now, you will be prompted to select the connection name for creating the adapter. In the previous article, we demonstrated how to create a JDBC connection. If you haven’t already gone through that, please click here to refer it first. Once you have the JDBC connection, select it, and then click “Next” to proceed.

• Next, you will be prompted to choose the adapter template. Please select “CustomSql” and then click on the “Finish” button.

• Configure the Custom Sql like below.
• In the “SQL” field, enter the following SQL query: “Select * from Employee where EmpId=?”. This query will retrieve the details of the Employee from the table, where the ‘EmpId’ will be provided at runtime.
• In the ‘Input JDBC Type’ section define your jdbc variable type and in ‘Input In the ‘Input JDBC Type’ section, specify the type of your JDBC variable. Then, in the ‘Input Field Type’ section, define the data type in which you will supply the value. Lastly, in the ‘Input Field’ section, provide the variable name for reference. In our case 
  • Input JDBC Type = varchar
  • Input Filed Type=java.lang.String
  • Input Field=EmpId
• Configure the ‘Result Field’ as “Count” and set the ‘Result Field Type’ to “java.lang.String” to obtain the count of the rows retrieved from the table.

• Now we have done with the configuration, now if you open the ‘Input/OutPut’ tab you will observe that the variable which we have defined has been created.

• Now let’s run the service to see if it is working as expected, run the adapter service by giving ‘EmpId’ as ‘100’.
• Congratulations!! we got our result as expected.

SelectSQL Implementation :

• In the previous section, we explored the custom SQL implementation, where we manually wrote an SQL query and used the EmpId to fetch records from the Employee table. Now, let’s explore an alternative method to achieve the same result without writing the SQL query by ourselves.
• Let’s replicate the procedure we used to create the CustomSql adapter service and create a new adapter service called “SelectSQLDemo” using the SelectSQL Template.
• Configure below properties in the Select SQL
  • In the Table tab of the adapter select the Employee table from current  schema.

    

     

    

     

  • Now we have joins tab which we are not going to use as our select query does not need the joins.
  • In the SELECT tab, choose “All” to retrieve all columns or “DISTINCT” to fetch distinct values. Then, select the specific columns you require from the table. Finally, you can configure the result field and result field type, just like we did in the earlier demo, to customize the output as per your requirements.
    

     

  • Moving to the WHERE tab, we need to specify the condition for the adapter to retrieve values. To do this, click on ‘insert rows’ and select “empid” from the column dropdown. If needed, you can set multiple conditions by defining the column and operator as per your specific requirements and save it. This will help filter the data based on the specified conditions.

    

     

• Now all the configuration has been done run the adapter service by giving the empId as 100 and see the result😊
  

   

UpdateSql implementation :

• create a new adapter service called “updateSQLDemo” using the updateSQL template.
• In the Table tag, choose the “Employee” table, just as we did in the previous demonstration.
• In the UPDATE tab, insert the row for the update operation and then select the column names that you wish to update. For this particular demonstration, we will update the Employee name, so let’s choose “empname” from the dropdown list. This will set the target column to be updated in the SQL query.
  

   

• In the WHERE tab, you can define the condition that specifies which records should be updated with the new Employee Name. Let’s use the same condition that we utilized earlier, such as selecting the “empid” column from the dropdown and setting the appropriate operator. 
  

   

• Now in the result field define the Result Filed as Count and the type as String.
  

   

• Now we are done with configuration so let’s run it with below input and see the out come.
  
   
• Congratulations!! We did it.

  

   

  

   

   

InsertSQL Implementation :

• Create a adapter service with name ‘insertSQLDemo’ and select InsertSQL template.
• In Table tab select the Employee table from curentschema.
• In the INSERT tab define the columns in which you want to insert values.

• Let’s run it and see if it works…

• Congratulations !! We got the appropriate result.

Delete SQL Implementation :

• Create a adapter service with name ‘deleteSQLDemo’ and select DeleteSQL template.
• Configure the Table and Result tabs in the same manner as we did in the earlier demonstration.
• In the WHERE tab, specify the condition that will be used to delete records from the table. To do this, insert the row and select the “empid” column from the dropdown list. 

• Let’s run the service with empid set to 103.
• Hurrah !!! We got the result!

DynamicSQL Implementation :

• Create a adapter service with name ‘dynamicSQLDemo’ and select DynamicSql template.
• Now in the SQL field write below query and save the adapter service(Select * from ${table} where ${column}=’${value}’;).

• The provided SQL query allows for dynamic runtime configuration of the table name, column name, and its corresponding value. This means that the specific table, column, and value can be determined and set during the execution of the code, enabling flexibility in retrieving the desired data from the database. However, it’s crucial to ensure proper handling and validation of the dynamic inputs to avoid security vulnerabilities like SQL injection.
• Upon opening the input/output of the adapter, you will observe that three variables have been automatically generated with the names specified within curly braces {}. These variables will be utilized to extract and store the corresponding values from the table during the adapter’s execution.

• Let’s execute the service by providing the following values for the variables:

• We got the result as expected😀.

• Now let’s run the same adapter service by changing column name and respective value like below.

• Congratulations!! we got the desired result.

StoredProcedure implementation :

• Create a adapter service with name ‘procedureDemo’ and use stored Procedure template.
• Please select the stored procedure as depicted in the screenshot from the Call tab.

• In the Result Set tab, specify the field details for the expected output as shown below.

• Now we are done with the configuration, let’s run the service and observer the output.
• Congrats We did it!!

StoredProcedureWithSignature Implementation :

• Create an adapter service with name ‘storedProcedureWithParam’ using the StoredProcedureWithSignature template.
• Upon selecting the schema details and the Stored procedure, you will observe that the defined input in the Stored procedure is automatically populated in the parameter section.

• Configure the ResultSet tab with the following fields, as depicted in the screenshot and save it.

• Now let’s Run the service by giving ’empIdParam’ as 100 and see the outcome.
• Hurrah !!! We did it.

BatchInsertSQL Implementation :

• Create an adapter service with name ‘batchInsertDemo’ using BatchInsertSQL template.
• Upon creating the adapter service, you may encounter an error message indicating that for batch insert transactions, the transaction type should be set to ‘Local Transaction.’ To resolve this, you have two options: you can either create a new connection with the same details, setting the transaction type to local, or you can update the existing connection to have the correct transaction type.

•  Now try to select the “Employee” table in the Table tab. If it is not visible, recreate the adapter service to ensure that it becomes visible and select it.

• In the Insert tab, specify all the columns exactly as demonstrated in the snapshot.

• Now let’s save the adapter service and run with below inputs.

• Congrats We achieved our goal!!

BatchUpdateSQL Implementation :

• Create an adapter service with name ‘batchUpdateDemo’ using batchupdate template.
• In the Table tab, choose the table on which you wish to execute the update, just as we did in the previous adapter service.
• In the Update tab, specify the columns you wish to update. For this demonstration, we will only select the “EmpPhoneNo” field to update. In the Where tab, configure the condition for the update operation.

• Upon opening the Input Output tab, you will observe that a document list has been generated in the input section, containing ‘EmpId’ and ‘EmpPhoneNo’ fields. This enables you to provide multiple inputs during runtime for the update operation.
• Let’s run the service by giving below input.

• Congrats! We got the result as expected😀.