Execute SQL query

Executes an arbitrary SQL query

Function ExecuteSQLQuery(Val QueryText, Val Parameters = "", Val ForceResult = False, Val Connection = "", Val Tls = "") Export

Parameter	CLI option	Type	Required	Description
QueryText	--sql	String	✔	Database query text
Parameters	--params	Array Of Arbitrary	✖	Array of positional parameters of the request
ForceResult	--force	Boolean	✖	Includes an attempt to retrieve the result, even for nonSELECT queries
Connection	--dbc	String, Arbitrary	✖	Connection or connection string
Tls	--tls	Structure Of KeyAndValue	✖	TLS settings, if necessary. See GetTlsSettings

Returns: Map Of KeyAndValue - Result of query execution

tip

Query parameters are specified as an array of structures of the following type: {'Type': 'Value'}. List of available types is described on the initial page of the MSSQL library documentation

Without specifying the ForcifyResult flag, result data is returned only for queries beginning with SELECT keyword For other queries, result:true or false with error text is returned

1C:Enterprise/OneScript code example

    CurrentDate = OPI_Tools.GetCurrentDate();
    Image       = "https://hut.openintegrations.dev/test_data/picture.jpg";
    OPI_TypeConversion.GetBinaryData(Image); // Image - Type: BinaryData

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

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

    // CREATE

    QueryText = "
        |CREATE TABLE test_table (
        | ID INT PRIMARY KEY,
        | FirstName NVARCHAR(50),
        | LastName NVARCHAR(50),
        | BirthDate DATE,
        | IsEmployed BIT,
        | Salary DECIMAL(10, 2),
        | CreatedAt DATETIME,
        | Age SMALLINT,
        | RowGuid UNIQUEIDENTIFIER,
        | Data VARBINARY(MAX)
        |);";

    Result = OPI_MSSQL.ExecuteSQLQuery(QueryText, , , Connection);

    // INSERT with parameters

    QueryText = "
        |INSERT INTO test_table (ID, FirstName, LastName, BirthDate, IsEmployed, Salary, CreatedAt, Age, RowGuid, Data)
        |VALUES (@P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10);";

    ParameterArray = New Array;
    ParameterArray.Add(New Structure("INT"     , 1));
    ParameterArray.Add(New Structure("NVARCHAR", "Vitaly"));
    ParameterArray.Add(New Structure("NVARCHAR", "Alpaca"));
    ParameterArray.Add(New Structure("DATE"    , CurrentDate));
    ParameterArray.Add(New Structure("BIT"     , True));
    ParameterArray.Add(New Structure("DECIMAL" , 10.30));
    ParameterArray.Add(New Structure("DATETIME", CurrentDate));
    ParameterArray.Add(New Structure("SMALLINT", 20));
    ParameterArray.Add(New Structure("UUID"    , New UUID));
    ParameterArray.Add(New Structure("BYTES"   , Image));

    Result = OPI_MSSQL.ExecuteSQLQuery(QueryText, ParameterArray, , Connection);

    // SELECT (The result of this query is shown in the Result block)

    QueryText = "SELECT FirstName, LastName, BirthDate, IsEmployed, Salary, CreatedAt, Age, RowGuid, Data FROM test_table;";

    Result = OPI_MSSQL.ExecuteSQLQuery(QueryText, , , Connection);

    // SQL query from file

    SQLFile = "https://hut.openintegrations.dev/test_data/TEST_DATA2.sql"; // Binary Data, URL or path to file

    Result = OPI_MSSQL.ExecuteSQLQuery(SQLFile, , , Connection);

    Closing = OPI_MSSQL.CloseConnection(Connection);

Bash

    # JSON data can also be passed as a path to a .json file
    
    oint mssql ExecuteSQLQuery \
     --sql "SELECT FirstName, LastName, BirthDate, IsEmployed, Salary, CreatedAt, Age, RowGuid, Data FROM test_table;" \
     --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 ExecuteSQLQuery ^
     --sql "SELECT FirstName, LastName, BirthDate, IsEmployed, Salary, CreatedAt, Age, RowGuid, Data FROM test_table;" ^
     --dbc "Server=127.0.0.1;Database=***;User Id=SA;Password=***;" ^
     --tls "{'use_tls':true,'accept_invalid_certs':true}"

Result

{
 "data": [
  {
   "Age": 20,
   "BirthDate": "2025-07-14",
   "CreatedAt": "2025-07-14T11:12:52",
   "Data": {
    "BYTES": "Base64"
   },
   "FirstName": "Vitaly",
   "IsEmployed": 1,
   "LastName": "Alpaca",
   "RowGuid": "35c4b71e-4095-4209-9594-0bcc03934e4f",
   "Salary": 10.3
  }
 ],
 "result": true
}