Retrieving the SQL from a group in the Infor CRM web client

In the web client you are able to retrieve the raw SQL statement of a group. At some point along the way in the life cycle of the web, the storage format of the SQL statement changed. There is a method GroupSQL that used to return the same SQL as what you would see if you viewed the SQL in the group builder. So previously the GroupSQL would return something like this:

select accountid, account from account where account = '3M' and status='active'

In version 8.3 of the Infor CRM web client the same call to GroupSQL returns something like this:

select accountid, account from account where account = ? and status = ?

The conditional values have been replaced by question marks and now reside in a collection of parameters. It is possible to get back the raw SQl by doing something like this:

 	EntityGroupInfo gInfo = GroupContext.GetGroupContext().GetGroupInfoForTable("Account");            
            for (int i = 0; i < gInfo.GroupsList.Count; i++)
            {
                if (gInfo.GroupsList[i].GroupID == "SOME ID")
                {
                    qry = gInfo.GroupsList[i].GroupSQL;                                        

                    var parameters = gInfo.GroupsList[i].Parameters;
                    foreach (System.Data.Common.DbParameter par in parameters)
                    {
                        int posit = qry.IndexOf("?");
                        if(posit>-1)
                        {
                            qry = ReplaceFirst(qry, "?", string.Format("'{0}'",  par.Value.ToString()) );
                        }
                        
                    }
                    
                    break;
                }
            }

That code uses a supporting function:

    public string ReplaceFirst(string text, string search, string replace)
    {
        int pos = text.IndexOf(search);
        if (pos < 0)
        {
            return text;
        }
        return text.Substring(0, pos) + replace + text.Substring(pos + search.Length);
    }
ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!