Execute SQL Scripts on Project Load
This chapter describes how SQL Scripts can be executed automatically when opening a project.
Ofter customer create additional views/queries to be used in combination with PlantLink to create some sort of data flow. Or to extract data in some form. To avoid running a SQL script manually, you just need to copy a SQL script file into the project and the views or tables will be created.
The project used at the start is the one from Getting Tag from connected objects into instruments and it can be downloaded with the following Link:
Download Sample Project (Start)
A link with the project created during this chapter is at the end of this chapter.
Activate Run SQL Script on project load
First you need to activate the feature for PlantLink in the Settings.
Check the box to allow PlantLink to find and execute SQL scripts.
Location and Naming of SQL Scripts
The SQL scripts must be located under the [PP]\ACPlantTools\PlantLink folder.
The names of the SQl scrip files are pre-defined as follows:
PT_SQLITE_PID.sql SQLite Script File for P&ID category
PT_SQLSERVER_PID.sql SQL-Server Script File for P&ID category
PT_SQLITE_3D.sql SQLite Script File for 3D category
PT_SQLSERVER_3D.sql SQL-Server Script File for 3D category
You can have all 4 files in your project. If the project is then using SQLite, then PlantLink will load the PT_SQLITE_.sql files. Otherwise the PT_SQLSERVER_.sql files.
Creating SQL Script using SQLite
In this example, we will use the View created in the ProcessPower.dcf from Getting Tag from connected objects into instruments. We can see the design of the view.
The Import/Export menu is available only in the SQLite Expert Professional version, but not in the SQLite Export Personal version.
Select Data Transfer Wizard.
Then click on Next.
Then Export.
SQL Script.
Save the file under [PP]\ACPlantTools\PlantLink under the name PT_SQLITE_PID.sql.
The path and file name will be shown. Click on Next.
Use UTF-8 and click on Next.
If the SQL Script is supposed to create views when the project is loaded, you would get an error if the view already exists. Therefore you should either check if the view already exists (and then not create the view), or you use DROP VIEW before the view gets created again.
This is why you should use the option Drop destination objects first.
Now you select Views.
By default, all views are selected.
Click on Unselect all.
Now you select only those views you want to actually have in the SQL Script. In our case PL_VIEW_Get_Tag.
Lastly, you click on Next.
Then Next again to export the SQL Script.
And finally a success page.
If you open the SQL Script file you will see this.
Everything in green between /* and */ are comments.
Creating SQL Script using SQL Server
When using SQL-Server databases you use SQL-Server Management Studio. Then you select the view and export it as shown below.
This time the file name is PT_SQLSERVER_PID.sql.
SQL-Server Management Studio will then export the view.
If you open the SQL file it looks like this:
However, this exported file won't work. The SQL Statements must be enclosed with the EXECUTE('...') command. And the GO command isn't needed either.
Test with SQLite
Delete the existing view with SQLite Expert.
Click on Yes.
Now start Plant 3D with the project. Start Plant 3D again to be sure, the view was also deleted and then recreated.
And finally, check if the link configuration shows Active.
Test with SQL Server
First we delete the view the SQL Script is supposed to create.
The Delete Object dialog will pop up and you click on OK.
If you start Plant 3D with the existing PT_SQLSERVER_PID.sql, the view will appear again. make sure you start Plant 3D a second time, because now the script is supposed to delete the view and then recreate it again. With the first try, the view was already deleted by you.
Check if the link configuration shows Active. Then the test was successful.
Last updated