Long2IP in VB.Net and Narrowing Conversion

This is post #2 demonstrating how VB.Net makes things harder than C#.  I am receiving IP data as a type Long number.  It turns out that PHP has this helpful built-in function called long2ip that will do the translation back to the familiar “192.0.1.100” string for you. 

I found a few examples of VB code that could do the same, but as best I (and the compiler) could tell, they relied on functions that were not carried into VB.Net.  The problem is very simple though, or so I thought.  All I need to do is narrow the Long into a Byte and then shift the bits a Byte at a time.  In C# this is a piece of cake, but try as I might I could not convince the .Net CLR that I was intentionally overflowing the variable.  Despite trying every combination of Option Strict Off/On with CType, CByte, DirectCast, the runtime dutifully threw an OverflowException.  I was unable to find a way to do a narrowing conversion in VB.Net.

I gave up and created a separate C# class library for my helper class based on this code I found:

public static void ToBigEndian(long number, byte[] outputArray) {
 int length = outputArray.Length;
 outputArray[length - 1] = (byte)number;
 for (int i = length - 2; i >= 0; i--)

    outputArray[i] = (byte)(number >> (8 * (i + 1)));

}

With that, it was trivial to create my own version of Long2IP:

Public Function Long2IP(ByVal IP As Long) As String
  Dim myIp(3) As Byte
  IPHelp.ToBigEndian(IP, myIp)
  Return myIp(0) & "." & myIp(1) & "." & myIp(2) & "." & myIp(3)
End Function

 

 

My first time (geo) caching

There are some days that I say, "we just have to blog about this." Memories that I know we will want to revisit. Today was one of those days. Last night (after a enjoyable visit to the botanical gardens) we realized that we had absolutely nothing scheduled...a rare event for a Saturday. It was a good day for a family hike, but the neighborhood trails were undoubtedly muddy from all of the rain, so we decided to try the local park built on historic coal mines. There was also the promise of a long-awaited geocaching adventure.

I think the thrill of the hunt really livens up a walk in the woods for little people. In the future we will definitely incorporate that into our hikes! We struck out on the first cache (apparently it is often muggled), but after much searching we found our second hide. The hike on the old rail grade, through the tunnel, and around the lake was quite challenging for our little guys, but they did great!

Then to top it off, we enjoyed a filling lunch at a mama-favorite: Cheeburger Cheeburger. The kids had a great time playing peek-a-boo with the staff :)

Use ROWNUM to return multiple non-aggreate GROUP BY columns

Somebody needs to explain something to me.  Why in the world can’t we use some of the aggregate functions without having to GROUP BY every other column?  I know things like SUM and AVG won’t work, but why can’t I do the following:

SELECT name, toy_category, Max(cool_factor) FROM toys GROUP BY toy_category

And have it just return the coolest toys?  Now that is a very simplistic example, but I needed to do something very similar to this.  The best way I found was to use Oracle’s ROWNUM column on a sorted resultset to allow me to join back to said resultset.  This also seems like the perfect time to use a WITH Clause (my other post about using WITH Clause) since we need to hit that data several times and preserve the ROWNUM.  The first thing we need to do is get the list of Toys into a named query ordered by the column we want to Max() – the order is very important:

WITH temp_all_toys AS (SELECT name, toy_category, cool_factor FROM toys ORDER BY cool_factor)

Now that we have all the toys in a discreet list we can look for the Max(ROWNUM) for each toy_category.

SELECT Max(rownum) as max_rownum FROM temp_all_toys GROUPY BY toy_category

The output of that query is pretty dull.  Just a bunch of random looking numbers…but they are really the “primary key” for our temp table so if we join them back to the temp_all_toys table, we can get at any of the columns!  Thanks for nothing GROUP BY.  There is one last trick I found for joining back: for some reason my query wouldn’t work unless I joined to a second subquery to get at the ROWNUM:

WITH temp_all_toys AS (SELECT name, toy_category, cool_factor FROM toys ORDER BY cool_factor)

SELECT
  all_recs.*
FROM
  (SELECT rownum as rnum, name, toy_category FROM temp_all_toys GROUPY BY toy_category) all_recs
JOIN
  (SELECT Max(rownum) as max_rownum FROM temp_all_toys GROUPY BY toy_category) max_recs
ON all_recs.rnum = max_recs.max_rownum

 

Create multiple Oracle temporary tables using WITH Clause

According to the Oracle docs, the WITH clause behaves much like a temporary table and is a great way to clean up lengthy SQL:

The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. WITH is a part of the SQL-99 standard. This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. Using the WITH clause, Oracle retrieves the results of a query block and stores them in the user's temporary tablespace. Note that Oracle Database does not support recursive use of the WITH clause.

This is exactly what I was looking for to solve a difficult task involving ROWNUM (ROWNUM post). What’s more, you are not limited to only a single “named query”. You can define as many as you want by separating them with a comma:

