MySQL is a great option if you are looking for a free and easy-to-use database. With Linx you can easily connect and interact with the data to;
- Create an API to access and manage – Good for single-page applications (SPA)
- Create a file from data in the database
- Automate workflows based on data stored in the database
Interacting with a MySQL database with Linx is done via ODBC. Once the ODBC data source is set up, you can interact with the MySQL database using the built-in Database Plugin in Linx. This will allow you to run any query you can on MySQL.
- Retrieve data for later use in your process
- Insert records you received or processed from any other source
- Manage data
ODBC for other databases
ODBC is useful for connecting to multiple database technologies, including MySQL, PostgreSQL, SQLite, Microsoft SQL Server, IBM DB2, Sybase, OpenLink Virtuoso and Microsoft Access. The techniques mentioned in this post can be applied to most databases where ODBC is used to connect.
Setting up ODBC for MySQL
To connect to your MySQL database, an ODBC data source must be created. To do this, download and install the official ODBC driver for MySQL: MySQL Connector.
When the connector is installed, the ODBC data source can be set up in the ODBC Data Source Administrator panel. Set up a new DNS by using the installed MySQL connector.
Important Notes:
If you want to see the schema in Linx and want to use query generation, be sure to allow schema support (under the metadata section).
To use the ODBC data source on a Linx server, be sure to create the data source as a System DSN.
A full tutorial on how to do this can be viewed on the Linx Community
Using ODBC with low-code
With the ODBC configured, you can access and use the data in any process. The Database plugin will allow you to interact with data stored in your MySQL database. You can use the ExecuteSQL function to execute simple or complex SQL queries to
- Read from databases (SELECT)
- Write to databases (INSERT)
- Maintain existing records (UPDATE)
- Remove records (DELETE)
Select the connection type as ODBC and use the connection editor to add the required details.
If you use the connection string frequently in many different functions, it is recommended that you create a Setting for the connection string. A setting is a re-usable parameter that most components or functions in Linx can reference. Settings can also be made secret for more sensitive values, such as connection strings that may contain a password.
If your ODBC connection allows schema support, you can also quickly generate queries by right-clicking on an element and selecting the template you want to generate:
You can also pass in parameters into your query, making it easier to filter, insert or update values.
Stored procedures can be executed with the ExecuteStoredProcedure function. Pass in any required parameter values, retrieve and loop through results and more.
You 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.
You can;
- 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
Adding additional functionality
With Linx, you can access many different functionality pieces. Once you have set up a way to connect to the database, you can do nearly anything relating to the database. Some examples of popular use cases include:
- Retrieve data from an API and write it to the MySQL database
- Use data from a MySQL database in a REST API
- Write data to a file or report
- Read data from a file into the database
- Integrate various systems to centralise thor functionality
- Migrate data from one database to another
Example: Create a straightforward file importer to import data into your MySQL database.
Additional functionality such as error handling, logging or validations can be added to the function, depending on your requirement. These functions can also be executed on a specific time or when an event triggers them such as when a file is dropped in a specific folder.