SPA 2007 Conference

Just got back from SPA 2007, held at Cambridge University’s Homerton College.

Held across 4 days (Sun-Wed), it consisted of a mixture of workshops, tutorials, case studies and more open ‘think tank/gold fish bowl’ type sessions.

I presented on ASP.Net / Oracle including lessons learned from a global intranet project I’m currently working on. It triggered some good discussions, both during and after the session, especially regarding the backend platform used (i.e. Oracle rather than SQL Server), state management, along with data and workflow modelling.

Here are some of the other sessions I attended too:

Strategies and Patterns for Systems Continuity
Lessons Learned from Scaling XP
Agile modelling practices on innovative projects
The whys and wherefores of Web 2.0
Effective error handling
Architecting the next generation of .Net applications

Quality of the sessions varied but most were well run and worth attending. I didn’t perhaps ‘learn’ as much as I’d hoped, but two points I did take away were:

1) Web 2.0 is not clearly defined (you don’t say!). I don’t like the term anyway. To me the web is constantly evolving, with new tools appearing and becoming established all the time. To label a ‘moment in web time’ like this does not make sense. There was no clear agreement on details at the conference but most did agree that Web 2.0 will/is having a significant impact on the Internet and business.
2) Agile development (XP, Scrum, Lean) is becoming more widespread and is being used to good effect. I’ve not yet used any of these methods ‘formally’ yet but do already use some of the techniques they encompass. Armed with the knowledge I’ve gained (and some new books!), I’m definitely going to see if I can make use of more Agile techniques in the future.

For me though, the session that I remember most was the plenary by the infamous Professor Tony Hoare… acknowledged widely for his Quicksort algorithm, Hoare Logic, CSP (for formally describing interacting concurrent processes) and his research leading to the Z specification language.

He’s a scientist whom I have looked up to since at University. His plenary was on ‘Assertions and Test driven design’. The talk maybe did not contain as much ‘brilliance’ as his stature and background deserves, but nevertheless none of this matters – just to see him present in person was more than enough for me. A man worthy of his Turing Award (1980) and Knighthood (2000).

Upgrading projects from ODP.Net 9.2 to ODP.Net 10.2

I've recently been upgrading a system from ASP.Net 1.1 to 2.0. At the same time we are upgrading the Oracle client from 9.2 to 10.2, and thus to ODP.Net (vs10.2.0.2.20 to be precise).

I've now completed the first pass of the conversion and all main parts of the system appear to be working fine - note we have left the Oracle back-end on 9.2 for now, but this is also going to be upgraded to 10.2 too... let's do it in stages ;-)

Here's a couple of issues I've come across so far regarding the upgrade:

a) -1 Result from ExecuteNonQuery

It is expected behavior that ExecuteNonQuery returns -1 when executing a stored procedure (regardless of what the stored proc does), but there was a bug in earlier 9.2.x versions where it returned 1 instead of -1. If you have any code that checks this result, it's worth a scan to see if this affects any of your logic. I was caught out in one of my calls to a stored proc that updated a number of rows, and I had incorrectly assumed it would return "number of rows UPDATEd" like a standard UPDATE statement would (and in this case I was expecting an update count of 1 for 'success' so it all worked fine thanks to the 9.2 bug!). Digging deep into the Oracle ODP.Net docs come up with this:

-------
ExecuteNonQuery returns the number of rows affected, for the following:
If the command is UPDATE, INSERT, or DELETE and the XmlCommandType property is set to OracleXmlCommandType.None.
If the XmlCommandType property is set to OracleXmlCommandType.Insert, OracleXmlCommandType.Update, OracleXmlCommandType.Delete.
For all other types of statements, the return value is -1.
-------

So I read this as meaning all stored procs return -1 for success. If you want the number of recs affected, you'll need to do this yourself by adding an output param instead. If anyone has found out something different please shout ;-)


b) Runtime data types of output parameters:

Ever had problems mapping .Net types to native database field types (and vice versa)? Umm me too. DBNULLS vs nulls, casting issues, loss of accuracy, I'm sure you've been there. Anyway, here's a couple of general issues to watch out for when moving to ODP.Net 10.2:

