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

Other languages:

iRidium API. Databases

The system and user SQL databases store information about changes of server variables in iRidium Server. On the basis of this information you can create graphs and logs

SQLite version: 3.8.7

To create requests to databases get acquainted with the SQL language.

System Database

It is available for reading only. It is filled with data from tags of the server project in which the data saving in the database is set up.

IR.GetDatabase()

Get access to the system database as to an SQL object to read data from it.

the constructor of SQL objects. It creates SQL objects for work with the SQLite database

Syntax

IR.GetDatabase();

input

value

description

-

output

Object [object SQLPrototype] type: Object
объект - the system database

Sample

var systembase = IR.GetDatabase();
IR.Log(systembase); // [object SQLPrototype]

the system database is available for reading only

User Databases

They are available for reading, writing, editing and deleting. Create your own database with the help of the constructor and fill it in with the help of SQL object methods.

new SQL()

the constructor of the SQL for creating user SQLite databases

Syntax

new SQL();

input

value

description

-

output

Object [object SQLPrototype] type: Object
the object - the user database

Sample

var mybase = new SQL(); 
IR.Log(mybase) // [object SQLPrototype]

to refer to the object write it in the variable

Methods of SQL Objects

To form a user database, create an SQL object and write it in the variable (name it database). You can apply the following methods to the object:

.Open()

to open (create) the database file

Syntax

sqlobject.Open(file, undeleted);

input

value

description

sqlobject type: object
the object - the user or system database

undeleted true type: boolean
folder:
false - deleted folder
...\iRidium pro documents\Server\Documents\
true - undeleted folder
...\iRidium pro documents\Server\Database\UserDB\

file "MyBase.db" type: string
the name of the database file

output

-

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
   mybase.Open('MyBase.db', true);
   mybase.Execute("BEGIN");
   mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
   mybase.Execute("COMMIT");  
   mybase.Close();
});

If there is no such file, it will be created in the folder of project documents, for example:
C:\Users\User\Documents\iRidium pro documents\Server\Documents\<Project Name>\MyBase.db
or
C:\Users\User\Documents\iRidium pro documents\Server\Database\UserDB\<Project Name>\MyBase.db

.Execute()

to perform actions with the database. Available actions are described in the SQL language

Syntax

sqlobject.Execute(action);

input

value

description

sqlobject type: object
the object - the user or system database

action "BEGIN" type: string
the action with the database

output

-

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
   mybase.Open('MyBase.db');
   mybase.Execute("BEGIN");
   mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
   mybase.Execute("COMMIT");  
   mybase.Close();
});

Use logic commands to write in the database fast and correctly:

mybase.Execute("BEGIN");   // to start work with the database
...
// actions with the database 
...
mybase.Execute("COMMIT");  // to finish work with the database

.Request()

requests to the database allow you to indicate conditions for data selection from the database. The rules for forming conditions are described in the SQL language

Syntax

sqlobject.Request(conditions);

input

value

description

sqlobject type: object
the object - the user or system database

conditions 'SELECT * FROM Info WHERE Value<100' type: string
the body of the request to the database

output

RecordSet Object [object RecordSetPrototype] type: object
the RecordSet object, it contains the request result

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");  
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumnName(0)+'\t'+RecordSetObj.GetColumnName(1)+'\t'+RecordSetObj.GetColumnName(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});

in response to the request we get the RecordSet object which contains the request result

.Close()

to finish work with the database

Syntax

sqlobject.Close();

input

value

description

sqlobject type: object
the object - the user or system database

output

-

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Close();
});

Before finishing work with the database, save all changes with the command mybase.Execute("COMMIT")

RecordSet Object

The RecordSet object - it is selection of records from the SQL database. We receive it after by request to the database:

var RecordSetObj = sqlobject.Request('SELECT <request>');

Example:

// to get all records from the Info table where the Value field < 100
var RecordSetObj = database.Request('SELECT * FROM Info WHERE Value < 100');
IR.Log(RecordSetObj); // [object RecordSetPrototype]

