Configure SQL Devices

SQL devices are configured and used in a slightly different way than other types of devices.

To configure an SQLdevice:

1. In the Setup activity, select Devices.

2. Add a row to the Grid Editor.

3. Complete the following fields:

   • Name - Specify a name for the device, for example mySqlDevice.

   • Format - Specify the format of the device. The format is the definition of the column names in the target database table. For example:

     {date,10}{day,15}{month,20}{year,4}

   • Header - Specify the connection string for the database. For example, Driver={SQL Server};Server=MyServer.

   • File name - Specify the complete database table name. For example, TestDatabase.dbo.MySqlCalendar.

   • Type - The type should be set to SQL_DEV.

4. Configure the rest of the fields as per other devices, see Add a Device.

5. Click Save.

Example

When configured, the device can be associated with SCADA elements, or used in Cicode. SQL devices are used in Cicode in a slightly different way to other types of devices. For example, the order of DevSetField and DevAppend is different with SQL devices.

The following example makes a connection to a SQL device, cleans the records, adds new records to the device, finds records matching the condition, then closes the connection:

FUNCTION

DataSQLDeviceLogging()

STRING strValue = "";

INT hConnection = DevOpen("mySQLDevice") // open the device

IF hConnection <> -1 THEN

DevZap(hConnection); //clean the records

DevSetField(hConnection, "date_","30-03-2011");

DevSetField(hConnection, "day_","Wednesday");

DevSetField(hConnection, "month_","March");

DevSetField(hConnection, "year_","2011");

DevAppend(hConnection);

DevSetField(hConnection, "date_","31-03-2011");

DevSetField(hConnection, "day_","Thursday");

DevSetField(hConnection, "month_","March");

DevSetField(hConnection, "year_","2011");

DevAppend(hConnection);

DevSetField(hConnection, "date_","01-04-2011");

DevSetField(hConnection, "day_","Friday");

DevSetField(hConnection, "month_","April");

DevSetField(hConnection, "year_","2011");

DevAppend(hConnection);

DevSetField(hConnection, "date_","02-04-2011");

DevSetField(hConnection, "day_","Saturday");

DevSetField(hConnection, "month_","April");

DevSetField(hConnection, "year_","2011");

DevAppend(hConnection);

IF DevFind(hConnection, "April", "month_") = 0 THEN

strValue = StrTrim(DevGetField(hConnection, "day_"));

END;

DevClose(hConnection); //close the connection

END;

END