Skip to main content

Execute SQL query

Executes an arbitrary SQL query

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

ParameterCLI optionTypeRequiredDescription
QueryText--sqlString✔Database query text
Parameters--paramsArray Of Arbitrary✖Array of positional parameters of the request
ForceResult--forceBoolean✖Includes an attempt to retrieve the result, even for nonSELECT queries
Connection--dbcString, Arbitrary✖Connection or connection string
Tls--tlsStructure 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'}. The list of available types is described on the initial page of the MySQL 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
    Image = "https://hut.openintegrations.dev/test_data/picture.jpg";
    OPI_TypeConversion.GetBinaryData(Image); // Image - Type: BinaryData

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

    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;

    Connection = OPI_MySQL.CreateConnection(ConnectionString, TLSSettings);

    // CREATE

    QueryText = "
                   |CREATE TABLE test_table (
                   |id INT AUTO_INCREMENT PRIMARY KEY,
                   |name VARCHAR(255),
                   |age INT,
                   |salary DOUBLE,
                   |amount FLOAT,
                   |type TINYINT UNSIGNED,
                   |date DATE,
                   |time TIME,
                   |data MEDIUMBLOB
                   |);";

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

    // INSERT with parameters

    QueryText = "
                   |INSERT INTO test_table (name, age, salary, amount, type, date, time, data)
                   |VALUES (?, ?, ?, ?, ?, ?, ?, ?);";

    ParameterArray = New Array;
    ParameterArray.Add(New Structure("TEXT"  , "Vitaly"));
    ParameterArray.Add(New Structure("INT"   , 25));
    ParameterArray.Add(New Structure("DOUBLE", 1000.12));
    ParameterArray.Add(New Structure("FLOAT" , 1000.12));
    ParameterArray.Add(New Structure("UINT"  , 1));
    ParameterArray.Add(New Structure("DATE"  , OPI_Tools.GetCurrentDate()));
    ParameterArray.Add(New Structure("TIME"  , OPI_Tools.GetCurrentDate()));
    ParameterArray.Add(New Structure("BYTES" , Image));

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

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

    QueryText = "SELECT name, age, salary, amount, type, date, time, data FROM test_table;";

    Result = OPI_MySQL.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_MySQL.ExecuteSQLQuery(SQLFile, , , Connection);

    Closing = OPI_MySQL.CloseConnection(Connection);
    # JSON data can also be passed as a path to a .json file
    
    oint mysql ExecuteSQLQuery \
     --sql "https://hut.openintegrations.dev/test_data/TEST_DATA2.sql" \
     --dbc "mysql://bayselonarrend:***@127.0.0.1:3306/" \
     --tls "{'accept_invalid_certs':true,'ca_cert_path':'','use_tls':true}"
Result
{
 "data": [
  {
   "age": 25,
   "amount": 1000.11999511719,
   "data": {
    "BYTES": "Base64"
   },
   "date": "2025-10-15T00:00:00+00:00",
   "name": "Vitaly",
   "salary": 1000.12,
   "time": "1970-01-01T09:20:08+00:00",
   "type": 1
  }
 ],
 "result": true
}