Hello,

I'm trying to add romanian and hungarian specific characters (like șțăîâ or őúűéá)  to a VarChar field with COLLATE ANSI_CI and some of them get changed when I save them into the database. What I'm doing wrong? Is there any solution for this problem?

Thanks,
Beni.

Beni,

Are you using the Unicode version of EDB *and* EDBMgr? Then you should be using "UNI_CI" instead of "ANSI_CI".

If you are NOT using the Unicode version of EDB, then I don't think you can't store characters above Char(128) into a Char field and you will need to switch to the Unicode version of EDB.

Barry

Thanks for the reply!

Is there any place on this forum or documentation or article in which I can see how to programatically create a field/database/session/engine which accept UNI_CI collation?

>Is there any place on this forum or documentation or article in which I can see how to programatically create a field/database/session/engine which accept UNI_CI collation?<

It depends on which EDB version you are using. I am still using EDB 2.13 which had separate install programs for ANSI and Unicode. I installed EDB Unicode for both the EDB server and EDBMgr.

The EDB v2.15 release now has ANSI or Unicode in the same installer. I assume you get Unicode by specifying the CatalogCharacterSet when defining a table in EDBMgr. (I don't have this version of EDB so I can't say for sure)

Now back to your question:

1) The easiest way would be to use EDBMgr (Unicode version) to create the table with the indexes and constraints the way you want it.

2) Then select Explorer > SQL from the EDBMgr pulldown menu (or press Ctrl+Alt+S) to display the SQL window (tab on right side of screen).

3) Double-Click on the table in the tree (left side) and you should see the SQL tab (right side) with the Create Table statement needed to create the table.

4a) Copy and paste the SQL into your Delphi application.  This is a bit messy because you need to put quotes around each line and concatenate them together.

4b) Or it may be simpler to use EDBMgr to create a "Codes" table that has a couple of key fields (Code1 VarChar(30), Code2 VarChar(30)) and a Clob field and just paste the Create Table SQL into the Clob and assign the two Code fields to "CreateTable" and "MyTableName". Now when you need to create this table from your program, just lookup the row using the two keys "CreateTable" and "MyTable" and retrieve the SQL from the memo and execute it in a TEDBQuery statement. This way you can store dozens of tables definitions in a single table and you won't clutter up your code.

Barry

Beni,

<< I'm trying to add romanian and hungarian specific characters (like
&#537;&#539;&#259;îâ or &#337;ú&#369;éá)  to a VarChar field with COLLATE
ANSI_CI and some of them get changed when I save them into the database.
What I'm doing wrong? Is there any solution for this problem? >>

How are you adding the data, via SQL or via navigational methods such as
Append/Post ?

You should be able to store any characters #255 and under in an ANSI
database.  However, if you try to store Unicode characters (> #255) in a
VARCHAR column, then you're going to need to use a collation for the VARCHAR
column that is capable of mapping the Unicode characters to their proper
ANSI code page representation, and ANSI_CI will not be able to do this.

I assume that you're using a Delphi version that has a default Unicode
string type ?

Tim Young
Elevate Software
www.elevatesoft.com

Barry,

<< If you are NOT using the Unicode version of EDB, then I don't think you
can't store characters above Char(128) into a Char field and you will need
to switch to the Unicode version of EDB. >>

You can use any AnsiString character <= #255.   The issue is that the
characters are being specified as Unicode and are not being mapped to ANSI
strings properly because of the ANSI_CI collation, which doesn't provide any
specific code-page mapping at all, rather it uses the default system
locale's code page for mapping the string.

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Beni,

<< I'm trying to add romanian and hungarian specific characters (like
&#537;&#539;&#259;îâ or &#337;ú&#369;éá)  to a VarChar field with COLLATE
ANSI_CI and some of them get changed when I save them into the database.
What I'm doing wrong? Is there any solution for this problem? >>

How are you adding the data, via SQL or via navigational methods such as
Append/Post ?

You should be able to store any characters #255 and under in an ANSI
database.  However, if you try to store Unicode characters (> #255) in a
VARCHAR column, then you're going to need to use a collation for the VARCHAR
column that is capable of mapping the Unicode characters to their proper
ANSI code page representation, and ANSI_CI will not be able to do this.

I assume that you're using a Delphi version that has a default Unicode
string type ?

Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the answer,
I'm adding the data in any possible ways (both insert/edit queries and Append/Edit/Post functions) and I'm using Delphi XE.

I did manage to create a database using the Unicode DB Manager and create a table in the database with UNI_CI fields my problem is that I have to deploy the application without a database: create the database and tables "on the fly".
So, I have in my application an EDBEngine, and EDBSession and an EDBDatabase. I set the config file path for the engine and session and create the database using a query (CREATE DATABASE "[DB Name]" PATH [DB Path]. After the database was created I start creating the tables in the database using CREATE TABLE queries in which I set the VARCHAR fields with "UNI_CI" collation. When I run such a query I'm getting an error which tells me the the collation UNI doesn't exist in the configuration file. It's clear that have to tell the engine that I want a unicode database but I have no idea how to do it.

Beni,

<< So, I have in my application an EDBEngine, and EDBSession and an
EDBDatabase. I set the config file path for the engine and session and
create the database using a query (CREATE DATABASE "[DB Name]" PATH [DB
Path]. After the database was created I start creating the tables in the
database using CREATE TABLE queries in which I set the VARCHAR fields with
"UNI_CI" collation. When I run such a query I'm getting an error which tells
me the the collation UNI doesn't exist in the configuration file. It's clear
that have to tell the engine that I want a unicode database but I have no
idea how to do it. >>

If you're using a release < ElevateDB 2.15, then you need to make sure to
install a Unicode version of ElevateDB for your IDE/compiler.  Without that,
you'll be creating an ANSI database, and that won't work for your needs.

As Barry mentioned, ElevateDB 2.15 now allows you to mix ANSI and Unicode
databases in the same application, so it's just a matter of a property
setting to determine which type of configuration/database you want to
create.

Tim Young
Elevate Software
www.elevatesoft.com

Had an old version of Elevate (2.11) - just downloaded the 2.15 version hope that my problems will be fixed.

Thanks for your help,
Beni.