dbquery

Executes SQL queries against an external database server via JDBC. The JDBC profile must be configured in advance.

Command properties

Syntax

Target

PROFILE

JDBC connection profile name. Connection profiles can be configured in the web console.

SQL

SQL statement to execute. SELECT, SHOW, EXECUTE, and EXEC statements convert the result set to records and output them. Other SQL statements (INSERT, UPDATE, DELETE, etc.) are executed without producing output records.

You can use placeholders starting with a colon (:) in the SQL statement. Placeholders are replaced with query parameter values set by the set command:

• Numeric types: inserted as-is
• Date types: converted to strings in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.SSS'
• null: inserted as the string null
• Other types: converted to single-quoted strings

Output fields

Error codes

Parse errors

Runtime errors

N/A

Description

The dbquery command executes SQL queries against an external database via JDBC and converts each row of the result set to a record for output.

SQL statements starting with SELECT, SHOW, EXECUTE, or EXEC retrieve a result set. All other SQL statements (INSERT, UPDATE, DELETE, DDL, etc.) are executed without producing output records.

When you use placeholders starting with a colon (for example :name) in a SQL statement, they are replaced with query parameter values set by the set command. This allows you to construct dynamic SQL queries.

When connecting to MySQL or MariaDB, forward-only cursors and streaming fetch are used to handle large result sets. For all other databases, result sets are fetched 2,000 rows at a time. You can override this value by setting the JVM system property logpresso.jdbc.fetch_size.

Examples

1. Querying data from an external database

   dbquery mydb SELECT * FROM employees WHERE department = 'sales'
   

   Retrieves records from the sales department in the employees table in the database connected via the mydb profile.

2. Dynamic query using placeholders

   set start_date = string(dateadd(now(), "day", -7), "yyyy-MM-dd")
   | dbquery mydb SELECT * FROM access_logs WHERE log_date >= :start_date
   

   Inserts the start_date parameter set by the set command into the SQL query to retrieve logs from the past 7 days.

3. Sorting processed query results

   dbquery mydb SELECT src_ip, dst_ip, bytes FROM traffic_logs
   | stats sum(bytes) as total_bytes by src_ip
   | sort -total_bytes
   

   Retrieves traffic logs from the external database, calculates total bytes by source IP, and sorts in descending order.