SCM

[#1011151] Command Timout causes subsequent reads to retrun no data.

View Trackers | Bugs | Download .csv | Monitor

Date:
2012-01-25 13:53
Priority:
3
State:
Open
Submitted by:
Ioannis Anagnostopoulos (janagn)
Assigned to:
Nobody (None)
Npgsql Version:
2.0.11
Category:
Group:
Resolution:
None
Summary:
Command Timout causes subsequent reads to retrun no data.

Detailed description
Hello all,

While trying to provide a mechanism to recover from a Command timeout without increasing the timeout value or setting it to 0 the following issue occurs:

If the command type is stored procedure and the ExecuteReader times out, any subsequent calls to this command will return no data even if there are. Same occurs even if you create a new command. The only way that possibly corrects the problem is to close and open again the connection. The example code that follows illustrates the problem:

Dim t As NpgsqlTransaction = Nothing
Dim dr As NpgsqlDataReader = Nothing
Dim timeout As Boolean = False
Do
If mGeoRefStatsCommand Is Nothing OrElse timeout Then
mGeoRefStatsCommand = AnalysisDateSet.PreparedQueriesCommand()
End If
mGeoRefStatsCommand.Parameters(0).Value = feedsIDs
mGeoRefStatsCommand.Parameters(1).Value = aDateSet.Dates
mGeoRefStatsCommand.Parameters(2).Value = aGeoref
If DataQuality = DataQualityEnumeration.FULL_DATA Then
mGeoRefStatsCommand.Parameters(3).Value = True
Else
mGeoRefStatsCommand.Parameters(3).Value = False
End If

Try
t = mGeoRefStatsCommand.Connection.BeginTransaction()
dr = mGeoRefStatsCommand.ExecuteReader()
If dr.HasRows Then
Do
While dr.Read
........
End While
Loop While dr.NextResult
End If
timeout = False
Catch ex As NpgsqlException
Console.WriteLine("Time out")
timeout = True
Catch ex1 As Exception
Console.WriteLine("Generic Excpetion at ")
Finally
If Not dr Is Nothing Then
dr.Close()
End If
If Not t Is Nothing Then
t.Commit()
End If
End Try
Loop While timeout

This sub is called over and over again, passing different parameters every time to an existing Command (mGeoRefStatsCommand). If however a time out occurs or the command has not been initialised, the command is created by calling the following Sub.

The PreparedQueriesCommand is as follows:

Public Shared Function PreparedQueriesCommand() As NpgsqlCommand

PreparedQueriesCommand = New NpgsqlCommand("ais_analyzer.georef_stats", GetDBConnection("FeedsPopulation"))
PreparedQueriesCommand.CommandType = CommandType.StoredProcedure
PreparedQueriesCommand.CommandTimeout = 0
PreparedQueriesCommand.Parameters.Add(New Npgsql.NpgsqlParameter("p_feed_ids", NpgsqlDbType.Array Or NpgsqlDbType.Integer))
PreparedQueriesCommand.Parameters.Add(New Npgsql.NpgsqlParameter("p_dates", NpgsqlDbType.Array Or NpgsqlDbType.Timestamp))
PreparedQueriesCommand.Parameters.Add(New Npgsql.NpgsqlParameter("p_georef", NpgsqlDbType.Varchar))
PreparedQueriesCommand.Parameters.Add(New Npgsql.NpgsqlParameter("p_full_data", NpgsqlDbType.Boolean))
PreparedQueriesCommand.Parameters.Add(New Npgsql.NpgsqlParameter("p_debug", NpgsqlDbType.Boolean))

End Function

Apparently this code will return no more rows of data if a time out occurs.

Followup

No Followups Have Been Posted

Attached Files:

Changes:

No Changes Have Been Made to This Item

Powered By FusionForge