The RecordSet object has unique properties. You can apply methods specific only for the RecordSet object.

Methods of RecordSet Object

RecordSet - it is a selection of records in the SQL database which you receive by request to the database. You can apply methods of work with database rows and columns to the RecordSet object. You can request object properties.

.GetRows()

returns the number of lines in RecordSet selection

Syntax

RecordSetObj.GetRows();

input

value

description

RecordSetObj type: object
RecordSet object contains a selection, received by request from the database

output

rows 1 type: number
number of lines in the selection

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetRows());
      mybase.Execute("COMMIT");
      mybase.Close();
});

.GetColumns()

returns the number of columns in RecordSet selection

Syntax

RecordSetObj.GetColumns();

input

value

description

RecordSetObj type: object
RecordSet object contains a selection, received by request from the database

output

columns 1 type: number
number of columns in the selection

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumns());
      mybase.Execute("COMMIT");
      mybase.Close();
});

.GetColumnName()

the column name (the column index: 0...n)

Syntax

RecordSetObj.GetColumnName(index);

input

value

description

RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database

index 0 type: number
the column index, 0...n

output

column name ID type: string
the column name

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');     
      IR.Log(RecordSetObj.GetColumnName(0)+ '\t'+ RecordSetObj.GetColumnName(1)+ '\t'+ RecordSetObj.GetColumnName(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});

.GetColumnIndex()

gives the column index by its name

Syntax

RecordSetObj.GetColumnIndex(name);

input

value

description

RecordSetObj type: object
RecordSet object contains a selection, received by request from the database

name 'id' type: string
column name

output

index 0 type: number
column index (numeration of columns starts with 0)

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumnIndex('Value')); // 1
      mybase.Execute("COMMIT");
      mybase.Close();
});

.GetColumnType()

the column type: int (1), float (2), string (3). The column index: 0...n

Синтаксис

RecordSetObj.GetColumnType(index);

на входе

значение

описание

RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database

index 0 type: number
the column index, 0...n

на выходе

type

1

type: number
the data type:

1 - int
2 - float
3 - string

Пример

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetColumnType(0)+ '\t'+ RecordSetObj.GetColumnType(1)+ '\t'+ RecordSetObj.GetColumnType(2));
      mybase.Execute("COMMIT");
      mybase.Close();
});

.GetRowValue()

the value in the table cell selected by the indexes of the column and row (indexes: 0...n)

Syntax

RecordSetObj.GetRowValue(column, row);

input

value

description

RecordSetObj type: object
the RecordSet object, it contains data selection received by request to the database

column 0 type: number
the column index, 0...n

row 0 type: number
the row index, 0...n

output

data 1 type: number, string
the value in the cell

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN"); 
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      IR.Log(RecordSetObj.GetRowValue(0,0)+ '\t'+ RecordSetObj.GetRowValue(1,0)+ '\t'+ RecordSetObj.GetRowValue(2,0));
      mybase.Execute("COMMIT");
      mybase.Close();
});

.Free()

frees memory from RecordSet selection. It is used for optimal memory use.

Syntax

RecordSetObj.Free();

input

value

description

RecordSetObj type: object
RecordSet object contains a selection, received by request from the database

output

-

Sample

var mybase = new SQL();
IR.AddListener(IR.EVENT_START,0,function()
{
      mybase.Open('MyBase.db');
      mybase.Execute("BEGIN");
      mybase.Execute('CREATE TABLE Info(ID INT, Value REAL, Name TEXT)');
      mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,19.9,"sunny day")'); 
      var RecordSetObj = mybase.Request('SELECT * FROM Info WHERE ID<30');  
      mybase.Execute("COMMIT");
      mybase.Close();
      RecordSetObj.Free();
});

RecordSet object properties

RecordSetObj.property

Rows 4 type: Number
the number of rows in the table

Columns 12 type: Number
the number of columns in the table

Содержание