U++ forum: Welcome to the forum

MSSQL Exception Handling [message #53447]

Fri, 03 April 2020 12:11

Pradip
Messages: 109
Registered: February 2019
Location: India

Experienced Member

Hi there,

Hope everyone is safe during this COVID threat! Stay indoors, stay safe, please!

Since we must work-from-home, we are faced with situations where the VPN connection is not stable, so the mssql connection can break anytime. Our app needs to be able to reconnect, in case it gets disconnected while running a query.

With this idea, I'm trying the following:

connection code (works fine):

MSSQLSession mssql;

bool ConnectMSSql() {
	String cs = "Driver={SQL Server Native Client 11.0};";
	cs << "Server=***;";
	cs << "UID=***;";
	cs << "PWD=***;";
	cs << "Database=***;";

	while(!mssql.Connect(cs))
		if(!ErrorRetryCancel(
			Format("\1Connection to database server failed:\n%s", mssql.GetLastError())
		))
			return false;
		
	SQL = mssql;
	return true;
}

code for re-connection:
this also works, but SQL.GetErrorCode() == 0 must be very wrong. All I want to do here is "only if connection is broken the run connect", but SQL.GetErrorCode() seems to always return 0, no matter if the error is due to disconnection or sql logic error! I've tried SQL.GetErrorClass() == Sql::CONNECTION_BROKEN, but it didn't seem to work.

bool ReconnectMSSql() {
	return SQL.GetErrorCode() == 0 && ConnectMSSql(); // is error code right?
}

finally, code for queries:
this is the biggest question: do i have to use the same syntax for every query in the app? also for Select queries? till now i've used try...catch only for Update and Insert queries.

		bool retry;
		do {
			retry = false;
			try { // set start date
				q & ::Update(ACTIVITIES)(startDtId, newStartDt).Where(ACT_ID == Id);
			}
			catch(SqlExc &e) {
				if(ReconnectMSSql()) retry = true;
				else {
					Exclamation("[* " + DeQtfLf(e) + "]");
					return false;
				}
			}
		} while(retry);

Is there any easier way to do this, so that anytime a query is executed it will check the connection status and reconnect if needed?

Thanks in advance for helping!

Regards,
Pradip

[Updated on: Fri, 10 April 2020 18:07]

Report message to a moderator