escape Word chracters for PostGRES

Dan Berger wrote:

Marnen Laibow-Koser wrote:

Are the characters getting into the database properly?

No, the system is chocking at this character

Is your database encoding set to UTF8?

Yes

Does your database.yml file specify UTF8 encoding?

No....

That could be your problem, then. Change it and restart the app.

What exactly are the problems you're seeing? Specific errors or
corrupted data would be helpful.

Here is my error:
RuntimeError: ERROR C22021 Minvalid byte sequence for encoding "UTF8":
0x92 HThis error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding". Fwchar.c L1534: INSERT INTO "table_cells"
("row_id", "updated_at", "col_span", "table_meta_id", "additional_info",
"content", "column_id", "created_at") VALUES(2, '2009-10-21
19:36:14.440000', 1, 154, NULL, E'Dido’s Woman', 3, '2009-10-21
19:36:14.440000')

Another error:
IOError (closed stream):

I believe the problem is that the ’ in Dido’s is not being escaped,
despite the E' indicating that it should/will be.

Well, the error you're getting would indicate an encoding mismatch
between the app and the DB -- hardly surprising if you're not telling
the app about the DB encoding. So fix your database.yml and see what
happens.

Thanks,
Dan

Best,

Dan Berger wrote:

OK, I added this line to my database.yml file:

development:
  encoding: utf8
  ...
  ...

And that provides the same results. When I now call this on each string:

str = Iconv.conv('UTF-8//TRANSLIT','ISO-8859-1',str)

those curly quotes are now preceded by a "Â", which goes into the
database like this -- not what I need, either. It's as if this "Â" is
what is being used to escape the funny characters.

No, that's an extra byte that's there because UTF-8 is a multibyte
encoding. -- not really an escape character. But now that your database
and app encodings are identical, try taking out the Iconv call; it will
probably not be necessary. If that doesn't work, then you may need to
call Iconv with reversed arguments -- after all, it should be converting
*to* UTF-8.

In any case, I would strongly suggest that, as a first step, you make
sure that you can round-trip characters between the app and the DB.
Then verify the file uploads.

Best,

Again, thanks. I did pull out the Iconv, and still, the same results.
Using a strait form, yes, I can get a curly quote in and out. Just not
when that curly quote is in a .csv, which I parse into an array of
arrays, like this:

[["a", "b", "c"],["cell1", "cell2", "cell3"]] and then input into the
database like this:

Table.create(:table_attrib => var) do |tc|
               tc.column_id = col_id
               tc.row_id = r_id
               tc.content = cell
             end

Try getting the fields you want and putting them in directly, ignoring any CSV stuff... That is, get them out of the CSV file, but don't let your CSV library mess with them. Maybe that's the culprit.

You might also fiddle around with this Iconv line...

Iconv.new('UTF-8//Ignore', 'UTF-8').iconv("your string here")

I've had to do that from time to time to try to fixup borked UTF characters...

The //Ignore will drop anything it can't convert... if my memory is right. Been awhile.

-philip

Dan Berger wrote:

Again, thanks. I did pull out the Iconv, and still, the same results.
Using a strait form, yes, I can get a curly quote in and out.

OK. Then your app and DB are talking the same encoding. Great. (And
remember to specify UTF8 in your HTML headers.)

Just not
when that curly quote is in a .csv,

Well, yeah, the uploaded file could be in a different encoding, so you
probably should run it through Iconv before doing any processing. Let
me check the Iconv docs and see if I can find anything useful.

Or you could encourage your users to save their files in UTF-8, or
perhaps to paste them into a form instead of uploading (which should
bypass the issue entirely, assuming your content-type headers are
correct).

Best,

A good LART is also helpful in getting users to stop pasting Word's
garbage quotes... :slight_smile:

--Matt Jones

Dan Berger wrote:

OK, we solved it with a handful or regexs

cell.gsub!("\222", "'")
cell.gsub!("\226", "—")
cell.gsub!("\223", '"')
cell.gsub!("\221", "'")
cell.gsub!("\224", '"')

This seems like a very bad solution -- particularly the 2nd line, which
is pre-escaped HTML instead of a literal character. Please read my
previous message in this thread for better ideas.

Thanks!

Best,

Philip Hallstrom wrote:

               tc.row_id = r_id
               tc.content = cell
             end

Try getting the fields you want and putting them in directly, ignoring
any CSV stuff... That is, get them out of the CSV file, but don't let
your CSV library mess with them. Maybe that's the culprit.

You might also fiddle around with this Iconv line...

Iconv.new('UTF-8//Ignore', 'UTF-8').iconv("your string here")

I've had to do that from time to time to try to fixup borked UTF
characters...

The //Ignore will drop anything it can't convert... if my memory is
right. Been awhile.

-philip

I found next solve of this problem:
Iconv.new('UTF-8', 'Windows-1250').iconv(text)

because charcters as 0x92 are used in Microsoft Word.