PostgreSQL: Invalid byte sequence for encoding UTF8

I have a comma delimited file (.csv) contains stock master data and wanto to import them into PostgreSQL table .During importing, I am facing Invalid byte sequence for encoding "UTF8" error message.

C:\>psql -Umyname -dmydb -p5490
--1. do import
mydb=# \copy myshema.stocks from C:\temp\inv.csv with delimiter as ';'csv
ERROR: Invalid byte sequence for encoding "UTF8": 0xa0
CONTEXT:  copy stock, line 26120

--2. check my client encoding
mydb=# show client_encoding;

client_encoding
----------------
UTF8
(1 row)

--3. modify my client encoding
mydb=# \client_encoding LATIN1

--3a. check current client encoding
mydb=# show client_encoding;

client_encoding
----------------
LATIN1
(1 row)
--4. Re do importing
mydb=# \copy myshema.stocks from C:\temp\inv.csv with delimiter as ';'csv

--5. reset client_encoding;

That is it.

Create a Control to act like a Button

System.Windows.Forms.UserControl provides an empty control that can be used to create other controls. We might consider to inherit the class to create a control with custom appearance, feature and behaviour.
I then create a control that functionally same as standard button, but my control could not be be assign to AcceptButton and CancelButton property of a form. To allows the control to act like a button on a form, just implement the IButtonControl interface to the control.

public class FerBtn : System.Windows.Forms.UserControl, System.Windows.Forms.IButtonControl
{
}

After visit this, I copy and paste the DialogResult property, the NotifyDefault and PerformClick methods, and add a definition for IsDefault.

private bool mIsDefault;
[Browsable(false)]
public bool IsDefault 
{
  get { return this.mIsDefault; }
  set { this.mIsDefault = value; }
}

Now this control could be assign to AcceptButton and CancelButton property of a form.

To Get Distinct Rows By specific columns

My purpose today is to get the rows with unique data based on criteria of uniqueness value on the column, including combinations of column.  As you may think, just use the DISTINCT clause. You are right! Let's try with PostgreSQL.

Firt, list the data using this statement:

SELECT a.nik, a.nama, g.ket as job, h.akunno, h.alokasi
FROM ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama
=========================================================
nik   nama          job                   akunno  alokasi
=========================================================
100.200  ALBERT    Manajer                400     0.00
100.300  JHONI     Danru Satpam           423     1.00
100.400  ZAENAL    Anggota Satpam         423     1.00
100.500  ROBI      Anggota Satpam         423     1.00
200.400  TEDDY     Operator St. Kempa     603.07  0.40
200.400  TEDDY     Operator St. Kempa     603.08  0.40
200.400  TEDDY     Operator St. Kempa     603.09  0.20
300.500  JOKO      Operator Kolam Limbah  603.11  0.50
300.500  JOKO      Operator Kolam Limbah  603.12  0.50
300.650  SEPTIAN   Operator St. Kempa     603.09  0.20
300.650  SEPTIAN   Operator St. Kempa     603.07  0.40
300.650  SEPTIAN   Operator St. Kempa     603.08  0.40
400.600  OPA OPO   Kerani I TUK           401     1.00
----------------------------------------------------------

DISTINCT 
DISTINCT will remove all duplicate rows from the result set and one row is kept from each group of duplicates. Let's try with DISTINCT clause:

SELECT DISTINCT a.nik, a.nama, g.ket as job, h.akunno, h.alokasi
FROM ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama

What is your result?
I've got same result set with previous!


DISTINCT ON
Now try with this command:

 SELECT DISTINCT ON (nik,nama) a.nik, a.nama, g.ket as job,h.akunno, h.alokasi
FROM  ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama

=========================================================
nik   nama          job                   akunno  alokasi
=========================================================
100.200  ALBERT    Manajer                400     0.00
100.300  JHONI     Danru Satpam           423     1.00
100.400  ZAENAL    Anggota Satpam         423     1.00
100.500  ROBI      Anggota Satpam         423     1.00
200.400  TEDDY     Operator St. Kempa     603.07  0.40
300.500  JOKO      Operator Kolam Limbah  603.11  0.50
300.650  SEPTIAN   Operator St. Kempa     603.09  0.20
400.600  OPA OPO   Kerani I TUK           401     1.00
---------------------------------------------------------
Those are the result set that I want.

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY. Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

