SQL Select Statement with Comma-Separated List Condition

I have one problem the other day; Why does my “SELECT * FROM tbl WHERE col IN (@list)” does not work? The col datatype is int, while the @list parameter is a comma-separated varchar.

The Problem
In a stored procedure, I’m composing a SELECT statement with a WHERE clause in which the column condition datatype is integer, while the parameter supplied to it has the datatype of varchar:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind..Products
WHERE ProductID IN (@ids)

ProductID is int, @ids is a comma-separated string, i.e.: ‘9,12,27,37’.

When try to call:
EXEC get_product_names '9,12,27,37'

But this fails with:
Server: Msg 245, Level 16, State 1, Procedure get_product_names, Line 2
Syntax error converting the varchar value '9,12,27,37' to a column of data type int.

Then comes the solution, convert the comma-separated string to a recordset in a temporary table.

The Solution
There’s quite a number of solutions out there, but I’ve found a pretty good ways along with a very comprehensive explainations here. The one that I’m implementing is called the Iterative Method.

Create a new User Defined Function:

CREATE FUNCTION iter_intlist_to_table (@list ntext)
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          number  int NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      nvarchar(4000),
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
                                 @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(',', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (number) VALUES(convert(int, @str))
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
                                 len(@tmpstr)))
            SET @pos = charindex(',', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (number) VALUES(convert(int, @leftover))

      RETURN
   END

Modify the Stored Procedure to the one below:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
      SELECT ProductID, ProductName
      FROM   Northwind..Products
      WHERE  ProductID IN (SELECT number FROM
                       dbo.iter_intlist_to_table(@ids))
   go

Execute the Stored Procedure:
EXEC get_product_names_iter '9,12,27,37'

Now the stored procedure will happily accepts the comma-separated varchar parameter, and returns the records needed. Is there any other method to this? Feel free to share. 🙂

Why the Honda City is ugly

Ok, the Honda City is not that ugly; I just thought it would make a great headline. But still, the Honda City is not exactly everyone’s dream car. Are Honda designers not talented? Surely they could come up with a better design? Yes, quite easily, I’m sure. It’s just that Honda is applying a slightly different twist on the basic microeconomic principle of price differentiation.

Price differentiation means selling the same product at different prices in different markets. The ultimate objective is to charge each buyer the maximum that he or she is willing to pay. However for the car industry this is not possible; it might even be illegal. By having different categories of the same basic product, sellers attempt to implement price differentiation, but in a justifiable and completely legal way.

I read somewhere that a computer hardware manufacturer has two versions of a laserjet printer: the difference is that one is faster than the other. In reality, the two models are exactly the same, except that one has been deliberately programmed to have lower throughput. Of course, the “faster” model is priced higher. So customers who are able to afford it will go for the perceived higher-end product. This way, the printer manufacturer will get more revenue than if it were to sell just the one model.

Thus, the City is not looking as good as it could be because Honda does not want people who could afford the Accord or the Stream to be content with the City. The fact is that the Accord, the Camry, the Perdana V6 and such carry very high profit margins. Honda, Toyota, Nissan et. al. are no longer content to be mere volume producers of low-margin cars – by making their entry-level models not very desirable, they ensure that people strive to upgrade to their higher-margin offerings.

Air Crash Investigation

I’m not sure why, but I love watching Air Crash Investigation on the National Geographic Channel. Maybe I’m fascinated with how passenger airliners can be both awesome and fragile at the same time. They’re huge, majestic, and chock-full of gizmos and gadgets and instrumentation; and yet, a small glitch or mistake can cause them to simply fall from the sky and leave absolutely no survivors.

Here are those that I’ve seen:

1. Aloha Airlines Flight 243 – “Hanging by a Thread” – Metal fatigue causes part of the roof to be ripped off. A stewardess is sucked out of the plane. A survivor reports having seen cracks on the fuselage when boarding the 737.

2. Flash Airlines Flight 604 – “Vertigo” – Night-time flight; the aircraft rolls into the sea a few minutes after take-off. Investigators attribute it to pilot error (spatial disorientation) but Egyptian authorities disagree, citing mechanical failure.

3. Helios Airways Flight 522 – “Ghost Plane” – This one is rather eerie. The pressure setting is configured incorrectly causing everyone on board to lose consciousness, including the flight crew. Cabin recordings show that the pilot and co-pilot were trying to diagnose the problem but were unable to think straight due to lack of oxygen. After radio contact was lost, fighter jets were scrambled to intercept the Boeing 737. In the last moments, the fighter pilot witnessed someone entering the flight cabin and trying to regain control of the aircraft. Minutes after that, the plane crashed into a mountain, killing all 121 on board. It was later surmised that the mystery person was a flight attendant who was wearing a portable oxygen mask.

4. Turkish Airlines Flight 981 – “Behind Closed Doors” – Faulty design leads to a cargo door blowing out in mid-flight. The resulting explosive decompression damages the DC-10’s hydraulic system, causing all control to be lost.  This is a tragic case of negligence as the airplane manufacturer was in fact aware of the design flaw but did not make the necessary improvements.

5. Air Canada Flight 143 – “Gimli Glider” – This is my favourite (if ever is there such a thing as a favourite disaster; anyway, in this case, there were only a small number of minor injuries). Air Canada had only just converted from imperial to metric. Not only did both ground crew and flight crew miscalculate the amount of fuel required, the fuel gauges were out of order: the plane runs out of fuel mid-air. Fortunately the pilot is also a glider pilot and knows how to fly a plane without engine power. Approaching the runway too fast, he executes a forward slip, a manoeuvre used only on small planes, the first (and perhaps the only) time being performed on a 767. The CG reconstruction helps us fully appreciate this feat – the airplane is shown flying sideways, and only correcting just before touchdown. After this incident, the scenario was programmed into a flight simulator; nobody has ever managed to pass the test. Therefore passengers of Flight 143 were fortunate that the only pilot who could have saved them was actually flying the plane at the time.

Local Trivia

Has a Malaysia Airlines plane ever crashed? Yes, in fact: flight MH653 on 4 December 1977. A Boeing 737 was hijacked under “mysterious circumstances” and plunged almost vertically into a swamp in Tanjung Kupang, Johor, instantly killing all 100 on board.

Guest-Host Connection in Vmware through Firestarter

Following my previous post on VPNC, after modifying the user-pre file in Firestarter, it somehow screws-up my guest-host connections in Vmware. The host, running on Ubuntu 8.04, no longer be able to connect to it guests, and vice-versa. The following is what I did to make the connection works. I had to post this up for future reference, just in case I forgot.

1. Edit /etc/firestarter/user-pre . First, make the file writable:
sudo chmod 600 /etc/firestarter/user-pre

2. Edit the file:
sudo gedit /etc/firestarter/user-pre

3. Copy the following to the file:
$IPT -A INPUT -i vmnet+ -j ACCEPT
$IPT -A OUTPUT -o vmnet+ -j ACCEPT

4. Save the file and close.
5. Change the user-pre file permission back to the way it was:
sudo chmod 440 /etc/firestarter/user-pre

6. Restart Firestarter:
sudo /etc/init.d/firestarter restart

Now the connection is restored. Both the guest and host can share files, access the web servers, etc.

VPN Connection in Ubuntu using VPNC

I’ve been using VPNC in my Ubuntu to access my company’s VPN from https://getmoreprivacy.com/ for quite some time now. The story is, I’ve tried using Cisco VPN, but it will only get me as far as connecting to the VPN server. When I’m trying to access the company’s intranet, I’ll get a “page not found” error slapped to my face. I’ve found out it has to do with my machine’s Firestarter. I doesn’t really feel comfortable removing or stopping my Firestarter, so I have to find out ways to access VPN, and still having my Firestarter active.

After doing some googling, I stumbled upon VPNC, alternate VPN client for Cisco servers.

Installing VPNC:
1. Enter the following in the terminal to install VPNC on your computer:
sudo apt-get install vpnc resolvconf

2. Create a <filename>.conf file in /etc/vpnc/ . Enter following command to create myoffice.conf:
sudo gedit /etc/vpnc/myoffice.conf

3. Copy the following text to that myoffice.conf file:
IPSec gateway <server IP address>
IPSec ID <group name>
IPSec secret <group password>
Xauth username <username>

You can get the values for <server IP address>, <group name> and <group password> by opening your Cisco VPN client .pcf file. For <group password>, the value is encrypted. What you have to do is, copy all the characters from the “enc_GroupPwd” value, go to this cisco vpnclient password decoder site, paste the characters and click “Decode”. It will give you the decrypted group password. Now, copy the decrypted password and replace it with the <group password> in your myoffice.conf. Replace your user name to <username>. Save the file and close.

Running VPNC:
You can run VPNC simply by running this command:
sudo vpnc myoffice /etc/vpnc/myoffice.conf

Enter your VPN password once connected. To disconnect:
sudo vpnc-disconnect

Making it work with Firestarter:
You might have a problem accessing for company’s intranet if you have Firestarter installed. Instructions below will make it work.

1. Edit /etc/firestarter/user-pre . First, make the file writable:
sudo chmod 600 /etc/firestarter/user-pre

2. Edit the file:
sudo gedit /etc/firestarter/user-pre

3. Copy the following to the file:
iptables -A INPUT -j ACCEPT -s xxx.xxx.xx.xxx -p esp
iptables -A INPUT -j ACCEPT -s xxx.xxx.xx.xxx -p udp -m multiport --sports isakmp,10000
iptables -A INPUT -j ACCEPT -i tun+
iptables -A OUTPUT -j ACCEPT -d xxx.xxx.xx.xxx -p esp
iptables -A OUTPUT -j ACCEPT -d xxx.xxx.xx.xxx -p udp -m multiport --dports isakmp,10000
iptables -A OUTPUT -j ACCEPT -o tun+

Replace your VPN server’s IP address to the xxx.xxx.xx.xxx.

4. Save the file and close.
5. Change the user-pre file permission back to the way it was:
sudo chmod 440 /etc/firestarter/user-pre

6. Restart Firestarter:
sudo /etc/init.d/firestarter restart

Thanks to Arun for the tips.