Sample Script to Read Data From SQLServer into WinCC

WinCC supports the use of Microsoft ActiveX Data Objects (ADO) to query/manipulate databases.  ADO is supported using the OLE Automation interface provided by WinCC.

There are countless books, web-sites and training courses available on ADO scripting.  It is not the intention of this document to explain the basics of ADO but instead to demonstrate how ADO scripts can be utilised within WinCC scripts.

Below is a sample project function which when called from a script will connect to SQLServer, query the sample publishers database and display the results on the screen.

Sample Script

void QuerySQLServer()
{
__object *cn; 
__object *rs; 

cn = __object_create("ADODB.Connection");
rs = __object_create("ADODB.Recordset");

cn->ConnectionString = "PROVIDER=SQLOLEDB; SERVER=MYSERVER; DATABASE=PUBS; UID=sa; PWD=";
cn->Open;

rs = cn->Execute("SELECT fname + ' ' + lname FROM authors");
printf("Authors Name\r\n");
while(!rs->eof) { 
     printf(rs->Fields(0));
     printf("\r\n");
     rs->Movenext;
}
rs->Close;
cn->Close;
__object_delete(rs);
__object_delete(cn);
}

Explanation

Here is a rundown of what the script is doing.

  Create a project function named QuerySQLServer

void QuerySQLServer()

  Declare two object datatype pointer variables

__object *cn; 
__object *rs;  

  Set the cn variable to a Connection object from the ADO library and set the rs variable to be a Recordset object

cn = __object_create("ADODB.Connection");
rs = __object_create("ADODB.Recordset");

  Set the connection string to point to the correct database.  This connection string must always be modified to reflect the local settings. The connection string is always dependant on the database you are using.

cn->ConnectionString = "PROVIDER=SQLOLEDB; SERVER=MYSERVER;DATABASE=PUBS;UID=sa;PWD=";

  Open the Connection object which will open a connection to the database using the information specified in the connection string

cn->Open;

  Query the Authors table in the sample publishers database using the cn variable and write the results into the rs variable. This is a very simple SQL string that returns data from one table. If you want to perform another action, such as join multiple tables or update data then all you need do is modify this SQL string.

rs = cn->Execute("SELECT fname + ' ' + lname FROM authors");

  Print a line of static text to display the column heading.  The output is printed to the debug/diagnostics window (which can be accessed under Smart Objects/Application Window in the Graphics Designer).

printf("Authors Name \r\n");

  Step through the resultset and print each row of data to the screen

while(!rs->eof) { 
     printf(rs->Fields(0));
     printf("\r\n");
     rs->Movenext;
}

NOTE:  this script uses the printf statement to display the results.  This is purely for the sake of simplicity.  In a real world application the data would be written to a proper container such as a set of tags or grid control.

top

NB: The C script examples use the OLE Automation interface in WinCC which is an unsupported interface. If possible you should try to use the VBS script interface to access objects. The examples have been tested by myself but please be aware that you use them at your own risk.

2007 Salma Ghafoor