Connecting to SQL Server from Java

Almost every time I try to connect to SQL Server from Java I end up getting some sort of error and spending quite sometime Googling the error, so thought I’d put together a few things I have learnt. I’m using eclipse, so the examples are based on that, but I’m sure you could easily find the equivalent if you’re using a different editor.

There seem to be primarily two ways to connect to SQL Server – using Windows Authentication or SQL Authentication (with a username/password). Also, we have two commonly used drivers – JDBC and JTDS. This gives us four combinations and here are the connection strings/settings you need to get things up and running:

  1. Windows authentication + JDBC
This is the connection string we have to use:
 
String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB;integratedSecurity=true";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url);

For this to work, you’ll need to have sqljdbc driver included in the build path:
 
image
 
 
Note that you need to point to the sqljdbc4.jar (shown below) in ‘Microsoft JDBC Driver 4.0 for SQL Server’ folder. Make sure you have all other files that go along with it as well as simply having only the .jar won’t do.
 
image
 
 
You would think this would be enough, but if you just run it now you’re going to get the following error:
 

Jan 22, 2012 1:41:43 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll cause :- no sqljdbc_auth in java.library.path

So you also need to add the path to sqljdbc_auth.dll as a VM argument:
 
image
 
Now at one stage I was having the 3.0 JDBC driver and was getting the same error above and after wasting a decent amount of time found out that SQL Server 2012 requires the JDBC 4.0 driver. So if you are working with SQL Server 2008 you might need 3.0, although I’m guessing 4.0 should be backward compatible.
 

2.  SQL authentication + JDBC

This is probably the easiest scenario. You need to add sqljdbc4.jar similar to (1), but don’t need the VM arguments. The connection string to use in this case is:

String userName = "username";
String password = "password";

String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB";

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url, userName, password);

Some details related to setting up a new user can be found here.
 

3.  Windows authentication + JTDS

JTDS uses Windows authentication by default, so this is the connection string we need to use (don’t need to explicitly mention ‘integrated security’).

String url = "jdbc:jtds:sqlserver://MYPC/MYDB;instance=SQLEXPRESS";
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(url);

You also need jtds.jar in the Java build path:
 
image
 
Now at this point if you just run it you are going to get the following error:
 
java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
 
Took me a while to figure out how to resolve this, but what you need to do is something a bit similar to (1). Simply have the following as a VM argument:
 
image

’Path’ above is where your SSO folder is after installing the JTDS driver. Seems like for Windows authentication to work, Java needs to know where this ntlmaauth.dll is.

 
image
 

4.  SQL authentication + JTDS

Very similar to case (2). No VM arguments needed, but still need jtds.jar in the build path. Connection string to use is:

