Computer Programming

In MySQL there appears to not be support for passing a variable to a stored procedure and using that variable in an IN CLAUSE. Here’e the body of an extremely simple stored procedure that I will use to illustrate
the problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySampleStoredProc`(idlist varchar(1000))

select * from tblSampleTable where theid in (idlist);

END$$
DELIMITER ;

If you run this with ’123,456′ as the lone parameter then the result will annoyingly be the result as if you had just sent in ’123′. That is, it only pays attention to the FIRST ITEM in the csl (comma seperated list) when
it is passed as a parameter.

So how can one work around this? Well, what I did was create a temporary table in the stored proc and parsed the parameter string, inserting each item in the string into the temporary table as I went. Then just just used an INNER JOIN (or an IN CLAUSE) against the temporary table. My code to create the temporary table is listed below:

declare seperatorcount int;
 declare idstring varchar(10);
 declare testifdonestring varchar(1000);

 CREATE TEMPORARY TABLE temptbl_IdList (pid int);

 set seperatorcount=1;
 myloop: LOOP
 set aplayeridstring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -1))
    );
Â
 insert into temptbl_IdList (pid) values (idstring);
 Â
 set testifdonestring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -seperatorcount))
    );
 if idlist = testifdonestring
  then LEAVE myloop;
 end if;
 SET seperatorcount = seperatorcount + 1;
Â
END LOOP myloop;

Now how whole proc would look something like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySampleStoredProc`(idlist varchar(1000))

 declare seperatorcount int;
 declare idstring varchar(10);
 declare testifdonestring varchar(1000);

 CREATE TEMPORARY TABLE temptbl_IdList (pid int);

 set seperatorcount=1;
 myloop: LOOP
 set aplayeridstring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -1))
    );
Â
 insert into temptbl_IdList (pid) values (idstring);
 Â
 set testifdonestring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -seperatorcount))
    );
 if idlist = testifdonestring
  then LEAVE myloop;
 end if;
 SET seperatorcount = seperatorcount + 1;
Â
END LOOP myloop;

select * from tblSampleTable where theid in (select pid from temptbl_IdList);

drop table temptbl_IdList;

END$$
DELIMITER ;

A fantasy sports (fantasy basketball / fantasy football) website I do some work for had some Ajax related issues some time ago. I recently came across the problem somewhere else and figured it might make sense to write it up.

There were error messages that were not consistent. In testing a page there might not be any problems, but then when it goes to a production server the problem shows up. Or maybe it does not have a problem when being accessed from one location, but then another user does have a problem. If you have these sorts of symptoms I’m here to tell you it might not be Ajax’s problem. Instead, we may need to blame the firewall. Typically the ajax error will say something like :

“Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled. Details: Error parsing near ‘ … [some more here] …

Some firewalls do not recognize AJAX headers. This causes the firewall to alter the message (by removing the header) that our servers send to the browser viewing the page. When that happens, the user’s browser cannot make sense of the response and gives the error mentioned above. We actually had users who were able to convince their network admin to disable an option on their firewall (temporarily) that is typically called something like “REMOVE UNKNOWN HEADERS”. When the option was disabled, the site functioned normally for them.

Unfortunately, disabling the users firewall is not a viable solution. One recommendation might be to have the admin “tell” the firewall about the header so it will recognize it and quit removing it. Depending on the type of firewall (a product called Watchguard was the offender in our test case) there may be a way to make certain headers (the ajax header) known rather than disabling them all. There is nothing to “fix” for us as it is more a flaw with the firewall than anything else; if you are encountering this problem you will need to work with your network admin on the problem.

All of that being said, we found that it can also often be fixed by code on the website end! That is a much better option, yes? So here’s all you have to do.

<asp:contentplaceholder id=”ContentPlaceHolderCodeForAjaxStrippingFirewalls” runat=”server”>
<script language=’javascript’ type=”text/javascript”>
function beginRequest(sender, args)
{
   var r=args.get_request();
   if (r.get_headers()["X-MicrosoftAjax"])
   {
       b=r.get_body();
       var a=“__MicrosoftAjax=” + encodeURIComponent(r.get_headers()["X-MicrosoftAjax"]);
       if (b!=null  && b.length>0)
       {
             b+= “&” ;
       }
       else b= “” ;
       r.set_body(b+a);
     }
}
</script>
</asp:contentplaceholder>

