Execute SQL

api

The SqliteDatabaseAPI.Execute API enables clients to execute SQL against databases.

cli

Use the db command.

./volt db --help

To execute a statement against a database resource with alias db-demo:

./volt db @db-demo "select * from tcpdump limit 10"

You can also write to the database if you have the appropriate permissions:

./volt db @db-demo "create table foobar (id integer primary key, key text, value text)"

./volt db @db-demo "insert into foobar (key,value) values ('hello','world')"

./volt db @db-demo "select * from foobar"

fusebox

The fusebox has a built-in SQL terminal. Select the database resource and then click the view button on the toolbar.

fusebox-sql-execute

javascript

grpc

The following example runs an SQL SELECT statement against a database resource with alias db-demo. It assumes the client configuration is stored in the volt.config.json file in the current working directory.

import grpc from "@grpc/grpc-js";
import { VoltClient } from "@tdxvolt/volt-client-grpc";

const client = new VoltClient(grpc);

const configPath = "./volt.config.json";

client
  .initialise(configPath)
  .then(() => {
    return client
      .SqlExecuteJSON({
        database_id: "@db-demo",
        statement: "select * from tcpdump limit 10",
      })
      .then((response) => {
        console.log(JSON.stringify(response, null, 2));
      });
  })
  .catch((err) => {
    console.error("failure in database-execute [%s]", err.message);
  });

web

The following example runs a parameterised query against the resource with alias @query-example, substituting the value %cage% into the first parameter. It assumes the configuration is store in localStorage.

import { VoltClient } from "@tdxvolt/volt-client-web";

const configJSON = localStorage.getItem("config");
const config = JSON.parse(configJSON);

const client = new VoltClient(WebSocket, config);

client
  .initialise()
  .then(() => {
    return client.SqlExecuteJSON({
      database_id: "@query-example",
      parameter: [{ string: "%cage%" }],
    });
  })
  .then((rows) => {
    console.log(JSON.stringify(rows, null, 2));
  })
  .catch((err) => {
    console.log("failure [%s]", err.message);
  })
  .finally(() => {
    console.log("finished");
    client.close();
  });

C++

  volt_client::SqliteDatabaseExecuteCallbacks executeCallbacks_;
  volt_client::SqliteDatabaseExecuteClient* executeRpc_ = nullptr;

  tdx::volt_api::data::v1::SqlExecuteRequest req;
  req.set_database_id(resource_.id());
  req.set_statement("select * from fs20Actuator");
  req.set_page_size(100);

  executeCallbacks_.onResponse =
      [this](tdx::volt_api::data::v1::SqlExecuteResponse const& response) {
        // Handle response here.
      };

  executeCallbacks_.onEnd = [this](bool serverSide) {
    if (serverSide) {
      // The server ended the execution rpc - this means it has no more data to
      // send (i.e. the cursor is at then end).
    }
  };

  executeCallbacks_.onError = [this](std::string err) {
    // Handle errors here.
  };

  if ((result = api_->executeDatabase(req, executeCallbacks_, &executeRpc_)) != error_code::ok) {
    // Handle failure starting call.
  }