Thread: Data Migration Wizard Problem

Hi,

I am trying to migrate a MS SQL7 database to Postgres. The Postgres database is UNICODE, and whenever the Migrator encounters an é character, I get an error as follows:

An error occured at: 3/03/2003 1:36:32 PM:

-2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90020)

Rolling back... Done.

sometimes the error is:
        -2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90000)

The actual statement (captured in debug) is:

INSERT INTO "member" ("memberid", "famname", "othername", "title", "streetaddr", "suburb", "city", "state", "postcode", "email", "dob", "age", "gender", "privacydate", "deleted", "enterdate", "enterby", "check1", "check2") VALUES ('XSY0101693', 'Blasé', 'Lisa M', '', 'Unit 15 46 Albany Creek Rd', 'ASPLEY', '', 'QLD', '4034', '', '1969-06-25 00:00:00', '32', ' ', '2001-12-24 00:00:00', '0', '2003-02-24 12:29:44', 'Transfer', '0', '0')

The import works fine until it hits this accented é character, which should be valid for Unicode I presume? I also tried using the escape sequence "\351", but that returned the same error.

If I create the table with SQL_ASCII encoding, then I don't get the error.

Any ideas?

Thanks,

Jim Sharples.

-----Original Message-----
From: Jim Sharples [mailto:jim@watagan.com]
Sent: 03 March 2003 10:04
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Data Migration Wizard Problem

Hi,

I am trying to migrate a MS SQL7 database to Postgres. The Postgres database is UNICODE, and whenever the Migrator encounters an é character, I get an error as follows:

An error occured at: 3/03/2003 1:36:32 PM:

-2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90020)

Rolling back... Done.

sometimes the error is:
        -2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90000)

The actual statement (captured in debug) is:

INSERT INTO "member" ("memberid", "famname", "othername", "title", "streetaddr", "suburb", "city", "state", "postcode", "email", "dob", "age", "gender", "privacydate", "deleted", "enterdate", "enterby", "check1", "check2") VALUES ('XSY0101693', 'Blasé', 'Lisa M', '', 'Unit 15 46 Albany Creek Rd', 'ASPLEY', '', 'QLD', '4034', '', '1969-06-25 00:00:00', '32', ' ', '2001-12-24 00:00:00', '0', '2003-02-24 12:29:44', 'Transfer', '0', '0')

The import works fine until it hits this accented é character, which should be valid for Unicode I presume? I also tried using the escape sequence "\351", but that returned the same error.

If I create the table with SQL_ASCII encoding, then I don't get the error.

Any ideas?

Thanks,

Jim Sharples.

Hi Dave,

I tried creating 2 ODBC connections to a test Unicode database, one using the standard PostgreSQL driver, and another using the PostgreSQL30W [PostgreSQL + Unicode (Beta)] driver.

I then used these to create 2 links to the same table in the database in Access, and added a record using the accented character.

Guess what? The standard driver gave the error as below, but the PostgreSQL30W driver worked properly!

So, I probably would need to compile a new pgSchema.dll using the other driver (I checked the source code where the driver is specified). Would there be some way to dynamically load the correct driver depending on the database encoding? Or is this a possible future enhancement?

I will also try some experiments using the Unicode driver with a non-Unicode database etc.

Regards,

Jim Sharples.


At 11:12 AM 3/03/2003 +0000, Dave Page wrote:

Hi Jim,
 
That error is coming from PostgreSQL, but I do not know why (but then I know nothing about Unicode). Jean-Michel, do you have any ideas?
 
Regards, Dave.

-----Original Message-----

From: Jim Sharples [mailto:jim@watagan.com]

Sent: 03 March 2003 10:04

To: pgadmin-support@postgresql.org

Subject: [pgadmin-support] Data Migration Wizard Problem

Hi,

I am trying to migrate a MS SQL7 database to Postgres. The Postgres database is UNICODE, and whenever the Migrator encounters an é character, I get an error as follows:

