StoredProcedure examples

[cebartling]

Subject basically says it all. I'm having trouble getting the Spring.Data.Objects.StoredProcedure to work and the documentation is a bit scant on the subject. I have the following stored proc:

exec GetNextKey(in @objectName, in @createdBy, inout @nextKeyValue)

where @objectName is a string and @createdBy and @nextKeyValue are bigint values. Note that the next key value is put into the @nextKeyValue inout parameter.

Any assistance would be greatly appreciated. Also, if this would be easier done through an IAdoOperations object, please feel free to enlighten me.

Regards,

-- chris --

[WesWilson]

Chris, can you post more info about this? What is the error/stack trace? Depending on the error, it might help to post the stored procedure code, too.

Best wishes,
Wesley

[bjornwang]

I have problems aswell. Spring throws a System.ArgumentOutOfRangeException when calling a Stored Procedure in a MS SQL 200 database:


System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: index

at Spring.Collections.LinkedList.ValidateIndex(Int32 index)
at Spring.Collections.LinkedList.GetNode(Int32 index)
at Spring.Collections.LinkedList.get_Item(Int32 index)
at Spring.Data.AdoTemplate.AdoResultProcessorsQueryCo mmandCallback.DoInCommand(IDbCommand command)
at Spring.Data.AdoTemplate.Execute(IDbCommandCreator commandCreator, ICommandCallback action)
at Spring.Data.AdoTemplate.QueryWithCommandCreator(ID bCommandCreator cc, IList namedResultSetProcessors, IDbParameters declaredParameters)
at Spring.Data.Objects.StoredProcedure.QueryByNamedPa ram(IDictionary inParams)
at MiddleTier.Integration.DAO.NewsDAO.GetNews(Int32 pageId, Int32 colNum, Int32 numRows) in NewsDAO.cs:line 65
at Tests.MiddleTier.Integration.DAO.NewsDAOTest.TestN ewsDAO() in NewsDAOTest.cs:line 102


My DAO code looks like this:


public class NewsDAO : StoredProcedure
{

private static string procedureName = "sp_mp_get_col_contents";

public News2DAO(IDbProvider dbProvider) : base(dbProvider, procedureName)
{
DeriveParameters();
Compile();
}

public virtual IDictionary 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"] = "20070101"; // Preview date is irrelevant
inParams["@so_id"] = -1; // 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;

return QueryByNamedParam(inParams);
}

}


And my NUnit test does nothing more then call the method as of yet:


[Test]
public void TestGetNews()
{
NewsDAO newsDAO = new NewsDAO(dbProvider);
IDictionary newsList = newsDAO.GetNews(1051, 1, 10);
}


The procedure itself has the following signature:


CREATE procedure [dbo].[sp_mp_get_col_contents]
@page_id int, -- Which page is the requested column on?
@col_num int, -- Which column number does it have?

@flg_contents int = 0, -- Transfer document contents? {0|1}
@flg_clip int = 0, -- Clip contents to only 8k data? {0|1}
@num_rows int = 100, -- Maximum number of rows to return
@first_row int = 1, -- First row to return

@flg_preview int = 0, -- Preview unavailiable documents? {0|1}
@preview_date datetime = null, -- Preview any special date?

@so_id int = null, -- Override the column sortorder
@date_format int = 104, -- Display format of 'start_date'

@flg_list_docs int = 1, -- List published documents? {0|1}
@flg_list_imgs int = 0 -- List published images? {0|1}
as


It ends off with a select statement that returns a table:


/* Returns data with full contents field */
select ce_id, type, title,comments, contents, start_date, rel_path, ref_string, num_rows = @tot_num_rows
from #return_table
where order_num between @first_row and @last_row
order by order_num


A typical execution from Transact SQL with return table looks like this:



exec sp_mp_get_col_contents
@page_id = 1051,
@col_num = 1,
@flg_contents = 0,
@flg_clip = 0,
@num_rows = 7,
@first_row = 1,
@flg_preview = 0, -- Preview date not used
@preview_date = null,
@so_id = null, -- Standard sort order used
@date_format = 104,
@flg_list_docs = 1,
@flg_list_imgs = 0


ce_id type title comments start_date rel_path ref_string num_rows
----- -------- ------------------------------ --------------------------- ---------- -------- ---------- --------
18046 document Virksomhet etter forsikrin ... Brev datert 30. januar 2007 31.01.2007 NULL NULL 621
18002 document Utvikling av tilsynsverktø ... Brev datert 26. januar 2007 29.01.2007 NULL NULL 621
17973 document Risikoen for finansiell se ... Foredrag av Bjørn Skogs ... 25.01.2007 NULL NULL 621
17976 document Nye regler om budgivning Rundskriv 7/2007 25.01.2007 NULL NULL 621
17970 document Financial Stability Forum ... NULL 24.01.2007 NULL NULL 621
17961 document CEIOPS' arbeidsprogram for ... Den europeiske forsikri ... 23.01.2007 NULL NULL 621
17965 document Nye regler om garantiordni ... NULL 23.01.2007 NULL NULL 621

[Erich Eichinger]

Hi,

at least for bjornwang it seems, that his StoredProcedure's "resultProcessors" list is empty. Use one of the methods AddResultSetExtractor(), AddRowCallback() or AddRowMapper() to specify the method, how results of the query should be processed.

cheers,
Erich

[bjornwang]

Hi Erich,

Thanks for your reply.

The doc says "You may also register IRowCallback and IResultSetExtractor callback interfaces via the AddRowCallback and AddResultSetExtractor methods", so I figured this was optional.

I'll try this out to see if it works.

One might consider throwing a more descriptive exception from Spring, saying "You have to register a RowCallBackHandler or ResultSetExtractor" or something similar.

[Erich Eichinger]

Hi,

I'm sorry for the inconvenience. I already made a Jira Entry (http://opensource.atlassian.com/projects/spring/browse/SPRNET-468) to remind us improving errorhandling.

Regarding the issue itself: In case of a StoredProcedure producing a resultset, you must specify one out of the three mentioned possible methods to tell AdoTemplate how to handle the resultset. Not specifying anything does not make sense in this case - AdoTemplate can't make any assumptions, how you want to extract your data from an IDataReader.

-Erich

[bjornwang]

Hello again,

Thanks for helping out, Erich.

Everything works fine when I just attach a row mapper.

However, new problems surfaced when we started running the code on our production database, which is an older version of SQL Server.

I started a new thread on this subject:
http://forum.springframework.net/showthread.php?p=5176