WITH query_name AS (subquery)

     [, query_name AS (subquery) ]...

Calling a PHP SOAP 1.2 web service using Visual Studio and VB.Net

Oh the fun of learning new technologies.  The endless errors, outbursts of profanity, stumping dead-ends…and finally, the trill of success!  Nothing beats it in my book.  This story is about my latest “trophy” (a fishing metaphor), creating a PHP web service and then calling it from a .NET console application.  Once you get everything working, it all seems so simple…but rest assured, creating a WSDL file by hand can be a tricky experience if you are new to one of the technologies in question.  First things first, to follow this example you need to download the sample application that contains a couple of PHP files and console application.  I tried to boil this down to the Hello World level so it would be easiest to understand.  I assume you already have PHP setup and have enabled the SOAP extension. Here is the part that took me awhile to get working.  I wanted a web service that took a string as a parameter and returned a string.  Apparently Visual Studio requires this “complexType” description in the “types” section; otherwise you could just define your parameters in the “message” section and be done with it.

      <s:element name="GetReportsListRequest">

        <s:complexType>

          <s:sequence>

            <s:element minOccurs="1" maxOccurs="1" name="Category" type="s:string"/>

          </s:sequence>

        </s:complexType>

      </s:element>

      <s:element name="GetReportsListResponse">

        <s:complexType>

          <s:sequence>

            <s:element minOccurs="1" maxOccurs="1" name="ReportsList" type="s:string"/>

          </s:sequence>

        </s:complexType>

      </s:element>

When VS2005 reads this WSDL (lots of XML omitted in the above – see download), it generates the following stub:

Public Function GetReportsList(ByVal Category As String) As String

Now another tricky part was getting the PHP service to return the string in the correct object hierarchy.  It took the following code to do so:

function GetReportsList($P) {

  $reportList = "Report1|Report2" . $P->Category;

  return array("ReportsList"=>$reportList);

}

For an example of how to return complex types, see the references section at the end of this article.

One other quirky thing was how to call the web service from a PHP client (I’ll throw that in for free).  You have to wrap your parameter array in a blank array because of the WSDL GetReportsListRequest hierarchy:

$return = $client->__soapCall("GetReportsList",array(array( "Category" => "abc")));

References:

PHP SOAP Manual
User Comment in SoapServer.addFunction documentation
Trouble with Visual Studio and WSDL
WSDL Essentials

 

A .Net developer's guide to MySQL

I was given the opportunity forced to get to know all things MySQL so these are the observations of a Microsoft/Oracle developer’s first foray into MySQL.

The MySQL Downloads page is the one-stop for the latest MySQL goodies. As of this writing the Community Server (free) was at release 5.1. Once you install the database, you quickly realize a need for some additional tools. The MySQL GUI Tools bundle includes the essential Administrator and QueryBrowser applications that will be familiar to any Microsoft developer. Now that you can create and maintain your new RDBMS, you need some drivers to connect the .NET code to. I tested both the ODBC and the Native (Connector/Net) drivers and from what I can see, I’d go with the Native drivers for development, but I’d also install the ODBC drivers for general Microsoft bliss (Visio, Excel, etc.).

Working with the Native driver is nearly identical to any other ADO.Net provider, save that everything is prefixed with “MySql” (e.g. MySqlConnection, MySqlCommand, etc.). After adding a project reference to MySql.Data, you need only import MySql.Data and MySql.Data.MySqlClient and the rest is completely straightforward:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports MySql.Data
Imports MySql.Data.MySqlClient



<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class ReportService
    Inherits System.Web.Services.WebService

    Dim _connString As String = "server=localhost;database=csrm;user id=root;password=****;"

    <WebMethod()> _
    Public Function ReportList(ByVal group As String) As Report()
        Dim newList As New ArrayList
        Dim newItem As Report


        Dim conn As New MySqlConnection(_connString)
        Try
            conn.Open()

            Dim cmd As New MySqlCommand("SELECT * FROM report", conn)
            Dim reader As MySqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleResult)

            While reader.Read
                newItem = New Report()
                newItem.Code = reader("Code")
                newItem.Name = reader("Name")
                newList.Add(newItem)
            End While

            reader.Close()

        Catch ex As Exception
            Throw New ApplicationException("Error occured: " & ex.Message, ex)
        Finally
            conn.Close()
        End Try

        Return newList.ToArray(GetType(Report))
    End Function

    <WebMethod()> _
    Public Function ReportOutput(ByVal reportCode As String) As DataTable
        Dim dt As New DataTable("Report")

        Dim conn As New MySqlConnection(_connString)
        Try
            conn.Open()

            Dim cmd As New MySqlCommand("ServiceReport", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New MySqlParameter("IN_REPORT_CODE", reportCode))

            Dim da As New MySqlDataAdapter(cmd)
            da.Fill(dt)

        Catch ex As Exception
            Throw New ApplicationException("Error occured: " & ex.Message, ex)
        Finally
            conn.Close()
        End Try

        Return dt
    End Function

