Sample simple database query script

[EcoStruxureIT]

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();