MariaDB SPIDER_DIRECT_SQL(): Syntax, Prerequisites, and Examples

SPIDER_DIRECT_SQL() is a user-defined function provided by MariaDB’s Spider Storage Engine. It sends a SQL string to a remote server described by its parameters argument. The function returns 1 when the statement succeeds and 0 when it fails. If the remote statement returns a result set, Spider stores the rows in the table or tables named by tmp_table_list. See the MariaDB reference for the function’s supported syntax.

Syntax

SPIDER_DIRECT_SQL('sql', 'tmp_table_list', 'parameters')

The function takes three arguments:

• sql: the statement to execute on the remote server.
• tmp_table_list: the local table name or names for any result sets returned by the remote statement. Use an empty string when the statement does not return rows.
• parameters: Spider connection parameters that identify the remote server.

The Spider Storage Engine and the SPIDER_DIRECT_SQL() function must be available on the MariaDB instance that runs the call. The remote server and credentials must also be configured for Spider access.

Execute a statement on a remote server

This example follows MariaDB’s documented srv and port parameter form. Replace node1 and the port with a server known to your Spider configuration:

SELECT SPIDER_DIRECT_SQL(
  'SELECT * FROM s',
  '',
  'srv "node1", port "8607"'
);

For a statement that returns rows, specify a local table in tmp_table_list. Create the receiving table with columns compatible with the remote result first:

CREATE TEMPORARY TABLE remote_rows (
  id INT,
  name VARCHAR(100)
);

SELECT SPIDER_DIRECT_SQL(
  'SELECT id, name FROM employees WHERE department_id = 10',
  'remote_rows',
  'srv "node1", port "8607"'
);

SELECT id, name FROM remote_rows;

The function’s return value reports whether the remote statement executed successfully. The selected rows are read from remote_rows; they are not returned as the function’s own row set. MariaDB’s Spider Cluster Management guide includes additional examples that store remote results locally.

Direct and background execution

SPIDER_DIRECT_SQL() performs direct SQL execution. If the operation should run concurrently across Spider backends, MariaDB provides SPIDER_BG_DIRECT_SQL(). Choose the function based on whether the remote work should finish as part of the current call or run in the background.

Common mistakes

• Leaving out an argument: the call requires sql, tmp_table_list, and parameters, in that order.
• Expecting SELECT rows as the function result: the function returns a success flag; put returned rows in a compatible local table through tmp_table_list.
• Using an unconfigured server name: the server and connection parameters must match the Spider configuration for the MariaDB installation.
• Embedding reusable passwords in SQL text: protect remote credentials and limit which users can run statements on backend servers.