An error occured at: 3/03/2003 1:36:32 PM:

-2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90020)

Rolling back... Done.

sometimes the error is:
        -2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90000)

The actual statement (captured in debug) is:

INSERT INTO "member" ("memberid", "famname", "othername", "title", "streetaddr", "suburb", "city", "state", "postcode", "email", "dob", "age", "gender", "privacydate", "deleted", "enterdate", "enterby", "check1", "check2") VALUES ('XSY0101693', 'Blasé', 'Lisa M', '', 'Unit 15 46 Albany Creek Rd', 'ASPLEY', '', 'QLD', '4034', '', '1969-06-25 00:00:00', '32', ' ', '2001-12-24 00:00:00', '0', '2003-02-24 12:29:44', 'Transfer', '0', '0')

The import works fine until it hits this accented é character, which should be valid for Unicode I presume? I also tried using the escape sequence "\351", but that returned the same error.

If I create the table with SQL_ASCII encoding, then I don't get the error.

Any ideas?

Thanks,

Jim Sharples.


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/2003

-----Original Message-----
From: Jim Sharples [mailto:jim@watagan.com]
Sent: 03 March 2003 14:45
To: Dave Page; pgadmin-support@postgresql.org
Cc: jm@poure.com
Subject: RE: [pgadmin-support] Data Migration Wizard Problem

Hi Dave,

I tried creating 2 ODBC connections to a test Unicode database, one using the standard PostgreSQL driver, and another using the PostgreSQL30W [PostgreSQL + Unicode (Beta)] driver.

I then used these to create 2 links to the same table in the database in Access, and added a record using the accented character.

Guess what? The standard driver gave the error as below, but the PostgreSQL30W driver worked properly!

So, I probably would need to compile a new pgSchema.dll using the other driver (I checked the source code where the driver is specified). Would there be some way to dynamically load the correct driver depending on the database encoding? Or is this a possible future enhancement?

I will also try some experiments using the Unicode driver with a non-Unicode database etc.

Regards,

Jim Sharples.


At 11:12 AM 3/03/2003 +0000, Dave Page wrote:

Hi Jim,
 
That error is coming from PostgreSQL, but I do not know why (but then I know nothing about Unicode). Jean-Michel, do you have any ideas?
 
Regards, Dave.

-----Original Message-----

From: Jim Sharples [mailto:jim@watagan.com]

Sent: 03 March 2003 10:04

To: pgadmin-support@postgresql.org

Subject: [pgadmin-support] Data Migration Wizard Problem

Hi,

I am trying to migrate a MS SQL7 database to Postgres. The Postgres database is UNICODE, and whenever the Migrator encounters an é character, I get an error as follows:

An error occured at: 3/03/2003 1:36:32 PM:

-2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90020)

Rolling back... Done.

sometimes the error is:
        -2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90000)

The actual statement (captured in debug) is:

INSERT INTO "member" ("memberid", "famname", "othername", "title", "streetaddr", "suburb", "city", "state", "postcode", "email", "dob", "age", "gender", "privacydate", "deleted", "enterdate", "enterby", "check1", "check2") VALUES ('XSY0101693', 'Blasé', 'Lisa M', '', 'Unit 15 46 Albany Creek Rd', 'ASPLEY', '', 'QLD', '4034', '', '1969-06-25 00:00:00', '32', ' ', '2001-12-24 00:00:00', '0', '2003-02-24 12:29:44', 'Transfer', '0', '0')

The import works fine until it hits this accented é character, which should be valid for Unicode I presume? I also tried using the escape sequence "\351", but that returned the same error.

If I create the table with SQL_ASCII encoding, then I don't get the error.

Any ideas?

Thanks,

Jim Sharples.


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/2003

Hi Dave,