String userName = "username";
String password = "password";
String url = "jdbc:jtds:sqlserver://MYPC/MYDB;instance=SQLEXPRESS";
Class.forName("net.sourceforge.jtds.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, userName, password);

 
Once you have the Connection, you can run a query/stored procedure as follows:
 
Query/UDF
 
try{           

    PreparedStatement ps = conn.prepareStatement("select * from yourTable/UDF/query");
    /* if UDF, and need to pass params, can do something like:
    ...prepareStatement("select * from UDF('" + UDFinputVal + "')" */

    ResultSet rs = ps.executeQuery();
            
    while (rs.next()) {
    
        String relatedConcept = rs.getString(1);
        int shortestDistance = rs.getInt(2);
        float weightedDistance = rs.getFloat(3);
                    
        // do stuff
    }  

    rs.close();
    ps.close();            
}
catch (Exception e) {
    e.printStackTrace();
}

Stored procedure
 
try{           

    // execute SP that requires two string arguments
    PreparedStatement ps = conn.prepareStatement("exec storedProcedure ?,?");
    ps.setEscapeProcessing(true);

    ps.setString(1, "str1");
    ps.setString(2, "str2");
            
    ResultSet rs = ps.executeQuery();
    
    while (rs.next()) {
        String strVal = rs.getString(1);
        // do stuff
    }
    
    /* If you get multiple results back from you SP, you can iterate through them using:
    
    ps.getMoreResults();
    rs = ps.getResultSet();
    
    while (rs.next()) {
        String strVal = rs.getString(1);
        ... 
    }  */
            
    rs.close();
    ps.close();            
}
catch (Exception e) {
    e.printStackTrace();
}

Keywords: com.microsoft.sqlserver.jdbc.AuthenticationJNI, connecting to SQL Server from Java, eclipse connect to SQL Server, Failed to load the sqljdbc_auth.dll cause :- no sqljdbc_auth in java.library.path, java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property, jdbc, jtds, SQL Server Windows authentication, executing SQL Server stored procedure from Java, calling SQL Server user-defined-function (UDF) from Java, SQL Server multiple tables ResultSets

Posted in General | Tagged , , , , , , , , , , | 10 Comments

Determining shortest distance between ontology concepts using Jena OntTools

For something I was trying to do, I wanted to determine the shortest distance between two concepts in an ontology. In some forum I was reading someone had suggested to use Jena’s findShortestPath() method. Couldn’t really find a solid example as to how this works, so here’s something that worked for me. May be there’s a better/proper way to do this, so if anyone knows how to, I’m happy to get some feedback (although I’m no longer using Jena for my work).

First of all here’s a good tutorial that explains how to set up Eclipse to work with Jena. It’s basically a Jena Hello World example.

I was working with an OWL ontology created in Protégé OWL and one of the major issues I ran into was I kept getting the following error:

WARN [main] (RDFDefaultErrorHandler.java:36) – file:///C:/Users/ProjectName/(line 741 column 35): {W102} Unqualified property attributes are not allowed. Property treated as a relative URI.

Found a few forums where others had also run into the same issue, but with no proper solution. At the same time, in some other forum someone had mentioned that Jena needed the ontology to be in RDF format, so what I did was I saved my ontology as RDF/XML:

image

and that was it!

Now that I could create the Jena model, here’s the function that I used to determine the shortest distance between two OWL Classes.

public static void FindJenaDistance() {

        // Jena implementation 

        long startTime = System.currentTimeMillis();
        
        // this file needs to be created by doing "Save As.." and "RDF/XML" for a 'normal' OWL file. Otherwise we get Jena parse errors
        String inputFileName = "C:/Users/.../data/owlfile.owl";

        String ns = "http://www.ihtsdo.org/";

        OntModel model = ModelFactory.createOntologyModel(OntModelSpec.OWL_MEM);
        InputStream in = FileManager.get().open(inputFileName);
        model.read(in, "");        
        
        System.out.format("Ontology load time: (%7.2f sec)%n%n", (System.currentTimeMillis() - startTime) / 1000.0);        

        OntClass fromSubClass = model.getOntClass("http://www.ihtsdo.org/SCT_372418009");        
        OntClass toSuperClass = model.getOntClass("http://www.ihtsdo.org/SCT_106582005");

        Path path = OntTools.findShortestPath(model, fromSubClass, toSuperClass, Filter.any);

        if (path != null){
            int superClasses = 0;
            for (Statement s: path) {
                if (s.getObject().toString().startsWith(ns)) {
                    // filter out OWL Classes
                    superClasses++;
                    System.out.println(s.getObject());
                }
            }
            System.out.println("Shortest distance from " + fromSubClass + " to " + toSuperClass + " = " + superClasses);
        }else if (fromSubClass == toSuperClass){
            System.out.println("Same node");
        }else {
            System.out.println("No path from " + fromSubClass + " to " + toSuperClass);
        }   

        System.out.format("\nProcessing time: (%7.2f sec)%n%n", (System.currentTimeMillis() - startTime) / 1000.0);

    }

Posted in General | Tagged , , , | 1 Comment

Importing existing source code into Eclipse

This sounds like a trivial task, but I spent a good few hours reading various forums, blogs and other online material trying to find an answer to this, but I simply couldn’t! Now that I finally figured out how to do this, thought I’d write it up and hopefully someone will find this useful as well. Chances are that you are reading this post because you’ve also run into the same issue!

There are so many posts that describe how to create a new workspace when starting out a new project as well as how to ‘import’ existing source code into a new workspace. However, my scenario was that I had just checkedout several projects from svn (TortoiseSVN to be precise) into a root folder and wanted to use Eclipse to work directly on these files (which were already in sync with the repository). If I had created a new workspace and imported the source files, this would have created a duplicate folder structure in the new workspace, which I didn’t want to do. After checkingout the code, I had a folder structure similar to this:

image

The steps to create a new workspace and work directly on the source files are:

  1. Switch/create the new workspace location to be root folder – CLI in my case.
  2. Create a new Java Project – make sure the ‘Project name’ is the same as the folder name (this is the key!) – Eclipse automatically determines that this folder already exists and traverses the folder looking for relevant files. In the following example, I want to use Eclipse to work on the files under the ‘ModuleExtraction’ (not shown above) folder.

clip_image002

3. In the next screen you can see how Eclipse has included all the subfolders:

clip_image002[7]

4. Repeat steps 2 and 3 for all the projects you want to include in your workspace. You should be able to see all your projects in Eclipse now. Here I have included two for my purposes:

image

Once you’ve done this process for at least one project, you’ll see the Eclipse .metadata folder in your root directory:

image

That’s it really!

By the way, if you come from a .net background, you can think of the workspace as your solution, and the .metadata folder as the place where all your preferences, global settings, projects etc. belonging to the solution are stored.

One more thing – there is subclipse that is suppose to integrate svn capabilities directly into eclipse. You need to install Subclipse SVN manager and do something like the following in eclipse:

a. Help -> Install new software

b. Under “Work with:”, put the URL: http://subclipse.tigris.org/update_1.8.x (or whatever the version you used) <enter>

c. Wait until “Subclipse” appears, then check the box next to it

d. Choose “Next”, then “Next”, accept license agreements, then “Finish”

e. “OK” for any warnings on unsigned content

f. Wait for install to complete

g. Restart Eclipse

Here is a pretty good guide that gives all the glory details related to setting subclipse up.

I have always used the Windows Explorer based technique (described here) when working with svn and I find it pretty easy and straightforward. Never used subclipse myself, but my colleagues who used to use subclipse have now started using the technique discussed here due to some issues they’ve run into when working with subclipse within eclipse. Better to try both and see for yourself which approach works best for you I guess.

Please post a comment if you found this useful :-)

