Lots of SQL programmers probably know this trick already, but it was a new one for me and seems worth sharing. In just a few lines of code, without using a cursor, you can concatenate values from multiple rows into a single string. For example, if you have a table with a FirstName column, and a SELECT statement returns the FirstName values 'Aaron', 'Betty' and 'Carol', it's easy to form a string like this: 'Aaron, Betty, Carol'. The comments in the code snippet below explain how.
-- create a test table and insert some rows of test data
CREATE TABLE Test (
FirstName VARCHAR(10),
LastName VARCHAR(10)
)
INSERT INTO Test VALUES ('Aaron', 'Aardvark')
INSERT INTO Test VALUES ('Betty', 'Baboon')
INSERT INTO Test VALUES ('Carol', 'Condor')
-- form a comma-delimited list of the FirstName values from all records
DECLARE @myList VARCHAR(100) -- this works with a VARCHAR or NVARCHAR variable, but _not_ with a CHAR variable
SET @myList = '' -- initialize the variable; if you don't, the output string will be blank
-- The next SELECT statement is the key. It appends the FirstName from each record to whatever is in the string so far.
-- The CASE statement is just for putting commas between the names, but not in front of the first one.
SELECT @myList = @myList + CASE @myList WHEN '' THEN '' ELSE ', ' END + FirstName FROM Test
SELECT @myList AS MyList -- output the string, which should say 'Aaron, Betty, Carol'
-- delete the test table
DROP TABLE Test
Sunday, June 5, 2011
Monday, May 16, 2011
Fun book about AI - "Final Jeopardy"
An interesting computer science read: "Final Jeopardy" by Stephen Baker. It's an account of IBM's massive project to build Watson, the computer system that defeated two human champions on the game show "Jeopardy." My key takeaway is that there are two approaches to artificial intelligence, and they're both hard.
You can simulate human-like reasoning with something like a neural network -- hard because it requires vast processing power
Or you can "teach" a computer system myriad rules and bits of information -- hard because it requires lots of people to spend lots of time.
You can simulate human-like reasoning with something like a neural network -- hard because it requires vast processing power
Or you can "teach" a computer system myriad rules and bits of information -- hard because it requires lots of people to spend lots of time.
Tuesday, May 10, 2011
Console app to send a SOAP request
For troubleshooting purposes, it can be useful to invoke a web service method by sending an XML-formatted SOAP request and receiving the response. Here's a C# console application to do that. Just replace the URL and the XML request string with your own values.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;
namespace post
{
class Program
{
static void Main(string[] args)
{
// Replace with the URL of your web service.
string strUrl = "http://oosapi/OosApiService.asmx";
// Replace with the XML-formatted SOAP request for the web service method you wish to call.
// Don't forget to escape any double quotes.
string strRequest = @"b5a3e821-6f7d-4ad0-b5d0-b723222bc319 ";
// Create the Request object.
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strUrl);
req.Method = "POST";
req.ContentType = "text/xml";
req.ContentLength = strRequest.Length;
// Send the request.
StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), System.Text.Encoding.ASCII);
swRequest.Write(strRequest);
swRequest.Close();
// Receive the response.
StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
string strResponse = srResponse.ReadToEnd();
srResponse.Close();
// Output the response.
Console.WriteLine(strResponse);
Console.ReadKey();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.IO;
namespace post
{
class Program
{
static void Main(string[] args)
{
// Replace with the URL of your web service.
string strUrl = "http://oosapi/OosApiService.asmx";
// Replace with the XML-formatted SOAP request for the web service method you wish to call.
// Don't forget to escape any double quotes.
string strRequest = @"
// Create the Request object.
HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strUrl);
req.Method = "POST";
req.ContentType = "text/xml";
req.ContentLength = strRequest.Length;
// Send the request.
StreamWriter swRequest = new StreamWriter(req.GetRequestStream(), System.Text.Encoding.ASCII);
swRequest.Write(strRequest);
swRequest.Close();
// Receive the response.
StreamReader srResponse = new StreamReader(req.GetResponse().GetResponseStream());
string strResponse = srResponse.ReadToEnd();
srResponse.Close();
// Output the response.
Console.WriteLine(strResponse);
Console.ReadKey();
}
}
}
Monday, April 11, 2011
.NET cache stores only a reference -- and what to do about it
.NET provides the System.Web.Caching.Cache object as a way to persistently store data. This is useful, for example, if you retrieve some data from a database and want to refer to it repeatedly, perhaps from several different pages, without the overhead of accessing the database again each time.
When I attempted to store an instance of a class I had created with several members -- I discovered a quirk of the Cache object: adding such an object to the cache seems to store a reference to the object, not a copy of the object. An example will make this clear.
// define a class with some members
class Animal
{
public string species;
public string name;
public Animal(string s, string n)
{
species = s;
name = n;
}
}
// create a dog named Spot
Animal animal = new Animal("dog", "Spot");
// store Spot in the cache
HttpContext.Current.Cache.Insert("MyAnimal", animal);
// change Spot's name to Rover
animal.name = "Rover";
// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");
// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;
I expected the above code to set the label to Spot, but it actually sets it to Rover. Cache. Insert appears to store a reference to the object. I have yet to find anyplace this is mentioned in Microsoft's documentation. Kudos to Martin Bakiev of Penn State University for figuring this out.
Here's one way to get around the problem. Add another constructor to the Animal class. Use to create a copy of the object, and then store that copy in the cache. This has the desired effect, setting the label to Spot.
// define a class with some members
class Animal
{
public string species;
public string name;
public Animal(string s, string n)
{
species = s;
name = n;
}
public Animal(Animal a)
{
species = a.species;
name = a.name;
}
}
// create a dog named Spot
Animal animal = new Animal("dog", "Spot");
// store Spot in the cache
//HttpContext.Current.Cache.Insert("MyAnimal", animal);
HttpContext.Current.Cache.Insert("MyAnimal", new Animal(animal));
// change Spot's name to Rover
animal.name = "Rover";
// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");
// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;
That avoids the problem, but it's not very convenient if you want to store instances of many different classes in the cache; you'd need to create a new constructor for each class. I found a better solution: I wrote methods that use the .NET BinaryFormatter class to serialize an object on its way into the cache, and deserialize it on the way back out. I may be able to share that code in a future post.
When I attempted to store an instance of a class I had created with several members -- I discovered a quirk of the Cache object: adding such an object to the cache seems to store a reference to the object, not a copy of the object. An example will make this clear.
// define a class with some members
class Animal
{
public string species;
public string name;
public Animal(string s, string n)
{
species = s;
name = n;
}
}
// create a dog named Spot
Animal animal = new Animal("dog", "Spot");
// store Spot in the cache
HttpContext.Current.Cache.Insert("MyAnimal", animal);
// change Spot's name to Rover
animal.name = "Rover";
// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");
// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;
I expected the above code to set the label to Spot, but it actually sets it to Rover. Cache. Insert appears to store a reference to the object. I have yet to find anyplace this is mentioned in Microsoft's documentation. Kudos to Martin Bakiev of Penn State University for figuring this out.
Here's one way to get around the problem. Add another constructor to the Animal class. Use to create a copy of the object, and then store that copy in the cache. This has the desired effect, setting the label to Spot.
// define a class with some members
class Animal
{
public string species;
public string name;
public Animal(string s, string n)
{
species = s;
name = n;
}
public Animal(Animal a)
{
species = a.species;
name = a.name;
}
}
// create a dog named Spot
Animal animal = new Animal("dog", "Spot");
// store Spot in the cache
//HttpContext.Current.Cache.Insert("MyAnimal", animal);
HttpContext.Current.Cache.Insert("MyAnimal", new Animal(animal));
// change Spot's name to Rover
animal.name = "Rover";
// get Spot from the cache
Animal animal2 = (Animal)HttpContext.Current.Cache.Get("MyAnimal");
// this is the cached object; the name should be Spot, but instead it's Rover ??!!
Label1.Text = animal2.name;
That avoids the problem, but it's not very convenient if you want to store instances of many different classes in the cache; you'd need to create a new constructor for each class. I found a better solution: I wrote methods that use the .NET BinaryFormatter class to serialize an object on its way into the cache, and deserialize it on the way back out. I may be able to share that code in a future post.
Friday, April 8, 2011
Determining whether a numeric value has at most two decimal places
I was working on a .NET website and wanted to write some C# code to validate that a value input by the user was numeric and had at most two decimal places. This is useful, for example, when validating that the input represents a dollar amount.
I first tried this, which didn't work:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (100 * d != (int)(100 * d)) // max of two decimal places
return false;
return true;
}
catch
{
return false;
}
The above is unreliable because, since d is a floating-point number, 100 * d isn't always exactly equal to (int)(100 * d), even when d has two or fewer decimal places. For example, 100 * 1.23 might evaluate to, say, 122.9999999.
This post on StackOverflow offers several solutions, but none of them looked right for my purpose. Instead, I came up with this:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (s.IndexOf(".") >= 0)
{
if (s.Length > s.IndexOf(".") + 3)
return false;
}
return true;
catch
{
return false;
}
The same thing could be accomplished using a regular expression, if you prefer.
I first tried this, which didn't work:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (100 * d != (int)(100 * d)) // max of two decimal places
return false;
return true;
}
catch
{
return false;
}
The above is unreliable because, since d is a floating-point number, 100 * d isn't always exactly equal to (int)(100 * d), even when d has two or fewer decimal places. For example, 100 * 1.23 might evaluate to, say, 122.9999999.
This post on StackOverflow offers several solutions, but none of them looked right for my purpose. Instead, I came up with this:
try
{
// must be numeric value
double d = double.Parse(s);
// max of two decimal places
if (s.IndexOf(".") >= 0)
{
if (s.Length > s.IndexOf(".") + 3)
return false;
}
return true;
catch
{
return false;
}
The same thing could be accomplished using a regular expression, if you prefer.
Friday, April 1, 2011
Reading a stored procedure's output variable in .NET
I recently wrote some C# code to call a stored procedure, pass some input and output parameters, read the resulting recordset, and then read the value of an output parameter. When I tried to read the output parameter's value, the result was null. It took a long time to figure out why.
It turns out that, when using SqlDataReader.ExecuteReader -- as opposed to ExecuteScalar or ExecuteNonQuery, you must close the reader before you can obtain the value of an output variable. (A tip of the hat to The Code Project for that information.)
Here's an example:
The SQL code:
CREATE PROCEDURE test
@foo INT,
@goo INT OUTPUT
AS
BEGIN
SET @goo = @foo * @foo
SELECT @goo AS hoo
END
First version of the C# -- wrong, output value is null:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());
// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
} // reader is closed here
}
Second version of the C# -- right, output value is 81:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());
// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
} // reader is closed here
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
}
It turns out that, when using SqlDataReader.ExecuteReader -- as opposed to ExecuteScalar or ExecuteNonQuery, you must close the reader before you can obtain the value of an output variable. (A tip of the hat to The Code Project for that information.)
Here's an example:
The SQL code:
CREATE PROCEDURE test
@foo INT,
@goo INT OUTPUT
AS
BEGIN
SET @goo = @foo * @foo
SELECT @goo AS hoo
END
First version of the C# -- wrong, output value is null:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());
// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
} // reader is closed here
}
Second version of the C# -- right, output value is 81:
string connectionString = "...";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("test", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@foo", 9);
SqlParameter outParam = cmd.Parameters.AddWithValue("@goo", 0);
outParam.Direction = ParameterDirection.Output;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
rdr.Read();
// obtain a value from the recordset
int result = Int32.Parse(rdr["hoo"].ToString());
// obtain the value of the output parameter
// reader is still open
// System.NullReferenceException occurs because the value is null
} // reader is closed here
Label1.Text = cmd.Parameters["@goo"].Value.ToString();
}
Thursday, March 3, 2011
Configuring access to a web service in production, test and development environments
I'm developing an API in the form of a .NET web service. Third parties will consume the API, and so will my own website. The servers and developer workstations hosting the API must support four different ways of accessing the API:
1. Third parties must be able to access the API on my production web server using a secure (HTTPS) URL visible outside my private IP space.
2. The production version of my website must be able to access the API on my production web server using a secure (HTTPS) URL visible inside my private IP space.
3. The test version of my website must be able to access the API on my test web server using a secure (HTTPS) URL visible inside my private IP space.
4. A developer's local copy of my website must be able to access the API on the developer's computer using a non-secure (HTTP) URL visible on the local computer, avoiding the need to configure a local IIS site to support SSL.
Here's how I fulfilled all four requirements:
IIS:
You might wonder why I didn't just use the name "oosapi" in the hosts file for all three environments. The answer is that I wish to use SSL on the live and test web servers. My SSL certificates are for the domains localupmenus.com and localupmenustest.com, not oosapi. If I used oosapi, an "invalid certificate" warning would occur, and .NET would refuse to connect to the web service.
In the web.config for the my website, one of three keys identifying the URL for the web service is uncommented:
The above handles requirements #2 - #4. That just leaves requirement #1. For third party access, I created a DNS entry in LocalUp's GoDaddy account, mapping oosapi.localupmenus.com to 74.116.125.134. That's the public IP that corresponds to the private IP x.y.z.134.
2. The production version of my website must be able to access the API on my production web server using a secure (HTTPS) URL visible inside my private IP space.
3. The test version of my website must be able to access the API on my test web server using a secure (HTTPS) URL visible inside my private IP space.
4. A developer's local copy of my website must be able to access the API on the developer's computer using a non-secure (HTTP) URL visible on the local computer, avoiding the need to configure a local IIS site to support SSL.
Here's how I fulfilled all four requirements:
IIS:
- On the live web server, I created a website with IP address x.y.z.134*, ports 80 and 443, no host header.
- On the test web server, I created a website with IP address x.y.z.130, ports 80 and 443, no host header.
- On my local computer, I created a website with IP address "All Unassigned", port 80, and host header "oosapi".
- On the live web server, the hosts file includes the entry "x.y.z.134 oosapi.localupmenus.com".
- On the test web server, the hosts file includes the entry "x.y.z.130 oosapi.localupmenustest.com".
- On my local computer, the hosts file includes the entry "127.0.0.1 oosapi".
You might wonder why I didn't just use the name "oosapi" in the hosts file for all three environments. The answer is that I wish to use SSL on the live and test web servers. My SSL certificates are for the domains localupmenus.com and localupmenustest.com, not oosapi. If I used oosapi, an "invalid certificate" warning would occur, and .NET would refuse to connect to the web service.
In the web.config for the my website, one of three keys identifying the URL for the web service is uncommented:
- On the live web server:
- On the test web server:
- On my local computer:
The above handles requirements #2 - #4. That just leaves requirement #1. For third party access, I created a DNS entry in LocalUp's GoDaddy account, mapping oosapi.localupmenus.com to 74.116.125.134. That's the public IP that corresponds to the private IP x.y.z.134.
Subscribe to:
Posts (Atom)