SQL Builder is a Designer Tool provided by ONEWEB to create SQL queries without knowledge of any SQL syntax, by simply choosing the columns from tables in your ER Diagram. Since the SQL Builder is based on the ER Diagram, the user has to first generate the database structure using ER Diagram. Users can either create it from scratch or import an existing schema from the database onto the ER Diagram Designer.
Once the user has a valid Diagram, he/she can start building SQL queries on that.
Open application and choose the ‘Data Designer’ menu
Click the icon ‘Create SQL-Builder’ to open the modal window. To create, Enter the name and click on ‘Create SQL-Builder’
Create SQL-Builder success. Click on the icon ‘Start design SQL-Builder’ to open the SQL builder project
Once the user opens the project, they can see the design space.
Inside the SQL Builder project, you can find a Side Menu
In the Side Menu there are the following options:
Search box - to search and filter elements from your side menu
Connection - to manage connections
My ER-Diagram - to view the ER Diagrams in your app and include the tables in Design Panel.
My Query - to view the other queries in your app and include the query in Design Panel.
Side Menu can be closed by clicking on the icon for ‘AppSpace’
Inside SQL Builder ONEWEB also provides an option to connect to the database and run the query to verify. Users can find the Connection option on the Side Menu
Click on the Connection, it opens the dialog to configure New Connection or you can use an existing connection in your application.
How to use Existing Connection Users can choose to select the connection by choosing the Database, then choose a server, and finally select database schema by clicking on ‘set active’.
Once the connection is successful, the user can see the server info updated on the Connection menu.
Users can expand the connection and browse for the tables. Click on ‘Connection’, then click the database name and then the schema name.
How to confgure New Connection : Click on the New Connection button.
Now follow the wizard through the following steps.
Database Engine : Select Database Engine
Connection Detail
Database driver: User can choose from existing drivers or upload a new driver version. User also has a sample driver attached here to download and use.
Once the configuration is complete, the user can test it by using the Test option. If the alert message shows success, then it means an SQL Builder can connect already.
Click the close button in an alert box and click Save & Done button.
Once the new connection is successfully created, it will be shown in the list of available connections. The user can edit and delete by clicking on the icon beside the connection name.
Users can expand the My ER Diagram Menu on the left-hand side to choose the ER Diagram and the tables to be used in the SQL query. Drag and drop the table from the left panel onto the design diagram to start building the query.
Once the user drag and drop the table into the design panel, it will show all the columns in that table in the design panel.
Users can also see the other SQL Builder projects in their app on the side menu under My Query. Users can drag and drop another builder project to the design panel to use it as a sub query. It will show the output of that query as a table in the design panel.
SQL Builder also has a configuration panel that shows the current SQL configuration which is being built
It has the following tabs
Columns : Displays the field or columns that are selected from the table for running the query.
Union List : To configure union with another query.
Join List : Displays the different joins between tables in the query.
Query : Displays the output SQL query from the builder.
Data : In this panel, user can execute the SQL command to get sample data if a valid connection is present.
It also has the following submenu in the columns tab.
Columns Tab Columns that are selected in the design panel will show in the columns tab and users can add additional configurations to them if they want.
If offers the following features
Select: Select to include the columns in the select clause or un-select as required User can also delete the column tab by clicking at icon ‘x’
Function: To add SQL functions to the select columns like Min, Max, Count, etc.
Distinct: To set a column value as distinct in select.
Column: To display the name of selected columns.
Alias: To configure alias name for a select phrase.
Object: Schema Name and Table name
Group by: For selecting the column to be used in group by claus
Having: For setting the condition for group_by clauses
Sort: To include the column in the sort clause.
Sort Type: To set sort by ascending or descending.
Union List Tab When you have more than one SQL project in your application, you can join them together to form complex queries. The union tab allows users to configure the union with another SQL builder project that is selected and dragged to the design panel.
Join List Tab To configure the join condition for the SQL query.
Query Tab To generate the SQL query from all the configurations.
Data Tab To execute the SQL query generated against a valid connection and view the result set from the database. The current version retrieves only the first 5 records from the result set.
Click on the + Condition to specify the condition
New : To generate a new concatenated column in the generated query
Exit : Exit from the project
Save : Save project
Condition wizard : Configure condition
New Concatenated column . To add a new column in the select clause as a concatenated string of multiple columns. Users can choose the object or table and then choose the columns that need to be concatenated.
Exit To exit from the current SQL Builder project.
Save To save the current SQL Builder configurations.
Condition Wizard To add condition statements to the SQL. User can add where conditions as well as group conditions.
