Many companies need the ability to interact with information in a relational database. They need to analyze, update, and display data often without in-depth knowledge of a database language like SQL. Proficy Portal can use data from the results of database statements or queries created by the SQL Statement Builder to construct applications that interact with a relational database.
In general, the SQL Statement Builder is a graphical interface from Proficy Portal to your relational database. It gives you the ability to design statements in the SQL language without the complexity of knowing or remembering the exact syntax required. It also allows the SQL expert to edit the text of a generated statement. In this way, the SQL Statement Builder is useful to all users.
The statements are stored in a statement library and can be executed elsewhere during a Proficy Portal session. The SQL statements may include parameters to allow users to enter data at run time or to allow a single statement to be reused in a variety of displays. You can also use the text of SQL statements in other applications.
You can use SELECT statements created in the Statement Builder with Proficy Portal to access data in the following scenarios:
Connecting a SELECT statement to a chart for analysis of production trends.
Displaying the results of a Batch production run, perhaps in the form of an electronic journal.
Analyzing the frequency of alarms.
Viewing and analyzing downtime and other event information.
You can also use the SQL Statement Builder to create CALL, INSERT, UPDATE, and DELETE statements. Use these in applications to manipulate the data in your relational database in the following ways:
Using stored procedures so that the input parameters are determined by Proficy Portal and the results can be processed further in Proficy Portal. For example, you could create a display that specifies input parameters to a CALL statement. When you click a push button, the CALL statement executes and returns data to an Proficy Portal object, such as a grid or a chart.
Inserting new data in a relational database. For example, a manager could use a grid connected to an INSERT statement to record fault and operational information.
Updating existing data in a relational database. For example, a customer could use a grid connected to an UPDATE statement to change inventory information.
Deleting data from a relational database. For example, you could use a grid connected to a DELETE statement to remove outdated information.
The statements you create serve as data sources to Proficy Portal objects. When you connect a statement to an object, Proficy Portal retrieves the requested data and displays it. Very often, however, you want to see if the statement is retrieving the data you think it is. To help you determine this, the Statement Builder also provides a testing feature so you can execute a statement without connecting it to an object.
The Statement Builder is designed to work with the latest ANSI-approved version of SQL. You can use it to access SQL Server, Oracle, DB2, and other relational databases.
For more information on using the SQL Statement Builder application, refer to the following sections:
