Implemented SQL Client with UI

Owned by shreedutta.mukherjee (Unlicensed)
Last updated: Jun 14, 2022
11 min read

Introduction

A SQL client is a database management resource that uses a structured query language (SQL). This is a namespace dedicated to these resources to support the implementation of SQL operations. SQL is the standard language for storing, manipulating, and retrieving data from databases. You can manipulate the database to take action on the data. The SQL task allows you to connect to the database and use the SQL connection to access and work with the database. Supports MySQL, Oracle, and Snowflake databases.

Overview

The SQL client should be used to create new applications or extend existing applications. The SQL Server Client (SQL Native Client) includes a SQL ODBC driver and SQL OLEDB provider in a native dynamic link library (DLL) that supports applications that use native code APIs (ODBC, OLE DB, and ADO) to use SQL. The SQL Client allows you to access and write data in a relational database management system at the same time.

You can use the SQL client to connect to different DBMSs to query, navigate tables and views, and export data directly from your smartphone or tablet. You cannot edit the value directly in the table view. However, you can execute any SQL statement supported by the database, including inserts, updates, and deletes, and you can execute multiple commands in a single query. Highlighting the syntax helps you write the query, and undoing / redoing makes it easier to edit. This app allows you to filter, sort, and search tables and views by type, schema/database, and name.

You can connect SQL Client with various server and databases. Those are mentioned below:

  • Microsoft SQL Server

  • MySQL

  • PostgreSQL

Features

  • SQL Client helps to execute and save SQL statements

  • Execute multiple SQL commands in one query

  • It allows you to list, filter, sort, and search tables and views

  • The SQL statements can be shared and used in future

  • You can export data from tables, views, or queries to csv-files based on your need.

  • You can use the syntax highlighting. It helps to determines the color and style of source code displayed in the Visual Studio Code editor.

  • You are allowed to undo/redo the SQL statements based on your requirements.

Type of Actions for SQL Queries

The SQL Clients are associated with various types of queries. The following query types are supported for various actions in the database: 

  1. Select: Select queries allow you to retrieve data from the database defined in the query and the selected SQL connection.
    A simple select query format can be defined as follows: 

    SELECT [Columns or *] FROM [table] Where [Condition]

  2.  Insert: Insert Query allows you to insert data into the database tables listed in the query itself. For Sapper SQL tasks, you can insert it as a single row or in a batch. For more details about batch processing, please refer to this link: https://sappersoftware.atlassian.net/wiki/spaces/SD/pages/125075279

  3. Update: Update queries are useful for updating data in one or more rows of a database table using the records defined in the query. The update query specifies the rows that are updated by the conditions described in the where clause of the query.

  4. Delete: Delete query allows you to delete one or more rows from a database table based on the conditions defined in the query where clause.

Type of Query Execution

SQL Client consists of some additional functionalities to process data into or from databases.
There are the following types of functionalities:

Simple Query Execution 

 Simple query execution helps process the queries effectively. It allows you to modify one query at a time. For example, you can add one employee and modify one employee at a time.

Dynamic Query Execution 

Dynamic SQL is a SQL statement created and executed at run time based on the input parameters passed. Let's look at some examples using the EXEC command and the sp_executesql extended stored procedure. SQL tasks allow you to execute queries with dynamic values. Dynamic queries allow you to use data slots in your queries and modify database tables based on the data received from the data slots.

Batch Execution

Batch execution refers to the process of initiating a batch of current processing. When a batch is sent for execution, a set of commands related to the defined component parameters is sent to the database. Batch processing is a unique feature of SQL tasks. This allows you to insert data into the database all at once. A single call to the database runs the data or batch provided.

Benefits of SQL Client

  • SQL clients can run in open-source mode, which makes queries cheaper.

  • SQL consists of simple statements that make it easy to learn and write SQL queries.

  • SQL is available to programmers who create applications that use databases.

  • SQL is used to create the database and manage the security of the database. Its main use is to update, retrieve, and share data with multiple users.

  • You can give different users of a database different views of the database structure and content via SQL.

  • SQL is used to connect a front-end computer (client) to a back-end database (server). Therefore, the client-server architecture is supported.

SQL Client Data Location

Once the SQL task configuration is complete, a set of data locations will be created with your data defined for further use in automation.

The SQL task data locations are as follows:

  • Records: A set of connected data with various names and data types.

  • Count: An aggregate function that returns the number of rows, returned by a query.

Use of SQL Task

You can use the Execute SQL task for the following purposes:

  • You can use the SQL task for Truncate a table or view in preparation for inserting data.

  • You can create, alter, and drop database objects such as tables and views for the SQL task.

  • You are allowed to re-create fact and dimension tables before loading data into the SQL task.

  • Run stored procedures. If the SQL statement invokes a stored procedure that returns results from a temporary table, use the WITH RESULT SETS option to define metadata for the result set.

  • Save the row set returned from a query into a variable.


How to use SQL with Simple Query

  1. Click on the Automation from the Sapper home page.

 

2. Create or choose the required automation from the list of automation for which you wanted to use the SQL client task.

 

3. Drag and drop the SQL task in the automation and click on it. The form will appear on the screen.

4. Add a name and an appropriate description for reference.

 

