Skip to main content Link Menu Expand (external link) Document Search Copy Copied

How do I run SQL queries with fbsql?

Valid SQL queries can be run directly in the fbsql interface and via files in accessible directories.

This reference explains fbsql flags relating to database connections and schema

Before you begin

Enter fbsql --help at the CLI to list available commands. \q quits the fbsql interface.


    [ c|connect [ <cloud-database-name> | - ] ] |
    [ d[< tablename>|t|v ] ] |
    [ l|list ] |
    [ set <variable-name> [variable-value,...] ] |
    [ unset <variable-name> ] |
    [ i|include <filename> ] |
    [ watch <seconds> ] |
    [ t|timing [on|off] ] |
    [ p|print ] |
    [ r|reset ] |

Meta-command flag prefix

Meta-command flags can be run from the fbsql interface or CLI using an appropriate prefix:

Interface Prefix Description Example
fbsql \ Leading backslash followed by one or more meta-command flags and arguments separated by \ backslash characters. \print \echo "this is a test"
CLI fbsql [-c|--command] Meta-command flags enclosed in single or double quotes are executed without opening the fbsql interface fbsql --command '\print \echo this is a test'

Database flags

Argument Description Additional information
[c|connect] <cloud-database-name> Connect to an available database in the currently connected cloud account. Connect cloud database additional
[c|connect] - Disconnect from the current cloud database  
d List all tables in the connected database. Equivalent to Cloud SHOW DATABASE statement SHOW DATABASE
d <tablename> Equivalent to SHOW COLUMNS statement SHOW COLUMNS
dt Equivalent to SHOW TABLES statement SHOW TABLES
dv Show definition of all views CREATE VIEWS
[l|list] List databases in connected Cloud database  

File flags

Flag Description Additional information  
file <filename> [<alias>] Create alias for filename to use in SQL queries Filename alias  
[w|write] <filename> Write most recent query or query buffer to file   File write
[i|include] <filename> Run content from file which can include valid meta-commands and SQL queries Equivalent to fbsql --file command  

Query flags

Flag Description Default
timing Toggle time SQL statement takes to execute off
watch <seconds> Repeat query from buffer or last in history at interval of until failure or manual break  

Query buffer flags

Flag Description
[p | print] Display most recent query or query buffer to fbsql interface followed by a newline
[r | reset] Reset query buffer

Set variable flags

Flag Description Additional information
set List all variable names  
[set|unset] <variable-name> Set or unset named variable Named variables in single quotation marks are treated as string literals in queries
set <variable-name> <value>... Set a variable name and value. Multiple values are concatenated. SET variable names

SQL query syntax

Additional information

Flag execution

  • Flags can be executed individually or as a sequence delimited by \
  • Use single quotation marks ' ' to insert whitespace

fbsql query buffer

The query buffer holds partial or not executed:

  • meta-commands
  • SQL queries

Cloud database connect additional

The following commands are equivalent:
fbsql -[d|-dbname] <cloud-database-name>

Filename alias

Aliases are case sensitive and can be inserted into statements in two ways:

Quotation marks Example
Single :'<variable-name>'
Double :"<variable-name>"

Query output formatting

Query output can be formatted using PSET (Print Set) meta flags.


Filename alias examples

Alias for CSV file

Create a filename alias for file containing CSV data:

\file insert_test.csv icsv

Use the alias in SQL statement:

fbsql-# bulk replace
     -#     into insert_test (_id, int1, string1, timestamp1)
     -#     map (0 id, 1 int, 2 string)
     -#     transform (@0, @1, @2, current_timestamp)
     -# from
     -#     'icsv'
     -# with
     -#     format 'CSV'
     -#     input 'STREAM';

Alias for SQL file

  • Create docviewtest.sql and add the following SQL statement:
SELECT * from doctest where _id = 0;

Add filename alias:

fbsql -c '\file docviewtest.sql mynewview'

Use the alias in a SQL statement:

CREATE VIEW docview AS :mynewview;

SET variable

SET variable Result
set myvarname 1,2,3,4 myvarname = '1,2,3,4'
set myvarname 1 2 3 4 myvarname = '1234'

SET variable in a query

\SET prod products

Query with variable

select * from :prod;

Further information