How to reconnect when SQLEXEC failed? 4

[gryff15]

My system processes a large number of records which takes hours to complete. Due to this, the connection sometimes gets cut in the middle. How do I code such that there will be a chance to reconnect it and resume the process? It should not also cause a hang in the server.

Connhandle_A = Sqlconnect("Db1","Sa","")
Connhandle_B = Sqlconnect("Db2","Sa","")
 
Query = 'Select ...'
=Sqlexec(Connhandle_A,Query,Cursor1)

Select Cursor1
Do While Not Eof()
	&& Some Codes...

	Query2 = 'Select ...'
	If Sqlexec(Connhandle_B,Query2,Cursor2) < Success
		Aerror(Laerrors)
		Messagebox(Laerrors[2],16,"Error In Connection")
		Sqldisconnect(Connhandle_A)
		Sqldisconnect(Connhandle_B)
		Quit
	Endif

	&& Some Codes ....

Enddo

Sqldisconnect(Connhandle_A)
Sqldisconnect(Connhandle_B)

- webrider -

 

[Steve Meyerson]

Hi Webrider,

My client had a similar problem with an intermittent (wiFi) connection to server.

My solution required a huge workaround, was certainly not elegant but solved the problem.

First, i found I needed to disconnect after completion of each task, however small, (connection) with the server. This eliminated long connection times.

In your case it may mean downloading ALL the data you will need in the first connection. Then operating on that data as needed on the local machine. And only then, reconnect to update the server with the changes.

FWIW, in my case, much rewriting was involved.

Steve

 

[Chris Miller]

Since you only fetch once through connection a, first you could reorder this:

Connhandle_A = Sqlconnect("Db1","Sa","")
 
Query = 'Select ...'
=Sqlexec(Connhandle_A,Query,Cursor1)
SqlDisconnect(Connhandle_A)

Connhandle_B = Sqlconnect("Db2","Sa","")
Select Cursor1
Do While Not Eof()
	&& Some Codes...

	Query2 = 'Select ...'
	If Sqlexec(Connhandle_B,Query2,Cursor2) < Success
		Aerror(Laerrors)
		Messagebox(Laerrors[2],16,"Error In Connection")
		Exit && exit loop, continue after enddo
	Endif

	&& Some Codes ....

Enddo
Sqldisconnect(Connhandle_B)

Now you're searching for an alternative to just give up.

Well, first of all an error code doesn't necessarily mean connection error, a disconnection from the server. It could be in the process of generating the Query2 string you get in a NULL and actually make the whole Queryy2 a NULL instead of a string, or you merge in a value that contains a single quote and makes it an invalid query.

You do AERROR, but then don't look into it or at least log it. Edit: I see you display laErrors[2], okay so what does that tell you? It likely has nothing to do with Connhandle_B becoming invalid, connection lost or anything like that. Notice all SQL errors are ERROR 1526, which error message starts with "Connectivity Error:", but rarely really is a connectivity problem, most likely a SQL query error.

Chriss

 

[gryff15]

Messagebox(Laerrors[2],16,"Error In Connection") says "Connection handle is invalid"
When I try Messagebox(Laerrors), it says "1466".



- webrider -

 

[Mike Lewis]

When I try Messagebox(Laerrors), it says "1466"

That's because laErrors by itself (without a subscript), acceses the first element of the array, which in this case is the error number. To get the text of the error message, you would have to look in laErrors[highlight #FCE94F](2)[/highlight].

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

That is indeed pointing out a broken connection.

It's something to discuss with server admins, an sql handle is nothing that's complicated, as it's just an integer number, it's not like a phone line or so, so you get back to the server with your latest query and at the same time tell it I'm number X (the one given to you).

You could, whenever you get this error just repeat Connhandle_B = Sqlconnect("Db2","Sa","") and then rerun the query, but there usually isn't a connection validity timeout, especially not when you do one query after another in a loop like you do. There's a query timeout perhaps, but that doesn't invalidate the handle.

VFP can't tell you why the handle became invalid, since your code doesn't SQLdisconnect within the loop only the server guys could get to the bottom of why this handle was invalidated. And I'd not patch this with an automatic reconnect when there's a chance to find out what's causing it.

Questions can begin with Wifi vs cable connection, changing routes. But it's very exotic a handle is invalidated because the server detects a query coming from another route than last time, it's not that unusual in a network. It could be a shared connection or number of CALs thing. Unless you say this is a SQL Server installed locally on your dev machine.

