Strange, guess that it's more a problem of the DbType of the return, I noted this to have a look
Cheers -Gilles On 9/13/07, Lee Shinlever <[EMAIL PROTECTED]> wrote: > > It is the oddest thing. We actually modified the SPROC by removing the > RETURN statement and just allowing the results of the SELECT to come back to > iBatis. It wasn't until we remove the SELECT @DateActive = > DATEADD(minute, -(@MinutesSinceLastInActive), @CurrentTimeUtc) line, did > that calculation in our application code and passed it in as a parameter > that it finally worked. > > For some reason iBatis just did not like that calculation being run inside > the SPROC. > > Lee > > On Thu, 2007-09-13 at 13:55 -0400, Clough, Samuel (USPC.PRG.Atlanta) > wrote: > > Maybe I don't understand this correctly, but are you trying to use SQL > Server's ability to generate a result set from a stored proc call? If you, > I would say you should probably use a select element rather than a statement > element so that iBatis expects a result set. Otherwise, iBatis is probably > expecting to return you the procedures return code or maybe the rows > modified return from the database, I'm not sure. > > > > *From:* Lee Shinlever [mailto:[EMAIL PROTECTED] > *Sent:* Wednesday, September 12, 2007 4:27 PM > *To:* [email protected] > *Subject:* Return Results > > > > > Greetings, > > Currently we are replacing all of the data access code in the ASP.NetSecurity > Providers using > Spring.Net and iBatis.Net. Basically instead of implementing a custom > provider each time security persistence needs changed we just use one > provider and change its data access component (iBatisNet) as needed. > > We did run into one operation that just won't return the expected results > no matter what we do short of modifying the actual stored procedure. We > don't have a problem changing the SPROC to make it work but we were > wondering if anyone could point out what might be happening here. > > *Components:* > * *1) SQL Server 2005 > 2) Visual Studio 2005 > 3) iBatisNet DataMapper 1.6.1.0 > > *Original Stored Procedure:* > > ALTER PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline > @ApplicationName nvarchar(256), > @MinutesSinceLastInActive int, > @CurrentTimeUtc datetime > AS > BEGIN > DECLARE @DateActive datetime > SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInActive), > @CurrentTimeUtc) > > DECLARE @NumOnLine int > > SELECT @NumOnLine = Count(*) > FROM aspnet_Users AS u WITH (NOLOCK) INNER JOIN > aspnet_Applications AS a WITH (NOLOCK) ON > u.ApplicationId = a.ApplicationId INNER JOIN > aspnet_Membership AS m WITH (NOLOCK) ON > u.UserId = m.UserId > WHERE (u.LastActivityDate > [EMAIL PROTECTED]) AND ( > a.LoweredApplicationName = LOWER(@ApplicationName)) > > *return @NumOnLine* > > END > > *Statement:* > <procedure id="GetNumberOfUsersOnline" > resultClass="int" > parameterMap="GetNumberOfUsersOnline"> > aspnet_Membership_GetNumberOfUsersOnline > </procedure> > > *Parameter Map:* > <parameterMap id="GetNumberOfUsersOnline" class="Hashtable"> > <parameter property="ApplicationName" column="ApplicationName" > dbType="string" size="256"/> > <parameter property="MinutesSinceLastInActive" > column="MinutesSinceLastInActive" dbType="int"/> > <parameter property="CurrentTimeUtc" column="CurrentTimeUtc" > dbType="DateTime"/> > </parameterMap> > > *DAO Code: * > > ...snippet... > Hashtable parms = new Hashtable(); > parms.Add("ApplicationName", applicationname); > parms.Add("MinutesSinceLastInactive", minutessincelastinactive); > parms.Add("CurrentTimeUtc", currenttimeutc); > > int results = 0; > > results = (int) > CustomMapper.Instance().QueryForObject("GetNumberOfUsersOnline", > parms); > > return results; > ...snippet... > > *Testing:* > Using the same parameters, expected results = 2 ... > 1) Removed the *return @NumOnLine* line > 1) Ran the SPROC in the SQL debugger - SQL output results were fine > 2) Ran the original DAO code using SQL command objects - SQL command > results were fine > 3) Ran our DAO code using iBatisNet - no results ever return > 4) Change mapping file and DAO code to reflect using a Hashtable as > the return class - iBatisNet did not return any results > 5) Changed [EMAIL PROTECTED] to anything else (date value) - iBatisNet > results were fine > > > Thanks, > Lee > > > > > ------------------------------ > > *Princeton Retirement Group, Inc - Important Terms * > > This E-mail is not intended for distribution to, or use by, any person or > entity in any location where such distribution or use would be contrary to > law or regulation, or which would subject Princeton Retirement Group, Inc. > or any affiliate to any registration requirement within such location. > > This E-mail may contain privileged or confidential information or may > otherwise be protected by work product immunity or other legal rules. No > confidentiality or privilege is waived or lost by any mistransmission. > Access, copying or re-use of information by non-intended or non-authorized > recipients is prohibited. If you are not an intended recipient of this > E-mail, please notify the sender, delete it and do not read, act upon, > print, disclose, copy, retain or redistribute any portion of this E-mail. > > The transmission and content of this E-mail cannot be guaranteed to be > secure or error-free. Therefore, we cannot represent that the information in > this E-mail is complete, accurate, uncorrupted, timely or free of viruses, > and Princeton Retirement Group, Inc. cannot accept any liability for E-mails > that have been altered in the course of delivery. Princeton Retirement > Group, Inc. reserves the right to monitor, review and retain all electronic > communications, including E-mail, traveling through its networks and systems > (subject to and in accordance with local laws). If any of your details are > incorrect or if you no longer wish to receive mailings such as this by > E-mail please contact the sender by reply E-mail. > > > ------------------------------ > > -- Cheers, Gilles <a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE";>Wish List</a>
