Accessing Remote Drive in Linux via SSHFS

There may be a case where you may want to access a certain directory or drive on a remote machine, and mount it to your local PC. For example, I have a headless CentOS box running Apache where all of my web application codes are sitting. Editing those web application codes are a nightmare when there is no Integrated Development Environment (IDE). I can install IDE (like Eclipse) on my Ubuntu box, but I need a way to map the remote /www folder to my local Ubuntu folder. This is very easy in Linux using sshfs.

Install sshfs:
$sudo apt-get install sshfs

Create your local directory and change the permission:
$sudo mkdir /media/directoryname
$sudo chown your-username /media/directoryname

Where “directoryname” is the name of the directory on your local computer where you want to access the files from the remote computer. Let say I want the files on the server to be available at /media/directoryname.

Add yourself to the group “fuse”.
$sudo adduser your-username fuse

To mount the remote directory to your local:
$sshfs your-username@myserver:/www/html ~/directoryname

To unmount:
$fusermount -u ~/directoryname

Drop it, Dropbox!

It’s been awhile since I’m searching for a perfect online backup application. I’ve tried many, but none have really captured my attention. Not until I’ve tried Dropbox.

Dropbox is a free, multi-platform, online backup and synchronization application. My first attraction to Dropbox is because the client tools supports Linux. It also suports Mac OSX, and oh yeah, Windows too. Installation on my Ubuntu Hardy laptop is a breeze. Just download the debian package, and the rest of the installation is pretty straight forward. No extra tricky setup, configuration or anything. Upon the completion of the installation, just point your Dropbox location to a folder path on your machine, and you ready to go.

  An icon installed on the taskbar for easy access.

Online Backup
Dropbox currently provides 2GB of storage, which is sufficient for me at this time to backup all of my important files. Backing up files is very easy. Just open your Dropbox folder, and copy-paste the file(s) that you wish to backup to that folder.

Dropbox location on your folder.

Seamless sync
I have a couple of computers at home, which is running Ubuntu 8.04, and also a Windows 2003 machine running at my office. Installing Dropbox client on all of these boxes enabling me to sync all my files between those boxes, seamless, and without much effort at all. Just make sure Dropbox client is running, and those files will be sync automatically.

Dropbox website
Just in case you want to grab or upload your files, but Dropbox client are not installed, you can always go to Dropbox site.

Dropbox web interface

Another cool feature is, the files you stored has “Revisions”. Meaning, you don’t have to worry if any of our files get accidentally deleted or overwritten, since you can always retrieve deleted or the previous version of your files.

Dropbox revision feature

Feel free to watch Dropbox screencast for more info on their features and functions. Have you tried Dropbox or any equivalent online backup application? Feel free to share your experiences.

Save MySQL Output to a Text File

I have just found out a nifty little trick regarding MySQL output the other day. I just had to put this up as a reminder to myself, and also to share with the viewers.

Let say, you want to have the output of your SELECT statement created to a text file. The MySQL database server could be sitting on a different box (or the same machine, but that doesn’t really matter). All you have to do is issue a command in your terminal:

echo "select * from MyTable" | mysql -h mydatabaseserver -u myusername -pmypassword mydatabase > output.txt

That’s it! Just replace the <MyTable>to your table of choice (or just replace the whole SELECT statement to suit your needs), <mydatabaseserver> to your database server name, <myusername> to your database user name, <mypassword> to your password, and <mydatabase> to your database.
The piping ‘>’ will redirect your output to a text file on your desired location.

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
      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
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
         SET @textpos = @textpos + @chunklen

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

         SET @leftover = @tmpstr

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


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

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.