As many times on a dev machine your handle will be 1 or low, you might also be victim of code that simply executes a loop with all low numbers to close any connection and thereby closes yours, but that would also be very crude and not so probable. You can judge for yourself, even you only SQLDisconnect with your own handle.

So my next idea would be logging when you got which handle number and when it became invalid, collect a few cases and at the same time ask server admins to log connections too and see if you can bring SL server connection data and your log data together to find an underlying cause by the times it occurs and looking what happened server side during these times.

Chriss

Edit: You should log your Connhandle_B value, the time you got it, the times it worked and the time it stopped working, as all these time points can indicate activities on the server for the same connection as seen by the server. And to make that a step easier, also log SQLGetProp(Connhandle_B,'ODBChdbc'), SQLGetProp(Connhandle_B,'ODBChstmt'), and SQLGetProp(Connhandle_B,'UserId'), the VFP Connhandle_B may not be what's relevant to the server side. Direct communication only occurs through the ODBC layer and that has those handles - ODBChdbc and ODBChstmt, not VFPs Connhandle_B.

 

[tomk3]

Hello,

maybe you can put the query2 part in a try catch
in catch do a Connhandle_B = Sqlconnect("Db2","Sa","") to get a new handle ,
I would do it with a counter with a sleep in between and maybe doevents force , if it fails for example 3 times throw and log an error and exit .

Of course it would be better to finfd out WHY connnection handle gets invalid and repair that.

regards
tom

 

[Chris Miller]

You can of course do both.

I just fear if the reason for the invalid handle is a CAL problem or a technical problem, you would also not be able to reconnect.
It's worth logging anyway. IF it always happens at the same time, it may have to do with something scheduled.

There's one thing I didn't ask yet, what's "Success"? SQLExec returns 0 for an ongoing query (which can be due to asynchronous connection and/or multiple queries in a batch or sqlexec of a prepared statement, the code doesn't point out any such specialty) - and it's 1 for an ended query, values <0 ( I think it's always -1) for errors.

The question what Success is (perhaps 1) isn't very important when the case always is Error 1466, "Connection handle is invalid", that's very clearly pointing out the problem is with the connection being invalidated from the server side (at least nothing points out you yourself invalidate your own handle).

Chriss.

 

[gryff15]

Hi, the success is 0.
Thank you for your suggestions. I also would like to confirm if it's true that if when I put the sqlconnect before the loop then disconnect after,

=sqlconnect...
do
 ...some codes
enddo
=sqldisconnect...

, while the loop is running with open connection, and it takes so long because of many records, the server will hang and will prevent the other users to process because someone is using this connection?
The codes before had connection inside the loop:

do
=sqlconnect...
 ...some codes
=sqldisconnect..
enddo

, it works for small number of records but if it is large, the connection to odbc disrupts after some time. I don't know what's the best practice.

- webrider -

 

[Chris Miller]

In frameworks and in code I programmed myself, a SQL server connection is usually done at application start and a disconnect only happens at application exit. So there is no value in a fresh connection. In contrast to your current experience connections don't break after some time, they last all day in applications users use all day, even when they only use them from time to time.

The connection isn't something physical. Not even a specific routing, networks are not switching actual wire connections. Connections also don't go stale or if there is a mechanism disconnecting idle client it's based on last connectivity, not on duration of the connection, i.e. a concurrent connection limit would make the server disconnect the client that hasn't done anything with his connection for the longest time, not the client that has the oldest connection.

Also, in case of several licensing or technical problems that cause a disconnect, doing SQLconnect at the next loop start will only try to connect once and fail if that first connection try fails due to too many concurrent connections or a network outage or any other reason that's more permanent at the time. Again, I'm strong about the importance of finding out what happens at the server. You can't force a connection in case the network is down, for example. You can't force a connection if the database is taken offline for example.

So putting the connect inside the loop just costs more time in a normal situation the connection would continue to work. As already said it's not really a permanent or physical connection, the whole communication in networks goes stateless, to reidentify you there is the handle you pass over again and again.

It could help in your situation as you experience disconnects from the server side. By the way, busy state of the server causes another error, and also no invalidation of your connection, there's another reason to not try this as a solution.

