Thursday, 24 February 2011

CSV files in MS Excel and Oracle Open Office

My input file is created in MS Excel, it is 1 row of data with 3 columns (in the second column there is a newline character):


When I save this file as .csv in excel I get following structure:
COL1,COL2,COL3
³ó ?????,"2 äöü ??? lines
In 1 field ???",+123456

File is corrupted

When you open this file in Excel no multibytes and even lost "+" in phone number but the new line character properly inserted.



When I save the input Excel file as Unicode Text .txt the Unicode (MS Unicode ucs-2le) all the issues with .csv will be resolved (while saving).
COL1 COL2 COL3

łóś ещьул "2 äöü ыва lines
In 1 field ещь" +123456


Unfortunately while opening such file with Text Import Wizard:





In third step you can change type to Text (to rescue phone number):


After clicking "Finish" the result is dissapointing:

Multibytes and phone number is properly shown, but the newline is broken.

When you change the extension of the previous Unicode File from .txt to .csv and double click on it to open it with Excel you will have new line and multibytes but the phone will be corrupted:


When you try to open the same Unicode .csv file with Oracle Open Office:
File -> Open -> .csv file
In Import Wizard you set character set to Unicode (it's MS Unicode), delimiter to Tab and column types to Text (to rescue phone number):


Success story:



The question is:
"Why excel cannot properly open a CSV file created by itself?"

Or maybe there is a workaround (without modyfing the CSV file) to do so?

Oracle Open Office FTW!!!

Friday, 22 October 2010

validating emails in oracle db using java stored function => and testing from ruby

1. email validation function is written in java (flexibility) and compiled as stored procedure in oracle db (performance)

1a) write EmailValidations.java:
import java.lang.String;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

class EmailValidations {
public static int is_valid(String email) {
if (email == null || email == "") return 0;

String upper_email = email.toUpperCase();

Pattern p = Pattern.compile("^[\\w\\.-]*[^\\.]@([\\w\\-]+\\.)+[A-Z]{2,4}$");
Matcher m = p.matcher(upper_email);
return m.matches() ? 1: 0;
}
}


1b) load and compile java class into your oracle db:
$ loadjava -u scott/tiger@emeadb11 -v -resolve EmailValidations.java
--> replace scott/tiger@emeadb11 with your valid username/password@tnsname_entry


2a). create package that will contain your function
create or replace PACKAGE             "MISC_PKG"
IS
FUNCTION is_email_valid (email_address in varchar) RETURN number;
END MISC_PKG;
/


2b). create package body:
create or replace
PACKAGE BODY "MISC_PKG" AS

FUNCTION is_email_valid (email_address in varchar) RETURN number
AS LANGUAGE JAVA
NAME 'EmailValidations.is_valid(java.lang.String) return int';

END MISC_PKG;
/


3) test with ruby-plsql

3a) spec_helper.rb that will contain your credentials and include rspecs and ruby-plsql gems
require 'rubygems'
require 'rspec'
require 'ruby-plsql'

DB_USER = "scott"
DB_PWD = "tiger"
DB_SRVR = "emeadb11"

plsql.connection = OCI8.new DB_USER, DB_PWD, DB_SRVR
p "connected"

plsql.connection.autocommit = false

RSpec.configure do |config|
# config.filter_run :focus => true
config.after(:each) do
plsql.rollback
end
end

3b) write ruby specs spec_is_email_valid.rb
require File.dirname(__FILE__) + '/spec_helper'

describe "check is_email_valid function" do

it "should return 1 for valid emails" do
plsql.misc_pkg.is_email_valid("c123@o2.pl").should == 1
plsql.misc_pkg.is_email_valid("c@o2.pl").should == 1
end

it "should return 0 for invalid emails" do
plsql.misc_pkg.is_email_valid("c123@o2,pl").should == 0
plsql.misc_pkg.is_email_valid("c@o2pl").should == 0
end

it "should return 0 as in email_err_reason" do
plsql.misc_pkg.is_email_valid("S.J@GMAIL").should == 0
plsql.misc_pkg.is_email_valid("SY.STORT").should == 0
plsql.misc_pkg.is_email_valid("01516081607").should == 0
end


it "should return 0 when invalid characters in it" do
plsql.misc_pkg.is_email_valid("J.AMS.@CATS.ORG").should == 0
plsql.misc_pkg.is_email_valid("STNJ@ISON.CO.ZA/JACO@VTR.CO.ZA").should == 0
end

it "should return 0 when null email" do
plsql.misc_pkg.is_email_valid("").should == 0
plsql.misc_pkg.is_email_valid(nil).should == 0
end
end


4) you can test it with:
$ rspec spec_is_email_valid.rb 
"connected"
.....

Finished in 5.64 seconds
5 examples, 0 failures


You can easily extend your tests once the change was done to your is_valid() java function.

For more information on testing pl/sql functions from ruby see excelent blog (author of ruby-plsl):
http://blog.rayapps.com/2009/11/27/oracle-plsql-unit-testing-with-ruby/

Sunday, 29 August 2010

1und1.de domain with heroku rails app

