The SQL Connector opens a new connection for each transaction and closes it when it is done so there should not be any open connections left, not even when the transaction fails. However it is possible that the ODBC driver itself reuses connections to the database and that these connections are not reset when the database closes down. There should be a timeout for open connections. What happens if you disable the SQL Connector and try to enable it again after 20 minutes (the ODBC connection pooling timeout is often 15 minutes)?
It is not difficult to implement a database connection in the Scripting Connector. This might solve the problem but it is also possible that there will still be an open connection. However maybe you are right and the Transaction/Rollback feature in the SQL Connector causes the problem.
Using a proprietry software package that implements a standard ODBC driver – ClearSCADA (www.clearscada.com) I've created the necessary tables and it works if the Diafaan service is started/PHP_script_to_send_SMS_messagesstarted after the Database is running. however the database server may not always be up for a number of reasons and yeah as mentioned it just doesn't seem like it attempts to reconnect. but the strange thing is I can go into the properties of the SQL connector and test the database connection (assuming this creates a new temporary connection), but testing the tables fails with log message (assuming this thinks a connection still exists – and maybe it does, but the database server rejects the request and a new connection is required):
SELECT Id,Phone,Message FROM SMSOutgoing WHERE Id='0'
ERROR [08S01] [Control Microsystems][ClearSCADA database driver]An established connection was aborted by the software in your host machine.
Looking at the SQL Connector log i'm also seeing errors at times:
25/3/2011 7:20:06 PM ERROR [HYC00] [Control Microsystems][ClearSCADA database driver]Rollback not supported
Not sure if that's the reason why it's not coming back online, but using QTODBC (query tool) I do find that after the server has been restarted, you must manually re-connect.
Although not the prefered method, I was also looking at manually implementing something similar using a scripting connector that would first validate the connection and re-connect on failure. If so, what do you think would be the best way to go about it? And is there someway I can execute a reconnect or disconnect/connect method on the existing SQL Connector object from within the scripting connector code?
---> Quick update: seems every time I stop the database server, the last log recorded IS the 'rollback not supported' error message. Is this a fatal error? ie. SQL connector stops permanently at this point?
Which database server/ODBC driver do you use? I have tried a couple of database servers but I cannot replicate the problem with any of them. If I had to make a guess I would first have a look at the ODBC driver, maybe a newer version is available or the connection string has options that influence the database connection.
Just purchased the full license, but noticing an issue when using the SQL connector.
First of all I'm using an ODBC ansiSQL92 compatible DB. As long as the database is running when the Diafaan service starts there is no issue. However when the database is shut down and restarted or the Diafaan service starts before the database is up and running, it seems the connector shows 'SQL Database Error' on the status page and does not attempt to re-establish a new connection (even if I go in to the properties - I can 'test' the db connection successfully, but the send/PHP_script_to_send_SMS_messagesceive/log tables fail). the only way to bring it back online again is to completely stop and restart the diafaan service.
Most Users Ever Online: 494
Currently Browsing this Page:
Guest Posters: 428
Newest Members:, Henk Helmantel
Administrators: Henk Helmantel: 713