Note: I put this inside a contentplaceholder (I am using masterpages) so I didn’t have to include it manually on every page. If you are not utilizing masterpages then you could just put the script on each page that uses ajax. The problem goes bye bye and the network admins get to keep their silly little rules in place.

Note2: I found the hint about what might be causing the problem here. I’m not entirely sure where I found the fix… it was a long day thank you… but it works.

Recently I needed fo find a way for a user to be presented with a list of possible values, but to also be able to ignore that list and enter their own value into a datagrid cell. I figured I could just use a combo box column (dropdownlist) and it would behave the same way as a combobox does when it is on a form (not within a datagrid). In that situation the user can just enter a value into the text portion of the combo box assuming they did not like any of the options in the box itself. Well, that’s not the way a comboboxcolumn works… you choose from the list it makes available… that’s all you get for free.

Unfortunately, that was not an option. I had to allow the user to enter their own value if they didn’t see the one they wanted in the list. So, what I did was I set the column up as a regular Text based column. Then I implemented a pop-up menu that showed when the user right clicked on a cell in a particular column. Before popping up it determines what the correct menu items for cells in the columns in which it resides  and then makes those options available to the user. It took me a little while to figure out – hopefully this will save someone some time.

My sample code below is in VB. Basically it makes use of the fact that when in a datagrid a right click causes “CellContextMenuStripNeeded” to be fired. So we build up our menu strip there. You could ofcourse also have a static menustrip and set striptoShow directly to it rather than building up stripToShow everytime.

”””””””””””””””’ SET UP SOME PRIVATE VARIABLES WE WILL NEED ””””””””””””””’

 Private theCellImHoveringOver As DataGridViewCellEventArgs
Private stripToShow As ContextMenuStrip

 ”””””””””””””””’ DETERMINE WHICH POPUP TO SHOW (IF ANY) ””””””””””””””’

‘NOTE: dgv is a DataGridView

Private Sub dgv_CellContextMenuStripNeeded(ByVal sender As Object,
ByVal e As DataGridViewCellContextMenuStripNeededEventArgs) _�
Handles dgv.CellContextMenuStripNeeded

 Try

      If (theCellImHoveringOver .ColumnIndex = -1) Then
          Return ‘ nothing special to pop up here
     End If

     If (dgv.Columns(theCellImHoveringOver.ColumnIndex).Name = “Column_Name_Here”) Then
          If (dgv.Rows(theCellImHoveringOver.RowIndex).Cells(“Column_Name_Here”).Selected) Then   �
               ‘ NOTE: I only want it to do this if the cell i right click on is SELECTED
               If stripToShow Is Nothing Then
                      stripToShow = New ContextMenuStrip()�
                      stripToShow .Items.Add(“Item1″, Nothing, New System.EventHandler(AddressOf Me.stripToShow _Item_Click))
                      stripToShow .Items.Add(“Item2″, Nothing, New System.EventHandler(AddressOf Me.stripToShow _Item_Click))
                      ‘NOTE: I added a handler for the click. In my handler I will set the cell value to whatever the user selects from the menu
               End If
               e.ContextMenuStrip = stripToShow�
          End If
     End If

Catch ex As Exception
        MessageBox.Show(“There was an error popping up the menu. Please try again.”)
End Try

 End Sub 

          ”””””””””””””””’ Keep up with which cell I am on ””””””””””””””’

Private Sub dgv_CellMouseEnter(ByVal sender As Object, _
ByVal e As DataGridViewCellEventArgs) _
Handles dgv.CellMouseEnter

     theCellImHoveringOver = e

End Sub

”””””””””””””””””” Handler of Menu Clicks  ””””””””””””””””””’

 

Private Sub stripToShow _Item_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

      Dim str As String = CType(sender, System.Windows.Forms.ToolStripItem).Text

      dgv.Rows(theCellImHoveringOver.RowIndex).Cells(“Column_Name_Here”).Value = str

End Sub

I think that is all there is to it! Let me know if you use it. It’s nice to hear others find this type of stuff useful as well.