Gold R5 missing stored procedure

[AbleMods]

Just wanting to confirm (will report via Feedback), the install AbleCommerce.sql script will create a stored procedure acsp_MergeUserGroupMembers. This is used when a Group is deleted and users assigned to the group need to be reassigned to a new group.

But the Upgrade script from 707 to Gold does not create the stored procedure.

So an upgraded store trying to delete a group will get an error because the stored procedure is missing.

Can I just run the SQL CREATE PROCEDURE from the install script to re-create it in the database?

[AbleMods]

UPDATE: The stored procedure is actually in the database.

What I cannot find is where it's been defined in any of the hbm XML files. Google searches suggest this might be required to call stored procedures from nHibernate.

Here's the actual page error we get trying to delete a group:

Server Error in '/' Application.

Named query not known: acsp_MergeUserGroupMembers

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: NHibernate.MappingException: Named query not known: acsp_MergeUserGroupMembers

Source Error:


Line 217: public static IQuery GetNamedQuery(string queryName)
Line 218: {
Line 219: return AbleContext.Current.Database.GetSession().GetNamedQuery(queryName);
Line 220: }
Line 221:

Source File: d:\AbleMods-Client Projects\blah-Gold\Projects\Gold R5 Source\CommerceBuilder\DomainModel\NHibernateHelper.cs Line: 219

Stack Trace:


[MappingException: Named query not known: acsp_MergeUserGroupMembers]
NHibernate.Impl.AbstractSessionImpl.GetNamedQuery(String queryName) +451
CommerceBuilder.DomainModel.NHibernateHelper.GetNamedQuery(String queryName) in d:\AbleMods-Client Projects\blah-Gold\Projects\Gold R5 Source\CommerceBuilder\DomainModel\NHibernateHelper.cs:219
CommerceBuilder.Users.GroupRepository.Delete(Int32 groupId, Int32 newUserGroupId) in d:\AbleMods-Client Projects\blah-Gold\Projects\Gold R5 Source\CommerceBuilder\Users\GroupRepository.cs:91
CommerceBuilder.Users.GroupDataSource.Delete(Int32 groupId, Int32 newUserGroupId) in d:\AbleMods-Client Projects\blah-Gold\Projects\Gold R5 Source\CommerceBuilder\Users\GroupDataSource.cs:45
AbleCommerce.Admin.People.Groups.DeleteGroup.DeleteButton_Click(Object sender, EventArgs e) in d:\AbleMods-Client Projects\blah-Gold\Site\Admin\People\Groups\DeleteGroup.aspx.cs:41
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3804

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18055

[AbleMods]

Any AbleCommerce developer response?

[ForumsAdmin]

We are calling it as a standard SQL query. There shouldn't be any additional NHibernate mapping needed.
Here is what we are doing in CommerceBuilder.Users.GroupRepository

Code: Select all

IQuery query = NHibernateHelper.CreateSQLQuery("exec acsp_MergeUserGroupMembers @sourceGroupId=:sourceGroupId, @targetGroupId=:targetGroupId");
query.SetParameter("sourceGroupId", groupToDelete.Id);
query.SetParameter("targetGroupId", newUserGroupId);
query.ExecuteUpdate();

As long as 'acsp_MergeUserGroupMembers ' exists in database there should be no error.

[ForumsAdmin]

Check your GroupRepository.Delete method, is it calling the stored procedure as above using NHibernateHelper.CreateSQLQuery or is it calling the stored procedure with NHibernateHelper.GetNamedQuery ?

[AbleMods]

it's doing GetNamedQuery()

Code: Select all

                // TARGET GROUP IS VALID, MOVE THE USERS WHILE MAINTAINING ANY SUBSCRIPTION ASSOCIATIONS
                IQuery namedQuery = NHibernateHelper.GetNamedQuery("acsp_MergeUserGroupMembers");
                namedQuery.SetParameter("sourceGroupId", groupToDelete.Id);
                namedQuery.SetParameter("targetGroupId", newUserGroupId);
                namedQuery.ExecuteUpdate();

[ForumsAdmin]

You will need to get the latest source code. The issue was apparently fixed in R6.

[AbleMods]

I have it fixed now using the R6 code.

The resulting change is simple but requires full source code if you're running R5 or earlier. In full source code, find the /Users/GroupRepository.cs file. Locate this line of code:

Code: Select all

IQuery namedQuery = NHibernateHelper.GetNamedQuery("acsp_MergeUserGroupMembers");

and replace it with this line:

Code: Select all

IQuery query = NHibernateHelper.CreateSQLQuery("exec acsp_MergeUserGroupMembers @sourceGroupId=:sourceGroupId, @targetGroupId=:targetGroupId");