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”.

Using Predicates with VB.Net Array and List Find methods

Some things are just harder in VB.Net than C#.  Using the Find and FindAll methods when you want to pass a parameter to the search delegate is definitely one such instance.  With C# you can use anonymous methods, but those are not found in VB.Net.  Fortunately a couple of guys cooked up an extended Predicate that allow you to pass in a search object.  It all starts with this class and delegate that you define in a common location (warning, trying to read this code may make you dizzy):

Public Delegate Function PredicateWrapperDelegate(Of T, A) _

    (ByVal item As T, ByVal argument As A) As Boolean

 

Public Class PredicateWrapper(Of T, A)

    Private _argument As A

    Private _wrapperDelegate As PredicateWrapperDelegate(Of T, A)

 

    Public Sub New(ByVal argument As A, _

        ByVal wrapperDelegate As PredicateWrapperDelegate(Of T, A))

 

        _argument = argument

        _wrapperDelegate = wrapperDelegate

    End Sub

 

    Private Function InnerPredicate(ByVal item As T) As Boolean

        Return _wrapperDelegate(item, _argument)

    End Function

 

    Public Shared Widening Operator CType( _

        ByVal wrapper As PredicateWrapper(Of T, A)) _

        As Predicate(Of T)

 

        Return New Predicate(Of T)(AddressOf wrapper.InnerPredicate)

    End Operator

 

End Class

Wherever you want to use Array(Of T).FindAll you call it like this and pass a “finder method” that you define nearby:

    Public Shared Function GetAgencies(ByVal secretariatCode As String) As Agency()

        Dim agList As Agency() = WSHelper.GetAgencies()

        Dim agSearchObj As New Agency

        agSearchObj.SecretariatCode = secretariatCode

       

        Return Array.FindAll(Of Agency)(agList, New PredicateWrapper(Of Agency, _
         Agency)(agSearchObj, AddressOf AgencyMatch))

 

    End Function

 

    Private Shared Function AgencyMatch(item As Agency, argument As Agency) As Boolean

        Return item.SecretariatCode = argument.SecretariatCode

    End Function

Note: specifying the array type (e.g. Array.FindAll (Of Agency)  ) is critical, otherwise you get an error that says: Type argument inference failed for type parameter 'T' of 'Public Shared Function FindAll(Of T)(array() As T, match As System.Predicate(Of T)) As T()'. Type argument inferred from the argument passed to parameter 'match' conflicts with the type argument inferred from the argument passed to parameter 'array'.

I found it a little easier to use List(Of T).FindAll since it does not require that you pass the array (or specify the type).  An example of calling FindAll:

    WSHelper.GetAgencyList.FindAll(New PredicateWrapper(Of AjaxAgency, _
      AjaxAgency)(ajaxAgencyToFind, AddressOf AgencyMatch2))

 

    Private Function AgencyMatch2(ByVal item As AjaxAgency, ByVal argument As AjaxAgency) As Boolean

        Return item.secretariatCode = argument.secretariatCode

    End Function

A special thanks to Paul Stovell for his original creation of the PredicateWrapper.

 

Upload Oracle BLOB in Stored Procedure using OracleClient OracleLob

I ran into the 32k limit on OracleClient parameters of stored procedures when trying to upload documents to Oracle using a byte array:

   comm = New OracleCommand("ADD_DOCUMENT_PROC", conn)
   comm.CommandType = CommandType.StoredProcedure
   comm.Parameters.Add("IN_PARENT_ID", OracleType.Number).Value = parentId
   comm.Parameters.Add("IN_DOC_TYPE", OracleType.VarChar).Value = docType
   comm.Parameters.Add("IN_DOC_BLOB", OracleType.Blob).Value = o.DocumentBlob
   comm.ExecuteNonQuery()

Then I found a “fast way” using the Oracle.DataAccess client.

   Dim startOffset As Integer = 0
   Dim writeBytes As Integer = 0

   conn.Open()
   Dim blob As New OracleBlob(conn)

   blob.BeginChunkWrite()
   Do
       writeBytes = IIf(startOffset + BUFFER_SIZE > o.DocumentBlob.Length, o.DocumentBlob.Length - startOffset, BUFFER_SIZE)
       blob.Write(o.DocumentBlob, startOffset, writeBytes)
       startOffset += writeBytes
   Loop While startOffset < o.DocumentBlob.Length
   blob.EndChunkWrite()

   comm = New OracleCommand("ADD_DOCUMENT_PROC", conn)
   comm.CommandType = CommandType.StoredProcedure
   comm.Parameters.Add("IN_PARENT_ID", OracleDbType.Int32).Value = parentId
   comm.Parameters.Add("IN_DOC_TYPE", OracleDbType.Varchar2).Value = docType
   comm.Parameters.Add("IN_DOC_BLOB", OracleDbType.Blob).Value = blob
   comm.ExecuteNonQuery()

It worked locally, but the client is not installed on our servers, so going back to the ADO.Net OracleClient, I found an example in the MSDN docs that is working quite well. It seems the key is to use DBMS_LOB.CREATETEMPORARY:

