Friday, December 19, 2008

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) ]...

Friday, December 12, 2008

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

 

Wednesday, December 10, 2008

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 ;



Friday, December 5, 2008

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

Tuesday, December 2, 2008

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

Friday, November 21, 2008

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.

 

Wednesday, November 5, 2008

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