WinCC-SQL Connection with ADODB Memory Leak

Hey,
Has anyone here experienced a memory leak while using ADODB to connect to an SQL (Or any other) database? I've got a WinCC C-Script that queries an InSQL Database which uses SQL Server 7.0. I'm sure my memory leak located in this script and I've narrowed it down to the ADODB object..I'm just looking for someone to confirm my suspicions and offer a possible work-around.

Cheers

R.K.

Hello Rusty!

Try posting the code or parts of the code so that we could have a look. Maybe someone in here is experienced with C script programming.

Regards
Borte

I use ADODB extensively in RSView SE (which has a visual basic underpinning, so this may not apply), and also in several other custom applications (C++ mainly).

Some hints, typically, memory leaks come from repeatedly OPENING new connections to the database. Unless you really need multiple connections, you should always check the state of the DB, and if it is currently open, close it and re-open it.

Same goes for recordsets. Especially for recordsets, actually, as people use them, and tend to forget them.

Do a simple check... if dbRecordSet.State = ADODB.adStateOpen THEN dbRecordSet.Close.

Again, unless you have some absolute reason to keep a recordset around, it's best to open it, populate it, manipulate it, and close it.

The other thing to watch for, is do NOT use unbounded SELECT queries (SELECT * FROM myTable).

I actually do not like using any select queries in code, and usually disable users from being able to issue SELECT's entirely. You are generally much better off creating stored procedures in your database itself, and selecting/adding/updating/deleting records using the stored procedures. (Note, that this also helps prevent breaking code if the DB is restructured, just edit the procedure).

Hope that helps a bit.

Hey Guys,
Thanks RDrast, you're reply was the most valid and helpful information anyone has given me on this topic,,,and I've been searching for nearly 2 weeks. Anyhow, as Borte suggested, I've attached a sample script which is much simpler than what I'm actually doing. Anyhow, I disabled my actual script and ran this simpler one instead to confirm that the memory leak is occurring at the ADODB Connection to my database. I run my usual script once a minute so the memory leak tends to add up relatively fast (I've got to reboot my WinCC Server every 2-3 weeks) I'll try building a stored procedure like RDrast suggested.
My script is relatively cumbersome, it involves counting how many times a PLC discrete clockbit (HeartBeat) tag equals 1 in the space of a minute. Since I'm monitoring several PLCs I dynamically alter my query via my C-script and loop it for each PLC. Originally I opened my ADODB connection once while I ran the entire loop, I recently tried opening and closing the connection for each PLC with slightly better results..does this behaviour make sense to anyone?
Anyhow, thanks for all the help, please do let me know if you see any inherent problems with my basic script.

Cheers Mates
R.K.

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

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

cn->ConnectionString = "PROVIDER=SQLOLEDB; SERVER=SQLServer;DATABASE=Runtime;UID=sa;PWD=";

cn->Open;
rs = cn->Execute("SELECT TagName, Value FROM v_DiscreteLive WHERE TagName in ('Com_Tick2s_PLC003')");
printf("Com_Tick2s_PLC003");
while(!rs->eof)
{
printf(rs->Fields(1));
printf("\r\n");
rs->Movenext;
}
rs->Close;
cn->Close;
__object_delete(rs);
__object_delete(cn);
}

Hmmm... that script looks okay.

Can you put some checks in to make absolutely sure that the connection and recordset do NOT exist on entry, and are truely deleted on exit?

It almost sounds like ADO is deleting the recordset and/or connection, but not destroying the contents of it's buffers, and leaving them hanging.

ADO will also cache things (which is bad) in order to try to improve performance; for the life of me though, I don't remember if that is configurable or not.

I'll try to remember to do some thinking on this over the weekend.

One thought, though it's usually not the 'best' practice, might be to create a global connection and global recordset object, and always work with them. That would guarantee that ADO isn't creating multiple, possibly cached, instances of them.

Okay... In reviewing my notes, I did come across the fact that ADODB doesn't always properly close out a recordset. I guess that the __delete call will do that, as it is effectively new'ed on create, but even though it goes out of scope, you might try setting rs and cs to NULL just before exiting the routine. May or may not help.

You might also put a diagnostic assert just after closing the connection, checking the 'State' property. It should be 0 if it is completely closed.


I can't think of anything else right now, ADO is much more stable that RDO was; if you are actually having memory leaks, and can't track them to something staying open, it may be the way cscript handles the allocation/deallocation of memory. In that case, I think still that declaring a static, global, recordset and connection object would help.

Good Luck.

right on ndrast..I'll give it a shot. Thank you so much for all the help.

Cheers

Rusty_K said:

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

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

cn->ConnectionString = "PROVIDER=SQLOLEDB; SERVER=SQLServer;DATABASE=Runtime;UID=sa;PWD=";

cn->Open;
****rs = cn->Execute("SELECT TagName, Value FROM v_DiscreteLive WHERE TagName in ('Com_Tick2s_PLC003')");
printf("Com_Tick2s_PLC003");
while(!rs->eof)
{
printf(rs->Fields(1));
printf("\r\n");
rs->Movenext;
}
rs->Close;
cn->Close;
__object_delete(rs);
__object_delete(cn);
}

Click to expand...

I believe that you're handling the recordset incorrectly.
You shouldn't create the initial recordset {with __object_create(ADODB.Recordset)} when you then get the Connection to create another one for you with cn->Execute(...).

If you want to create the recordset first, then I think you should be using the rs->open(...) function, and specifying the connection there.

VBS can be easier to use when creating the query string in my opinion. Anyone have any information on execution times, ANSI-C vs VBS??