How to get query results to an external DB via MIDServer

[SAI156]

Hi,All

I intend to execute SQL queries to an external DB via MIDServer.
Can I immediately return the results of the retrieval to the ServiceNow instance to receive the values?

[Appli]

Hi, you can create Data Source of JDBC type and execute SQL query over specified MID server.

Data may be returned instantly (if you will click "Load All Records" under Related Links), correct.

[SAI156]

Thanks for the reply!
The replies I received were very helpful!
For example, is it possible to already have a batch file on the MID server and also immediately get the results of an SQL query executed from that batch file?

[Rahul Kumar17]

hi,

Yes, it is possible to immediately return the results of the retrieval to the ServiceNow instance by using a MID Server script include to execute the SQL queries and then returning the results to the calling script. Here's an example:

1. Create a script include in ServiceNow that will contain the MID Server script code. For example, create a script include named "MySQLQueries" and set the "Client callable" option to true.

2. Write the MID Server script code in the script include to execute the SQL query using a JDBC driver for the external database. Here's an example of how to execute a SQL query using the JDBC driver:

var db = new Packages.java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");
var stmt = db.createStatement();
var rs = stmt.executeQuery("SELECT * FROM mytable");

var results = [];

while (rs.next()) {
var row = {};
row.column1 = rs.getString("column1");
row.column2 = rs.getString("column2");
results.push(row);
}

rs.close();
stmt.close();
db.close();

return results;

1. In the ServiceNow script where you want to retrieve the results, call the script include function and pass any necessary parameters. Here's an example:

var results = new MySQLQueries().mySQLQueryFunction(param1, param2);

In this example, "mySQLQueryFunction" is the function name in the script include that executes the SQL query and returns the results.

1. You can now use the "results" variable in your ServiceNow script to process the returned data as needed.

Note: Be sure to properly secure the MID Server script by configuring the MID Server user to only have access to the necessary tables and columns in the external database.

Thanks,

Rahul Kumar

[SAI156]

Thanks for the replies!
The replies I received were very helpful!
For example, I already have a batch file on the MID server. Can I also immediately get the results of an SQL query executed from that batch file?