Эта страница является переводом страницы ODBC. Перевод выполнен на 100%.

---

Handbook

ODBC

Working with databases via ODBC

methods to work with ODBC

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, but you can test it in the Test Mode. 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/ (Bitness of software for ODBC-connection must agree with bitness of iRidium server.)

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

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

It is not recommended to set User and Password in ODBC Data Sources app for security reasons, as they are not encrypted there. iRidium server sends these data when it is connected to the database.

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.

Testing connection to the data base

When ODBC object is created, the script can't identify connection to the dat base and sends a request even if there is no connection. In this case connection to database must be tested beforehand. For example, it's necessary to connect to a database created in MySQL. To do it, create a project and create a script in it.

IR.AddListener(IR.EVENT_START,0,function()
{  
   var BaseMySQL = new ODBC("root", "123", "MySQLresurs");
   if(BaseMySQL == false)
   {
      IR.Log("Wrong connection");
   }
   else
   {
      IR.Log("Right connection");
   }
});

Launch the project:

• if MySQL is connected, data appear in the log;
• if MySQL is not connected, false value appears in the log.

It's necessary to do the same test with a server project.

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 false, if connection failed (nonexistent connection or wrong password)

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