Eclipse: Tomcat Server ports are already in use

Working with Eclipse IDE, I use Apache Tomcat to hosting applications on Windows. I forgot what I've done when encounter this problem:"Several ports required by Tomcat  are already in use."

Yes, there must be another process using the ports. Apache Tomcat service was not started on Windows Sevices. Also I've checked using web browser http://localhost:8080/ and I got status report 404 message. Yes Apache Tomcat still run and listening on 8080. I suspect that the problem arises because using Eclipse incorrectly.

To list the listening port using: C:>NETSTAT -na | find "LISTENING"

Yes they were running. The running process could not stopped when exit from Eclipse IDE. Oh yeah, i've terminated the Eclipse IDE abnormally using End Process on Task Manager. So I have to kill the PID:

C:>TASKLIST /FI "PID eq 5740"
C:>TASKKILL /F PID 5740

then re run the server from Eclipse environment.

It works.

Remove Title Atributes of Menu Links from Taxonomy Generated Menu

Drupal 7: To generate menus using Taxonomy menu, we do the following steps:

1. http://mysitedom.com/admin/structure/taxonomy/my-vocabulary/list

2. Click the Edit tab

3. Select MENU location from list.

3. Check the option to "Select to rebuild the menu on submit".

4. Click the Save button, so a menu structure will be generated for the selected Vocabulary.

But I have a problem with the title attribute of URLs that are automatically filled in by the descriptions of vocabulary terms. I thought this is quite annoying, especially against the long description.

Do not hack core. This phrase is commonly heard in the Drupal community.

For my requirement, I've did this:

Modify taxonomy_menu.module file under \myhome\sites\all\modules\taxonomy_menu\ directories, to make changes of the function_taxonomy_menu_save. At line number 568-569 which contains the command:

'options' => array('attributes' => array('title' => trim($item['description'])

? $item['description'] : $item['name'])),

commenting out the line:

/* 'options' => array('attributes' => array('title' => trim($item['description'])

? $item['description'] : $item['name'])), */

After hitting my menu at http://mysitedom.com/admin/structure/menu/manage/my-taxo-menu, click Edit of each generated menu link to clear 'Description' field, I repeat the four steps above to rebuild menu, now Title attribute dissapear from menu link.

Forgot Debian Root Password

Here I tell you what I did

