Brothers In Code

...a serious misallocation of .net resources

Fiddler Rocks for Debugging Web Applications!

Every once in a while you run across a free tool that makes tough problems trivial in a clean and simple way.  I've used a handful of http debugging proxies, including those that aren't cheap, let alone free, and none of them hold a candle to Fiddler (www.fiddler2.com). 

More than once I've been stuck with some cryptic browser problem that just wasn't a first-page google fix.  And of course the problem is often different depending on the browser version, if it was over an https connection, etc.  I recently had a problem where a dynamic pdf just would not open in IE when requested over ssl from IIS 7.  The browser would just open a blank page so I had no error message to search on.  I decided to fire up fiddler and compare the http with the https response.  Sure enough, IIS was appending some no-cache headers to the response. 

And that was just one issue.  I've used Fiddler to:

  • Inject CSS in order to test a style on a site I didn't host.
  • Redirect traffic from mobile devices to my VS.net development server so I could avoid using my local IIS server.
  • To see xml exchanged between Oracle and a SharePoint webservice. 

Bottom line, if you anything with the web or the http protocol, download Fiddler today.

Executing SQL Plus Batches with Bind Variables from .Net

In an earlier post I talked about bind variables in SQL Plus and PL/SQL and only linked to an OTN forum post to show how these bind variables related to .net.  I didn't realize there were a couple of mistakes in that post so i thought I go thru the entire process here.

Using bind variables normally doesn't amount to much more than using the SQL parameters you're probably already familiar with.  The only difference I can think of is the use of a colon instead of the '@' character and that you only use the colon when using it in a parameterized query (you do not use it for stored proc params).  However, with very little SQL Plus experience, i found executing a batch of statements from .net a little tricky.

I suggest starting with a raw SQL Plus / PL/SQL batch and get that working.  In the following example, i wanted to call a stored proc with fewer params than it asked for which required a lookup in a separate table.

var badge_no varchar2(15);
var p_return_success number;
var p_return_string varchar2(4000);
exec :badge_no := '1234';
exec :p_return_success := -1
declare
  l_myKey varchar(50);
begin
  select rf_key into l_myKey from rf_security where badge_no=:badge_no;
  CHECK_BADGE(
    P_BADGE_NO => :badge_no,
    P_KEY => l_myKey,
    P_RETURN_SUCCESS => :p_return_success,
    P_RETURN_STRING => :p_return_string);
exception
  when NO_DATA_FOUND then
    :p_return_success := 0;
    :p_return_string := 'User not found';
end;
/
print badge_no
print p_return_success
print p_return_string

For those of you that aren't familiar with SQL Plus and PL/SQL, the PL/SQL portion of this starts with the 'declare' and ends with end; 

Note: One minor thing that bit me.  If you're using SQL Developer as your query tool, I found that it's a bit buggy with bind variables and output.  For example, I originally had the ':p_return_success := -1 ' inside of the 'begin' block.  That worked as expected in SQL Plus but not in SQL Developer (which is much easier to use).

When you add a parameter like this:

cmd.Parameters.Add("P_BADGE_NO", OracleDbType.Varchar2).Value = AuthHeader.Token.UserId.ToString();