So following toms advice really means reacting to the disconnect from the server, not precautionarily connecting each time. You do exception handling not precautious preventions. Many cases that break a connection also don't allow a reconnect, which is also why I said I don't give tom's idea much hope, it would only cope with a short network quirk but as he expects you may need two, three or more connection tries to get back. You'd need to allow some time to pass and only give up fully when you can't connect after several tries in several minutes, perhaps. As this is unintended you could also retry for hours, it's up to you what you think would work, there might be a point the data in the cursor you loop should be re-queried and the process restart all over, there might be good reasons you want to continue to the end before you repeat this all over.

But in short, disconnecting and connecting often isn't best practice.

Chriss

 

[Steve Meyerson]

But in short, disconnecting and connecting often isn't best practice.

I agree 100 percent!

However: In a certain instance I solved the problem using that undesirable method. My app connects & disconnects 30 times or more. It cured the problem with no ill effects. The user doesn't see the delay(s).

I hope you nail down the culprit. Sometimes we just need to do what works however inelegant.

Steve

 

[Chris Miller]

Why not really solve the problem as tom suggests?

A TRY..CATCH actually won't help, but you know to react to error 1466:

Connhandle_A = Sqlconnect("Db1","Sa","")
 
Query = 'Select ...'
=Sqlexec(Connhandle_A,Query,Cursor1)
SqlDisconnect(Connhandle_A)

Connhandle_B = Sqlconnect("Db2","Sa","")
Select Cursor1
Do While Not Eof()
	&& Some Codes...

	Query2 = 'Select ...'
	If Sqlexec(Connhandle_B,Query2,Cursor2) < Success
		Aerror(Laerrors)
                If laErrors[1]=1466
                   Connhandle_B = Success-1
                   starttime= datetime()
                   Do While Connhandle_B < Success and datetime()-starttime<900 && try for 15 minutes
                      wait window '' timeout 5
                      Connhandle_B = Sqlconnect("Db2","Sa","")
                   Enddo 
                   If Connhandle_B < Success
                      Exit
                   Endif
                   && perhaps SKIP -1 in Cursor1, if you already had SKIP 1
                   && more difficult, perhaps need to undo the effects of && Some Codes... 
                   && as you redo them for the same Cursor1 record
                   Loop
		Else
                   Messagebox(Laerrors[2],16,"Error In Connection")
		   Exit && exit loop, continue after enddo
                Endif
	Endif

	&& Some Codes ....

Enddo
Sqldisconnect(Connhandle_B)

Chriss

 

[Steve Meyerson]

Hi Chris,

Your code solves the problem for failed connection attempts. My problem was the unwanted disconnection was occurring when the program was idle (e.g. the user could be away from he computer).

Explanation: In this unusual situation, the real problem was the wifi reliability as he moved his laptop around with him as he traveled to ski lodges or to malls. That's why I took the "quick-burst" method of fetching the least amount of data for each operation.

Steve

 

[Chris Miller]

our code solves the problem for failed connection attempts

Not only, and look closer, the essential part reacts to an error of SQLExec, not an error of SQLConnect.

You could also use that for the case someone moves with his device. Though I don't think that every change of access point, for example. As said, network connections are not dependant on going through the same nodes (route), if SQL Server makes that a requirement and disconnects because that happens, it's really a problem of SQL Server configuration.

Edit: There's only one point: The first SQLConnect is assumed to work, the first (if at all) Error 1466 is not expected to come from the first SQLExec, i.e. the connection is assumed to work at least once, which is what you expect anyway, also if you connect and disconnect every time. So it's nothing to worry about too much. If it actually fails, it fails at the start, and then you'd restart the whole PRG anyway.

Chriss

 

[Steve Meyerson]

... then you'd restart the whole PRG anyway.

Yes, that's exactly what was happening. It's just happening less since my rewrite.

Steve

 

[Chris Miller]

I think you're still misinterpreting me, I don't think it would help to go on, though.

IF connect/disconnect in every loop works for you, I can just hope for you to never encounter a more general problem like network outage or a down server that'll let you down anyway.

Chriss

 

[Steve Meyerson]

Very true. If the server' down, we're out of business either way.

Fortunately that hasn't happened (yet). We've used Hostgator (Houston Texas) as a host for years and haven't experienced a down time (yet). They're not cheap but they offer 24/7 telephone support.

Steve