Monitoring (and Java and Oracle Batching (for large number of inserts))

Background

I’ve been involved with a monitor project for a while – sadly I’m a bit of geek who enjoys looking at numbers etc. In the very early days we were looking at providing real time statistics just from VLE access, and then things evolved to look at bringing all the data together from various systems (e.g. the assignments system) to provide better metrics, and essentially a student dashboard as a early warning system. I blogged about the idea back in January of 2011 (in a work blog) and have since been involved with various prototypes to get the project off the ground. I think the original idea stemmed from this video showing some initial findings about VLE access and student performance (which originally came via a tweet by George Kroner). I’m pleased to say that the Student Dashboard is now being piloted a small set of users and more information can be found out about the project via the University of Hertfordshire LTI Blog.

Eh, I thought this was about Java and Oracle and Inserting Lots of Records…

Well it is, I got a bit side tracked there. Basically, the background was the scenario of having lots of records to be processed from various sources to generate the required structures for reporting from. And its grown over time, and today I needed to come back and revisit it. The good news is I found out about a Batch Insert for Oracle which meant inserted this number of records has just greatly decreased.

Sample Code

[Note – some lines have been removed for protection, should ultimately work though, but no promises of no syntax errors).

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
   conn = getDBConnection();
   conn.setAutoCommit(false);

   ps = conn.prepareStatement("INSERT INTO table_name (Field1, Field2,Field3,Field4,Field5) VALUES (?, ?, ?, ?, ?)");
   ((OraclePreparedStatement)ps).setExecuteBatch (1000);
   for (EntryType entry : myEntryList) {
      ps.setString(1, entry.getField1());
      ps.setString(2, entry.getField2());
      ps.setString(3, entry.getField3());
      ps.setString(4, entry.getField4());
      ps.setInt(5, 1);
      ps.executeUpdate();
   }
   ((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued request
   conn.commit();
} catch (Exception e) {
   e.printStackTrace(System.err);
} finally {
   if (rs != null) {
      rs.close();
   }
   if (ps != null) {
      ps.close();
   }

   if (conn != null) {
      conn.close();
   }
}
Advertisements

C# and JSON – Hello,World Example

I haven’t really played around with JSON data streams, but the need arose when consuming some data from another system recently. In order to do a quick and dirty solution I simply read it in as a String and then split the string where I needed to, and found the data I required. I’ve now revisited this example to do it properly!

My raw data string looks very similar to this:

{"patron":
{"FIRSTNAME" : "Gregor",
"MIDDLENAME": "",
"LASTNAME" : "Bowie",
"ERROR" : ""
},

"HasPaidRecently" :false,
"patronComments" :"You have no books on loan",
"potentialChargeAfterRenewal":12.6,
"patronAccountStatus" :"A",
"totalFineFee" :0,
"calculatedOverdueFine" :0,
"totalReplacementCharges" :0,
"totalBritishLibraryCharges" :0,
"returnedItemFines" :12.6,
"oldOutstandingFines" :0,
"numberOfItemsBorrowed" :0,
"caseId" :151,
"thereWillBeRowsToDisplay" :true,
"widgetLogId" :48721,
"sessionId" :"B80B9913C513EC8C4B81F68B3FD0A8AD"
}

In order to process this data using .NET Serializer a couple of classes representing the object structure need to be created (due to the inner Patron syntax above).

The parent class takes this form:


public class Patron
{
   public NameDetails patron { get; set; }
   public string HasPaidRecently { get; set; }
   public string patronComments { get; set; }
   public string potentialChargeAfterRenewal { get; set; }
   public string patronAccountStatus { get; set; }
   public double totalFineFee { get; set; }
   public double calculatedOverdueFine { get; set; }
   public double totalReplacementCharges { get; set; }
   public double totalBritishLibraryCharges { get; set; }
   public double returnedItemFines { get; set; }
   public double oldOutstandingFines { get; set; }
   public int numberOfItemsBorrowed { get; set; }
   public int caseId { get; set; }
   public bool thereWillBeRowsToDisplay { get; set; }
   public int widgetLogId { get; set; }
   public string sessionId { get; set; }
}

With the inner NameDetails class looking as:


public class NameDetails
{
   public string FIRSTNAME { get; set; }
   public string MIDDLENAME { get; set; }
   public string LASTNAME { get; set; }
   public string ERROR { get; set; }
}

Note, I’ve made my variable names match exactly the format they are given in the JSON. I would imagine, although haven’t tried, adding a DataMember attribute would override the naming if required.

Now for the source code that takes the raw string and converts it into the object.


Patron pat1 = new Patron();
DataContractJsonSerializer serializer = new DataContractJsonSerializer(pat1.GetType());
MemoryStream sr = new MemoryStream(Encoding.Unicode.GetBytes(rawData));
Patron pat = serializer.ReadObject(sr) as Patron;
sr.Close();

Console.WriteLine("Person: " + pat.patron.FIRSTNAME + " " + pat.patron.LASTNAME);
Console.WriteLine("Has Paid Recently? " + pat.HasPaidRecently);
Console.WriteLine("Fines: " + pat.potentialChargeAfterRenewal);

Note – this isn’t production code, merely a spike to prove things.

Scientia Timetabling – Creation of SPDA Files

Scientia provide the timetabling solution for our University and there appear to be exciting times ahead with lots of discussion about possible improvements to the timetables delivered by the University.

Of course to deliver a better solution it first means better integration with our existing systems, and the preferred method of integration by Scientia is via the use of their SPDA tool.

The SPDA tool is configured to point at various databases (note, requires 32-bit Oracle Client, not 64-bit!), and then a link definition file is put in place to determine the field mapping between Scientia and the source data table. Scientia do recommend putting in place at staging table between the source data and the SPDA tool, e.g.

tblModules (Source System)   ->    tblModules_TT (Staging) (with Status column)   -> Scientia

During recent testing I discovered that using Oracle fields of DateTime cause the SPDA tool to fall over (reported as Data Type unsupported error, and then followed by an Automation Runtime error). Strangely it appears despite declaring the fields we’re interested in, the SPDA code reverts to running a SELECT * query, rather than specified columns?! So stick with numbers and varchar2 in the staging tables!

Below are two example SPDA files that are currently working for us, one for module and one for staff. Their contents aren’t anything rocket science, only thing worth noting is that the Scientia data columns are on the left, and our staging table columns are on the right.

Modules Example (not using Status flag)

;;LDF to set up the link between S+ and Staff Feed
;Version 2

BEGIN-CLASS Module, T_ST_Module_TT, HostKey
HostKey, MODULE
Name, MODULE
Description, DESCRIPTION
Department, DEPARTMENT
END-CLASS

Staff Example (using Status flag)

;;LDF to set up the link between S+ and Staff Feed
;Version 2

IN-TRANSFER TT_Status

BEGIN-CLASS StaffMember, STAFF_DELTA, HostKey
HostKey, STAFF_NO
Name, FULLNAME
Description, USERNAME
Email, MAIL
END-CLASS

Windows Shutdown

Seriously Windows, this has been annoying me for quite some time now. I’m occasionally forced to use Microsoft Windows for an OS (I much prefer life in Ubuntu), and more often than not, when I go to Shutdown the machine the little yellow icon appears in the menu informing the user than Updates need to be installed. (Is there another option to click to avoid installing the updates?)

Unfortunately for me this happened tonight. I choose Shut Down because I wanted to switch my laptop off and head home for the night. I’m now sat here 20 minutes later because I can’t turn my laptop off, or store it securely overnight, waiting for Windows to update itself – currently still on Update 1 of 33.

Seriously – Shut Down should mean Shut Down. Not please wait (for a long time) whilst I do something.

International Blended Learning Conference 2012

image

For the second year in a row I attended the University of Hertfordshire’s International Blended Learning Conference. Last years conference saw me missing one or two sessions as I negotiated the purchase of our house – hopefully this year would be as interesting – if slightly less eventful!

The conference really is truly excellent, offering so many benefits that are often lost as I focus on day-to-day tasks, and often forget about the bigger picture. So at a very simple level just being able to see some of the excellent working taking place at our institution and further afield really is a source of inspiration. The role our team plays in building the foundations for some of these activities to take place really helps keep focus on continuing to deliver functionality for others to utilise.

I attended a number of the sessions throughout the 2 days, and all had their merits. Three though particularly stood out for me for different reasons:

  • Jessie Paterson from Edinburgh University demonstrated the effective use of blogs to promote critical reading of recommended reading. An excellent presentation and the benefits reportedly being better writing standards across the course as well as the module, along with better engagement and discussions around the texts on the module. (With my student hat on for a moment, this is something I intend to do off my own back for next years recommended readings!)
  • James McDowell from the University of Huddersfield demonstrated the use of video feedback to his students and how quickly the feedback could be turned around. I particularly liked the play on words of feed-forward rather than feedback. As a team we’re currently exploring ways to integrate video/audio feedback with the assignments system, so the findings from his research were invaluable.
  • Guy Saward from the University of Hertfordshire has built upon the publicly available RSS Feeds from each module within our VLE to publish notifications out to Facebook and Twitter using some RSS Aggregators. With it working well, 2 questions now need to be answered of (a) do students want it, and (b) how to automate it across an institution. Interesting times ahead I think.

The ability to network with colleagues is also worth its weight in gold at these conferences, and this conference appeared to have the right balance between ‘social’ and ‘presentation’ time. Listening to the experiences of our colleagues with the VLE and being able to remove a number of minor hurdles with simple conversations. Also at the end of one particular presentation, one colleague turned round and said they’d love to be able to do that. After a 10 minute initial conversation and then some follow-up emails it looks like they might be a position to do it next semester, and I look forward to providing them support in this activity.

Another outstanding outcome of the conference was the ‘Thinking Space’ initiative, which resulted in this magnificent image being created by Joel Cooper from the ideas put forth by the conference attendees:

If there is a downside to such a thought provoking and inspiring conference it would have to be the size of the To Do List sitting in front of me now. But then again, is that such a bad thing?

First Steps into the World of PHP

So a tweet goes out from one of my favourite websites on the Internet BoroGuide asking if anyone knows anything about PHP and MySQL. Knowing nothing about PHP I respond with I’ll help you with the MySQL part, but PHP will be straight over my head.

Turns out the PHP stuff is fairly straight-forward. The following is nothing ground breaking, but just more of a reference for myself in case I need to revisit any of this in the future. For what its worth, its also not tested as I don’t have a ready-made PHP environment to run in.

1. Connecting to a MySQL database and running a query in PHP


<?php
   // Make a MySQL Connection
   mysql_connect(<server>,<user>,<passwd>) or die(mysql_error());
   mysql_select_db(<db_to_use>) or die(mysql_error());
   // Retrieve all the data
   $likeclause = "matchclause";
   $result = mysql_query("SELECT * FROM table WHERE column like '%$likeclause%'") or die(mysql_error());

   // Retrieve number of rows retrieved
   $totalRows = mysql_num_rows($result);

2. Example if…else… clause in PHP

   if ($totalRows == 0)
   {
      // No data for this date
      echo "No data retrieved";
   }
   else
   {
      // got some data
   }

3. To iterate over the ResultSet

while ($row = mysql_fetch_array( $result ))
{
   echo $row['<column_name'];
}

Java File Content Header

The built in method setContentLength() from the response object in Java will only accept an Integer, and therefore limit the content length to that of the max integer on the system architecture. (So therefore, about 2Gb for files being returned).

In order to overcome this limitation, use the addHeaders method rather than the provided method:

resp.addHeader("Content-Length", Long.toString(f.length()));