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.
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. }