That gets translated into 'var badge_no varchar2' by the provider.  So the statement trimmed down for .net is (assuming you're using Oracle.OracleClient from ODP.net and not System.Data.OracleClient):

      OracleCommand cmd = new OracleCommand(
        @"
        declare
          l_myKey varchar(50);
        begin
          select rf_key into l_myKey from rf_security where badge_no=:badge_no;
          CHECK_BADGE(
            P_BADGE_NO => :badge_no,
            P_KEY => l_myKey,
            P_RETURN_SUCCESS => :p_return_success,
            P_RETURN_STRING => :p_return_string);
        exception
          when NO_DATA_FOUND then
            :p_return_success := 0;
            :p_return_string := 'User not found,';
        end;
        ";
      cmd.Parameters.Add("P_BADGE_NO", OracleDbType.Varchar2).Value = AuthHeader.Token.UserId.ToString();
      cmd.Parameters.Add("P_RETURN_SUCCESS", OracleDbType.Decimal).Direction = ParameterDirection.Output;
      cmd.Parameters.Add("P_RETURN_STRING", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;

      using (cmd.Connection = new OracleConnection(AppConfig.General.ConnectionString))
      {
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();
      }

Notice that we left l_myKey variable in.  This variable is only used locally within the batch and we do not need it's value to go in or out.  That's why we declared it as pl/sql variable inside of the declare instead of a SQL Plus variable outside.

However, as is, this will blow up with the following error:

ORA-06550: line 1, column 1:
PLS-00103: Encountered the symbol "" when expecting one of the following

It turns out that the very old SQL Plus has a problem with the windows newline.  I just added a .Replace to my string and it worked from there.

      OracleCommand cmd = new OracleCommand(
        @"
        declare
          l_myKey varchar(50);
        begin
          select rf_key into l_myKey from rf_security where badge_no=:badge_no;
          CHECK_BADGE(
            P_BADGE_NO => :badge_no,
            P_KEY => l_myKey,
            P_RETURN_SUCCESS => :p_return_success,
            P_RETURN_STRING => :p_return_string);
        exception
          when NO_DATA_FOUND then
            :p_return_success := 0;
            :p_return_string := 'User not found,';
        end;
        ".Replace(Environment.NewLine, " "));

 Good Luck,

B

I Love Visual Studio's Build Events!

I've been using Visual Studio for more than 10 years, yet I've only discovered build events in the last year.  I really don't know how I survived with out them.

The big push for me was SharePoint.  There's a lot of things to do after you build a project: register a dll, run some stsadm.exe commands, copy some files to the 12 hive, etc.  Doing all this manually started to drive me nuts. 

But build events saved me.  Basically if you can execute your task from the command line, then it can be a build event.

A simpler example involves config files.  In a multi-project solution, I often need to keep multiple copies of a config file - say a dll is used by both a web app and a unit test project.  Keeping these in sync can be frustrating - all of a sudden your unit tests start blowing up because you only changed an expired connection string in the web app and not in the unit test.  I wrote a simple build event to copy the config files from the main project.  I even appended a little note that it was copied by the build event so I remember to edit the main file and not the copy.

There's pre and post-build events and there's lots of "macro" variables that'll keep your build events friendly over multiple developers or PCs.  You'll find the build events tab by right clicking a project file and selecting properties.

B

Forcing a page to expire in both IE and firefox.

My http header method of doing this was pretty dated.  Instead I'm now using:

Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Cache.SetNoStore();

The first line was enough for IE.  Firefox also required the second line.

 

 

 

 

 

 

Oracle ODP.net xcopy deployment for asp.net.

I just got done wrestling with multiple versions of the oracle client.  We wanted to install an Asp.net app that uses the 11g ODP.net install on a server that already had a 10g client installed.  While 11g and 10gR2 are supposed to have decent support for multiple oracle homes, the 10g client that was installed was only R1.  To make matters worse, the app using the 10g client was an old asp app via an ODBC dsn.  So rather than risking messing up the existing asp apps, I decided to give the xcopy deployment a shot. 

Getting the actual software setup is pretty well documented with an article on Oracle's website.  It really came down to just unzipping the package and running:

install.bat odp.net20 c:\oracle\product\1.1.0-xcopy-dep odac11_xcopy

However, my whole point in doing this was to avoid polluting the existing environment so a couple of items were out.   First of all, the last step in the article is to add a couple of environment variables.  Because I didn't want to interfer with the oracle home of an existing client install, i wanted to avoid this.  However without it, I got a nice exception: "Oracle.DataAccess.Client.OracleException The provider is not compatible with the version of Oracle client"

The fix was to add the following to my Application_Start event in the global.asax:

Environment.SetEnvironmentVariable("PATH", @"c:\oracle\product\1.1.0-xcopy-dep;c:\oracle\product\1.1.0-xcopy-dep\bin;", EnvironmentVariableTarget.Process);
Environment.SetEnvironmentVariable("ORACLE_HOME", @"c:\oracle\product\1.1.0-xcopy-dep;", EnvironmentVariableTarget.Process);

Inline TNSNAMES

This got me to an oracle error that I recognized: "Oracle.DataAccess.Client.OracleException ORA-12154: TNS:could not resolve the connect identifier specified."  This is usally fixed by changing tnsnames.ora but in this case it didn't work.  While it might be possible to use tnsnames with this xcopy deployment, I decided on a different route.  I could have used the host/service name format in the connection string, but this oracle environment is setup with RAC (clustering).  The oracle driver has a nice feature where the tns infomation can be inline in the connection string. 
So this:"Data Source=MyTNSName;User Id=user;Password=pwd;"
became this (which is right out of my tnsnames.ora):

 "Data Source=  (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = orahost1.company.com)(PORT = 1522))
          (ADDRESS = (PROTOCOL = TCP)(HOST = orahost2.company.com)(PORT = 1522))
          (LOAD_BALANCE = on)
          (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = MyTNSService.company.com)
            (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))
          )
        )
      ;
      User Id=user;Password=pwd;"

Bypassing Distributed Transactions and the OracleMTSRecoveryService

That nearly got everything working.  The only thing left was a particular block of code that I had wrapped in a System.Transactions.TransactionScope block.  This normally requires the OraceMTSRecoveryService.  This is something that is installable with the xcopy deployment (run install.bat with no params for usage), but this would have overwritten an existing version of the service if it was already there.  Instead I decided to force the transactions to local only (it was only a single call to the db so I knew I wouldn't need a distributed transaction).  This could be done by adding "Promotable Transactions=local" to the connection string but I'm compiling against an older version of the driver (2.111.6.20 x64) in order to support both 64 and 32 bit with the same code and this option wasn't supported in this version.  Instead I changed the following registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\2.111.7.20]
"PromotableTransaction"="local"

After that everything seems to be working.

P.S. 

Consider using a config change instead of a registry change with:
  <oracle.dataaccess.client>
    <settings>
      <add name="PromotableTransaction" value="local"/>
    </settings>
  </oracle.dataaccess.client>

Reason? I forgot to change the registry fix back and fought with "there was an error promoting the transaction to a distributed transaction oracle" for a day before I figured it out Embarassed.

5/17/2011 - Update:

I've done this again with the 4.0 provider and tnsnames.ora worked fine.  I think the first time I did this I didn't also update sqlnet.ora to use tnsnames with names.directory_path=(TNSNAMES,EZCONNECT). 

Another thing I don't remember doing in the 2.0 version was using the xcopydir\odp.net\bin\4\oraprovcfg utility.  I needed to put Oracle.DataAccess.dll in the GAC, but doing it manually didn't work.  I had to run oraprovcfg /action:gac /providerpath:<fullpath to Oracle.DataAccess.dll>

Last, I've also discovered that ODP.net supports the following configuration that should be a replacement for setting the path and oracle_home environment variables in code:


<configuration>
  <oracle.dataaccess.client>
    <add key="DllPath" value="c:\oracle\product\1.1.0-xcopy-dep\BIN"/>
  </oracle.dataaccess.client>
</configuration>

 See the following for more info:http://docs.oracle.com/html/E10927_01/InstallODP.htm

Waking a Sleeping Computer Remotely

I've been more aware of the power my 3 pc's suck down.  I recently retired one and set the other two to standby when I'm not using them.  The only thing that proved to be a little bit of a nuisance was accessing the main pc from my laptop when it was sleeping.  To get it to wake up remotely without some funky "magic packet" utility, i did the following:

In the properties for my network adapter (device manager), on the power management tab I checked "allow this device to bring the computer out of standby."  Then on the advanced tab, my adapter had a power management selection called "on wake up & directed".  From there I actually had nearly the opposite effect.  After going in to standby, the pc would wake up less than 30 seconds later.  It turns out my "server" upstairs was doing netbios requests to the pc's MAC address.  Once I shut that pc down, standby worked normally.

From there all I needed to do was direct a network packet at my pc.  Just doing a ping isn't enough since the adapter doesn't even know what an IP address is.  Instead, I did a static arp entry:

arp -s 192.168.1.11 00-00-E9-60-A1-A8

After that a ping to .11 woke it right up.

B