<< Click to Display Table of Contents >> Manual > Monitoring system construction guide > Building server logic > Executing your own logic in the background (SC2) > Access the DB from script Ver2 |
overview
By using Direct query database(SQL) control, you can connect to the database from Script Ver2 via ODBC and execute SQL.
Action used: Script Ver2 Action
Download the sample
The creation examples shown on this page are provided with samples.
The sample can be downloaded from below.
Server configuration file: script2_sample02.txt
Script file: script.txt
The above sample includes a SQL file (DDL) to generate the table. Use it as a reference when creating the table.
For details about each event and method used in the sample, please refer to the Control Reference. |
Operation check
This sample requires a database to be prepared, and you must define a DSN to connect to the database in the ODBC settings of Windows.
Once the database is ready, bring the sample server configuration file online from FA-Server (yellow arrow).
After going online, the script's operation results will be output as a log to Output01.
In this sample, the SQL statement "SELECT * FROM LOGTABLE" is issued to the connected database to retrieve all data from the LOGTABLE table. This SQL statement is written in the sample script. Feel free to edit the SQL statement to match the actual table name and run it.
Commentary
This section explains the scripts written in the script definition file, and explains how to access the database.
1)Initial Processing
Initial processing is performed in the OnInitialize event.
As an initial process, use the New operator to create a DirectQueryDB control object.
Example script
var objDB; event OnInitialize() { ::SvsDump("OnInitialize:" + ::SvsGetActionName()); objDB = new DirectQueryDB; ::SetTimer(0, 5000, -1); }
Processing content
i.Use the New operator to create a DirectQueryDB control object. In this sample, the variable "objDB" is defined as a global variable to store the object.
ii.Set the timer with SetTimer. When the timer expires, the OnTimer event is generated. Here, we set an unlimited timer with a 5-second cycle, and execute SQL within the OnTimer event that occurs every 5 seconds.
2)Execute SQL Processing
Execute SQL within the OnTimer event. First, you need to connect to the database using the Connect method of the DirectQueryDB control. In this sample, a user-defined function called mConnectDB is prepared and the process for connecting to the DB is described. After connecting to the database, execute SQL (retrieving all table data using SELECT) using the QuerySQL method and output the retrieved data to the Output log.
Example script
event OnTimer(timerid,counter) { ::SvsDump("OnTimer Id:" + ::CStr(timerid) + " counter:" + ::CStr(counter)); if(!mConecctDB()){ return; } var vFieldNum, vRecordNum, vFieldName, vFieldType, vData; if(!objDB.QuerySQL("SELECT * FROM LOGTABLE", vFieldNum, vRecordNum, vFieldName, vFieldType, vData)){ ::SvsDump("Fail to query sql. Msg:" + objDB.GetLastError()); objDB.DisConnect(); return; } ::SvsDump("FieldName:" + ::CArrayToStr(vFieldName, ",", F)); ::SvsDump("Data:" + ::CScript(vData)); } function mConecctDB() { var vDSN = "LOGGERDB"; var vDbName = "LOGGERDB"; var vDbUser = "user"; var vDbPass = "password"; if(!objDB.Connect(vDSN, vDbName, vDbUser, vDbPass)){ ::SvsDump(objDB.GetLastError()); return F; } return T; }
Processing content
i.Connect to the database. The connection process is written in the user function "mConnectDB". mConnectDB uses the Connect method to connect. The arguments passed to the Connect method are the DSN name, database name, login user, and login password. Edit these descriptions to suit the actual operating environment.
ii.Once you connect to the database, execute the SQL by executing the QuerySQL method. In this sample example, it executes "SELECT * FROM LOGTABLE".
iii.From the Execute SQL results of "ii", the field names and column data are output to the Output log.
In the sample, the QuerySQL method is used to obtain the results of SQL execution, but the ExecuteSQL method is used to issue UPDATE or INSERT statements. If you use the ExecuteSQL method, you can also use the BeginTrans method to perform transaction processing. |