Hi,

Apparently w/o any change in my program, I started to get the error "Unable to start the connection. Exceeded max # of SQL connections." while updating an SQL DB in a remote site (online store).

I checked with the guys at the datacenter and they confirmed they hadn't implemented any limit, also, they controled the active connections while running my program and there was only one.

The error pop up after a few UPDATE and INSERT querys.

Any tip about what I should do to trace this down and find the culprit?

Thanks in advance


PS: When I said above "Apparently w/o any change in my program...", it's because, before this error came up, I've added a new function in the program to SELECT data from the SQL database but, it shouldn't interfeere with the UPDATE process because it's completely independent but, innocently, I can have changed some common code so, I admit to be the culprit here but, just don't know what to do to find the problem.

My first guess would be that for whatever reason, the SQLOP_CONNECT calls are not being matched up with SQLOP_DISCONNECT. And that adding the additional code just simply pushed you over the limit.

Probably the easiest way to study it would be to activate the tracing, which you can do either by setting an environment variable ASQLTRACE=1, or by passing the flag SQLCONF_TRACE (&h0001) flag on the SQLOP_INIT_LIB call.

That should result in pretty detailed traces in the ashlog.log which might help reveal if there is a pattern of making connections without disconnecting.

Hi,
It's impressive how a program can run for months with so many mistakes

oh! by the way, the issue is fixed

When you mentioned SQLOP_DISCONNECT I wondered :rolleyes: I don't remember to have seen such command in the program!

So, I decided to review thoroughly all the SQL sequence and read the SQL documentation because, I confess to have written this program on the fly by copy/paste from another similar program w/o try to really understand the SQL logic.
In the process, I found that haven't implemented any error control, in the faith that everyhting would run fine, didn't use SQLOP_DISCONNECT neither SQLOP_INIT_CONN which I still was not able to use, is it really necessary?

But the culprit was too much SQLOP_CONNECT, I found an extra one in a routine called on each loop, probably I pasted it by mistake into that function.

The problem is solved, Jack, many thanks for the tip wich put me on the right track.

I'm still reviewing everyhting and, this time, in the aim to really understand the process what, probably, will bring me a few more times with doubts.

By now, just my thank you

This probably violates the one issue per thread rule, but it is really important to remember to use SQLOP_FREE_RESULT to free result sets after you've used them. This is used in conjunction with SELECT statements, and it frees the memory used by MySQL to store the results before you retrieve them in your program.

Thanks Stephen,

That one was in my list to confirm here because, I wasn't using it prior to my new module (the one that triggered all the mess) so, let me open the question:

The SQLOP_FREE_RESULT should be used after any query, like UPDATE or INSERT or, is it specifically for the SELECT?

Should I use it even before the SQLOP_DISCONNECT?

It's used only with SELECT queries. SQLOP_FREE_RESULT is a call to tell MySQL, or other RDBMS to free the corresponding resultset from memory itself, so yes it has to be done before you disconnect.

While it may be good form, it isn't absolutely necessary to do an explicit SQLOP_FREE_RESULT prior to SQLOP_DISCONNECT. The connector maintains handles for all the stored resources, such as result sets and prepared statements, and thus is able to clean up any leftovers associated with the connection when you disconnect.

Similarly, SQLOP_CLOSE_LIB will free everything.

As for what happens to the SQL connections when a program ends, it's a bit murky. Prior to 6.1.1404.0, SQL connections were not automatically closed at the end of a RUN or SBX. But since then, they are closed for RUN programs, but left alone for SBXs. So an SBX can establish an SQL connection that remains in place after return to the program, to be accessed by subsequent calls to that or some other SBX. But exiting a RUN program will disconnect all your SQL connections, just as it auto-closes all your files.

That seems clear and logical but the murkiness comes from an error in the documentation that suggests there is an AF_SQLPERSIST flag to ASFLAG.SBR that overrides the auto-close behavior. (I suspect this was an idea that later got rejected - Stephen do you recall discussing this?) Also, since the auto-close on RUN termination wasn't supported prior to 1404, it would probably be a good idea to implement an explicit cleanup in your error TRAP routines.

(Note: if in doubt as to what actually happens in the file-auto-close routine, activate the FOPENS trace; the ashlog.log will explicitly record whenever files and SQL connections are automatically closed.)

Yes, the AF_SQLPERSIST was implemented so that anyone who relied upon the behavior prior to 6.1.1404.0 (where connections were not closed by RUN programs) could just set that AFLAG and have the prior behavior restored.

I consulted the source revision control system (perfect example of how valuable such a system is - anyone not using one should really reconsider). And sure enough, it was implemented (just as documented), in 6.1.1404.0(.1)

But then it was explicitly removed (and again documented), in 6.1.1405.0(.3)

However, there isn't much explanation for the removal, either in the code or the doc. I'm guessing that it was a combination of uncertainty over how/when the flag took effect and uncertainty that anyone would really need or expect SQL connections to remain viable after the RUN program exited. (As implemented, the flag was tested just prior to doing the auto-close, rather than when the connection was established, which is certainly the most straightforward way to do it, but conflicts somewhat with the way some of the other ASFLAG options work.)

No one has complained or commented about the change or the rollback which leads me to suspect it's not much of an issue. But if someone thinks it should be re-implemented, i.e. if someone has a need to be able to establish an SQL connection in a RUN file and have it persist after that RUN exits, I'm happy to revisit it. (Note that in order for the connection to be accessible after the termination of the RUN module that established it, the handle would have to have been saved in a file or otherwise made available to a subsequent RUN program to use.)