Linx natively allows interaction with an MSSQL database via the Database plugin. Using this plugin, you can:
- Execute custom SQL statements
- Bulk copy data to a specific table
- Execute stored procedures (and pass in parameters)
- Begin transactions (to facilitate performing multiple database operations on the same transaction)
This functionality can be used by dragging the required function from the plugins tab onto the function you are building.
Pairing database access to other standard functionality in Linx, allows for quick development across several business areas. These include
- Building an API to provide the data
- Building ETLs
- Generating reports
- Sending Emails
- Automating business processes
- Read data from an API or message queue and store it in a database for later use
Executing SQL statements
By using the ExecuteSQL function, you can connect to a database and execute a custom SQL script. The statement can be simple or complex, depending on your requirements and SQL aptitude. You can:
- Read from databases (SELECT)
- Write to databases (INSERT)
- Maintain existing records (UPDATE)
- Remove records (DELETE)
- Perform complex scripts with CASE statements, IF statements and any functions that your instance of SQL Server can process (T-SQL)
Additionally, you can pass in parameters (illustrated above) from a function, meaning that you can specify what values you need to write, specify filters and more.
It is not essential to have a mastery of SQL to carry out basic operations. Linx includes a tool that will create templates for Select, Insert or Update requests without needing in-depth knowledge of SQL.
You will need to provide a connection string to interact with the database. This can be provided as either a string, or you can use the connection editor to build up the connections string for your SQL Server database:
A Linx database connection allows you to have full control over how the data is returned, what the column names should be and how you want to interact with the returned data.
- Bind the result to a custom type
- Alter the resulting column names outside of your SQL query
- Iterate through the results to interact with each record independently
- Return the entire dataset as a list, making it easier to interact with it later
- Return only the first row of the set
Execute Stored Procedures
Many interactions can be facilitated by executing stored procedures. To do this, you can either
b) you can execute it with the ExecuteStoredProcedure With this function, you select what stored procedure you want to execute, along with parameters that need to be passed in. The parameters can be completely dynamic, meaning you can get them from any other function.
Bulk Copy Data
Use the DB Bulk Copy function if you need to write a large quantity of data to a table. This function creates a Loader object in its execution path. Use the SetValue function to add the rows that you want to insert.
This DB Bulk Copy function is effective when working with a time-sensitive load or when you just quickly need to get data into a specific table. It works well for ETL processes, when staging data.
This feature can be used with MS SQL Server and Oracle databases.
