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	--db	String, Arbitrary	✖	Existing connection or path to the base. In memory, if not filled

Returns: Structure Of KeyAndValue - Result of query execution

tip

Available parameter types: String, Number, Date, Boolean, BinaryData. Binary data can also be passed as a {'blob':File path} structure. Binary data (BLOB) values are returned as {'blob':Base64 string}

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

    TFN = GetTempFileName("sqlite");

    Image = "https://api.athenaeum.digital/test_data/picture.jpg";
    OPI_TypeConversion.GetBinaryData(Image); // Image - Type: BinaryData

    Connection = OPI_SQLite.CreateConnection(TFN);

    // CREATE

    QueryText = "
    |CREATE TABLE test_table (
    |id INTEGER PRIMARY KEY,
    |name TEXT,
    |age INTEGER,
    |salary REAL,
    |is_active BOOLEAN,
    |created_at DATETIME,
    |data BLOB
    |);";

    Result = OPI_SQLite.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("Vitaly"); // TEXT
    ParameterArray.Add(25); // INTEGER
    ParameterArray.Add(1000.12); // REAL
    ParameterArray.Add(True); // BOOL
    ParameterArray.Add(OPI_Tools.GetCurrentDate()); // DATETIME
    ParameterArray.Add(Image); // BLOB

    Result = OPI_SQLite.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_SQLite.ExecuteSQLQuery(QueryText, , , Connection);

    // Transaction

    QueryText = "BEGIN TRANSACTION;
    | CREATE TABLE IF NOT EXISTS users (
    | id INTEGER PRIMARY KEY AUTOINCREMENT,
    | name TEXT NOT NULL,
    | age INTEGER 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;";

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

    Closing = OPI_SQLite.CloseConnection(Connection);

Bash

    oint sqlite ExecuteSQLQuery \
     --sql "INSERT INTO test_table (name, age, salary, is_active, created_at, data)VALUES (?1, ?2, ?3, ?4, ?5, ?6);" \
     --params "['Vitaly',25,1000.12,true,'2025-01-10T11:19:07.6029006Z',{'blob':'C:\\Users\\Administrator\\AppData\\Local\\Temp\\5nzyfbwnqqx.png'}]" \
     --db "C:\Users\Administrator\AppData\Local\Temp\k554u1jcflp.sqlite"

CMD/Bat

    oint sqlite ExecuteSQLQuery ^
     --sql "INSERT INTO test_table (name, age, salary, is_active, created_at, data)VALUES (?1, ?2, ?3, ?4, ?5, ?6);" ^
     --params "['Vitaly',25,1000.12,true,'2025-01-10T11:19:07.6029006Z',{'blob':'C:\\Users\\Administrator\\AppData\\Local\\Temp\\5nzyfbwnqqx.png'}]" ^
     --db "C:\Users\Administrator\AppData\Local\Temp\k554u1jcflp.sqlite"

Result

{
 "data": [
  {
   "age": 25,
   "created_at": "2025-01-09 23:01:34",
   "data": {
    "blob": "Base64"
   },
   "id": 1,
   "is_active": 1,
   "name": "Vitaly",
   "salary": 1000.12
  }
 ],
 "result": true
}