1. Reboot server.
2. Press downarrow key, and grub-boot loader screen appears.
3. I select the line start with "kernel /boot/vmlinuz-2.6.26-2.686 root=/dev/sda1 ro quiet".
4. Press 'e' to edit the argument and modify the line to: "kernel /boot/vmlinuz-2.6.26-2.686 root=/dev/sda1 rw single init=/bin/bash"
5. Press 'b' to boot
6. Bash prompt appears....but I was facing the problem that the keyboard did not work..(I've got error messages something like:"Device not accepting", "Unenumerate", that tell USB port not recognized)....I am using KVM Switch. I did unplug the USB keyboard from KVM Switch then plugging in another USB keyboard.
7. After repeating steps 1-5, I was able to type using the new keyboard.
8. I was trying using mount command such as:

#mount -n -o remount rw /

#mount -rw -o remount /

#mount -t ext3 /dev/sda1 /usr

but passwd command failed. ("bash: passwd: command not found")

(Perhaps it would works if I (re)mount rest of all my partitions in read/write (rw) mode such as /usr /var etc, but i did not do at the time).

What I did then was disabling the root password from the password file using Nano editor. I've check that the second data field in /etc/passwd is “x” for every username, so the system uses shadow passwords, and I must edit /etc/shadow.

10. #nano /etc/shadow

I changed==> root:this_is_the_encrypted_passwd:15401:0:99999:7:::

to this ====> root::15401:0:99999:7:::

(Edit the second data field in the password file so that it is empty.)

Reboot the system and at the login prompt, type root (without password).

Verifying numbers with the Luhn algorithm

The Luhn algorithm also known as the "modulus 10" algorithm, is a checksum formula used to verifying a variety of identification numbers, such as credit card numbers and IMEI numbers.

Luhn algorithm provide simple and inexpensive method for computing check digits and provide a simple device for verifying numbers which have a single check digit appended. The Luhn Mod-10 Method is used in a checking system for multi-digit numbers to indicate whether, in transmitting a number, an error has been made, such as a transposition of the digits. It may be used where a great many parts are ordered, manufactured, invoiced, shipped,and billed by multi-digit numbers.

When a number is first assigned to a new part a check digit is computed. This single check digit is appended to the righthand of the part number. The value of this check digit is so computed that in verifying the number by cross addition of multiple digits of the number and the check digit, in accordance with a rule of substitution, the result will be a zero.

e.g: The multi-digit number is 3046016202394 and the check digit is 9. The whole number is 30460162023949.

3 0 4 6 0 1 6 2 0 2 3 9 4 9
x2 x1 x2 x1 x2 x1 x2 x1 x2 x1 x2 x1 x2 x1
-- -- -- -- -- -- -- -- -- -- -- -- -- --
6 0 8 6 0 1 12 2 0 2 6 9 8 9
6+ 0+ 8+ 6+ 0+ 1+ 3+ 2+ 0+ 2+ 6+ 9+ 8+ 9 = 60

In the above summation, any two-digit product is included as the sum of its two digits. In that case the sum of number 12 is 3 (1 + 2). Since the sum of the digits in the bottom row is 60, which is divisible by 10, so the Number is valid because the 60/10 yields no remainder, or 60 mod 10=0. The computation PASSED since the result is zero.

Here I use PostgreSQL stored function to do generate a check digit for string of numbers.

CREATE OR REPLACE FUNCTION gen_lun10_checkdigit(character varying)

RETURNS character AS

$BODY$

SELECT ((10 - SUM(doubled_digit / 10 + doubled_digit % 10) % 10) % 10)::character

FROM (SELECT MOD( ($1::int8 / (10^n)::int8), 10::int8 ) * (2 - MOD(n,2)) AS doubled_digit

FROM generate_series(0, length($1)- 1) AS n) AS doubled_digits ;

$BODY$

LANGUAGE sql IMMUTABLE STRICT

COST 100;

To find the check digit, call the function by sending a multi-digit number, e.g:

select gen_lun10_checkdigit('3046016202394') as checkdigit;

checkdigit

----------

9

In real world, the Lun algorithm is widely used to verifying numbers such Credit Card and IMEI numbers.

To work with these I wrote this function in PostgreSQL.

CREATE OR REPLACE FUNCTION gen_lun10_number(pprefix character varying,pnumextralen integer) RETURNS character varying AS
$BODY$
DECLARE
vfmt character varying(16);
vnumber bigint;
vnum1 bigint;
vnum2 bigint;
x text;
vresult character varying(19);
BEGIN
pnumextralen := pnumextralen - 1;
vfmt := REPEAT('9', 16);
vnum1 := REPEAT('9', pnumextralen)::int8;
vnum2 := REPEAT('1', pnumextralen)::int8;

SELECT TRUNC (random() * ( vnum2 - vnum1) + vnum1) INTO vnumber;
x = pprefix || vnumber :: text;

vnumber = x::int8;

SELECT 10 * vnumber + ((10 - SUM(doubled_digit / 10 + doubled_digit % 10) % 10) % 10)::int8 FROM (SELECT MOD( (vnumber/ (10^n) ::int8), 10) * (2 - MOD(n,2))::int8 AS doubled_digit FROM generate_series(0, LENGTH(x)-1) AS n) AS doubled_digits INTO vresult;

IF (substr (pprefix,1,1)='0') THEN
vresult := '0' || vresult;

END IF;
RETURN vresult;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;


You could also find PostgreSQL functions here or other languages in here.

A. Bank Card Number

A bank card number is the primary account number found on credit cards and bank cards. It has a certain amount of internal structure and shares a common numbering scheme. Credit card
numbers are a special case of ISO/IEC 7812 bank card numbers.

An ISO/IEC 7812 number is typically 16 digits in length. It consists of:

• a six-digit Issuer Identification Number (IIN), the first digit of which is the Major Industry Identifier (MII),
• a variable length (up to 12 digits) individual account identifier,
• a single check digit calculated using the Luhn algorithm.

Using the above function, given prefix and length, we got these random number, and I use this site to check credit card.

AMEX

select gen_lun10_number('37',13);--->379385470967314

select gen_lun10_number('34',13);--->344631823379950

VISA

select gen_lun10_number('4539',12); --->4539442682130873

select gen_lun10_number('4556',12);--->4556142088753892

DISCOVER

select gen_lun10_number('6011',12);-->6011865781139571

select gen_lun10_number('622126',10);-->6221269895829522

DINNERS

select gen_lun10_number('304',11);--->30460162023949

select gen_lun10_number('305',11);--->30570718114024

To generate 10 number with prefix '12345' with length of appended numbers 11, simply I use this call.

SELECT c.result

FROM (SELECT gen_lun10_number('12345',11) AS result

FROM generate_series(0,9)) as c;

result

-----------------

1234517933942749

1234569174718767

1234582260609360

1234579067268580

1234516226418235

1234565468996135

1234535863889839

1234535902922765

1234594959690825

1234569877669242

B. IMEI

The International Mobile Equipment Identity or IMEI is a number, usually unique, to identify GSM, WCDMA, and iDEN mobile phones, as well as some satellite phones. It is usually found printed inside the
battery compartment of the phone. The IMEI number is used by the GSM network to identify valid devices and therefore can be used for stopping a stolen phone from accessing the network in that country.

For example, if a mobile phone is stolen, the owner can call his or her network provider and instruct them to "blacklist" the phone using its IMEI number. This renders the phone useless on that network and sometimes other networks too, whether or not the phone's SIM is changed.

The IMEI is only used for identifying the device and has no permanent or semi-permanent relation to the subscriber. Instead, the subscriber is identified by transmission of an IMSI number, which is stored on a SIM card that can (in theory) be transferred to any handset. However, many network and security features are enabled by knowing the current device being used by a subscriber.

To work with IMEI, we have to know IMEI Structure, IMEI number has 15 decimal digits. Actualy it has 14 digits plus a check digit. First 8 digits of IMEI number are Type Allocation Code which will give you the mobile phone brand and model. Other 7 digits are defined by manufacturer (6 are serial number and 1 is check digit).

To view IMEI of your mobile phone, just press: *#06# and press Call button.

Below were some generated IMEI numbers with predetermined prefix and length.

Samsung SGH-A800

select gen_lun10_number('35357800',7);-->353578007008467 , 353578007478082

RiM BlackBerry Bold 9700

select gen_lun10_number('35425504',7);--> 354255042721045 , 354255045759083

Apple iPhone 3G model MB496RS

select en_lun10_number('01174400',7); -->011744003170479 , 011744001398676

Nokia 6210

select gen_lun10_number('449337',9); -->449337138770594 , 449337951410641

These sites give IMEI check and information here or here.

C. Highlights
The Luhn algorithm offers error detection, not offer security, just like a CRC in software. The algorithm help us from accidental errors. By applying the Luhn algorithm using PostgreSQL, I could generate identification numbers for data verification and validation.

You probably interest to read this article that gives inspiration, ...an internal auditor discovered the Luhn algorithm, a simple redundancy check that can be used to validate different identification numbers during fraud and IT audit investigations.

Data Verification and Validation

Verification is a quality control process that is used to evaluate whether a product, service, or system complies with regulations, specifications, or conditions imposed at the start of a development phase. Verification can be in development, scale-up, or production. This is often an internal process.

Validation is a quality assurance process of establishing evidence that provides a high degree of assurance that a product, service, or system accomplishes its intended requirements. This often involves acceptance of fitness for purpose with end users and other product stakeholders. This is often an external process.

It is sometimes said that validation can be expressed by the query "Are you building the right thing?" and verification by "Are you building it right?"

"Building the right thing" refers back to the user's needs, while "building it right" checks that the specifications are correctly implemented by the system. In some contexts, it is required to have written requirements for both as well as formal procedures or protocols for determining compliance.

Verification and validation is done to make sure that the data is accurate and error free as the incorrect data can lead to data deformation or miss understanding of the document.

Data verification checks that the document meets specifications and that it fulfills its intended purpose this can be done by doing some checks like double entry so that the data must be entered twice or proof reading to make sure it is accurate and no errors in it, as for validations , it can be done by coding which means giving a code to specific words so that it would be easier to enter and the probability of mistake will be less ,also format check can be done as it checks that the data are in a specific format, added to that ,spelling and grammar checks for checking the language and writing mistakes.

Data validation checks that data are valid, sensible, reasonable before they are processed it is a quality assurance process of establishing evidence that provides a high degree of assurance that the document accomplishes its intended requirements.

Source