Posted in General | Tagged , , , | 7 Comments

Weird behaviour in modal dialog with ActiveX controls

For a simple test program I was implementing, I had a couple of forms – Form 1 which had all the authentication stuff and Form 2 which had the ActiveX object. The idea was to show Form 2 once a user has successfully logged in.

I had the following code in Form1:

if (iSiteEntFrm == null)
{
    iSiteEntFrm = new Form2(axiSiteNonVisual1);
    iSiteEntFrm.ShowDialog();
}
else
{
    iSiteEntFrm.ShowDialog();
}

Basically I’ve got one instance of Form2 which I try to hide/show once it’s been instantiated. “When a form is displayed as a modal dialog box, clicking the Close button (the button with an X at the upper-right corner of the form) causes the form to be hidden”, so it seemed like this should work. In my case, the first time Form2 was loaded, it worked fine as expected and showed the following:

image

However, when I close Form2 (by clicking ‘X’), and then click again on the button on Form1 which loads Form2, this is what I get – an empty Form2 with no controls!

image

I also tried creating a new Form2 each time the ‘load’ button was clicked:

iSiteEntFrm = new Form2(axiSiteNonVisual1);
iSiteEntFrm.ShowDialog();

but I kept getting a ‘System.Windows.Forms.AxHost+InvalidActiveXStateException’ exception.

image

