Using OpenOffice Base to bring a SQL table into a Spreadsheet

By Mike • November 18, 2008 • No Comments

This was rather simple, actually. The task at hand was bring down information from an iSeries database into a format I could use use on another system. For this kind of quick and dirty work, I like to use a spreadsheet. We’re only talking about 1800 rows of data or so, and of those, I really only needed 2 columns.

1. Create an ODBC source via your Control Panel. If this is over your head, you’re in the wrong place. In my particular case, the source was connected to an IBM iSeries with IBM’s iSeries Access ODBC Driver. That driver is included with IBM iSeries Access for Windows. (Everything IBM has incredibly long names. Take “IBM WebSphere Development Studio Client for iSeries,” for example, which is both the name of the program and the name of the folder containing it. To be fair IBM has since renamed it “IBM Rational Application Software Delivery Platform.” Why not just call it Eclipse and be done with it?)

2. Start OpenOffice, and use the “Connect to an existing database” option. This gives you a pulldown from which you’ll select ODBC, and browse to the source you created in step 1. You’ll be prompted to “save the database,” which produces a file OpenOffice calls a “Database document.”

3. Base opens up its database view. In the tables section, navigate to the table you want to copy. Right click on the table, and then copy.

4. Open up OpenOffice Calc. In cell A1, paste. Boom! The table’s data is in your spreadsheet.

And of course this might freeze up everything if there’s too much data to fit over your VPN. Not to worry - you can always create a View to narrow down the fields to just what you need. The same instructions apply for a View as for a Table.

Categories: Tech
Tags: , ,

Magento - display SKU on product page

By Mike • November 16, 2008 • No Comments

Magento’s default theme doesn’t display the SKU on the product page (at least as of v1.1.6). As per this discussion thread, it’s fairly easy to add by editing the template:

In whichever theme you’re using, in the file template/catalog/product/view.phtml, add the line:

<?php echo $this->htmlEscape($_product->getSku()) ?>

Here’s a few lines of my view.phtml to give you some context:

40     <div class="product-essential">
41     <form action="<?php echo $this->getAddToCartUrl($_product) ?>" method="post" id=”product_addtocart_form”>
42
43         <div class="product-img-box">
44             <?php echo $this->getChildHtml('media') ?>
45         </div>
46
47         <div class="product-shop">
48             <h3 class="product-name"><?php echo $this->htmlEscape($_product->getName()) ?></h3>
49             <?php echo $this->htmlEscape($_product->getSku()) ?>
50
51             <?php if ($this->canEmailToFriend()): ?>

Here’s the code in action, showing our SKU CND1903, a Canon EOS 50D (body only).

We are also reminded in the Magento thread that we should not use the default theme in place, as it’s subject to changes from release to release. Instead, you should create a copy of the default theme (if that’s what you want to start with), and work with that. See also: http://www.magentocommerce.com/design_guide/articles/working-with-magento-themes#head-how-to-create-a-theme

Categories: Tech, eCommerce
Tags: , ,

grep: unknown directories method

By Mike • November 14, 2008 • No Comments

Thank you LornaJane for your post about “grep: unknown directories method.” This was making me crazy today.

This happens when you’re wildcarding and there’s a file in your directory that starts with a hyphen (”-”).  In my case it was -orders.php: someone had “saved off” copy of a file called orders.php. (People, this is why we use version control systems. Never make backups like this! NEVER!).

In LornaJane’s case she demonstrated removing a file in subversion, but I had to remove my file from a plain old directory. Here’s the command I used, on Fedora Core 8:

rm -i ./-orders.php

Problem solved, the grep’d jawnie found and now back to whatever it was I was doing when this obstacle presented itself.

Categories: Tech
Tags: , ,

ssh login without giving a password RHEL5

By Mike • November 13, 2008 • No Comments

Thank you to Mathias Kettner for his article at http://linuxproblem.org/art_9.html

My need was to pull a text stream from Server B into Server A. It was actually an osCommerce problem: I need a plain text input to UnForm, our form-making software. At the time our backend system gets ready to print a pick ticket for warehouse personnel, I need the backend system to call back to the osCommerce database and grab the packing slip information.

This is due to a limitation in our backend software - we can’t store the part numbers the way they need to be displayed to the customer. The backend software is not an SQL-based solution, so cramming extra fields into the backend is a painful process.

However, we don’t have to: the data exists … drat, on the webserver. That’s OK. With a little ssh program execution we can do it, have it come back to us as text, or simply send the data to the TCP port UnForm is listening on, and let it work its form-making magic.

To get ssh to execute a remote without passing passwords:

Here I’m assuming you already have usernames on both servers, and both servers are RHEL5. If not, please see Mathias’ directions, as he has a few extra tidbits you may need.

1. Logon on to the server that you will be executing the ssh commands from, let’s call that Server A. There, you’ll create your authentication key pairs:

ssh-keygen -t rsa

2. Append the Server A’s public key to Server B’s authorized keys. You’ll of course be prompted for your password - for the last time.

cat .ssh/id_rsa.pub | ssh userB@serverB 'cat >> .ssh/authorized_keys'

3. Sign in to serverB, because you have to set the permissions correctly on the authorized_keys file:

ssh userB@serverB
(enter password)
userB@serverB> chmod 640 ./.ssh/authorized_keys

4. You should be good to go! Try a simple test (from serverA):

userA@serverA> ssh userB@serverB "pwd"
/home/userB

Categories: Tech
Tags: , ,

Next Page »