Private Sub AddDocument(ByVal parentId As Integer, ByVal docType As String, ByVal o As Document)
   Dim conn As OracleConnection
   Dim comm As OracleCommand

   Try

       conn = OpenConnection()

       Dim tx As OracleTransaction = conn.BeginTransaction()

       comm = conn.CreateCommand()
       comm.Transaction = tx

       ' This will create a temp blob object in the DB
       comm.CommandText = "declare xx blob; " & _
           "begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
          
       comm.Parameters.Add(New OracleParameter("tempblob", _
           OracleType.Blob)).Direction = ParameterDirection.Output
       comm.ExecuteNonQuery()

       Dim tempLob As OracleLob
       tempLob = comm.Parameters(0).Value
       tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)
      
       ' You may be able to chunk this to improve performance?
       tempLob.Write(o.DocumentBlob, 0, o.DocumentBlob.Length)
       tempLob.EndBatch()

       comm.Parameters.Clear()
       comm.CommandText = "ADD_DOCUMENT_PROC"
       comm.CommandType = CommandType.StoredProcedure
      
       ' Add the BLOB object parameter
       comm.Parameters.Add("IN_DOC_BLOB", OracleType.Blob).Value = tempLob
      
       comm.Parameters.Add("IN_PARENT_ID", OracleType.Number).Value = parentId
       comm.Parameters.Add("IN_DOC_TYPE", OracleType.VarChar).Value = docType
      
       comm.ExecuteNonQuery()

       ' You want to explicitly destroy the LOB obj
       tempLob.Dispose()

       tx.Commit()
       comm.Dispose()

   Finally
       If conn.State <> ConnectionState.Closed Then
           conn.Close()
       End If
       conn = Nothing
   End Try

End Sub

Binding to a DropDown list in a Footer Row

I typically use a separate ObjectDataSource that I place outside the GridView for clarity and easy of access in the CodeBehind. Alternatively, you could load it by hand using FindControl or declaring an OnLoad handler.

<footertemplate>
    <asp:DropDownList ID="inDataAssetIDFooter" runat="server" DataSourceID="dsDataAsset" 
        DataValueField="ID" DataTextField="Name" CssClass="DropDownList" AppendDataBoundItems="true">
        <asp:ListItem Value="0">Select Data Asset</asp:ListItem>
    </asp:DropDownList>
    <asp:CompareValidator ID="vldDANameFooter" runat="server" 
    Operator="GreaterThan" ValueToCompare="0" ControlToValidate="inDataAssetIDFooter" 
    Text="*" ErrorMessage="Data Asset is Required"/>
</footertemplate>

<asp:ObjectDataSource ID="dsDataAsset" runat="server" SelectMethod="GetUnRelatedDataAssetsByAppID"
    TypeName="MyProj.Lib.DataAssetDAL">
    <SelectParameters>
        <asp:QueryStringParameter Name="ID" QueryStringField="PID" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>

FileUpload file type validation using regular expression validator

The MSDN docs have an excellent example of how to perform file type validation using regular expressions. I tweaked the implementation to allow for configuration via the web.config:

<asp:RegularExpressionValidator
id="vldDocument" runat="server"
ValidationExpression=""
OnLoad="Setup_Doc_Validator"
ControlToValidate="inDocument" />

The codebehind gets the following handler:

    Protected Sub Setup_Doc_Validator(ByVal sender As Object, ByVal e As System.EventArgs)
       Dim docValidator As RegularExpressionValidator = CType(sender, RegularExpressionValidator)

       docValidator.ValidationExpression = DocumentDAL.GetValidationRegularExpression
       docValidator.ErrorMessage = String.Format(ProjectConstants.DOC_TYPE_ERROR_MESSAGE, _
           DocumentDAL.GetAllowableTypes())
   End Sub

    Public Shared Function GetValidationRegularExpression() As String
      Dim allowableTypes As String = DocumentDAL.GetAllowableTypes()
      Dim regEx As String = "^(([a-zA-Z]:)|(\\{2}\w+)\$?)(\\(\w[\w].*))+("

      ' Note, the file extensions must be PIPE delimited in the Reg Exp, but the web.config uses commas
      regEx &= allowableTypes.Replace(",", "|") & ")$"
      Return regEx
  End Function

IE 6.0 Hack - Float Issue

I ran into a strange browser compatibility issue when displaying a GridView.  FireFox wanted to float the grid off the screen to the right until I added Align="Left" to the GridView, but IE reacted by floating the grid behind the master page template so the content got truncated.  I ended up adding an IE hack to the CSS by placing “* html” before the class:

* html .ReportGrid {float:none;}

 

Oracle PL/SQL Split Function Boosts WHERE IN Clause Usability

Big thanks to Tech Republic for an article about creating a SPLIT user-defined function that accepts a delimited string and creates a table with N rows of data:

function Split
(
    p_list varchar2,
    p_del varchar2 := ','
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row(substr(l_list,1,l_idx-1));
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split;

 

And here’s how I used it to perform a “dynamic” query passing in a list of values:

select * 
from myTable
where IN_VARCHAR_LIST IS NULL OR 
    APP.AGENCY_ID IN(
        select column_value 
        from table(Split(IN_VARCHAR_LIST)))

Warning Users About Unsaved Changes

I was asked to implement a popup warning to alert users they were going to lose data if they navigated away from a form without saving. It turned out to be easier than I thought by using window.onbeforeunload

<script language="javascript">
   var doCheck = false;
   window.onbeforeunload = checkSave;

   function mustCheck() {
       doCheck = true;
   }

   function checkSave() {
       if (doCheck) {
           return "You have some changes that have not been saved. If you CONTINUE, your changes WILL BE LOST.";
       }
   }
</script>

And then adding a startup script in the FormView PreRender

ClientScript.RegisterStartupScript(Me.GetType, "mustCheck", "mustCheck();", True)

UPDATE - You have to add the following attribute to any buttons that do not need the warning (Cancel, Save, etc.):

OnClientClick="doCheck = false;"