I’m not sure of the exact reason for either of these errors, but it appeared to be some sort of an ActiveX initialisation issue. After spending a good 4hrs trying to resolve this issue, here’s the simple solution that finally worked!

iSiteEntFrm = new Form2(axiSiteNonVisual1);
DialogResult r = iSiteEntFrm.ShowDialog();
if (r == DialogResult.Cancel)
{
    iSiteEntFrm.Dispose();
}

Checking if ‘Close’ has been selected and then releasing the resources did the trick in this case.

Posted in General | Tagged , | Leave a comment

Silverlight ListBox inside a ListBox

For something I was trying to implement on the Windows 7 phone, I wanted to have a ListBox inside a ListBox (i.e., a nested ListBox). So for example, if I have a ‘Patient Vitals’ ListBox with two items, where each item is a ListBox with a bunch of items, it’ll look like this:

image

I wanted to be able to select a row within the nested ListBox, ‘Sbp’ for instance. I searched online and came across a number of posts where people had asked the same question, and tried to implement this functionality, but I simply couldn’t find the solution I was looking for. I kept running into a couple of major issues – (1) the entire inner ListBox item (e.g., from ‘Chief_complaint’ to ‘O2_saturation’) would get selected instead of the single row, and (2) not being able to access the row no/value of the selected row. Anyway, here is a solution that works, and thanks to John Gillotte for helping me out with the solution.

Here’s the code snippet for the xaml:

<Grid> <ListBox x:Name="lstBoxMainGrid" ItemsSource="{Binding}" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" SelectionChanged="lstBoxMainGrid_SelectionChanged" Margin="15,15,10,0" FontFamily="Segoe WP" FontSize="25"> <ListBox.ItemContainerStyle> <Style TargetType="ListBoxItem"> <Setter Property="Template"> <Setter.Value> <ControlTemplate> <ContentPresenter HorizontalAlignment="Stretch" VerticalAlignment="Stretch"/> </ControlTemplate> </Setter.Value> </Setter> </Style> </ListBox.ItemContainerStyle> <ListBox.ItemTemplate> <DataTemplate> <StackPanel> <ItemsControl ItemsSource="{Binding UserViewColumns}"> <ItemsControl.ItemTemplate> <DataTemplate> <StackPanel Orientation="Horizontal" Background="{StaticResource RectangleShadingBrush}"> <TextBlock x:Name="colName" Width="160" Text="{Binding ColName}" HorizontalAlignment="Left" Margin="0" TextWrapping="NoWrap"/> <TextBlock x:Name="colValue" Foreground="Gold" Text="{Binding ColValue}" TextWrapping="Wrap" FontWeight="Bold"/> </StackPanel> </DataTemplate> </ItemsControl.ItemTemplate> </ItemsControl> </StackPanel> </DataTemplate> </ListBox.ItemTemplate> </ListBox> </Grid>

and here are the helper methods/classes in C#:

