Hi,

first Application with german language to enter.

My problem:

I have a Memo Field or Edit Field. When - for example - an ä is entered, I see in the database an ä

When I change it in the database back to ä I can get it back to the Memo or Edit Field as ä. But when I store it again, it is shown as ä

Database table is set to Collation: UNI.

Example:
INSERT INTO "Data" (editcontent, memocontent) VALUES ('''+Mainform.Edit1.txt+''', '''+Mainform.Memo1.text+''')';


CREATE TABLE "Data"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"editcontent" VARCHAR(100) COLLATE "UNI",
"memocontent" VARCHAR(900) COLLATE "UNI"
)
VERSION 1.00
READWRITE
ENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';



Any idea what is going wrong or what to change?

Additional information:
After checking with GuessEncoding() I get the result from the database as UTF-8 and send information to the Database as UTF-8. But the umlauts are converted.
Any idea at which point the string is catched and converted?

I am using

temptxt : string:

temptext := '...';  //SQL Statement generated here

EDBDatabase1.StartTransaction(EmptyEDBStringsArray);
EDBDatabase1.Execute(temptxt);
EDBDatabase1.Commit();

Teco wrote:

> Additional information:
> After checking with GuessEncoding() I get the result from the
> database as UTF-8 and send information to the Database as UTF-8. But
> the umlauts are converted.  Any idea at which point the string is
> catched and converted?
>
> I am using
>
> temptxt : string:
>
> temptext := '...';  //SQL Statement generated here
>
> EDBDatabase1.StartTransaction(EmptyEDBStringsArray);
> EDBDatabase1.Execute(temptxt);
> EDBDatabase1.Commit();

Hi Teco, we have customers everywhere. Some are using german umlauts,
others using turkish characters, others are using cyrillic. Everything
works good without manual changing anything. Also mixing all characters
within one string is posible!

Which IDE are u using? Something old?

--
--
Yusuf Zorlu | MicrotronX

Hi Yusuf,

I am using Lazarus IDE 2.0.6 (Free Pascal).

Following an example of the code I am using.


var
  temptxt1 : string;
begin
temptxt1 := 'UPDATE "Data" SET content1 = ''' + Memo1.text+''' where ID = ' + IDMarker;

EDBDatabase1.StartTransaction(EmptyEDBStringsArray);  //prepare Transaction for all affected tables

EDBDatabase1.Execute(temptxt1);   //Compiler warning for this line as below.

Databaseform.EDBDatabase1.Commit();




When I add the compiler directive
{$codepage utf8}    

I get the following Warning message:
Warning: Implicit string type conversion with potential data loss from "WideString" to "AnsiString"

I have open a topic at the Lazarus Forum, Databases. Link:    https://forum.lazarus.freepascal.org/index.php/topic,49994.0.html

But they don't have an idea what causes the problem.

Teco wrote:

> I get the following Warning message:
> Warning: Implicit string type conversion with potential data loss from "WideString" to "AnsiString"

That is of course the cause. WHenever I've had this sort of thing, it has been implicit conversions which cause the characters to "change" as they go back and forth. You need to identify which part of these strings is being treated as ANSI and cast it to Wide as soon as possible so that it all stays as Unicode.

--

Matthew Jones

"Matthew Jones" wrote:

> That is of course the cause. WHenever I've had this sort of thing, it has been implicit conversions which cause the
> characters to "change" as they go back and forth. You need to identify which part of these strings is being treated
> as ANSI and cast it to Wide as soon as possible so that it all stays as Unicode.


I have checked with some Lazarus functions (Thanks to the folk at the Lazarus Forum)

function GuessEncoding(const s: string): string;
The complete sql statement is in UTF-8. No parts with other encoding


The following source changes the string to UTF8, equal whatever it was before, just to make sure it is UTF-8.

temptxt2 := UTF8String(RawByteString(temptxt1));
showmessage(temptxt2);
EDBDatabase1.Execute(temptxt2);

Still the same failture.

Engine and session is also set to csUnicode.

The warning comes from the line    EDBDatabase1.Execute(temptxt2)

Just checked with Lazarus and another Database Engine. Using SQLite and the SQLite connector from Lazarus has no failture. Only this part of the source code has been changed.

Looks like a problem inside the Elevate DB Connector.

On 6/2/2020 4:50 AM, Teco wrote:
> "Matthew Jones" wrote:
>
> function GuessEncoding(const s: string): string;
> The complete sql statement is in UTF-8. No parts with other encoding
>

EDB is UTF-16LE internally (not utf-8) so to me look like encoding
issues. Especially once you get past normal ASCII (like umlauts) since
encodings differ between utf-8 and utf-16.

> The following source changes the string to UTF8, equal whatever it was before, just to make sure it is UTF-8.
>
> temptxt2 := UTF8String(RawByteString(temptxt1));
> showmessage(temptxt2);
> EDBDatabase1.Execute(temptxt2);
>
> Still the same failture.

I would suggest whatever you send into execute to be in UTF-16 (not
utf-8) and see if that fixes this first.

Raul

Raul wrote:

On 6/2/2020 4:50 AM, Teco wrote:
> "Matthew Jones" wrote:
>
> function GuessEncoding(const s: string): string;
> The complete sql statement is in UTF-8. No parts with other encoding
>

EDB is UTF-16LE internally (not utf-8) so to me look like encoding
issues. Especially once you get past normal ASCII (like umlauts) since
encodings differ between utf-8 and utf-16.

> The following source changes the string to UTF8, equal whatever it was before, just to make sure it is UTF-8.
>
> temptxt2 := UTF8String(RawByteString(temptxt1));
> showmessage(temptxt2);
> EDBDatabase1.Execute(temptxt2);
>
> Still the same failture.

I would suggest whatever you send into execute to be in UTF-16 (not
utf-8) and see if that fixes this first.

Raul

Dear Raul,

here is the test result
Orign:
äöü

UTF-8
äöü

UTF-16
äöü

On 6/2/2020 8:18 AM, Teco wrote:
> here is the test result
> Orign:
> äöü
>
> UTF-8
> äöü
>
> UTF-16
> äöü
>

Teco,

I did a quick and dirty test rig and this all works OK to me so not sure
what's different with your code .

Unicode session and data table as per your SQL from your original post.

App has a tdbgrid on top and tedit and tmemo on bottom to add records.

EDB 2.31 B13 usingf delphi rio though

This is my code for inserting data (should be basically same as yours).

All components are designtime setup and only code is the button event

procedure TForm1.Button1Click(Sender: TObject);
const
  cInsertSQL = 'INSERT INTO "Data" (editcontent, memocontent) VALUES
(%s,%s);';
begin
  EDBQuery1.Active := false;
  EDBQuery1.SQL.Clear;
  EDBQuery1.SQL.Text := Format(cInsertSQL,
[engine.QuotedSQLStr(Edit1.Text),engine.QuotedSQLStr(Memo1.Text)]);
  EDBQuery1.ExecSQL;
  EDBTable1.Refresh;
end;

and attached is screenshot of it in action with various characters -
different unicode including western, cyrillic and emoji



Raul