Skip to main content

Ensure records

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

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

ParameterCLI optionTypeRequiredDescription
Table--tableString✔Table name
DataArray--rowsArray of Structure✔An array of string data structures: Key > field, Value > field value
KeyFields--uniqueArray Of String✖Name or names of key table fields for uniqueness validation
Transaction--trnBoolean✖True > adding records to transactions with rollback on error
Connection--dbString, Arbitrary✖Existing connection or database path
Tls--tlsStructure Of KeyAndValue✖TLS settings, if necessary. See GetTlsSettings
Returns

Map Of KeyAndValue - Result of query execution

1C:Enterprise/OneScript code example
    Address  = "127.0.0.1";
    Login    = "SA";
    Password = "12we...";
    Base     = "testbase1";

    TLSSettings      = OPI_MSSQL.GetTLSSettings(True);
    ConnectionString = OPI_MSSQL.GenerateConnectionString(Address, Base, Login, Password);

    Table = "test_guarantee";

    DataArray = New Array;

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

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

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

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

    Result = OPI_MSSQL.EnsureRecords(Table, DataArray, KeyFields, , ConnectionString, TLSSettings);
    # JSON data can also be passed as a path to a .json file
    
    oint mssql EnsureRecords \
      --table "test_guarantee" \
      --rows "[{'id':{'INT':'1'},'name':{'NVARCHAR':'Vitaly Updated'},'age':{'INT':'25'},'salary':{'DECIMAL':'1500.5'}},{'id':{'INT':'3'},'name':{'NVARCHAR':'Anton'},'age':{'INT':'30'},'salary':{'DECIMAL':'3000'}}]" \
      --unique "['id']" \
      --db "Server=127.0.0.1;Database=testbase1;User Id=SA;Password="'"12we3456!2154"'";" \
      --tls "{'use_tls':true,'accept_invalid_certs':true}"