Example 1:

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Month_YTD";
cmd.Parameters.Add("p_isd_id", OracleDbType.Int32,ParameterDirection.Input).Value = isdID;
cmd.Parameters.Add("p_mp_id", OracleDbType.Int32,ParameterDirection.Input).Value = mpID;
cmd.Parameters.Add("p_month_ytd", OracleDbType.Double).Direction = ParameterDirection.Output;
DBTool.ExecuteStoredProc(cmd);
// if (cmd.Parameters["p_month_ytd"].Value != System.DBNull.Value) // fine in 9.2 but condition never fires in 10.2
if (!((Oracle.DataAccess.Types.OracleDecimal)(cmd.Parameters["p_month_ytd"].Value)).IsNull) // works fine in 10.2
{
// not null... rest of code here
}
cmd.Dispose();

(Note DBTool is simply a DAL helper class that sits as part of a larger DAL library we have).

The commented out if statement worked fine in ODP.Net 9.2. but in 10.2 the output param (specified as OracleDbType.Double) now comes back as an OracleDecimal (hence my cast in the if statement). In fact I've also got cases where the output param type is specified as OracleDbType.Int32 and this still comes back as OracleDecimal. Oracle really needs to be consistent on all this, especially across versions.

Example 2:

Old working code in .Net 1.1/ODP.net 9.2

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Max_MP_ID";
cmd.Parameters.Add("p_max_mp_id", OracleDbType.Int32).Direction = ParameterDirection.Output;
DBTool.ExecuteStoredProc(cmd);
MaxMPID = (int) cmd.Parameters["p_max_mp_id"].Value; // this bit no longer works, you get System.InvalidCastException
cmd.Dispose();

Again, this now fails in 10.2 due to output param data type being returned as OracleDecimal (!).

This new code works fine though in 10.2:

OracleCommand cmd = new OracleCommand();
cmd.CommandText = "EPR_Month_PKG.Get_Max_MP_ID";
OracleParameter ParMaxMPID = new OracleParameter();
ParMaxMPID.Direction = ParameterDirection.Output;
ParMaxMPID.DbType = DbType.Int32;
ParMaxMPID.ParameterName = "p_max_mp_id";
cmd.Parameters.Add(ParMaxMPID);
DBTool.ExecuteStoredProc(cmd);
MaxMPID = (int) cmd.Parameters["p_max_mp_id"].Value;
cmd.Dispose();

The key bit is the specifying of DbType (instead of using OracleDbType). Doing this infers an OracleDbType (you can see a table of mappings in ODP.Net docs) and also ensures a correct mapping to the .Net int type. We could use this "DbType approach" to solve the issue in example 1 above too.

Strings as output params can be handled this way too. If you don't do it this way, I found nulls and empty strings were handled inconsistently in 10.2.


LATEST UPDATE (16 Mar 2007)

Having converted a number of calls to cater for the issues above I have added a static member to DAL class, e.g. DBTool.GetOracleParameterByDbType(...) so typical code can now look as follows:

cmd.CommandText = "EPR_MonthlyReport_PKG.Get_Target";
cmd.Parameters.Add("p_year", OracleDbType.Int32,ParameterDirection.Input).Value = year;
cmd.Parameters.Add("p_year_offset", OracleDbType.Int32,ParameterDirection.Input).Value = (int) Target.YearOffSet.TargetPlus1;
cmd.Parameters.Add("p_mp_id", OracleDbType.Int32,ParameterDirection.Input).Value = mpID;
cmd.Parameters.Add("p_person_id", OracleDbType.Int32,ParameterDirection.Input).Value = personId;
cmd.Parameters.Add(DBTool.GetOracleParameterByDbType("p_target", DbType.Double, ParameterDirection.Output));
DBTool.ExecuteStoredProc(cmd);
if (cmd.Parameters["p_target"].Value != System.DBNull.Value)
{
target = Convert.ToDouble(cmd.Parameters["p_target"].Value);
}

This is nice and tidy. Whether we should also be passing Input params by DbType is another matter but not wanting to interfere with existing code too much maybe go with this for now. Note passing a DbType also ensures that System.DBNull.Value works as expected too.

The helper method by the way is simply:

public static OracleParameter GetOracleParameterByDbType(string paramName, DbType dbType, ParameterDirection paramDirection)
{
OracleParameter NewParam = new OracleParameter();
NewParam.Direction = paramDirection;
NewParam.DbType = dbType;
NewParam.ParameterName = paramName;
return NewParam;
}