Working with Databases in Linx

Working with databases in your Linx solutions is made easy with the Linx Database plugin.

Linx supports almost any database, including ODBC databases, SQL Server, OLE, Oracle and MongoDB.

In this article we will show you how to connect with your database using the Connection Editor and how to write SQL statements using the SQL Editor.

Using the Connection Editor

To connect with your database you have to provide a connection string that specifies the configuration values that are required for the connection.

The Connection Editor helps build the connection string by offering the most common connection properties for your selected connection type.

For example, the Connection Editor will prompt you for the following when you are connecting to SQL Server:

Connection Editor

Here’s a quick reference of connection strings for some of the most commonly used database types:

SQL Server

Not named, Windows authentication: Data Source=serverAddress;Initial Catalog=databaseName;Integrated Security=True;

Named server, no Windows authentication: Data Source=serverName\instanceName;Initial Catalog=databaseName;Integrated Security=;User ID=username;Password=password;

Example string:

Data Source=JOHNC\SQLSERVER2016;Initial Catalog=employees;Integrated Security=False;User ID=sa;Password=myexamplepwd123

Oracle

Using TNS-names entry: Data Source=TNSname;User ID=username;Password=password;

Without using TNS-names entry: Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverAddress)(PORT=port))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=serviceName));User ID=username;Password=password;

OLE DB

Windows authentication: Provider=sqloledb;Data Source=serverAddress;Initial Catalog=databaseName;Integrated Security=SSPI;

No Windows authentication: Provider=sqloledb;Data Source=serverAddress;Initial Catalog=databaseName;Integrated Security=;User ID=username;Password=password;

ODBC

Microsoft Access database: Driver={Microsoft Access Driver (*.mdb)};DBQ=databaseFilePath;

System Data Source Name, Windows authentication: DSN=dataSourceName;Trusted_Connection=Yes;

System Data Source Name, no Windows authentication: DSN=dataSourceName;Uid=username;Pwd=Password;


Using the SQL Editor

The SQL Editor can be used to write SQL statements.

Click on the Objects tab to expand your database table(s) and fields:

SQL Editor

Right-click on the table name, then select the type of SQL query you want to create, e.g. Generate Select:

SQL Query

The main frame will now display your generated SQL query, which can be edited if required, by either typing out the statement or by including specific variables from the Variables list:

SQL editor


To include a variable in your SQL statement:

  • place your cursor at the position in your statement where the variable must be added
  • then click on the Variables tab (if not already selected)
  • double-click on the required variable, which will then be added to your statement.

To test the execution of your SQL query, click the Test tab.


Conclusion

As shown in this article, working with databases in Linx is made easy with the help of the very intuitive Connection and SQL Editors.

Go here for more details on the Database plugin and check out this post from our Linx Community Site for more examples on how to use the SQL Editor.

For more useful resources on how to use Linx, visit the Linx Docs Site.

Scroll to top
Join our newsletter

Get the low-down on low-code.
Every month straight to your inbox.