Ensure table

Creates a new table if it does not exist or updates the composition of columns in an existing table

Function EnsureTable(Val Table, Val ColoumnsStruct, Val Connection = "", Val Tls = "") Export

Parameter	CLI option	Type	Required	Description
Table	--table	String	✔	Table name
ColoumnsStruct	--cols	Structure Of KeyAndValue	✔	Column structure: Key > Name, Value > Data type
Connection	--dbc	String, Arbitrary	✖	Existing connection or database path
Tls	--tls	Structure Of KeyAndValue	✖	TLS settings, if necessary. See GetTlsSettings

Returns: Map Of KeyAndValue - Result of query execution

tip

As a result of changing the table structure, data may be lost! It is recommended to test this method on test data beforehand

This function does not update the data type of existing columns

1C:Enterprise/OneScript code example

    Address  = "127.0.0.1";
    Login    = "SA";
    Password = "12we...";

    Base  = "testbase1";
    Table = "testtable";

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

    ColoumnsStruct = New Structure;
    ColoumnsStruct.Insert("smallint_field" , "smallint");
    ColoumnsStruct.Insert("double_field"   , "real");
    ColoumnsStruct.Insert("bigint_field"   , "bigint");
    ColoumnsStruct.Insert("custom_field"   , "nvarchar");

    // When using the connection string, a new connection is initialised,
    // which will be closed after the function is executed.
    // If several operations are performed, it is desirable to use one connection,
    // previously created by the CreateConnection function()
    Result = OPI_MSSQL.EnsureTable(Table, ColoumnsStruct, ConnectionString, TLSSettings);

Bash

    # JSON data can also be passed as a path to a .json file
    
    oint mssql EnsureTable \
     --table "testtable" \
     --cols "{'smallint_field':'smallint','double_field':'real','bigint_field':'bigint','custom_field':'nvarchar'}" \
     --dbc "Server=127.0.0.1;Database=***;User Id=SA;Password=***;" \
     --tls "{'use_tls':true,'accept_invalid_certs':true}"

CMD/Bat

    :: JSON data can also be passed as a path to a .json file
    
    oint mssql EnsureTable ^
     --table "testtable" ^
     --cols "{'smallint_field':'smallint','double_field':'real','bigint_field':'bigint','custom_field':'nvarchar'}" ^
     --dbc "Server=127.0.0.1;Database=***;User Id=SA;Password=***;" ^
     --tls "{'use_tls':true,'accept_invalid_certs':true}"

Result

{
 "result": true,
 "commit": {
  "result": true
 }
}