Материал из for iRidium developers
Перейти к: навигация, поиск
Эта страница является переводом страницы ODBC. Перевод выполнен на 100%.

Other languages:
English • ‎русский

iRidium server provides functions, controlled via script, that allow to get local or remote access to such databases as MySQL, PostgreSQL, SQLite, Oracle, SQL Server, Microsoft Access, dBase, Paradox, FoxPro, etc. via ODBC (Open Database Connectivity) .

ODBC is a commercial driver in iRidium pro. It is availabe in iRidium pro 1.2.10 and later.

ODBC driver works only on iRidium server, installed on Windows OS (x86/x64). Functions for ODBC and support of some DBMS (SQL Server, Microsoft Access, dBase, Paradox, FoxPro) are included to the standard installation packet for Windows OS by default.

To work with other databases, download software for ODBC connection to Windows OS from the manufacturer of DBMS. For example, to work with MySQL, download Connector/ODBC app for Windows: https://dev.mysql.com/downloads/connector/odbc/


1 Find ODBC Data Sources (32-bit or 64-bit) app and launch it in Windows.

ODBC administrator.png

2 Add a new connection to the database and set it.

ODBC config.png

3 Write a script that creates ODBC object and sends a request to the data base using the .Query() method.

To activate a license on the server correctly, connect to ODBC in the script not by EVENT_START, but with a delay, for example using IR.SetTimeout() function or by pressing a button.


Example. Writing in the database

This script gets data about electricity consumption and temperature from two sources: an electricity meter and temperature sensors via Modbus TCP protocol and writes received data in the SQLite local database via ODBC mechanism.

Data can be written in any local or remote SQL database the same way.

var SQLite = false;
var query_head = "INSERT INTO measure(datetime, consumption, temperature1, temperature2) VALUES(";
var cdt = 0;
var cons = 0;
var temp1 = 0;
var temp2 = 0;
 
IR.AddListener(IR.EVENT_START,0,function()
{
   IR.SetInterval(1000, getData);
});
 
function getData()
{
   if (!SQLite)  //if no ODBC object
   {
      SQLite = new ODBC("", "", "sqlite3"); // creating ODBC object
      if (SQLite)
      {
         IR.Log(SQLite + " connected!");
      }
   }
   else
   {    
      var currentdatetime = new iDate();             
      cdt = currentdatetime.value;
      // current date and time in OLE Automation date format (OADate)
      cons = IR.GetDevice("Modbus TCP").GetFeedback("Channel 1");
      temp1 = IR.GetDevice("Modbus TCP").GetFeedback("Channel 2");
      temp2 = IR.GetDevice("Modbus TCP").GetFeedback("Channel 3");
      var query = query_head + cdt + "," + cons + "," + temp1 + "," + temp2 + ");";
                         /*(datetime, consumption, temperature1, temperature2)*/      
      var response = SQLite.Query(query); // SQL query to SQLite database
      IR.Log(query);   
   }
}

Example. Reading from database

In this example a request is made to MySQL database, the received answer is unpacked in an array and the first item of "User" field is displayed.

When requests to read from the SQL database are made, mind the limits of the memory size and the processor performance.

IR.AddListener(IR.EVENT_START,0,function()
{
    IR.SetTimeout(50, getData);
    // waiting 50 milliseconds for appropriate iRidium ODBC driver license activation
});
function getData()
{
   var MySQL = new ODBC("root", "", "MySQLresurs"); // creating database connection object
   var response = MySQL.Query("SELECT * FROM users"); // SQL query to MySQL database
   if (response)          // if connection is successful and answer exists
   {
       var rows = response.GetRows();
       var columns = response.GetColumns();
       var data = {};               // two-dimension array with query result
       for (var i = 0; i < columns; i++) {
           var colName = response.GetColumnName(i);
           data[colName] = [];
           for (var j = 0; j < rows; j++) {
               data[colName][j] = response.GetRowValue(i, j);
           }
       }
    response.Free(); // free memory of recordset (optional action for memory optimization)
    IR.Log(data["User"][0]);   // first user in MySQL table
    }
}


ODBC object


new ODBC()

It creates ODBC object that is connected to the data base and contains the only method - Query().

Syntax

new ODBC(login, password, dsn);

input value description
login 'myname' type: string
Login (user name) to connect to the database.
password 'qwerty' type: string
Password to connect to the databases (can be empty for the local database)
dsn 'MySQLresurs' type: string
ODBC data source name (DSN)
output
object [object DevicePrototype] type: object
ODBC connection


Sample

var MySQL = new ODBC("root", "", "MySQLresurs");
IR.Log(MySQL); // [object DevicePrototype]

to send a request to an object, write the object in the variable


Methods of ODBC object


.Query()

It makes a request to the data base and returns an answer as a RecordSet selection.

Selections of SQL requests can be quite large. When a developer works with large selections, he is to mind such limitations as the size of RAM, performance of processor and communication controllers, bandwidth of network interfaces of the system. It is recommended to limit the size of selections.

Syntax

ODBCobj.Query(query);

input value description
ODBCobj [object DevicePrototype] type: object
Connection object to ODBC
query 'SELECT * FROM orders' type: string
Request line
output
RecordSetObj [ ] type: object
Selection as RecordSet object. If a request does not need an answer, false value is returned.


Sample

var response = DB.Query("SELECT * FROM users");