Saturday, November 8, 2008

Copying Large Files (>4GB) from a Synology to a USB Drive

Problem:

I have a few very big files (>4GB) on my Synology Disk Station 207+, that I needed to copy onto my MediaPlayer. This seemingly simple task actually ended up being a ton of headaches (BTW, I couldn't copy these files onto my 16GB thumbdrive either)

Here's some background on the problem and how I solved it.

1) My MediaPlayer (which is really just a 2.5" hard drive enclosure with some firmware to play media files) was formatted as FAT32. Problem, though, FAT32 has a file size limit of 4GB.

2) Synology Disk Stations can not write to NTFS partitions (I found out the hard way after doing a one way conversion)

3) The max size for a FAT32 partition is 200GB

4) Converting a FAT32 partition to NTFS made the files on the MediaPlayer invisible to the firmware

I didn't want to cripple my MediaPlayer, so my eventual solution was to divide the 320GB hard drive in my MediaPlayer to two partitions:

A 200GB FAT32 partition, that worked fine for copying files <4GB from my Synology to it (I have yet to see a media file larger), and was compatible with the Media Player's firmware.

A 100GB ext3 partition, which supported files >4GB, and also was writable from my Synology File Manager.

The problem is that the Synology DiskStation only automounts the first partition on a USB Drive. I found this blogger here: http://tomch.com/wp/?p=59 that describes how to mount the second partition in a drive. I'm afraid the information might be lost so here's a cut and paste from his site.


If you plug an external USB drive into your Synology Diskstation, it gets automounted and you can use it right now.
But NOT if your drive has multiple partitions…because the DS only takes care ofthe first partition on an external disk!

In my case, the first partition was a small leftover from another usage, and my main data was on partition 2. Therefore, I had to manually mount the 2nd partition:

* (You need telnet or SSH access enabled on the DS for this - check out the patches from Synology to turn on these features)
* Using “fdisk -l” gave me a list of available drives
* Lo and behold, my main partition was visible as sdb2
* Therefore, I simply had to do “mount -t ntfs /dev/sdb2 /volumeUSB1/usbshare” to mount it instead of the empty one that got mounted automatically.


You need to login to the DiskStation as the root user (same password as your admin user) to be able to mount/umount.

In my case, I had an ext3 partition as my secondary partition, so I had to pass ext3 as my parameter instead of ntfs. Additionally, I had to run e2fsck on my partition to fix errors on my partition before I could mount it. I think it's because I used Partition Magic to format the 2nd partition as ext3.

Once the files were brought over to my ext3 partition on my MediaPlayer, I brought them over to my Windows XP laptop using ex2ifs. Supposedly ext2fsd has better support so if I were to do it again I'd use that.

Monday, November 3, 2008

Ampersand Characters (&) in Oracle SQL scripts

An ampersand when detected inside a script will prompt SQL*PLUS to interactively ask for the value to use for the variable (It thinks anything prepended with an & is a variable).

To disable this behavior, you can specify to use 'set scan off;' as the first line in your SQL script:

set scan off;

INSERT INTO "4XX_DATA" ( ID, DATAAREAID, ACCOUNTNUM, USE_AXAPTA_NAME, AXAPTANAME,
USE_AXAPTA_ADDRESS, AXAPTAADDRESS, AXAPTAADDRESSLN2, AXAPTACITY, AXAPTASTATE, AXAPTAAXAPTAZIPCODE,
AXAPTAPHONE, SENTTODB, NOT_SENT_TO_LAWSON, USE_MATCH_ADDRESS, PRIMARY_ADDRESS, SECONDARY_ADDRESS,
CITY2, STATE2, ZIP_CODE, ZIP_4_CODE, MATCHZIP4, MATCHGRADE, CONFIDENCE_CODE, DUNS2, BUSINESS_NME,
SECOND_NME, USE_PRIMARY_ADDRESS, PRIMARY_ADDR_1, PRIMARY_ADDR_2, CITY_NME_50, STATE_ABBREV_10,
PRIMARY_ZIP, COUNTRY_NME_50, COUNTY_NME_45, USE_MAIL_ADDRESS, MAIL_ADDRESS_64, MAIL_ADDRESS_2,
MAIL_CITY_50, MAIL_STATE_ABBREV_GMDI, MAIL_ZIP, PHONE_16, FAX_NBR_NT_FMTD, GBL_ULT_DUNS_NBR,
HDQ_DUNS_NBR, SIC_1_32, DUNS, REASON__NOTES, PRIMARYZIP5, PRIMARYZIP4, MAILZIP5, MAILZIP4,
DNB_ADDRESS, DNB_CITY, DNB_STATE, DNB_ZIP_5, DNB_ZIP_4, DNB_COUNTY, DNBADDRESSLN2, RECFROM,
AXAPTACOUNTRY, DNB_COUNTRY, AXAPTACOUNTY ) VALUES (
'687', '435', '000181', 'FALSE', 'A & J Conti, Inc.', 'TRUE', '252 Hyde Park Ave.'
, NULL, 'Boston', 'MA', '02130', '617 328 4453', 'TRUE', 'FALSE', 'FALSE'
, '244 Bodge Hill Rd', NULL, 'Moultonboro', 'NH', '03254', '3410', '03254-3410', 'ZZZZZZZZZZZ'
, '0', NULL, 'A & J Conti, Inc.', NULL, 'FALSE', NULL, NULL, NULL, NULL, NULL, NULL
, NULL, 'FALSE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '000000000'
, NULL, NULL, NULL, NULL, NULL, '252 Hyde Park Ave.', 'Boston', 'MA', '02130', NULL
, 'SUFFOLK', NULL, NULL, 'US', 'US', 'Suffolk');

The culprit variable is the '& J' for the value of the AXAPTANAME column ('A & J Conti, Inc.')

For the nerds out there - 7za command line

Here's how to compress all the contents of a directory using 7zip on linux. This will create a new file called Cushman8-371-appliance.zip. Specifying -tzip means to use the most compatible zip format.

This is necessary on linux as zip on linux doesn't support files larger than 4GB.

[purisma@as01 virtual_machines]$ 7za a -tzip -r Cushman8-371-appliance.zip mydatafordnb-3.7.1-Cushman8/*

Issues with ETL from MS Access to Oracle via Access Export

So I just got a Excel spreadsheet that had a bunch of TRUE/FALSE values spread through various columns. I opened this Excel spreadsheet with MS Access, in preparation exporting it to Oracle.

When I tried to export this spreadsheet to my Oracle database via the Export via ODBC() option in ACCESS, I would get a strange TRANSLATION PROTOCOL ERROR.

I checked in my MS ACCESS Design View, and saw that these columns were of datatype YES/NO (I had never seen this datatype before, so I suspected this was the culprit of the TRANSLATION PROTOCOL ERROR).

I tried numerous times to force the YES/NO datatype to become TEXT by changing the first few values from FALSE to FAWLSE, NO, etc and nothing really worked until I did a wholesale search and replace of every TRUE to 'HELL YES' and every FALSE to 'HELL NO' within Excel. Once I did that, I opened the spreadsheet in ACCESS, and exported to my Oracle database with no problems.

Now for some updates to set the values back.... argh