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'}. The list of available types is described on the initial page of the PostgreSQL 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 = 5432;
    ConnectionString = OPI_PostgreSQL.GenerateConnectionString(Address, Base, Login, Password, Port);

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

    Connection = OPI_PostgreSQL.CreateConnection(ConnectionString, TLSSettings);

    // CREATE

    QueryText = "
                   |CREATE TABLE test_table (
                   |id SERIAL PRIMARY KEY,
                   |name NAME,
                   |age INT,
                   |salary REAL,
                   |is_active BOOL,
                   |created_at DATE,
                   |data BYTEA
                   |);";

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

    // INSERT with parameters

    QueryText = "
                   |INSERT INTO test_table (name, age, salary, is_active, created_at, data)
                   |VALUES ($1, $2, $3, $4, $5, $6);";

    ParameterArray = New Array;
    ParameterArray.Add(New Structure("NAME" , "Vitaly"));
    ParameterArray.Add(New Structure("INT"  , 25));
    ParameterArray.Add(New Structure("REAL" , 1000.12));
    ParameterArray.Add(New Structure("BOOL" , True));
    ParameterArray.Add(New Structure("DATE" , OPI_Tools.GetCurrentDate()));
    ParameterArray.Add(New Structure("BYTEA", Image));

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

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

    QueryText = "SELECT id, name, age, salary, is_active, created_at, data FROM test_table;";

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

    // DO + Transaction

    QueryText = "DO $$
                   |BEGIN
                   | CREATE TABLE users (
                   | id SMALLSERIAL,
                   | name TEXT NOT NULL,
                   | age INT NOT NULL
                   | );
                   | INSERT INTO users (name, age) VALUES ('Alice', 30);
                   | INSERT INTO users (name, age) VALUES ('Bob', 25);
                   | INSERT INTO users (name, age) VALUES ('Charlie', 35);
                   |END $$ LANGUAGE plpgsql;";

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

    // SQL query from file

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

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

    Closing = OPI_PostgreSQL.CloseConnection(Connection);

Bash

    # JSON data can also be passed as a path to a .json file
    
    oint postgres ExecuteSQLQuery \
     --sql "https://hut.openintegrations.dev/test_data/TEST_DATA.sql" \
     --dbc "postgresql://bayselonarrend:***@127.0.0.1:5432/" \
     --tls "{'accept_invalid_certs':true,'ca_cert_path':'','use_tls':true}"

CMD/Bat

    :: JSON data can also be passed as a path to a .json file
    
    oint postgres ExecuteSQLQuery ^
     --sql "https://hut.openintegrations.dev/test_data/TEST_DATA.sql" ^
     --dbc "postgresql://bayselonarrend:***@127.0.0.1:5432/" ^
     --tls "{'accept_invalid_certs':true,'ca_cert_path':'','use_tls':true}"

Result

{
 "data": [
  {
   "age": 25,
   "created_at": "2025-10-15",
   "data": {
    "BYTEA": "Base64"
   },
   "id": 1,
   "is_active": true,
   "name": "Vitaly",
   "salary": 1000.11999511719
  }
 ],
 "result": true
}