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 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
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

tip

All UNIQUE and PRIMARY KEY fields of the table serve as key fields


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

    TLS = True;
    Port = 3306;
    ConnectionString = OPI_MySQL.GenerateConnectionString(Address, Base, Login, Password, Port);

    If TLS Then
        TLSSettings = OPI_MySQL.GetTLSSettings(True);
    Else
        TLSSettings = Undefined;
    EndIf;

    Table = "test_merge";

    DataArray = New Array;

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

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

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

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

    Result = OPI_MySQL.EnsureRecords(Table, DataArray, , ConnectionString, TLSSettings);
    # JSON data can also be passed as a path to a .json file
    
    oint mysql EnsureRecords \
      --table "test_merge" \
      --rows "[{'id':{'INT':'1'},'name':{'TEXT':'Vitaly Updated'},'age':{'INT':'25'},'salary':{'DOUBLE':'1500.5'}},{'id':{'INT':'3'},'name':{'TEXT':'Anton'},'age':{'INT':'30'},'salary':{'DOUBLE':'3000'}}]" \
      --db "mysql://bayselonarrend:12we3456!2154@127.0.0.1:3307/testbase1" \
      --tls "{'use_tls':true,'accept_invalid_certs':true}"