## Answer If the database is SQL Server, use the "Linked Server" function, and if it is PostgreSQL, use the "FDW" function to make the other database accessible beforehand, then use "「Extended SQL」". --- ## Overview This is a sample code that uses "OnSelectingColumn" in "「Extended SQL」" to reference a table in an external database from Pleasanter. Use the "Linked Server" function of SQL Server to reference the external database you want to reference. ## Prerequisites 1. The linked server must be set up beforehand. For SQL Server, use the linked server function. For PostgreSQL, use the "FDW" function. [Linked Servers \(Database Engine\) \- SQL Server \| Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver16) [F\.33\. postgres\_fdw](https://www.postgresql.jp/docs/9.6/postgres-fdw.html) ## Description An example of an external DB table referenced from Pleasanter. | id | Name | Age | | :-: | :-: | :-: | | 1 | user1 | 21 | 1. Assume that the id of the external DB table is stored in ClassA of Pleasanter table. 1. Display the age of the external DB table in ClassZ of Pleasanter. ## Operation Procedure ### Table settings 1. Open Pleasanter and create a table. 1. Open "[Manage Table](/en/manual/table-management)" and enable "ClassZ" in the "「Index」" and "[Editor](/en/manual/table-management-editor)" tabs. 1. Note the site ID of the target table. ### Extended SQL settings 1. Save the JSON file example below as LinkSever.json. - Save to the following location: - C:\web\pleasanter\Implem.Pleasanter\App_Data\Parameters\ExtendedSqls\LinkServer.json 1. Change SiteIdList to the site ID you noted down. 1. SQL FROM clause in CommandText: [Linked Server Name].[DB Name].[Schema Name].[Table Name] Change [Collation] to the linked server you want to set and the table name you want to reference. 1. Restart Pleasanter. ## Sample Code This sample code is for SQLServer. ##### JSON(LinkServer.json) ``` { "Name": "LinkServerTest", "Description": "LinkServerTest", "SiteIdList": [xxx], "ColumnList": ["ClassZ"], "OnSelectingColumn": true, "CommandText": "(select age from [LINKSERVER].[postgres].[public].[staff] where id = Results.ClassA COLLATE Japanese_CI_AS)" } ``` ## Related Information <div id="ManualList"><ul><li><a href="/en/manual/table-management">Manage Table</a><span>10.02.2024 up</span></li></ul></article> <ul><li><a href="/en/manual/table-management-editor">Manage Table: Editor</a><span>06.20.2025 up</span></li></ul></article></div><input id="SearchTextHidden" type="hidden" value="" />