The SQL Table card component is a powerful tool that fetches data directly from a database rather than from a MODLR Cube. The SQL Table component requires the following properties:
Once this table has been created there are several formulas that can be used to fetch data from the table.
Using Query Parameters:
The SQL Table card component supports parametrized queries in order to protect from SQL injection attacks. A parameterized query can be set up using the query parameters property in the component. The steps are as follows:
- Columns: The number of columns in the table
- Query: The Query to be ran
- Datasource: The database to run the query in
- Row Limit: The maximum number of rows to be displayed
- Sticky Row Header: A yes/no value that determined if the header will be sticky when scrolling in the table
Once this table has been created there are several formulas that can be used to fetch data from the table.
- SQLCOLUMNNAME(column): Returns the name of the field for the specified column index in a cards SQL Table component.
- SQLROW(): Returns the entire row as a comma-delimited string in a cards SQLTable component.
- SQLCOLUMN(column): Returns the column value of the current row in a cards SQLTable component. The column argument can either be the column index or the name for the current row.
- SQLVALUE(row, column): Returns the value of a specific column from a specific row in a cards SQL Table component.
Using Query Parameters:
The SQL Table card component supports parametrized queries in order to protect from SQL injection attacks. A parameterized query can be set up using the query parameters property in the component. The steps are as follows:
- Write a parametrized query using ?, for example: select * from financial_transactions where id = ?
- Open the Query Parameters and a value for the parameter and click "Add Item"
- Enter a value for the placeholder