bjornwang
02-09-2007, 07:34 AM
Hello,
My team an I are facing some new problems with stored procedures that surfaced when switching form SQL Server 2005, in our test environment, to 2000 in the production environment.
On SQL Server 2000 no rows are returned when calling QueryByNamedParam(...). The stored procedure return rows as expected when called directly from SQL Management Studio:
exec sp_mp_get_col_contents
@page_id=1051, @col_num=1,
@flg_contents=0, @flg_clip=0,
@num_rows=10, @first_row=1,
@flg_preview=0, @preview_date=null,
@so_id=null, @date_format=104,
@flg_list_docs=1, @flg_list_imgs=0;
I'm initializing my StoredProcedure subclass using DeriveParameters(), and this does not throw any errors. I tried changing the parameters passed to QueryByNamedParam() to faulty values, and this throws an exception. To me, this indicates that the connection to the database is established, that the stored procedure is identified, and that the parameters it accepts are picked out correctly by Spring.
The result set from Spring, however, is always empty.
Using Debugging from Visual Studio, I can see that the MapRow(...) method in my RowMapper is never called. It seems no return rows are found by Spring, and then naturally no rows are mapped.
What I have noticed during my testing, is that Spring hides exceptions when parameters are assigned the wrong type, at least when using DeriveParameters() / QueryByNamedParam(...). For example, I can call an integer attribute of my stored procedure with a string value, and this passes without notice. ADO.NET usually throws exceptions for this, and I guess Spring somehow eats these. As the code works for us on SQL Server 2005, however, I guess this is all irrelevant.
I'm testing the situation from NUnit tests now, but I'm at a loss as to how I can configure the StoredProcedure class to do logging programatically, so I can get som more background information on the situation.
The code I'm using looks like this (convenience methods removed for clarity):
public class MogulPublishingNewsDAO : StoredProcedure, INewsDAO
{
private static readonly new ILog log = LogManager.GetLogger(typeof(MogulPublishingNewsDAO ));
private static string procedureName = "sp_mp_get_col_contents";
public NewsDAO(IDbProvider dbProvider, string baseUrl) : base(dbProvider, procedureName)
{
DeriveParameters();
AddRowMapper("articleRowMapper", new NewsFeedRowMapper(baseUrl));
Compile();
}
public virtual IList<NewsArticle> GetNews(int pageId, int colNum, int numRows)
{
IDictionary inParams = new Hashtable();
inParams["@page_id"] = pageId;
inParams["@col_num"] = colNum;
inParams["@flg_contents"] = 0; // Do not transfer document contents
inParams["@flg_clip"] = 0; // Do not clip document content (not transferred)
inParams["@num_rows"] = numRows;
inParams["@first_row"] = 1; // First row to transfer
inParams["@flg_preview"] = 0; // Do not preview documents under construction
inParams["@preview_date"] = null; // Preview date is irrelevant
inParams["@so_id"] = null; // Use standard sort order
inParams["@date_format"] = 104; // Use standard norwegian date format
inParams["@flg_list_docs"] = 1; // Include published documents;
inParams["@flg_list_imgs"] = 0; // Do not published images;
IDictionary outParams = QueryByNamedParam(inParams);
IList newsArticleList = outParams["articleRowMapper"] as IList;
return convertToTypedList(newsArticleList);
}
private static IList<NewsArticle> convertToTypedList(IList tmpList)
{
// TODO: The whole method should be removed when Spring supports generic
// row mappers for StoredProcedures using the AddRowMapper() method.
IList<NewsArticle> returnList = new List<NewsArticle>(tmpList.Count);
foreach (object o in tmpList)
{
returnList.Add(o as NewsArticle);
}
return returnList;
}
private class NewsFeedRowMapper : IRowMapper
{
private string baseUrl;
public NewsFeedRowMapper(string baseUrl)
{
this.baseUrl = baseUrl;
}
public object MapRow(IDataReader dataReader, int rowNum)
{
NewsArticle article = new NewsArticle();
article.Url = _buildUrlFromColumnElementId(dataReader.GetInt32(0 ));
article.Title = dataReader.GetString(2);
article.PublishDate = _convertToDate(dataReader.GetString(4));
return article;
}
}
}
My team an I are facing some new problems with stored procedures that surfaced when switching form SQL Server 2005, in our test environment, to 2000 in the production environment.
On SQL Server 2000 no rows are returned when calling QueryByNamedParam(...). The stored procedure return rows as expected when called directly from SQL Management Studio:
exec sp_mp_get_col_contents
@page_id=1051, @col_num=1,
@flg_contents=0, @flg_clip=0,
@num_rows=10, @first_row=1,
@flg_preview=0, @preview_date=null,
@so_id=null, @date_format=104,
@flg_list_docs=1, @flg_list_imgs=0;
I'm initializing my StoredProcedure subclass using DeriveParameters(), and this does not throw any errors. I tried changing the parameters passed to QueryByNamedParam() to faulty values, and this throws an exception. To me, this indicates that the connection to the database is established, that the stored procedure is identified, and that the parameters it accepts are picked out correctly by Spring.
The result set from Spring, however, is always empty.
Using Debugging from Visual Studio, I can see that the MapRow(...) method in my RowMapper is never called. It seems no return rows are found by Spring, and then naturally no rows are mapped.
What I have noticed during my testing, is that Spring hides exceptions when parameters are assigned the wrong type, at least when using DeriveParameters() / QueryByNamedParam(...). For example, I can call an integer attribute of my stored procedure with a string value, and this passes without notice. ADO.NET usually throws exceptions for this, and I guess Spring somehow eats these. As the code works for us on SQL Server 2005, however, I guess this is all irrelevant.
I'm testing the situation from NUnit tests now, but I'm at a loss as to how I can configure the StoredProcedure class to do logging programatically, so I can get som more background information on the situation.
The code I'm using looks like this (convenience methods removed for clarity):
public class MogulPublishingNewsDAO : StoredProcedure, INewsDAO
{
private static readonly new ILog log = LogManager.GetLogger(typeof(MogulPublishingNewsDAO ));
private static string procedureName = "sp_mp_get_col_contents";
public NewsDAO(IDbProvider dbProvider, string baseUrl) : base(dbProvider, procedureName)
{
DeriveParameters();
AddRowMapper("articleRowMapper", new NewsFeedRowMapper(baseUrl));
Compile();
}
public virtual IList<NewsArticle> GetNews(int pageId, int colNum, int numRows)
{
IDictionary inParams = new Hashtable();
inParams["@page_id"] = pageId;
inParams["@col_num"] = colNum;
inParams["@flg_contents"] = 0; // Do not transfer document contents
inParams["@flg_clip"] = 0; // Do not clip document content (not transferred)
inParams["@num_rows"] = numRows;
inParams["@first_row"] = 1; // First row to transfer
inParams["@flg_preview"] = 0; // Do not preview documents under construction
inParams["@preview_date"] = null; // Preview date is irrelevant
inParams["@so_id"] = null; // Use standard sort order
inParams["@date_format"] = 104; // Use standard norwegian date format
inParams["@flg_list_docs"] = 1; // Include published documents;
inParams["@flg_list_imgs"] = 0; // Do not published images;
IDictionary outParams = QueryByNamedParam(inParams);
IList newsArticleList = outParams["articleRowMapper"] as IList;
return convertToTypedList(newsArticleList);
}
private static IList<NewsArticle> convertToTypedList(IList tmpList)
{
// TODO: The whole method should be removed when Spring supports generic
// row mappers for StoredProcedures using the AddRowMapper() method.
IList<NewsArticle> returnList = new List<NewsArticle>(tmpList.Count);
foreach (object o in tmpList)
{
returnList.Add(o as NewsArticle);
}
return returnList;
}
private class NewsFeedRowMapper : IRowMapper
{
private string baseUrl;
public NewsFeedRowMapper(string baseUrl)
{
this.baseUrl = baseUrl;
}
public object MapRow(IDataReader dataReader, int rowNum)
{
NewsArticle article = new NewsArticle();
article.Url = _buildUrlFromColumnElementId(dataReader.GetInt32(0 ));
article.Title = dataReader.GetString(2);
article.PublishDate = _convertToDate(dataReader.GetString(4));
return article;
}
}
}