Execute SQL query

Executes an arbitrary SQL query

Function ExecuteSQLQuery(Val QueryText, Val Parameters = "", Val ForceResult = False, Val Connection = "") 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

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://api.athenaeum.digital/test_data/picture.jpg";
    OPI_TypeConversion.GetBinaryData(Image); // Image - Type: BinaryData

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

    ConnectionString = OPI_PostgreSQL.GenerateConnectionString(Address, Base, Login, Password);
    Connection       = OPI_PostgreSQL.CreateConnection(ConnectionString);

    // 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);
    | COMMIT;
    |END $$ LANGUAGE plpgsql;";

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

    // SQL query from file

    SQLFile = "https://api.athenaeum.digital/test_data/TEST_DATA.sql"; // Binary Data, URL or path to file

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

    Closing = OPI_PostgreSQL.CloseConnection(Connection);

Bash

    oint postgres ExecuteSQLQuery \
     --sql "https://github.com/Bayselonarrend/OpenIntegrations/raw/main/service/test_data/TEST_DATA.sql" \
     --dbc "postgresql://bayselonarrend:***@127.0.0.1:5432/"

CMD/Bat

    oint postgres ExecuteSQLQuery ^
     --sql "https://github.com/Bayselonarrend/OpenIntegrations/raw/main/service/test_data/TEST_DATA.sql" ^
     --dbc "postgresql://bayselonarrend:***@127.0.0.1:5432/"

Result

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