Toni Westbrook dot Com

The Joy of Computer Science and Engineering

Overview

I wanted to make available a GPL daemon I developed for Linux called the “ping failover daemon”, or pfailover. It is designed for hosts with two or more network interfaces, with the goal of rerouting traffic over the secondary interface when the primary fails. It achieves this by monitoring a host over the primary connection via ping, and changing the route tables when it doesn’t receive a response. In this way, it is smart enough to reroute if any hop along the way fails, as opposed to rerouting only under the circumstances of a link-loss. When it starts receiving responses to the host over the primary interface again, it restores the route tables, thereby activating the primary connection again.

The daemon also runs scripts whenever a connection is changed, so you can insert any functionality you want, such as sending out a warning email to the IT team saying something’s up.

Additionally, it allows you to setup as many monitors as you’d like, if you have complex setups with 3 or more network interfaces, or reroute in a different fashion depending on which monitored host goes down.

For programmers and scripters, it also allows full monitoring and control via the command line and shared memory, allowing other programs to integrate its functionality.

The Reason for Development

A few months back, we ran into an interesting situation at work. We had only had T1s for an Internet connection for the longest time, but we decided since broadband was so cheap for the bandwidth, we would also get a cable modem – mainly to be used for staff web traffic. At the same time, it was a great opportunity to setup a proxy server as the gateway to this new, speedy connection. Not only would this give us an additional speed boost due to caching, but would also allow us to do some management over web use.

As anyone with a cable modem knows (well, at least with Comcast) – connection loss and downtime are not questions of if, but rather when – and when I say “when”, I really mean how many times a week. Which is fine – there is a reason why organizations still go with T-carriers and not just broadband connections – they’re more expensive, but more reliable as well.

Anyway, with this in mind, we knew that it was just a matter of time before the proxy server lost its connection to the Internet via the cable modem, and staff would start complaining about Internet loss. And at the airport, uptime is a big deal, which is especially difficult being a 24×7 operation. While there are a few strategies on how to handle this, I decided I wanted a simple solution – the proxy server would simply reroute its web requests back out the internal network connection to the T1s, instead of to the cable modem connection. Then when the cable modem came back online, it would start routing back out that interface again.

I found some other packages to do this, but they were all very robust, complex, and just too big for what I wanted – I wanted a lightweight daemon with scripting ability, so I could start out simple, and grow it complex if necessary. So I decided it would be a fun project to code one up in C++ – I rarely get to write any C++ code anymore, so I take the opportunity when I can.

Installation and Usage

You will need the lastest version of the boost libraries to compile pfailover. The installer includes sample conf and script files to aid in setup – plus it’s fairly straightforward and should only take a few minutes to configure. You can see all the options by typing “pfailover –help”. Normally, after configuring it, you’ll want to run it as a daemon with the “pfailover -d” command. Once running, you can check the current status at any time by typing “pfailover -s=get:0″.

Download pfailover 0.4.1

[SQL Server] String or Binary data would be truncated.

Run into this error before? The cause is usually fairly clear, we’re attempting to insert or update a field in a table with more data than it can hold. E.g. we’ve executed a SQL insert statement that attempts to assign a string 100 characters in length to a varchar field that can hold 80 characters max. Microsoft SQL Server complains, and we receive a nice error in our .NET application.

Solution 1: Don’t send too much data

This is an easy solution (and not the purpose of the article) and not always possible, but it is completely valid and often times desirable if you are writing an application that works with a SQL database in a static manner where the fields you’re updating are known ahead of time. There are a number of ways to ensure you don’t exceed the maximum size of a field.

If your data is coming from a form item, such as a text box, ensure the maximum size of the text box doesn’t exceed the maximum size of the field in the database. If you’re working with items that cannot be limited, simply take a substring of the data and manually truncate it to a size allowed by the field in the database. Many times we know what data and what sizes we’re working with at design time.

Solution 2: Read field sizes from database and dynamically truncate data