End Class


<Serializable()> _
Public Class Report

    Private _code As String
    Public Property Code() As String
        Get
            Return _code
        End Get
        Set(ByVal value As String)
            _code = value
        End Set
    End Property

    Private _name As String
    Public Property Name() As String
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property

    Private _description As String
    Public Property Description() As String
        Get
            Return _description
        End Get
        Set(ByVal value As String)
            _description = value
        End Set
    End Property

End Class

The syntax for creating a stored procedure is slightly different than PL-SQL, but seems easy enough:

DELIMITER $$

DROP PROCEDURE IF EXISTS `csrm`.`ServiceReport`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `csrm`.`ServiceReport`(
    IN IN_REPORT_CODE varchar(5)
)
BEGIN

SELECT * FROM emailAddr;

END $$

DELIMITER ;

Using Oracle SQL Developer Copy Wizard to create local XE schemas

Teleworking is great.  Actually, it’s a modern wonder.  There are tons of benefits to the employee and employer.  In addition to better QoL scores, in many cases teleworking boosts developer productivity thanks to fewer distractions.  There are some disadvantages however.  Working remotely places one at the whim of residential bandwidth.  Once considered ginormous, 10Mbps connections are almost the norm in many suburban areas, however local conditions can vary and an outage can be devastating.  Even when the bits are flowing, downloading large record sets can clog even the biggest pipe costing the developer time during repeated compile/test cycles.  That brings us to development “unplugged”.  If you are a Microsoft shop, running a local copy of SQL Server express is nothing new, but if your application is tied to an Oracle backend, replicating that was typically beyond the skill set/toolset of the average code jockey.  Oracle 10g Express (XE) and SQL Developer has changed all that.  Now the process is as simple as adding a new user (see screenshot) to your local database with the browser admin utility and using the dead-simple Copy Wizard in SQL Developer’s Tools menu.  Unplugged is fast, but don’t forget to keep your integration server in-synch (daily at a minimum) so your work is not lost if the workstation croaks.  Note, the tns-free connection string did make my forehead wrinkle:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=XE)(SERVER=DEDICATED)));user id=<user>;password=<password>;

Tip: keep multiple connection strings in your web.config to be able to switch between the local and integration server; whichever key is last, “wins”.

Making the AJAX Control Toolkit 508 Compliant - TabContainer

The new ASP.NET AJAX Control Toolkit has many useful new controls, but being constrained by Section 508 can make life difficult for those of us wanting to make use of asynchronous browser techniques. Enter the TabContainer control. The TabContainer couldn’t be easier to use. Right out of the box you get a beautiful tab layout using familiar markup:

<ajaxToolkit:TabContainer runat="server" ID="Tabs" Height="138px">

<ajaxToolkit:TabPanel runat="server" ID="Panel3" HeaderText="Email">

<ContentTemplate>

Email:

<asp:TextBox ID="emailText" runat="server" /><br /><br />

<asp:Button ID="Button1" runat="server" Text="Save" OnClick="SaveProfile" /><br /><br />

Hit Save to cause a full postback.

</ContentTemplate>

</ajaxToolkit:TabPanel>

This results in a nice looking tabbed control:

Now the bad news: if you turn off JavaScript to test 508 accessibility, nothing is displayed because the tabs are rendered as a DIV with style set to visibility:hidden;

<div id="ctl00_ContentPlaceHolder1_Tabs" class="ajax__tab_xp" style="width:402px;visibility:hidden;">

I couldn’t find an easy way to get at the DIV output other than to “overwrite” the style with an Attribute added on PreRender:

Private Sub Tabs_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Tabs.PreRender

Tabs.Attributes.Add("style", "visibility:visible")

Tabs.ActiveTab.Attributes.Add("style", "visibility:visible")

End Sub

Now that will get your DIV contents visible again, but the tabs no longer work (more JavaScript), so I changed the TabContainer to use a HeaderTemplate and added NoScript tags with buttons:

<HeaderTemplate>

Signature and Bio

<noscript>

<asp:Button ID="btnTab0" runat="server" CommandArgument="0"

Text="Signature and Bio Tab" OnCommand="Tab_Command" /></noscript>
</HeaderTemplate>

Add a simple OnCommand handler and you are all set. Note, I prefer to set the handler in this way (declaratively) since I tend to copy/paste my html code and I might always forget to hook up the handler in the CodeBehind:

Protected Sub Tab_Command(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs)

Tabs.ActiveTabIndex = Integer.Parse(e.CommandArgument)

End Sub

Without any CSS or HTML trickery this is what you wind up with:

It’s not the most beautiful looking page, but then again, screen readers are by definition not concerned with “look and feel”.