private void lstBoxMainGrid_SelectionChanged(object sender, SelectionChangedEventArgs e) { currentUserViewDataRow = ((ListBox)sender).SelectedItem as GridDataRow; if (currentUserViewDataRow == null) { // handle condition } // do stuff } public class GridDataRow { private List<GridDataCol> userViewColumns; // getters, setters etc public List<GridDataCol> UserViewColumns { // return list; } } public class GridDataCol { public string ColName { get; set; } public string ColValue { get; set; } public GridDataCol(string name, string value) { ColName = name; ColValue = value; } }

Then you can set the main ListBox items using something like:

List<GridDataRow> rows = new List<GridDataRow>(); foreach(...) { // add GridDataRow instances to rows } lstBoxMainGrid.ItemsSource = rows;

Hope this will help someone out there :-)

Posted in Windows 7 phone development | Tagged , , , | 1 Comment

Getting started with Windows 7 phone application development..

I have just started developing a fairly simple Windows 7 phone application that talks to a web service and thought I would put together a few sites that helped me get started. I’ll briefly describe some of the issues I ran into during the process and also share a C# utility class I have put together to wrap all the web services calls.

  1. The MSDN documentation is probably the best place to get started. How to: Create Your First Silverlight Application for Windows Phone tutorial in Visual Studio 2010 is pretty good; I was having a bit of trouble finding the Windows Phone Developer Tools in App Hub – I went to ‘my dashboard’ and created an account, but if you just want to download the tools you need to go to resources -> downloads.
  2. How to: Perform Page Navigation on Windows Phone was probably the next most useful site. There’s a number of other tutorials/blogs describing the ‘how to get started’ process as well – Google is your friend.
  3. I wanted to talk to a web service and get the authentication token so that I can implement a ‘log in’ screen. I had some JavaScript code that was doing this already, but with my limited ASP.net knowledge, it was a challenge to figure out how to implement this. Fortunately, Developing Windows Phone 7 Applications for SharePoint 2010 site came to the rescue.

Initially, I had my two pages – a login page and another page to display after logging in. Idea was to let a user log in and then retrieve some data from a web service (using SOAP). Following is the authentication code I had. The web site didn’t have cookie based authentication, so the CookieJar stuff mentioned in (3) above wasn’t necessary – I have put together a neat Utils class that wraps all the web services calls (well, I thought it was neat anyway!) and the details are at the end, but in essence, this is what I had:

public partial class MainPage : PhoneApplicationPage { public MainPage() { InitializeComponent(); auth = new Authentication(); auth.LoggedIn += new EventHandler(UserLoggedIn); } private void btnLogin_Click(object sender, RoutedEventArgs e) { // get userName/password from controls auth.Authenticate(userName, userPassword); } private void UserLoggedIn(object sender, EventArgs e) { LoggedInEventArgs le = e as LoggedInEventArgs; string token = le.Token; Dispatcher.BeginInvoke(() => { NavigationService.Navigate(new Uri("/somepage.xaml?token=" + token, UriKind.RelativeOrAbsolute)); }); } }

So basically I register the UsedLoggedIn event handler in the MainPage and then fire the LoggedIn event once the user successfully logs in from the Authentication class shown below:

public class Authentication { public event EventHandler LoggedIn; private const string AuthServiceUri = "http://...AuthenticationWS.asmx"; private const string AuthEnvelope = @"<?xml version=""1.0"" encoding=""utf-8""?> <soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema""xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/""> <soap:Body> <GetToken xmlns='http://../Authentication'> <userName>{0}</userName> <pswd>{1}</pswd> </GetToken> </soap:Body> </soap:Envelope>"; private string _userName, _userPassword; public void Authenticate(string userName, string userPassword) { _userName = userName; _userPassword = userPassword; HttpWebRequest spAuthReq = HttpWebRequest.Create(AuthServiceUri) as HttpWebRequest; spAuthReq.Headers["SOAPAction"] = "http://..."; spAuthReq.ContentType = "text/xml; charset=utf-8"; spAuthReq.Method = "POST"; spAuthReq.BeginGetRequestStream(new AsyncCallback(spAuthReqCallBack), spAuthReq); } private void spAuthReqCallBack(IAsyncResult asyncResult) { UTF8Encoding encoding = new UTF8Encoding(); HttpWebRequest request = (HttpWebRequest)asyncResult.AsyncState; Stream _body = request.EndGetRequestStream(asyncResult); string envelope = string.Format(AuthEnvelope, _userName, _userPassword); byte[] formBytes = encoding.GetBytes(envelope); _body.Write(formBytes, 0, formBytes.Length); _body.Close(); request.BeginGetResponse(new AsyncCallback(ResponseCallback), request); } private void ResponseCallback(IAsyncResult asyncResult) { HttpWebRequest request = (HttpWebRequest)asyncResult.AsyncState; HttpWebResponse response = (HttpWebResponse)request.EndGetResponse(asyncResult); if (request != null && response != null) { if (response.StatusCode == HttpStatusCode.OK) { StreamReader reader = new StreamReader(response.GetResponseStream()); string responseString = reader.ReadToEnd(); XDocument xmlStr = XDocument.Parse(responseString); string token = xmlStr.Root.Value; if (this.LoggedIn != null) { // event will be null if no one is listening for it - you can't fire an event to no one, so check for null this.LoggedIn(this, new LoggedInEventArgs(token)); } } } } } public class LoggedInEventArgs : EventArgs { private string token; public LoggedInEventArgs(string tt) { this.token = tt; } public string Token { get { return token; } } }

Things were just fine until I got to this stage. I started running into problems only on the second page, after getting the authentication token. On my second page this is what I had:

// auto generated InitializeComponent(); code in constructor protected override void OnNavigatedTo(NavigationEventArgs e) { base.OnNavigatedTo(e); string token = ""; if (NavigationContext.QueryString.TryGetValue("token", out token)) { ExecuteSelectQuery(token); } } private void ExecuteStoredQuery(string token) { HttpWebRequest spAuthReq = HttpWebRequest.Create(qpurl) as HttpWebRequest; spAuthReq.Method = "POST"; spAuthReq.ContentType = "text/xml"; spAuthReq.Headers[".."] = token; spAuthReq.Headers[".."] = ".."; spAuthReq.Headers["SOAPAction"] = "http://.."; spAuthReq.BeginGetRequestStream(new AsyncCallback(spAuthReqCallBack), spAuthReq); } private void spAuthReqCallBack(IAsyncResult asyncResult) { string qryProcUrl = "http://....asmx"; string envelope = "<?xml version='1.0' encoding='utf-8'?>" + "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:q1='http://microsoft.com/wsdl/types/' xmlns:t='http://tempuri.org/'>" + "<soap:Body>" + // your soap body "</soap:Body>" + "</soap:Envelope>"; UTF8Encoding encoding = new UTF8Encoding(); HttpWebRequest request = (HttpWebRequest)asyncResult.AsyncState; Stream _body = request.EndGetRequestStream(asyncResult); byte[] formBytes = encoding.GetBytes(envelope); _body.Write(formBytes, 0, formBytes.Length); _body.Close(); request.BeginGetResponse(new AsyncCallback(ResponseCallback), request); } private void ResponseCallback(IAsyncResult asyncResult) { HttpWebRequest request = (HttpWebRequest)asyncResult.AsyncState; HttpWebResponse response = (HttpWebResponse)request.EndGetResponse(asyncResult); if (request != null && response != null) { if (response.StatusCode == HttpStatusCode.OK) { StreamReader reader = new StreamReader(response.GetResponseStream()); string responseString = reader.ReadToEnd(); XDocument xmlDoc = XDocument.Parse(responseString); // got the required data. Parse xmlDoc and do stuff on this page } } }

The strange issue I encountered was that this page was working just fine within the company network, but from home when I’m connected to the internet, I would keep getting a:

A first chance exception of type ‘System.Net.WebException’ occurred in System.Windows.dll

error which is not helpful at all!

image

The same code was working just fine on the network, so I was suspecting some security related issue. However, the JavaScript code I had which was running in IE/FF was working just fine, and both of them were talking to the same web service.

After an embarrassingly long time of trying to debug this issue, I found out about Fiddler – a neat tool to monitor network traffic. Eric Law’s blog on Fiddler and the Windows Phone 7 Emulator looked promising, however, I was having trouble getting the C# app on the phone to log traffic. Almost all the posts on the net show how to monitor traffic on the browser on the emulator, not traffic from an app on the emulator. Then I spotted how Eric says ‘1. Install Fiddler 2.3.0.7’ in his instructions, so I just checked mine, and it was v2.3.0.6. Downloaded the latest beta, which was v2.3.1.0, and I could see traffic from my app!!

Finally I at least knew where the problem was. I was getting the token just fine, but then when I was trying to talk to the web service to get some actual data, it was failing with an http 500 error (which is a server side error! – http://www.checkupdown.com/status/E500.html) . I was getting the following:

image

This didn’t explain why I was getting this error. After all, I could still talk to the same web service using IE/FF, and the same C# code worked just fine from work. I was sending the same request (so I thought anyway!) to the same web service, only difference was one was from the browser and the other from the emulator, so why would I keep getting a server error? This eliminated the possibility that it was something to do with the server authentication/security cos don’t think the server cares about the client the request originated from. This is when I started comparing the request from the browser and the request from the Win7 phone app using Fiddler.

Initially I didn’t spot anything different cos they both looked exactly the same. I was getting pretty frustrated by this time..Anyway, after some more painstakingly long time, I spotted the following:

Request from emulator: image

Request from browser: image

As you may (or may not!) see, the +s in the authorisation token were getting replaced by spaces! arghh…Steaming mad

Once I spotted this, it was easy to get the rest of it sorted. Now that I knew what to search for, I saw quite a few people had run into the same problem..only if I knew what to search for before! I still don’t know why the same C# app works fine from within the corporate network..but don’t think I’m going to bother. May be some proxy between my machine and the actual server ‘fixes’ the spaces in the request before it reaches the server.

People have suggested using HttpUtility.UrlEncode(string), but don’t think it’s going to work for me. It encodes the whole strong (see Using HttpUtility.UrlEncode to Encode your QueryStrings). In my case the issue is in using:

string token = ""; if (NavigationContext.QueryString.TryGetValue("token", out token)) { // do stuff }

and this is where ‘token’ gets its spaces. You can encode the Uri before passing it, 

token = HttpUtility.UrlEncode(token); Dispatcher.BeginInvoke(() => { NavigationService.Navigate(new Uri("/SomePage.xaml?token=" + token, UriKind.RelativeOrAbsolute)); });

but still in the NavigationContext.QueryString token would have spaces instead of the encoded values:

image vs image

So for the time being, I use a simple:

token.Replace(” “, “+”)

although this may not be an elegant solution. Happy to take any suggestions.

Finally, I promised to include the Utility class I have created which takes care of all the callbacks and the web service calls. This is what I have written (reusing some code from Developing Windows Phone 7 Applications for SharePoint 2010):

public class SelectQueryExecutedEventArgs : EventArgs { private string caller; private XDocument qryResult; public SelectQueryExecutedEventArgs(XDocument qryResult, string caller) { this.qryResult = qryResult; this.caller = caller; } public string Caller { get { return caller; } } public XDocument QueryResult { get { return qryResult; } } } public class Utils { public static event EventHandler SelectQueryExecuted; private static string envelope, caller; public static void ExecuteSelectQuery(string url, Dictionary<string, string> headers, string envelope, string caller) { HttpWebRequest spAuthReq = HttpWebRequest.Create(url) as HttpWebRequest; foreach (string s in headers.Keys) { spAuthReq.Headers[s] = headers[s]; } Utils.envelope = envelope; Utils.caller = caller; spAuthReq.ContentType = "text/xml; charset=utf-8"; spAuthReq.Method = "POST"; spAuthReq.BeginGetRequestStream(new AsyncCallback(spAuthReqCallBack), spAuthReq); } private static void spAuthReqCallBack(IAsyncResult asyncResult) { UTF8Encoding encoding = new UTF8Encoding(); HttpWebRequest request = (HttpWebRequest)asyncResult.AsyncState; Stream _body = request.EndGetRequestStream(asyncResult); byte[] formBytes = encoding.GetBytes(envelope); _body.Write(formBytes, 0, formBytes.Length); _body.Close(); request.BeginGetResponse(new AsyncCallback(ResponseCallback), request); } private static void ResponseCallback(IAsyncResult asyncResult) { HttpWebRequest request = (HttpWebRequest)asyncResult.AsyncState; HttpWebResponse response = (HttpWebResponse)request.EndGetResponse(asyncResult); if (request != null && response != null) { if (response.StatusCode == HttpStatusCode.OK) { StreamReader reader = new StreamReader(response.GetResponseStream()); string responseString = reader.ReadToEnd(); XDocument xDoc = XDocument.Parse(responseString); // now that we've got xDoc, fire the event so that the event handler // in the corresponding page can 'do stuff' Utils.SelectQueryExecuted(null, new SelectQueryExecutedEventArgs(xDoc, Utils.caller)); } } } }

I think the code should be fairly easy to follow, but basically all we are doing is having a SelectQueryExecuted event and firing it once we have the required data. The handlers that have registered with this event should then be able to handle it as needed. I’m passing the ‘caller’ back since all registered events will be getting this event, but we want only one of them to handle the event. May be there’s a better way to do this, but this is what I came up with. I have made the functions above ‘static’, but we could easily make them instance methods as well and then create a ‘Utils’ instance within each page, and this way only that page will get the event. I felt this would make the code cleaner, and easier to use. To register/call the above, this is what we do:

public partial class MainPage : PhoneApplicationPage { public MainPage() { InitializeComponent(); Utils.SelectQueryExecuted += new EventHandler(Authenticated); } private void btnLogin_Click(object sender, RoutedEventArgs e) { string authUri = @"http://...asmx"; string envelope = @"<?xml version=""1.0"" encoding=""utf-8""?> <soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/""> <soap:Body> <GetToken xmlns='http://..'> <userName>{0}</userName> <pswd>{1}</pswd> </GetToken> </soap:Body> </soap:Envelope>"; envelope = string.Format(envelope, userName, userPassword); Dictionary<string, string> d = new Dictionary<string,string>(); d.Add("SOAPAction", "http://.."); //.. any other headers that may be needed Utils.ExecuteSelectQuery(authUri, d, envelope, this.GetType().Name); } private void Authenticated(object sender, EventArgs e) { SelectQueryExecutedEventArgs se = e as SelectQueryExecutedEventArgs; if (se.Caller.Equals(this.GetType().Name)) { XDocument xDoc = se.QueryResult; string token = xDoc.Root.Value; Dispatcher.BeginInvoke(() => { NavigationService.Navigate(new Uri("/NextPage.xaml?token=" + token, UriKind.RelativeOrAbsolute)); }); } } } // Similarly, in the 'NextPage', this is what we have public partial class NextPage : PhoneApplicationPage { public NextPage() { InitializeComponent(); Utils.SelectQueryExecuted += new EventHandler(GotStuff); } protected override void OnNavigatedTo(NavigationEventArgs e) { base.OnNavigatedTo(e); string token = ""; if (NavigationContext.QueryString.TryGetValue("token", out token)) { string qryProcUrl = "http://...asmx"; string envelope = "<?xml version='1.0' encoding='utf-8'?>" + "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' xmlns:q1='http://microsoft.com/wsdl/types/' xmlns:t='http://tempuri.org/'>" + "<soap:Body>" + //... "</soap:Body>" + "</soap:Envelope>"; Dictionary<string, string> d = new Dictionary<string, string>(); d.Add("tokenKey", token.Replace(" ", "+")); d.Add("userid", ".."); //.. any other headers Utils.ExecuteSelectQuery(qryProcUrl, d, envelope, this.GetType().Name); } } private void GotVitals(object sender, EventArgs e) { SelectQueryExecutedEventArgs se = e as SelectQueryExecutedEventArgs; if (se.Caller.Equals(this.GetType().Name)) { XDocument xDoc = se.QueryResult; Dispatcher.BeginInvoke(() => { foreach (XElement item in xDoc.Descendants()) { // update whatever needed in the UI thread } }); } } }

I hope someone would find this info useful Smile

 

Tags/Keywords: Windows phone 7 development; A first chance exception of type ‘System.Net.WebException’ occurred in System.Windows.dll; Pluses (+s) getting replaced with spaces; C# utility class for calling SOAP web services; Passing authentication token between pages while navigating after logging in

Posted in Windows 7 phone development | Tagged | 6 Comments