There are many times when solution 1 is not an option, mostly in situations where the database we’re working with is unknown, whether partially or fully, at design time. I’ve worked on a number of ETL tools and interoperability oriented applications where I can’t know the field size in the database at design time, it must be discovered at runtime.

One of the great parts about ADO.NET is the ease in which it deals with parameters. At the core of executing SQL statements is the SqlCommand (or OleDbCommand, or odbcCommand) class. After obtaining an instance associated with a database connection, we assign our SQL statement to it and all our parameters and values to it. In our case, the size of the parameter values we assign to it may exceed the maximum size of our receiving database fields. We need a method of iterating through each parameter and ensuring the value assigned isn’t too big.

Reading table schema

The heart of this routine is detecting which table you’re dealing with, then reading the schema of that table, then discovering the max size of each field.

I start by declaring a sub procedure that takes a SqlCommand as argument by reference.

        Sub TruncateParameters(ByRef passCommand As SqlCommand)

I then declared the following variables

        Dim targetTable As String
        Dim LCV As Integer
        Dim tableCommand As SqlCommand
        Dim tableReader As SqlDataReader
        Dim tempRow As DataRow

targetTable is the name of the table we’ll be checking. I read it from the actual SQL statement in the SqlCommand – but I was dealing with fairly simple SQL statements. You may want to manually pass the table names in if you’re dealing with SQL statements that will be difficult to parse.

tableCommand, tableReader, and tempRow will be used to read in the schema of our targetTable.

Next I parse the SQL statement inside of passCommand to obtain the targetTable name, but again, you may want to manually pass the name of the table ahead of time if you’re dealing with difficult to parse SQL statements. Also, if you are familiar with regular expressions, you can pare this routine down quite a bit – I present it here completely algorithmically for people who aren’t familiar with regular expressions.

       If passCommand.CommandText.ToLower().IndexOf("update") > -1 Then
             'UPDATE command
             targetTable = passCommand.CommandText.Substring(passCommand.CommandText.ToLower().IndexOf("update") + 7)
       Else
             'INSERT command
             targetTable = passCommand.CommandText.Substring(passCommand.CommandText.ToLower().IndexOf("insert") + 12)
       End If

       targetTable = targetTable.Substring(0, targetTable.IndexOf(" "))

Next we read (up to) 1 row from our target table to obtain the schema.

        ' Open Data Reader for schema table
        tableCommand = New SqlCommand("SELECT TOP 1 * FROM " & targetTable, passCommand.Connection)
        tableReader = tableCommand.ExecuteReader()

This is where the magic happens – we iterate through each parameter in our passCommand, examine the field matching the parameter, get its max length, then truncate the value assigned to each respective parameter to the max size of the field. This routine assumes that you’ve used the same parameter names as field names. If you haven’t, you would need to do further parsing of the SQL statement to deduce a parameter name -> field name mapping. This routine is also targeted for strings, but would work just as well for binary data assuming you had a method of truncating such data. Also, there are a lot of shortcuts for doing case insensitive compares, my style is to convert to lower case and compare as I can never remember what is case sensitive and what is not. Feel free to use your own style.

        ' Loop through each parameter

        For LCV = 0 To passCommand.Parameters.Count - 1
            For Each tempRow In tableReader.GetSchemaTable().Rows
                If "@" & tempRow("ColumnName").ToString().ToLower() = passCommand.Parameters.Item(LCV).ParameterName.ToLower() Then
                    If tempRow("DataType").ToString().ToLower() = "system.string" Then
                        If passCommand.Parameters.Item(LCV).Value.ToString().Length > tempRow("ColumnSize") Then
                            passCommand.Parameters.Item(LCV).Value = passCommand.Parameters.Item(LCV).Value.ToString().Substring(0, tempRow("ColumnSize"))
                        End If
                        Exit For
                    End If
                End If
            Next
        Next

Finally, we close out our connection and end the sub procedure.

        tableReader.Close()

End Sub

There is some work involved, but the idea of the above routine can be customized to fit your situation. Once you have a solid parameter truncating routine in your arsenal, you can call it for any SqlCommand object, and ensure you never get a “String or Binary data would be truncated.” error message again!