This page is a
translated version of the page
DB API and the translation is 100% complete.
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();
-
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();
-
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);
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
|
-
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);
sqlobject
|
|
type: object the object - the user or system database
|
action
|
"BEGIN"
|
type: string the action with the database
|
-
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);
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
|
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();
sqlobject
|
|
type: object the object - the user or system database
|
-
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();
RecordSetObj
|
|
type: object RecordSet object contains a selection, received by request from the database
|
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();
RecordSetObj
|
|
type: object RecordSet object contains a selection, received by request from the database
|
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);
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
|
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);
RecordSetObj
|
|
type: object RecordSet object contains a selection, received by request from the database
|
name
|
'id'
|
type: string column name
|
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);
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
|
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();
RecordSetObj
|
|
type: object RecordSet object contains a selection, received by request from the database
|
-
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
|