Malformed string error

[Alan Braga]

Hi.

I'm having trouble with where clause using accents, the following Linq command:

contexto.Set<CondicaoFornecimento>()

   .Where(c => c.Descricao.Contains("tensão"));

Generate this SQL

SELECT "c"."Id", "c"."Codigo", "c"."Descricao"
FROM "CondicoesFornecimento" AS "c"
WHERE (POSITION(CAST(_UTF8'tensão' AS VARCHAR(6) CHARACTER SET UTF8), "c"."Descricao") > 0)

And the error "Dynamic SQL Error 

SQL error code = -104 

Malformed string"

If I remove the _UTF8, like that:

SELECT "c"."Id", "c"."Codigo", "c"."Descricao"
FROM "CondicoesFornecimento" AS "c"
WHERE (POSITION(CAST('tensão' AS VARCHAR(6) CHARACTER SET UTF8), "c"."Descricao") > 0)

It works fine.

My database was created with charset ISO8859_1 and Collate PT_BR.

Is there a way to remove this _UTF8 via configuration? 

Other question: Is there a way to Create tables and fields automatically in upper case? I know that I can configure individually and manually the name of fields and tables, but would be easy to have a configuration for that because write sql queries on the database is annoying with the " character.

Thanks in advance,

  Alan 

[Alan Braga]

I've made a test and the error doesn't happened if I switch _UTF8 with _ISO8859_1, like that

SELECT "c"."Id", "c"."Codigo", "c"."Descricao", "c"."Versao"
FROM "CondicoesFornecimento" AS "c"
WHERE (POSITION(CAST(_ISO8859_1'tensão' AS VARCHAR(6) CHARACTER SET UTF8), "c"."Descricao") > 0)
ORDER BY "c"."Descricao"

ISO8859_1 is part of my connection string, in charset part:

user=SYSDBA;password=masterkey;database=DATABASE.FDB;datasource=localhost;port=3050;dialect=3;charset=ISO8859_1;pooling=false;servertype=0

Maybe this can avoid problems like this one.

[Mark Rotteveel]

On 29-03-2023 17:46, Alan Braga wrote:
> Hi.
>
> I'm having trouble with where clause using accents, the following Linq
> command:
>

> *contexto.Set<CondicaoFornecimento>()*
> *   .Where(c => c.Descricao.Contains("tensão"));*
>
> Generate this SQL
>
> *SELECT "c"."Id", "c"."Codigo", "c"."Descricao"

> FROM "CondicoesFornecimento" AS "c"
> WHERE (POSITION(CAST(_UTF8'tensão' AS VARCHAR(6) CHARACTER SET UTF8),

> "c"."Descricao") > 0)*
>
> And the error "*Dynamic SQL Error *
> *SQL error code = -104 *
> *Malformed string*"

As far as I'm aware, this means your connection character set is not
UTF8 but something else. The character set introducer leads to the bytes
of the string literal to be interpreted as UTF8, but the byte of
character ã in ISO-8859-1 (0xE3) is not a valid first byte in UTF-8.

> If I remove the _UTF8, like that:
>

> *SELECT "c"."Id", "c"."Codigo", "c"."Descricao"

> FROM "CondicoesFornecimento" AS "c"
> WHERE (POSITION(CAST('tensão' AS VARCHAR(6) CHARACTER SET UTF8),

> "c"."Descricao") > 0)*
>
> It works fine.

More proof that the problem is with your connection character set. Using
connection character set UTF8 should fix that.

Mark
--
Mark Rotteveel

[Jiří Činčura]

> *contexto.Set<CondicaoFornecimento>()*
> * .Where(c => c.Descricao.Contains("tensão"));*

Best is to use variable here and not constant. It will be parameter then. You can also plug in then WithExplicitParameterTypes (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.EntityFrameworkCore.Firebird/Infrastructure/Internal/FbOptionsExtension.cs#L52).

Also, your connection string should use UTF8. There's no reason to use ISO8859_1.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

[Mark Rotteveel]

On 30-03-2023 11:50, Jiří Činčura wrote:
>> *contexto.Set<CondicaoFornecimento>()*
>> * .Where(c => c.Descricao.Contains("tensão"));*
>
> Best is to use variable here and not constant. It will be parameter then. You can also plug in then WithExplicitParameterTypes (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/src/FirebirdSql.EntityFrameworkCore.Firebird/Infrastructure/Internal/FbOptionsExtension.cs#L52).
>
> Also, your connection string should use UTF8. There's no reason to use ISO8859_1.

I can think of one reason: Having columns of type VARCHAR(n) or CHAR(n)
with a character set other than UTF8 with n > 8191.

Mark
--
Mark Rotteveel

[Jiří Činčura]

> I can think of one reason: Having columns of type VARCHAR(n) or CHAR(n)
> with a character set other than UTF8 with n > 8191.

Database column can be whatever charset you want. I was talking about connection charset.

[Mark Rotteveel]

On 30-03-2023 11:54, Jiří Činčura wrote:
>> I can think of one reason: Having columns of type VARCHAR(n) or CHAR(n)
>> with a character set other than UTF8 with n > 8191.
>
> Database column can be whatever charset you want. I was talking about connection charset.

A column is transliterated to the connection character set, which means
it would exceed the implementation limit if it is defined as with n > 8191.

Mark
--
Mark Rotteveel

[Jiří Činčura]

> A column is transliterated to the connection character set, which means
> it would exceed the implementation limit if it is defined as with n > 8191.

Ah, for selecting. Sure. For some reason I was thinking only about inserting/updating.

[Mark Rotteveel]

On 30-03-2023 12:05, Jiří Činčura wrote:
>> A column is transliterated to the connection character set, which means
>> it would exceed the implementation limit if it is defined as with n > 8191.
>
> Ah, for selecting. Sure. For some reason I was thinking only about inserting/updating.

Even with inserting/updating it will break, because the parameter is
also be described with the connection character set, and would also be
wider than the implementation limit.

Mark
--
Mark Rotteveel

[Jiří Činčura]

> Even with inserting/updating it will break, because the parameter is
> also be described with the connection character set, and would also be
> wider than the implementation limit.

Ha, no kidding. I even have that code in provider. Completely forgot about that.

But I still like UTF-8 more than I like "long" strings (also I personally don't have problem with blobs). ;)