Linx Main Linx Help

SQL Query Builder

#1

Linx has a built-in SQL Query builder that allows users to quickly generate SELECT, INSERT and UPDATE statements based on the schema of the table objects within a database. To support a broad range of SQL platforms and syntax variants, the generated statements are relatively basic. However, SQL Editor enables you to edit the statements to add any further complexity or specificity that may be required.

To generate these statements, first configure an ExecuteSQL function with the correct connection string. Once a connection has been established, open the SQL Editor.

On the right of the SQL Editor there are two tabs: one with Variables that contain all accessible variables/function outputs, and next to it the Objects tab. Click on the Objects tab to see a list of your database Tables, Views and Stored Procedures, which you can expand by double-clicking on them.

image

Tables

The table objects within your database are visible when you expand the Tables section. Within each table you can further expand it to view the schema of each table:

image

Generating a SELECT statement

To generate a SELECT statement, either drag and drop the table onto the SQL Editor pane, or alternatively you can right click on the table and select Generate Select .

image

This will generate a SELECT statement of all the fields from the selected table:

image

You can then alter this SELECT statement in the SQL Editor to retrieve the specific data that you require.

Generating an INSERT statement

To generate an INSERT statement, similar to the SELECT statement, right click the table and select Generate Insert.

image

This will create an INSERT statement for all the fields in your selected table, with blank spaces for your VALUES clause values, which you can then drag and drop other available variables onto the SQL Editor pane or edit them manually.

image

Generating an UPDATE statement

To generate an UPDATE statement, right click the table and select Generate Update.

image

This will generate an UPDATE statement for the selected table for ALL the fields as well as a condition clause which the user will have to either remove or alter for the query to execute successfully.

Views

The Views objects within your database are visible when you expand the Views section. Within each View you can further expand it to see the schema of the view output.

With Views, only SELECT statements can be generated. To generate a SELECT * FROM View statement, simply drag and drop the view onto the SQL Editor pane.

Stored Procedures

The Stored Procedures objects within your database are visible when you expand the Stored Procedures section. Within each Stored Procedure you can further expand it to see the input parameters per procedure.

To generate an EXECUTE Stored Procedure statement, either drag and drop the selected stored procedure onto the SQL Editor pane or double-click the stored procedure. This will create a command like the one below, which you can then modify appropriately.
image

0 Likes

#2

Excellent, thank you!
Didn’t know that this was a feature.

1 Like