3. Custom SQL

fmCustomSQLfmMySQLHelpfmCustomSqlInsertParamDialog

This section can be used to work with your store DB, fetching data easier and faster.

Go to Tools / Custom SQL. Here you can create a query, using SQL language.

Custom SQL

When you open “Custom SQL” page, you'll see it looks a lot like the Create New Report one. This page has 2 tabs: ‘Edit’ and ‘Logs’ and is divided into two sections: top one is showing the query content (SQL text) and the bottom one lists all available queries.

Custom SQL window

You can also open an SQL Query file, that was previously saved by clicking on the icon:

Load query from file

When creating a new query (New SQL icon), you need to specify its name and click OK. At the bottom part of the page, you'll see the query with this name created. Now you can either run a Query builder or write query text in the top part of ‘Edit’ tab directly. When you're finished with this query hit the save button.

Save query to file

Then you can execute it by pressing the ‘Execute’ SQL button. The ‘Results’ tab will appear, showing the returned data, arranged according to a certain condition.

Execute SQL button

If the query wasn't executed, you can check the ‘Logs’ tab for errors. ‘Logs’ tab will be cleared after you close Store Manager.

Logs tab

'Help SQL' button allows to facilitate the SQL queries formation using built-in templates.

For example, you specify a command with a request to Union (CONCAT) strings. Click on 'Help SQL' button and choose from the list of folders with queries examples the necessary one - in our case, this is a 'Functions'->'String Functions' folder - > CONCAT file. In the right pane of the 'SQL Help' window in the description field contains the information about this type of SQL query and is served link at which you can find more detailed information about CONCAT query. In the lower right pane are submitted examples of CONCAT function application.

This will help you to compose a correct SQL request. To verify the query press the corresponding button in the "Custom SQL" top toolbar that will launch the check editor.

You can link the created SQL query to the products of the store database or select the simple type from the drop-down.

Linked to products