Ensure records

Adds records or updates data of existing ones when key fields match

Parameters

Function EnsureRecords(Val Table, Val DataArray, Val KeyFields = "", Val Transaction = True, Val Connection = "") Export

Parameter	CLI option	Type	Required	Description
Table	--table	String	✔	Table name
DataArray	--rows	Array of Structure	✔	An array of string data structures: Key > field, Value > field value
KeyFields	--unique	Array Of String	✖	Name or names of key table fields for uniqueness validation
Transaction	--trn	Boolean	✖	True > adding records to transactions with rollback on error
Connection	--db	String, Arbitrary	✖	Existing connection or database path

Returns

Map Of KeyAndValue - Result of query execution

Advanced call ?

Parameter	Description
addin_mode	Manual selection of external component connection mode (for 1C): Isolated, NotIsolated
dontwait	Creates a background job and returns its data (for 1C and OneScript only)

tip

UNIQUE and PRIMARY KEY fields can be specified as key fields. If fields are not specified, uniqueness is determined by all suitable fields

1C:Enterprise/OneScript code example

    Base  = "/tmp/vnnmoosn.qqb.sqlite";
    Table = "test_merge";

    DataArray = New Array;

    RowStructure2 = New Structure;
    RowStructure2.Insert("id"    , 1);
    RowStructure2.Insert("name"  , "Vitaly");
    RowStructure2.Insert("age"   , 25);
    RowStructure2.Insert("salary", 1000.12);

    RowStructure1 = New Structure;
    RowStructure1.Insert("id"    , 2);
    RowStructure1.Insert("name"  , "Lesha");
    RowStructure1.Insert("age"   , 20);
    RowStructure1.Insert("salary", 200.20);

    DataArray.Add(RowStructure2);
    DataArray.Add(RowStructure1);

    KeyFields = New Array;
    KeyFields.Add("id");

    Result = OPI_SQLite.EnsureRecords(Table, DataArray, KeyFields, , Base);

Bash

    # JSON data can also be passed as a path to a .json file
    
    oint sqlite EnsureRecords \
      --table "test_merge" \
      --rows "[{'id':'1','name':'Vitaly Updated','age':'25','salary':'1500.5'},{'id':'3','name':'Anton','age':'30','salary':'3000'}]" \
      --unique "['id']" \
      --db "/tmp/v3fibjxn.cs0.sqlite"

CMD/Bat

    :: JSON data can also be passed as a path to a .json file
    
    oint sqlite EnsureRecords ^
      --table "test_merge" ^
      --rows "[{'id':'1','name':'Vitaly Updated','age':'25','salary':'1500.5'},{'id':'3','name':'Anton','age':'30','salary':'3000'}]" ^
      --unique "['id']" ^
      --db "/tmp/v3fibjxn.cs0.sqlite"