Yes, I can do it myself - write VB apps for a living. I was in the UK (if that's where you are physically located) at the end of January (business), and thought the weather wasn't too bad. Found out later that it was the warmest January in living memory! And then a few days later it snowed.

I was glad to get back to Australia.

Not sure if the Unicode driver will work for non-Unicode databases, but I will give it a go. Viewing Unicode data using the standard driver does result in some weird text, though.

Regards,

Jim.

At 02:50 PM 3/03/2003 +0000, Dave Page wrote:

Hi Jim,
 
Once we ar happy that the Unicode driver is stable, I will make it the default anyway. Do you have the ability to recompile pgSchema yourself?
 
Regards, Dave.

-----Original Message-----

From: Jim Sharples [mailto:jim@watagan.com]

Sent: 03 March 2003 14:45

To: Dave Page; pgadmin-support@postgresql.org

Cc: jm@poure.com

Subject: RE: [pgadmin-support] Data Migration Wizard Problem

Hi Dave,

I tried creating 2 ODBC connections to a test Unicode database, one using the standard PostgreSQL driver, and another using the PostgreSQL30W [PostgreSQL + Unicode (Beta)] driver.

I then used these to create 2 links to the same table in the database in Access, and added a record using the accented character.

Guess what? The standard driver gave the error as below, but the PostgreSQL30W driver worked properly!

So, I probably would need to compile a new pgSchema.dll using the other driver (I checked the source code where the driver is specified). Would there be some way to dynamically load the correct driver depending on the database encoding? Or is this a possible future enhancement?

I will also try some experiments using the Unicode driver with a non-Unicode database etc.

Regards,

Jim Sharples.

At 11:12 AM 3/03/2003 +0000, Dave Page wrote:

Hi Jim,

 

That error is coming from PostgreSQL, but I do not know why (but then I know nothing about Unicode). Jean-Michel, do you have any ideas?

 

Regards, Dave.

-----Original Message-----

From: Jim Sharples [mailto:jim@watagan.com]

Sent: 03 March 2003 10:04

To: pgadmin-support@postgresql.org

Subject: [pgadmin-support] Data Migration Wizard Problem

Hi,

I am trying to migrate a MS SQL7 database to Postgres. The Postgres database is UNICODE, and whenever the Migrator encounters an é character, I get an error as follows:

An error occured at: 3/03/2003 1:36:32 PM:

-2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90020)

Rolling back... Done.

sometimes the error is:
        -2147467259: ERROR:  Invalid UNICODE character sequence found (0xe90000)

The actual statement (captured in debug) is:

INSERT INTO "member" ("memberid", "famname", "othername", "title", "streetaddr", "suburb", "city", "state", "postcode", "email", "dob", "age", "gender", "privacydate", "deleted", "enterdate", "enterby", "check1", "check2") VALUES ('XSY0101693', 'Blasé', 'Lisa M', '', 'Unit 15 46 Albany Creek Rd', 'ASPLEY', '', 'QLD', '4034', '', '1969-06-25 00:00:00', '32', ' ', '2001-12-24 00:00:00', '0', '2003-02-24 12:29:44', 'Transfer', '0', '0')

The import works fine until it hits this accented é character, which should be valid for Unicode I presume? I also tried using the escape sequence "\351", but that returned the same error.

If I create the table with SQL_ASCII encoding, then I don't get the error.

Any ideas?

Thanks,

Jim Sharples.


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/2003

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/2003

-----Original Message-----
From: Jim Sharples [mailto:jim@watagan.com]
Sent: 03 March 2003 15:39
To: Dave Page; pgadmin-support@postgresql.org
Cc: jm@poure.com
Subject: RE: [pgadmin-support] Data Migration Wizard Problem

Hi Dave,

Yes, I can do it myself - write VB apps for a living. I was in the UK (if that's where you are physically located) at the end of January (business), and thought the weather wasn't too bad. Found out later that it was the warmest January in living memory! And then a few days later it snowed. 

I was glad to get back to Australia.
 

 

 
Not sure if the Unicode driver will work for non-Unicode databases, but I will give it a go. Viewing Unicode data using the standard driver does result in some weird text, though.