Local Queries

Local Queries are used in scripts to interact with ODBC data sources via GS Local.

Settings

Field	Description
Name	Unique name of the Local Query.
Query	SQL statement to execute.

Executing Local Queries

Local Queries are used with LocalDatabaseConnection to interact with ODBC data sources. The Internal ID of the Query is used as the first parameter of the execute() method.

Each computer that executes Local Queries must have GS Local installed and the appropriate ODBC data sources configured.

Danger

Any type of SQL statement can be executed against the ODBC connection; the only limitations are those enforced by the connection itself. Be cautious when exposing destructive operations like DROP or DELETE.

Parameters

Local Queries are parameterized to prevent SQL injection. Parameters should be included in the statement with the ? placeholder. During script execution, parameters are provided in an array as the second argument to the LocalDatabaseConnection.execute() method.

Only values can be provided via parameters. Other components of the query, like names of tables or columns, cannot be provided.

Example

The following Query has been created in GS with an Internal ID of 2.

SELECT lower_spec, upper_spec FROM specifications WHERE part IN (?) AND rev = ?;

The Query could be used with the following script.

const connection = await gsApi.local.database.connect('my_odbc_connection');
const specResults = await connection.execute(2, [['PN-4821-A', 'PN-1297-A'], 1]);
if (specResults && typeof specResults === 'object') {
    specResults.rows.forEach(row => {
        // Process each row...
        // row[0] contains the lower_spec, row[1] contains the upper_spec
    });
}

Import/Export

Local Queries can be imported and exported using the following CSV structure.

Column Header	Create	Update	Description
ID	Not Allowed	Required	Local Query's Internal ID
Name	Required	Optional	String
Query	Required	Optional	String