Work with Personal Databases

Creation of Personal Database

var mybase= new SQL(); 

Now create a database file.

mybase.Open('Example.db');

Logical commands are required to write in the databasa fast and correctly.

mybase.Execute("BEGIN");
...
// operations with the database; 
...
mybase.Execute("COMMIT"); 
mybase.Close();

The created database is empty. It does not contain any values, tables or field names. To create a table in the base, use the following

mybase.Execute('CREATE TABLE Info(ID int, Value double, Name string)');

where CREATE TABLE is a function to create a table with info name,
ID, Value, Name are names of table fields, that will contain values of the following types int, double, string. You can change field names, but you must always define the field type.

Use the following structure to write:

mybase.Execute('INSERT INTO Info(ID, Value, Name) VALUES(1,2.5,"sunny day")'); 

Brackets after value must contain as many parameters as there are fields in the brackets after INSERT INTO <Table name>().

There are certain peculiarities in writing values in table fields.
For example, if you want to write text, the syntax will look this way:

VALUES("Iridium", "Mobile"); (Text is written in double inverted commas).

If you want to write a number, do it this way:

VALUES( 1, 2.5, 36.788); (Number is written without inverted commas, decimal number is written with a point)

If you want to write variable value, use the following:

// var CV; (variables defined earlier)
// var V;
...
VALUES( '+CV+', '+V+' );

Deletion of Tables, Addition of Columns

To delete a table from the database, use the followign method:

mybase.Execute('DROP TABLE Testing');

To add a new column in the created table, us the following method:

mybase.Execute('ALTER TABLE Testing DROP COLUMN New_column');

Creation of Requests

In iRidium Script Editor you can write requests that will log data on certain conditions. To work with requests, study SQL language. Some of the frequently used scenes of working with the base are described below.

var mybase= new SQL(); // присвоили переменной sql свойства системной базы.

var this_request = mybase.Request('SELECT ...'); // assign this_request variable Request mybase property. Write body of request in brackets and invertied singular commas.

// logs the number of fields (columns) in the request
IR.Log(this_request.Columns); 

// logs the number of lines in the request
IR.Log(this_request.Rows);
 
//logs cell value in the line with indexes (line, column);
IR.Log(this_request.GetRowValue(0,0)); 

//logs field name, whose index agrees with the number in the brackets.
IR.Log(this_request.GetColumnName(0)); 

function request()
{  
    //Base opening. If there is no base, it is created automatically.
    mybase.Open('Example.db');
   
    //Starting work with the base
    mybase.Execute("BEGIN");
   
    // Creating a variable and assigning Request method to it
    var this_request = mybase.Request('SELECT * FROM Info WHERE Value<10');

    //logs field name whose index agrees with the number in the brackets.
    IR.Log(this_request.GetColumnName(0)+ '\t'+ this_request.GetColumnName(1)+ '\t'+     this_request.GetColumnName(2)); 

    for (i=0;i<this_request.Rows;i++) 
        //logs filed values
        IR.Log(this_request.GetRowValue(0,i)+ '\t'+ this_request.GetRowValue(1,i)+ '\t'+ this_request.GetRowValue(2,i));

    //Ending work with the base
    mybase.Execute("COMMIT"); 
  
    //Base closing       
     mybase.Close();
}