Category: Technology

Empty Strings, Nulls, and MS SQL

I have a database that I am exporting data from. Once it is in text format I run the dos sort command, then finally compress it. Everything was going well till I noticed that my dump files had lines that were being split in two

I started looking at the original file and found it contained Ascii character 0 or Null. So my first response was to try isnull(column,’-‘) in the query, but that didn’t work

I checked the lookup tables and found that a join was matching and returning an empty string.

On reading the BCP manual I found this

out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.

So nulls are converted to empty strings and empty strings are converted to nulls? Who thought this was a good idea?

So I changed my sql to be NULLIF(column,”) and now everyone is happy.

Sometimes you got to hate linux

The boot drive on my file server has been really slow, hdparm was returning some really low numbers and CPU was spiking, which i wasn’t expecting on my new hardware. I decided this morning to do some google searches.

Here is the original hdparm results

sudo hdparm -t /dev/sdf
/dev/sdf:
Timing buffered disk reads: 4 MB in 3.56 seconds = 1.12 MB/sec

Then i added the following in /etc/modprobe.d/blacklist

blacklist ata_generic

and in /etc/initramfs-tools/modules added

pata_atiixp
blacklist ata_generic

rebuilt the initramfs

sudo update-initramfs -u -v

rebooted and now I get

sudo hdparm -t /dev/sdf
/dev/sdf:
Timing buffered disk reads: 220 MB in 3.02 seconds = 72.83 MB/sec

Palm moderating their comments on the official Palm Blog

So I read the official plam blog from time to time and saw a story posted there about phones behaving badly. It then says

Got a pet peeve? Post it below.

So I commented something along the lines of.

Pet peeves let me see, how about the palm web browser blazer that frequently times out the first time you access a website then works perfectly the second time. Then there is a phone that randomly reboots for no good reason, is there ever a good reason to randomly reboot? Then finally there is the fact that I couldn’t post to the official palm blog using my treo 700p on verizon using blazer and instead had to use firefox on a real computer.

Surprising or maybe not the comment was never posted on the Palm site as an official comment so I am recreating it here.

Playing with gl_fusion

I have been using geeklog on a number of websites. Geeklog just released 1.5.0 which enabled openid support and a few other things, but gl Labs just released a drop in replacement for geeklog call glfusion. Unfortunately it wasn’t a drop in replacement for me, not because of GL Labs, but because of Geeklog’s dumb ass way of implementing paths in their new database based configuration settings. I tend to move the paths around when upgrading so I don’t completely bork a site in the process. So after installing glfusion in the glfusion-1.0.0 directory, nothing worked, I just got a message saying “Unfortunately, an error has occurred rendering this page”. So I hacked the error handler to give me something something more useful to point me in the right direction. I then had to manually update the conf values in the database, this brings me to dumb assessed part of the implementation, look at this row


| path_language | s:41:"/www/eduni-dc.org/geeklog-1.5.0/language/"; | text | Core | s:0:""; | 0 | -1 | 100 | 3 |

That s:41 actually means string of length 41. Took me a little while to work that one out. Come on guys have you never head of strlen()? Next why the F…. does this all have to be in one column, if you really need to know that something is a string of 41 characters then put them in a different columns.

So now I have got my rant out of the way, glFusion has some cool stuff it it. It comes with a number of plugins built in and ready to go. It also has mooTools built in to the nouveau theme. See my test page for some of the extra formatting fluff you get

I am most impressed that glLabs is watching the bug tracker for geeklog and has already applied a patch I submitted to geeklog before geeklog has even confirmed it as a bug. So one less thing I need to patch when upgrading my geeklog sites.

If you want to see my first glFusion site go to www.eduni-dc.org

The disadvantage of going electronic for you bills and statements

Today I needed to pull up the last pay slip from my old company. The company in question doesn’t offer the benefit of mailing out payslips, only the ability to view them online. For some dumb reason, like maybe the possibility that you might have to document your pay and contributions to the IRS I figured that i would have access to these records for a reasonable period of time say 2-5 years, but today a few months after leaving the company I get this message…..

Fortunately after my years of dealing with the USCIS I decided to print a copy of my pay slips so I am 99% sure I have the payslip I need at home.

I also noticed a similar thing when I closed a credit card, all of a suddenly you loose access to the online records, the same thing also happened when I closed a bank account.

This whole save the environment go to e-statements and e-bills is just an excuse for the company in question to save on printing and mailing costs. If they really wanted to save the environment they would allow you to access the records for 5 years after closing the account, that way you don’t have to print at your own expense copies of the statements.

So make sure you have copies of all your bank statements, bills, pay slips before you terminate your relationship with a company because you are more than likely going to be loosing access to the online versions of those documents as well.

Fileserver has a new home

I bought a Coolmaster case a few months back from the CompUSA liquidation sale, I didn’t really need it but it was a good price at the time. It sat in the garage till I had time and a reason to do something with it. Last week I bought a cpu/motherboard/memory combination at Microcenter with the thought that I could put it in the case. When I bought the computer guts I wasn’t quite sure what I was going to do with them. Then last week I officially ran out of disk space on the file server. So that was it, I found a purpose for all my new toys. I got Best Buy to price match a western digital 500 GB hard drive and I spent this morning putting it all together. I transfered all the drives from the old file server and added the new 500 gig.

So now I have
AMD 64 dual core X2 4800+
2 Gigs of ram
1 x 320 Western Digital pata drive
2 x 320 Western Digital sata drives
1 x 500 Western Digital sata drive
1 x 500 Seagate sata drive
1 x 500 Western Digital Green sata drive

The first 320 is the OS drive, scratch space, and backup.
The two 320 sata drives are a mirrored pair for personal files and src and mail
the three 500’s are going to be raid 5 and be used for media storage

IP to Numeric

To followup on my previous post about doing ip to numeric address conversions. It turns out that the sql functions I linked to didn’t quite work, they were written with the assumption that you were storing the data as an INT rather than a BIGINT, so for values over 127.0.0.1 it would store the number as a negative number in the database. My source data uses bigint so I got errors back when feeding in data that overflowed the int.

If you are interested I have included the edited functions after the link

Continue reading IP to Numeric

MS SQL numeric to ip

I love the fact that I know SQL and can go against the database directly instead of having to accept someone elses preconceived ideas of what kind of data I need.

I needed to find out all hits in a database from a specific range of IP addresses range. the table stores the IP’s in a numeric form which is a no brainer on mysql you just use inet_ntoa and inet_atoa to convert back and forward, but Microsoft SQL Server (mssql) doesn’t support that so a little googling later I came up with this page, which has some stored procedures for doing the convertion, now I am just waiting for the database to come back with the data. SWEET

New Laptop

So HY was kind enough to bring my new laptop home with her yesterday. After the initial unboxing and savoring that new computer smell I plugged it in. I decided to transfer all my files from my old 12 inch Powerbook since it was rebuilt a few months back and the machine will become a ‘public’ computer for anyone who needs it at the house. After waiting and cursing apple for lying about how long it would take all my files and settings were transferred over. From a normal user experience this is great since it gets you up and running very quickly and everything is set up exactly as you are used to….. but….. I am feel I am missing the ritual that you get when you defile a fresh install of an OS by tweaking, install and customizing till you happy with it.

For a mac my standard install list would be
Firefox (get the beta of V3)
Quicksilver
Adium
Microsoft RDC client (get the new beta version)
Chicken of VNC
Dev Tools
Darwin Ports
For intel macs I would also add vmware fusion (need to try out virtual box)