Trying to emulate functions of our old SCADA system

Ignition
1

Hello all,
I’ve been trying to copy some of the functionality of our old SCADA system into ignition. I have been playing around in the Script playground trying to get his to work and it’s basically a series of SQL commands that place data from our powerstations into a database. I will include a small snippet of the code in this post. I am really just trying to see if I should just copy what the script is doing using transaction groups (which is what I would like to do) or make small changes to the code so that it will run in Ignition. Any help is greatly appreciated.

Here is a small section of the code:

FUNCTION PSMWeb_Dump()

INT i;
INT hSQL, ISet,iexec, iEndofDayHour, iEndofDayMin, iEndofDayPeriod;
STRING ThisTime;
STRING ThisDate;
STRING Yesterday;
REAL rTotalkWHrs;

!Calculate today’s date in the format yyyy-mm-dd
ThisDate = IntToStr(DateYear(TimeCurrent(),1)) + “-” + IntToStr(DateMonth(TimeCurrent())) + “-” + IntToStr(DateDay(TimeCurrent()))
!Calculate yesterday’s date in the format yyyy-mm-dd
Yesterday = IntToStr(DateYear(DateAdd(TimeCurrent(),-86400),1)) + “-” + IntToStr(DateMonth(DateAdd(TimeCurrent(),-86400))) + “-” + IntToStr(DateDay(DateAdd(TimeCurrent(), -86400)))

! Time is a number between 0…95 (which corresponds to each 15 minute period in a day)
ThisTime=IntToStr(TimeHour(TimeCurrent())*4+TimeMin(TimeCurrent())/15);

iEndofDayHour = 0;
iEndofDayMin = 0;
iEndofDayPeriod = ((iEndofDayHour*4) + (iEndofDayMin/15));

! Connect to the SQL server directly
hSQL = SQLConnect(“Driver={SQL Server};Server=(LOCAL);Database=psmSQL;Uid=PSMWeb;Pwd=killjoy8;”);

!Generator 11

G11_Cal_Mwhacc = G11_Cal_Mwhacc + ((G11_A200_kW_PVR*G11_sGCB_CL)/1000/4)

!----------------------------- 15 MINUTE DATA ------------------------------------

! STATION EXPORT KW
!-----------------------

ISet=SQLSet(hSQL, "INSERT INTO [psmSQL].dbo.CitectData ");
SQLAppend(hSQL, "(PowerStation, ObjectID, PSMTag, PSMDate, PSMTime, PSMValue)");
SQLAppend(hSQL, " VALUES ");
SQLAppend(hSQL, "('0890',100,'StnkW','"  +ThisDate+ "',"+ThisTime+","+IntToStr(STN_EXP_KW)+")");
iexec=SQLExec(hSQL, "");
	IF iexec <> 0 THEN
		TraceMsg("Period "+ThisTime+": SQL Error Inserting Station Export kW. SQL Error:"+ SQLErrMsg());
END
SQLEnd(hSQL);

! GENERATOR 11 INSTANTANEOUS KW METERING
!---------------------------------------

ISet=SQLSet(hSQL, "INSERT INTO [psmSQL].dbo.CitectData ");
	SQLAppend(hSQL, "(PowerStation, ObjectID, PSMTag, PSMDate, PSMTime, PSMValue)");
	SQLAppend(hSQL, " VALUES ");
	SQLAppend(hSQL, "('0890',11, 'GenkW','"  +ThisDate+ "',"+ThisTime+"," +IntToStr(G11_A200_kW_PVR)+")");
	iexec=SQLExec(hSQL, "");
		IF iexec <> 0 THEN
			TraceMsg("Period "+ThisTime+": SQL Error Inserting GM 1 kW. SQL Error:"+ SQLErrMsg());
	END
	SQLEnd(hSQL);

! GENERATOR 11 ENGINE HOURS
!---------------------------------------

ISet=SQLSet(hSQL, "INSERT INTO [psmSQL].dbo.CitectData ");
	SQLAppend(hSQL, "(PowerStation, ObjectID, PSMTag, PSMDate, PSMTime, PSMValue)");
	SQLAppend(hSQL, " VALUES ");
	SQLAppend(hSQL, "('0890',11, 'EngHrs','"  +ThisDate+ "',"+ThisTime+"," +RealToStr(G11_TEM_RHRS,12,3)+")");
	iexec=SQLExec(hSQL, "");
		IF iexec <> 0 THEN
			TraceMsg("Period "+ThisTime+": SQL Error Inserting GM 1 Engine Hrs. SQL Error:"+ SQLErrMsg());
	END
	SQLEnd(hSQL);

! GENERATOR 11 MWh
!---------------------------------------

ISet=SQLSet(hSQL, "INSERT INTO [psmSQL].dbo.CitectData ");
	SQLAppend(hSQL, "(PowerStation, ObjectID, PSMTag, PSMDate, PSMTime, PSMValue)");
	SQLAppend(hSQL, " VALUES ");
	SQLAppend(hSQL, "('0890',11, 'GenMWh','"  +ThisDate+ "',"+ThisTime+"," +RealToStr(STN_G11_KWH/1000,12,3)+")");
	iexec=SQLExec(hSQL, "");
		IF iexec <> 0 THEN
			TraceMsg("Period "+ThisTime+": SQL Error Inserting GM 1 Engine MWHrs. SQL Error:"+ SQLErrMsg());
	END
	SQLEnd(hSQL);

! GENERATOR 11 MWh
!---------------------------------------

ISet=SQLSet(hSQL, "INSERT INTO [psmSQL].dbo.CitectData ");
	SQLAppend(hSQL, "(PowerStation, ObjectID, PSMTag, PSMDate, PSMTime, PSMValue)");
	SQLAppend(hSQL, " VALUES ");
	SQLAppend(hSQL, "('0890',11, 'GenMWhCal','"  +ThisDate+ "',"+ThisTime+"," +RealToStr(G11_Cal_MWhacc,12,3)+")");
	iexec=SQLExec(hSQL, "");
		IF iexec <> 0 THEN
			TraceMsg("Period "+ThisTime+": SQL Error Inserting GM 1 Engine MWHrs. SQL Error:"+ SQLErrMsg());
	END
	SQLEnd(hSQL);

Any help is greatly appreciated. I’m a little rusty with SQL right now and Python is very much new to me so any help is greatly appreciated.

2

You can definitely rewrite that in python without much trouble. I would use SQL queries (via runPrepQuery) to grab ThisDate, Yesterday, and ThisTime and to do your inserts (via runPrepUpdate). You can use the Try/Except functionality in python to catch any errors. It should all be pretty painless.

You should check out runPrepQuery and runPrepUpdate, and Try/Except in the user manual.

3

How frequently or what causes the script to run? I think you could create the same functionality with transaction groups and “expression items”. You could certainly accomplish the same in script, but the former should be simpler.

4

Thanks for the suggestions rob, i have been looking in the manual and have been going a little cross eyed trying to put 2 and 2 together.

Nathan, I really want to do it with transaction groups, but people who get paid a lot more than me want me to try it this way. I had set up something to at least get the 15 minute time periods to show them it would be easier with the transaction groups, but they want to try it with scripting.

Thanks for the suggestions guys, I really appreciate it.