Database Table Management

Version

Application Scenario

You may encounter challenges in the following aspects when using FineDataLink to process data:

1. Debug SQL statements: You often need to write and debug SQL statements when developing data. However, FineDataLink did not allow previewing SQL execution results. You must execute these statements in third-party database management tools or databases to ensure correctness before copying and pasting them to FineDataLink. This multi-tool workflow was inconvenient and reduced development efficiency.

2. Viewdata processing results: There was no dedicated function in FineDataLink for you to check whether the data processing results met expectations. You could only query data using Parameter Assignment, Data Synchronization, or third-party database management tools.

FineDataLink provides a Database Table Management function starting from V 4.1.6.1 to address the aforementioned issues.

Function Description

The Database Table Management function is described below.

• For databases supporting SQL statements, you can write SQL statements to query data and modify tables.

• You can view data and structures of tables, modify table names and descriptions, clear, delete, and duplicate tables, and so on.

Note:

Both conditions must be met to use the Database Table Management function.

• You have registered the Database Table Management function point (in FineDataLink of 4.1.9.2 and later versions). For details, see Function Point Limit.

• You have permission to use the Database Table Management function. For details, see Data Platform Use Permission.

1. Independently deployed projects: 

Choose Data Management >  Database Table Management, as shown in the following figure.

2. Projects integrated with other FanRuan applications:  

• FineDataLink of versions before 4.1.9.2

In the FineDataLink project integrated with a FineBI project, choose Data Center > Database Table Management to use this function, as shown in the following figure.

In the FineDataLink project integrated with a FineReport project, this function and Data Development are located in the Data Center module.

• This function in FineDataLink of 4.1.9.2 and later versions has been hidden temporarily.

Note:

You cannot view database objects such as stored procedures and functions currently.

You can select a pre-configured data connection on which you have Use permission to view the databases/schemas and tables/views associated with the selected data connection. You can search for a specific database, table, and view, as well as write and execute SQL statements for querying and debugging purposes.

Data Source and Data Connection

For details about data sources supported by Database Table Management, see Data Sources Supported by Database Table Management.

After selecting a pre-configured data connection on which you have Use permission, you can perform operations on tables associated with the data connection.

Search Bar and Refresh Button 

Number	Description
1	You can search for a database/table/view associated with the selected data connection.
2	You can click the Refresh button to refresh the selected table or view and view the latest table structure and data.
3	You can search for a field name, as shown in the following figure.
4	You can click the  icon to refresh the entire page.

Table Data, Table Structure and Source SQL Statement Tabs

1. You can select a table or view and view table data and the table structure, as shown in the following figure. You can open multiple tables simultaneously.

Note:

FineDataLink of 4.1.9.2 and later versions allows you to set the number of records per page and supports paginated data viewing.

2. You can view the view creation statements in FineDataLink of 4.1.9.2 and later versions, as shown in the following figure.

Note that:

• You can view the source SQL statement but cannot modify it.

• You can view the source SQL statement of tables or views in the following databases: MySQL, Oracle, SQL Server, PostgreSQL, SQLite, TiDB, DM, KingBase, GaussDB, Greenplum, Doris, StarRocks, GBase8a, YMatrix, and PolarDB PostgreSQL.

Other Buttons

1. When you select a table, you can click the More button and choose to modify its name and description, clear its contents, delete it, copy and paste it, or generate the corresponding SQL statement, as shown in the following figure.

• Name and Description: The table name can include English letters, Chinese characters, numbers, and underscores, with no length limit.

• Copy and Paste: You can copy and paste the table to another database.

• Generate SQL: You can generate various statements, including SELECT, INSERT, UPDATE, MERGE, INSERT ON CONFLICT, UPDATE FROM, DELETE USING, and DDL. (The supported statement types vary with databases. All supported statement types are listed here.) You can copy the generated statements.

• In FineDataLink of 4.2.0.3 and later versions, the statement to be executed will be displayed after you click Clear Table or Delete Table.

2. When you select a view, you can click the More button and choose to modify its name and description, delete it, duplicate it, or generate the corresponding SQL statement, as shown in the following figure.

• Name and Description: The view name can include English letters, Chinese characters, numbers, and underscores, with no length limit.

• Copy and Paste: You can duplicate views in the following databases: MySQL, Oracle, SQL Server, PostgreSQL, SQLite, TiDB, DM, KingBase, GaussDB, Greenplum, Doris, StarRocks, GBase8a, YMatrix, and PolarDB PostgreSQL. The view copy will be stored in the current database/schema by default.

• Generate SQL: You can generate various statements, including SELECT, INSERT, UPDATE, MERGE, INSERT ON CONFLICT, UPDATE FROM, DELETE USING, and DDL. (The supported statement types vary with databases. All supported statement types are listed here.) You can copy the generated statements.

Saved SQL File and SQL Editor Creation

1. You can write and execute SQL statements with the selected data connection for querying and debugging purposes, as shown in the following figure.

• A SELECT query can contain only one SELECT clause.

• Parameters are not supported in SQL statements.

• Only the most recent execution log is retained for each SQL statement.

• Starting from FineDataLink 4.1.9.2, you can rename the SQL file by clicking the xx icon next to the file name.

• Starting from FineDataLink 4.2.0.3, you can save SQL statements while editing them by pressing Ctrl+S.

2. You can click the Save button in the upper right corner to save the SQL statement to Saved SQL File for future use, as shown in the following figure.

Table Type Display

Starting from FineDataLink 4.2.0.3, you can view the type of Doris and StarRocks database tables, as shown in the following figure.