Nette and Microsoft SQL 2008 problem

steelbull

Firstly, I would like to say many thanks for every answer. I have problem with saving data to the Microsoft SQL database. Collation of my database is Latin1_general_CS_AS.

1 – All columns, to which I´m saving the text data are using data type NTEXT, NVARCHAR or NCHAR are using UCS-2 encoding (it´s standard for Microsoft).
2 – My Nette application is using the UTF-8 encoding.

For saving the data, I must use query like this:
INSERT INTO table(column1, column2) VALUES(N'žťčťščšľčš', N'ážčžťčžť');
//N before string means, the database automatically convert the UTF-8 to UCS-2

Nette will generate query without N' and then I have problem with some chars like čšľ…
I need add 'N to query on all columns, which are type of NTEXT, NVARCHAR, NCHAR or convert UTF-8 to UCS-2 with PHP before store to the database.

Pleeeease, help meeeee.
Thank you very much.

mb_convert_encoding($formData->description, “UCS2”, “UTF-8”), NOT HELPED
iconv(…) NOT HELPED
mb_convert_encoding($value, ‘UCS-2LE’, mb_detect_encoding($value, mb_detect_order(), true)); NOT HELPED

Milo

Collation does not relate to stored encoding, only to ordering.

As you wrote, the N prefix means that following string literal is in unicode. If you don't use the prefix, default database encoding is used. And if characters in string literals does not fit to encoding, they are trimmed. Check the database encoding, or try set UTF-8. I'm not sure how.

And check, that you are setting the HTML encoding to UTF-8. E.g. by <meta charset='utf-8'>.

steelbull

Yes, Im sure. Im using the UTF-8 in my application. Im sure, Its not possible do set UTF-8 encoding in Microsoft SQL 2008 database. UTF-8 is posiible to set only in SQL server 2012, wich is not free. Im using Microsoft SQL Server 2008, which is free.

I think, I need to update the Nette core to add N'' for all colums, wich are type of NCHAR, NTEXT and NVARCHAR. Second possibility is recore UTF-8 string to UCS-2 in PHP and after this, send to the database (but I tryied more ways, but not successfully).

Milo

Well, I'm using SQL Server 2012 Express (which is free) and I have no possibility to test server 2008.

The code you are interested in is SqlPreprocessor. But I don't think that automate prefixing by N is easy. What if someone want not to use it? How can be prefixing recognized?

So, you can open an issue on Github, or use the SqlLiteral in current implementation:

$ntext = new Nette\Database\SqlLiteral('N' . $connection->quote('ážščřčďťňúů'));

steelbull

Milo,
please, can you give mee one concrete example, how can I use SqlLiteral?

Now Im using:

$result = $this->context->table(self::ARTICLES_TABLE_NAME)->where(self::ARTICLE_COLUMN_ID, $formData->id)->update(
array(
self::ARTICLE_COLUMN_TITLE => 'človek',
self::ARTICLE_COLUMN_CONTENT => 'človek',
));

…will generate this query: UPDATE articles SET(title=‘človek’,content=‘človek’);

I need modify this query and add “N” before string like this example:
UPDATE articles SET(title=N'človek',content=N'človek');

Thank you for help.

Last edited by steelbull (2015-01-13 10:03)