Database integration

You can integrate IT Advisor: Energy Efficiency with any JDBC or ODBC compliant database. To do this, you must configure a data provider in the startup script. The configuration of the data provider, such as a database, is done in the startup script. You may need to add additional database drivers to connect to the database, if the generic drivers shipped with ITA: Energy Efficiency are not sufficient. Place these files in the folder /opt/jboss-as/server/isxo/lib on the server. The sample script shows you how to connect to a Microsoft SQL database. The additional database drivers are located in the file sqljdbc.jar, and the class name of the database driver is SQLServerDriver. Replace these values with values of choice to integrate to other databases. /* * Copyright (c) 2011 Schneider Electric. * * DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of * any kind, to the fullest extent permitted by law. Schneider Electric does not warrant or * guarantee the individual success developers may have in implementing the sample code on their development * platforms or in using their own Web server configurations. * * Schneider Electric does not warrant, guarantee or make any representations regarding the use, * results of use, accuracy, timeliness or completeness of any data or information relating to the * sample code. Schneider Electric disclaims all warranties, express or implied, and in particular, * disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related * to the code, or any service or software related thereto. * * APC by Schneider Electric shall not be liable for any direct, indirect or consequential damages or costs * of any type arising out of any action taken by you or others related to the sample code. */ /* * This script shows how to load the database driver for the MS SQL database. * * The file and class names will vary between database providers. Please consult the documentation of * the JDBC driver applicable to your database system of choice for further information. */ // Make the necessary classes available to the script importPackage(com.apc.e2m.server.scripting); // Define location of driver file driverFile = "sqljdbc.jar"; // Add the driver to the classpath ScriptClassPath.addFile(driverFile); // Load the driver class java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");      

You can integrate IT Advisor: Energy Efficiency with any JDBC or ODBC compliant database. To do this, you must configure a data provider in the startup script. An example of how to perform a simple database query from an IT Advisor: Energy Efficiency script. /* * Copyright (c) 2011 Schneider Electric. * * DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of * any kind, to the fullest extent permitted by law. Schneider Electric does not warrant or * guarantee the individual success developers may have in implementing the sample code on their development * platforms or in using their own Web server configurations. * * Schneider Electric does not warrant, guarantee or make any representations regarding the use, * results of use, accuracy, timeliness or completeness of any data or information relating to the * sample code. Schneider Electric disclaims all warranties, express or implied, and in particular, * disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related * to the code, or any service or software related thereto. * * Schneider Electric shall not be liable for any direct, indirect or consequential damages or costs * of any type arising out of any action taken by you or others related to the sample code. */ /* * This script shows how to make a simple connection to an MS SQL database * and read measurements using SQL * * The database drivers (JDBC drivers) should be loaded in the startup script. An example of this is available * in the file databasedriver.js in the startup folder. * * This script assumes that the MS SQL database driver has been loaded. */ // Make Java SQL classes available to the script importPackage(java.sql); // Define connection properties // The properties needed to connect to the database and the format of the connection URL differ // between different database. Please consult the documentation of the JDBC driver for the database used // for specific information on this matter username = "user1"; password = "password1"; server = "159.215.173.127"; port = 1134; databaseName = "sample"; connectionUrl = "jdbc:sqlserver://" + server + ":" + port + ";databaseName=" + databaseName + ";user=" + username + ";password=" + password + ";" // Create a connection to the database connection = DriverManager.getConnection(connectionUrl); // Create a statement to execute SQL // The sample database contains the Measurements table with two columns: measurement_id and measurement_value // We want to extract the value of the measurement with id 11111111 sql = "SELECT measurement_value FROM measurements WHERE measurement_id = '11111111'"; // Create and execute statement statement = connection.createStatement(); result = statement.executeQuery(sql); // Move cursor to first result and retrieve the value of measurement_value result.next(); value = result.getDouble("measurement_value"); // Return measurement returnValue = value; //Release resources result.close(); statement.close(); connection.close();    

You can integrate IT Advisor: Energy Efficiency with any JDBC or ODBC compliant database. To do this, you must configure a data provider in the startup script. An example of how to retrieve data from a database and calculate an average. /* * Copyright (c) 2011 Schneider Electric. * * DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of * any kind, to the fullest extent permitted by law. Schneider Electric does not warrant or * guarantee the individual success developers may have in implementing the sample code on their development * platforms or in using their own Web server configurations. * * Schneider Electric does not warrant, guarantee or make any representations regarding the use, * results of use, accuracy, timeliness or completeness of any data or information relating to the * sample code. Schneider Electric disclaims all warranties, express or implied, and in particular, * disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related * to the code, or any service or software related thereto. * * Schneider Electric shall not be liable for any direct, indirect or consequential damages or costs * of any type arising out of any action taken by you or others related to the sample code. */ /* * This script shows how to make a simple connection to an MS SQL database * and read measurements using SQL * * The database drivers (JDBC drivers) should be loaded in the startup script. An example of this is available * in the file databasedriver.js in the startup folder. * * This script assumes that the MS SQL database driver has been loaded. */ // Make Java SQL classes available to the script importPackage(java.sql); // Define connection properties // The properties needed to connect to the database and the format of the connection URL differ // between different database. Please consult the documentation of the JDBC driver for the database used // for specific information on this matter username = "user1"; password = "password1"; server = "159.215.173.127"; port = 1134; databaseName = "sample"; connectionUrl = "jdbc:sqlserver://" + server + ":" + port + ";databaseName=" + databaseName + ";user=" + username + ";password=" + password + ";" // Create a connection to the database connection = DriverManager.getConnection(connectionUrl); // Create a statement to execute SQL // The sample database contains the Measurements table with two columns: measurement_id and measurement_value // We want to extract the value of the measurement with id 11111111 sql = "SELECT measurement_value FROM measurements WHERE measurement_id = '11111111'"; // Create and execute statement statement = connection.createStatement(); result = statement.executeQuery(sql); // Move cursor to first result and retrieve the value of measurement_value result.next(); value = result.getDouble("measurement_value"); // Return measurement returnValue = value; //Release resources result.close(); statement.close(); connection.close();