Work with Personal Databases


Creation of Personal Database

Tables/fields/values can be added to and deleted from the created database, database components can be updated. To begin with, create a variable. where you will write database properties.


var mybase= new SQL(); 
where mybase is variable,
new SQL() - definintion of the database in Iridium JS.


Now create a database file.



mybase.Open('Example.db');
where .Open is opening a database file. If such a file does not exist, it wiil be created in the program documents folder, for example, C:\Users\UserName\Documents\iRidium mobile 3\Settings\ Server\Database\'Example.db' - database name with resolution.



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



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

where .Execute - функция выполнения действия с базой,
"BEGIN" - beginning of writing to the base,
"COMMIT" - end of writing to the base,
.Close() - closure of database;


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

where INSERT INTO writes to Info table, to be more exact to the following fields: ID, Value, Name.
VALUES are parameters that are written in the fields. Number 1 is written in ID field, number 2,5 is written in Value field, text "sunny day" is written in the Name field.



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+' );
First one inverted comma is put, then a plus, then the variable itself, then a plus and one inverted comma.


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 свойства системной базы.

To make a request to the base, do the following:
var this_request = mybase.Request('SELECT ...'); // assign this_request variable Request mybase property. Write body of request in brackets and invertied singular commas.
How to log a request:
// 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)); 

For example, it is necessary to select from the base information about tags that contain value <10;
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();
}
To study work with database in detail, download the sample project Database.sirpz
DataBaseExample.sirpz
Read here how to work with a system database.







This article was helpful for 2 people. Is this article helpful for you?