ARIS Community - We Love BPM

How to make query to SQL Sever from Aris script?

AndVGri's picture
by Andrey Gritsenko in Reports & Macros posted on 2017-04-13

Hi!
Is there a way to connect a Microsoft SQL Server and get a query result from Aris script? We need to synchronize data between external data source which exists on another SQL Server with objects attributes in Aris database.
On current moment we transfer those data into an Excel book and import them into objects attributes with Aris script from that book. It is a long way.
Regards,

7313 Views
0 Likes
2 Comments
Sorry there are no tags
There are no attachments
Ariene Kroeze posted on 2017-04-13

some years ago I created a small script to establish a connection to a SQL server. Maybe it still works:

 function ABC() {
    Dialogs.MsgBox('test');
};
 
function class_for_name(name) {
    return new Function('return new ' + name)();
}
 
var a = class_for_name('ABC');

function main() {

      // Create a variable for the connection string.
      var connectionUrl = "jdbc:sqlserver://localhost:1433;" +
         "databaseName=AdventureWorks;user=UserName;password=*****";

      // Declare the JDBC objects.
      var con = null;
      var stmt = null;
      var rs = null;

      try {
         // Establish the connection.
         class_for_name("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         con = new java.sql.DriverManager.getConnection(connectionUrl);

         // Create and execute an SQL statement that returns some data.
         var SQL = "SELECT TOP 10 * FROM Person.Contact";
         stmt = con.createStatement();
         rs = stmt.executeQuery(SQL);

         // Iterate through the data in the result set and display it.
         while (rs.next()) {
            Dialogs.MsgBox(rs.getString(4) + " " + rs.getString(6));
         }
      }

      // Handle any errors that may have occurred.
      catch (ex) {
         var lex=ex;;
         var aa=0;
      }
      finally {
         if (rs != null) try { rs.close(); } catch(ex) {}
         if (stmt != null) try { stmt.close(); } catch(ex) {}
         if (con != null) try { con.close(); } catch(ex) {}
      }
   
}
main()

greetings, Ariene Kroeze

Andrey Gritsenko posted on 2017-04-14

Thank you very match, Ariene Kroeze!

I will try.

Regards, Andrey.