Re: SQL Code 0842, SQL State 8002

On 21-Oct-2014 14:15 -0500, Michael Ryan wrote:

Is it an error message (as in not connecting) or a warning message
(as in you're already there)?

Before attempting to answer that, I want to clarify the terminology I will use. There is an important distinction between having a CONNECTION that is merely established [there can be many], and a CONNECTION that is [what might be called] _active_. The _active_ connection implies the remote DB to which [most recently] an implicit or explicit SET CONNECTION has transpired successfully. While there can be many remote connections established, there is only one remote DB [connection] on which work can be performed at the moment, and that connection is where "you are"; again, the _active_ connection.

As the SQLCODE is -842 [the msg SQL0842], that is most definitely an error rather than just a warning. However the error is IMO less about "not connecting" than about the implicit SET CONNECTION not having been established as part of the failed CONNECT TO request. So although the requested connection "already exists", that implies nothing about the currently _active_ connection. In that sense, the message might be perceived more as a warning than an error; a warning that the user\pgmr must recognize that the active connection remains unchanged, that the [implicitly or explicitly] requested connection is already _established_, but that the connection that "already exists" may or may not be the _active_ connection. Thus if the condition of "connection ... already exists" is desirable, and if the currently active connection is irrelevant [improbable, except if the next statement is a SET CONNECTION], then the error condition can be ignored; treated effectively as being purely informational.

For an example:

• Given: The active connection is currently SET to RDB2
• An [implicit or explicit] request made to: CONNECT TO RDB1
• If the prior request fails with the -842, then an implicit SET CONNECTION RDB1 [that would have transpired as part of a successful CONNECT TO RDB1] essentially also has failed; i.e. the connection remains SET to RDB2.

In that example then, the error implying that a connection to RDB1 _already exists_ is *not* meant to imply also, that the _active_ connection is _already RDB1_. Because as shown, the connection remains active to RDB2.

FWiW, in the Start SQL Interactive Session (STRSQL), a request to perform a CONNECT [without any clauses\parameters] will log a msg SQL7971 that will reveal connection details. I expect the SQLDA or GET DIAGNOSTICS could be used to obtain the same information in a program.