5. Select the connection that has been created for the SQL or create a new one from the plus button. Click here https://sappersoftware.atlassian.net/wiki/spaces/SD/pages/116359525 to know how to create an SQL connection. Here we have selected Snowflakes.

 

 

6. Choose the type of query according to the action you are going to perform. You can choose from the following query types: Select, Insert, Update and Delete.

 

7. Add the desired query as per the requirement and click on the tick button to verify the query. A query is a request for data or information from a database table or combination of tables.

8. Once the written query is valid then click on save to configure it. In case of an error, correct the query and verify it again.

 

 

Key

Description

Key

Description

Select

A Select statement retrieves zero or more rows from one or more database tables or database views.

Insert

The SQL Insert statement is used to add new rows of data to a table in the database.

Update

The use of update SQL queries in access databases is to add, change, or delete the information in an existing record

Delete

The SQL Delete query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise, all the records would be deleted.


How to use SQL with Dynamic Query

  1. Click on the Automation from the Sapper home page.

2. Create or choose the required automation from the list of automation for which you wanted to use the SQL client task.

 

3. Drag and drop the SQL task in the automation and click on it. The form will appear on the screen.

4. Add a name and an appropriate description for reference.

 

 

5. Select the connection that has been created for the SQL or create a new one from the plus button. Click here https://sappersoftware.atlassian.net/wiki/spaces/SD/pages/116359525 to know how to create an SQL connection. Here we have selected Snowflakes.

 

 

6. Choose the type of query according to the action you are going to perform. You can choose from the following query types: Select, Insert, Update and Delete.

 

7. After selecting the query type, the user needs to add the desired query to the input field. Dynamic queries must use a colon before each parameter.

8. Add the desired query according to the data and click the check button to confirm the query. A query is a request for data or information from a database table or combination of tables.

9. If the query is successfully validated, a key-value entry field will appear on the screen. These input fields are created according to the parameters present in the query. You must enter the file name according to the key in the value entry field. For example, for the IFSC key, drag and drop the file named IFSC Code into the value entry field of the data slot. Do the same for other key / value pairs.

10. Click on save to successfully save the configuration.

 

Key

Description

Example

Connection

A connection is a mechanism by which a task authenticates with a third-party app or service. A connection is input and might contain a username/password set, API key, OAuth 2.0 token, or some other proprietary authentication type.

Snowflake

Query Type

The query type is a type of query used in a SQL Client.

Select, Insert, Update and Delete.

Query

A query is a request for information expressed in a formal manner.

Sample

Batch Processing

Batch processing is a way to run a large number of iterative data jobs. It allows you to process data when computing resources are available and there is little or no user interaction. In batch processing, you can collect and store data and process it during an event called a "batch window." This process helps you to improve efficiency by prioritizing processing and data job completion. It is a intended for commonly used programs that can be run with minimal human intervention. In batch processing, the system can processes a set of tasks collected in a group. This is also known as workload automation (WLA) and job scheduling. It is a very inexpensive way to process large amounts of data in a minimum time. The process of batch processing only stops when the system will detects an error or anomaly and then it notify the employee or manager in charge.

 

 

 

 

Benefits of Batch Processing

Batch processing consists of numerous benefits that helps to improve the data management process. Those are mentioned below.

  • Batch processing helps you to enhance work efficiency by minimizing stress on processors.

  • As compared to stream processing, batch processing is less complicated, as there is no requirement of specific hardware or supporting system for inputting data.

  • After the completion of the batch processing the system requires low maintenance than the stream processing.

  • Batch processing automates most of the job processing components, which helps to reduce errors and enhance the quality of the data.

  • It helps to enhance the data accuracy by minimizing the complication and data errors.

  • Batch processing helps you to speed up the processing time by processing a huge amount of data seamlessly.

  • The batch processing process helps you to reduce your workload by automating the process.

How to use SQL Insert Query with Batch Processing

  1. Click on the Automation tab from the Sapper home page.

 

2. Create or pick the desired automation from the listing of automation for which you desired to apply the SQL client task.

 

3. Drag and drop the SQL task in the automation and click on it. The form will appear on the screen.

4. Add a name and an appropriate description for reference.

 

 

5. Select the connection that has been created for the SQL or create a new one from the plus button. Click here to know how to create an SQL connection. Here we have selected Snowflakes.

 

6. Add the required query as per the need and click on the tick button to verify the it.

7. Click on the below check box and a new input field named batch data will appear on the screen.

 

8. Now, feed the data into the batch records enter subject from the records slot. It should be an array form of records when you consider that you'll be storing records in a series of consecutively numbered entries. Batch data pipelines are performed manually. In every run, they extract all records from the data source, follow operations to the records, and post the processed records to the data sink. They are finished as soon as all records had been processed.

9. Add the query as per the requirement. Then you should make sure that the query is written based on the batch data parameters as it is case-sensitive.

10. Now, add the required query as per the need and click on the tick button to verify it. A query is a request for data or information from a database table or combination of tables.

11. Once the executed query is valid then click on save to store the configuration. In case of an error, correct the query and verify it again.

 

 

Key

Description

Batch Execution

Batch Execution refers to the process of initiating a Batch for current processing.

Batch Data

Batch data analytics methodology is to use all available data for batch process modeling and monitoring.

Tenant Variables

Tenant variables allow you to specify a variable which a tenant can change.