1. assuming you have an app on heroku appname.heroku.com
2. change CNAME entry for DNS on 1und1.de
- Domains
- mark your domainat1und1.de and select DNS -> Einstellungen Bearbeiten
- for CNAME entry put: appname.heroku.com
- apply and wait couple of hours until it’s populated
3. in your heroku app you need to add an add-on “Custom Domains” (Basic is free)
$ heroku addons:add custom_domains
Adding custom_domains to ... FAILED
! Please verify your account before installing this addon
! For more information, see http://docs.heroku.com/billing
! Verify now at http://heroku.com/verify

as you can see you need to verify your account before (credit card needed -> no charge applies)

when it’s done:
$ heroku addons:add custom_domains:basic
Adding custom_domains:basic to ... done

$ heroku domains:add www.domainat1und1.de
Added www.domainat1und1.de as a custom domain name to appname.heroku.com
$ heroku domains:add domainat1und1.de
Added domainat1und1.de as a custom domain name to appname.heroku.com

FULL instructions under: http://docs.heroku.com/custom-domains
 

Tuesday, 20 October 2009

oracle db - how to identify compilation errors

1.
select * from user_errors;

2.
SELECT RTRIM(INITCAP(e.type))||' '||e.name||': Line '||
TO_CHAR(e.line)||' Column '||TO_CHAR(e.position) linecol,
s.text sourceline,RPAD(' ',(e.position - 1))||'^' errpos,
e.text error
FROM user_source s,user_errors e
WHERE e.type = s.type
AND e.name = s.name
AND e.line = s.line
ORDER BY e.name,e.sequence

The latter one will give you the exact line where the error occcurs

Wednesday, 23 September 2009

encrypting partitions with LUKS in Linux

with these commands we encrypt a full partition located under /dev/sde1
(UUID=7a89446f-c43f-41b1-bc2b-99c74c64cdeb)

In the below steps I use dcfldd (which is much more user friendly than dd and gives you progress status) and cryptsetup.

sudo apt-get install dcfldd cryptsetup

sudo dcfldd if=/dev/urandom of=/dev/sde1 statusinterval=10 bs=10M conv=notrunc
sudo cryptsetup luksFormat /dev/sde1
sudo cryptsetup luksOpen /dev/sde1 maxtor_encrypted
sudo mkfs.ext3 /dev/mapper/maxtor_encrypted
sudo mkdir /mnt/maxtor_enc
sudo mount /dev/mapper/maxtor_encrypted /mnt/maxtor_enc -t ext3 -o noatime,nodiratime


it is safer to use UUIDs instead of /dev/sde1 because even if order of the drives changes (USB drives, or because of your BIOS) you always can uniquely identify the one with the unique UUID.
You can get the UUIDs of all of your disks' partitions by running
$ blkid

Now let's write to scripts for mounting and umounting our encrypted drive:
chris@emeadb:~/bin$ cat mycryptmount
#!/bin/bash
export PATH=/usr/local/bin/:/bin/:/usr/bin/
echo "mounting encrypted drive"

df | grep maxtor_enc && sudo /home/chris/bin/mycryptumount
echo "maxtor_enc umounted"

sudo cryptsetup luksOpen /dev/disk/by-uuid/7a89446f-c43f-41b1-bc2b-99c74c64cdeb maxtor_encrypted
sudo mount /dev/mapper/maxtor_encrypted /mnt/maxtor_enc -t ext3 -o noatime,nodiratime

chris@emeadb:~/bin$ cat mycryptumount
#!/bin/bash
export PATH=/usr/local/bin/:/bin/:/usr/bin/

sudo umount /dev/mapper/maxtor_encrypted
sudo cryptsetup luksClose maxtor_encrypted


chmod +x both of them

now you are able to mount with
$ mycryptmount

and umount with
$ mycrypumount

Sunday, 13 September 2009

Thursday, 28 May 2009

ruby oracle oci8 UTF8 corruption

I have a small script oci1.rb:
require 'oci8' 
conn = OCI8.new("test","test","test_db")
File.open("out.txt","wb") do |out|
tabs = conn.exec('SELECT * from test_utf8') do |r|
out.write(r.join(','))
out.write("\n")
end
end
conn.logoff

that runs perfectly on windows producing from my test_db:
polish,łóśżć 
russsian,фывафыва
german,äöü

when I run exactly the same on my linux box:
polish,loszc 
russsian,????????
german,aou

I tested then a small script that reads and writes UTF8 on linux to be sure my linux terminal can display UTF8
(read_write_utf8.rb):
File.open("utf8_out.txt","wb") do |out| 
File.open("utf8.txt","r").each do |line|
out.write(line)
end
end

and that works good!
chris@emeadb:~/work/ruby/oci$ ruby read_write_utf8.rb 
chris@emeadb:~/work/ruby/oci$ cat utf8.txt
polish,łóśżć
russsian,фывафыва
german,äöü
chris@emeadb:~/work/ruby/oci$ cat utf8_out.txt
polish,łóśżć
russsian,фывафыва
german,äöü


Finally the issue was with my NLS_LANG variable that was not set in the Linux box on the account I used for running the script.

I tried first to set it up in the ruby script itself:
ENV['NLS_LANG']='AMERICAN_AMERICA.UTF8'

that did not work.

the solution was to set it in the shell.
export NLS_LANG=AMERICAN_AMERICA.UTF8


you can set it in your .bashrc (depending on the shell you use) to make it default for the account.