16.4.2 machsql

machsql is an interactive terminal client for SQL queries. It also supports SQL script execution, saving results to files, and public key authentication.

Options

machsql -h

Connection Examples

Basic connection:

machsql -s 127.0.0.1 -u SYS -p MANAGER
machsql --server=localhost --user=SYS --password=MANAGER

Specify a port:

machsql -s 192.168.1.10 -P 5656 -u SYS -p MANAGER

Run a SQL script:

machsql -s 127.0.0.1 -u SYS -p MANAGER -f create_tables.sql

Specify a timezone:

machsql -s 127.0.0.1 -u SYS -p MANAGER -z +0900
machsql -s 127.0.0.1 -u SYS -p MANAGER -z -1230

Save results to a file:

machsql -s 127.0.0.1 -u SYS -p MANAGER -o result.csv -f query.sql

Public Key Authentication (Machbase 8.5+)

You can use public key challenge authentication instead of a password.

Connect with an ECDSA key:

machsql -s 127.0.0.1 -u app_user \
    -K /opt/machbase/keys/app_user_ecdsa.pem \
    --auth-sig-scheme=ECDSA

Connect with an RSA-PSS key:

machsql -s 127.0.0.1 -u app_user \
    -K /opt/machbase/keys/app_user_rsa.pem \
    --auth-sig-scheme=RSA_PSS

Supported key algorithms:

Additional Connection Parameters (6.1+)

Use -c to supply additional connection parameters.

machsql -s 127.0.0.1 -u SYS -p MANAGER -P 5656 \
    -c 'ALTERNATIVE_SERVERS=192.168.0.147:9209;CONNECTION_TIMEOUT=10'

You can also use an environment variable.

export MACHBASE_CONNECTION_STRING="ALTERNATIVE_SERVERS=192.168.0.148:8888;CONNECTION_TIMEOUT=3"
machsql -s 127.0.0.1 -u SYS -p MANAGER

The -c option takes precedence over the environment variable.

Selecting a Logical Database

In Machbase 8.7.0 Standard Edition, use -D or --database to select the logical database immediately after connection.

machsql -s 127.0.0.1 -u app_a -p 'AppA#1234' -D factory_a
machsql -s 127.0.0.1 -u app_a -p 'AppA#1234' --database=factory_a

In a -c connection string, specify DATABASE=factory_a or its compatibility alias, DBNAME=factory_a. If -D and the connection string specify different databases, the connection is rejected. Specify the database once or use the same value. After connecting, verify the actual server catalog with the following SQL.

SELECT CURRENT_DATABASE();
SHOW CURRENT DATABASE;

machsql Built-in Commands

These commands are available at the machsql prompt (Mach>).

Checking the Last INSERT’s ROWID

In Machbase 8.7.0 Standard Edition, you can check the inserted row’s ROWID immediately after a single-row INSERT ... VALUES.

INSERT INTO orders(item) VALUES('pump');
SHOW LAST ROWID;

Last ROWID : 2048

SHOW LASTID returns the same value. When no ROWID is available, the output is NULL, not 0. Do not reuse the previous value after a failed INSERT, batch/Append/loader operation, INSERT ... SELECT, UPSERT, or reconnection. Non-INSERT statements such as SELECT and COMMIT preserve the last value.

For per-table ROWID rules and SDK access, see ROWID and INSERT Result IDs.

DESC and PRIMARY KEY Metadata

DESC table_name displays a [ PRIMARY KEY ] section after column and index information. It shows the PRIMARY KEY name, column names, and key sequence. It covers declared primary keys in TRANSACTION, LOOKUP, and VOLATILE tables, and NAME in TAG tables. Ordinary LOG tables display no primary key rows.

DESC ACCOUNT;

This output is separate from SELECT result column metadata. To check whether a SELECT result column is part of a primary key through an SDK, see PRIMARY KEY Metadata Support.

ARRAY Display and DESC

In Machbase DBMS 8.7.0, DESC displays ARRAY columns using canonical declarations such as INT32[3] and DECIMAL(12,4)[2]. Query results use [value,null,value], where lowercase null represents an element NULL. If the entire column value is NULL, it appears as ordinary SQL NULL.

SELECT ID, CHANNELS, ARRAY_LENGTH(CHANNELS), CHANNELS[1]
  FROM SENSOR_ARRAY
 ORDER BY ID;

For ARRAY declarations, NULL distinctions, and expressions, see Numeric ARRAY Types.

Named Bind Parameter

You can use :name markers in machsql PREPARE SQL. Assign values to $1, $2, … variables in SQL occurrence order, rather than by name.

PREPARE INSERT INTO SENSOR_DATA (ID, NAME, VALUE)
        VALUES (:id, :name, :value);
$1 := 900;
$2 := 'machsql-client';
$3 := 72.125000;
EXECUTE;
PREPARE CLEAN;

Assign a value to each occurrence even when a name is repeated.

PREPARE SELECT ID, NAME
        FROM SENSOR_DATA
        WHERE ID = :id OR PARENT_ID = :id;
$1 := 900;
$2 := 900;
EXECUTE;
PREPARE CLEAN;

For marker naming syntax and occurrence ordering, see Named Bind Parameter Syntax.

Examples

# Connect interactively
machsql -s 127.0.0.1 -u SYS -p MANAGER

# Check tables after connecting
Mach> SHOW TABLES;

# Check table structure
Mach> SHOW TABLE sensor_data;

# Execute a SQL script and save results as CSV
machsql -s 127.0.0.1 -u SYS -p MANAGER \
    -f report.sql -o report_output.csv -i