Encoding in databases sql commands

Question

I will like to know what entity is responsible for doing the encoding conversions necessaries to accomplish a SQL command successfully. For example: you have several places where output a SQL command.

SELECT title from T1 where title='título'

This may be execute from within the database client (which I assume it reads the database encoding and encode its commands after that) but what happen when this is a string in a programming language whose string encoding is not the same as the database?

Where the conversion takes place? In the class that connects to the database? The database and the connector do some kind of agreement when they are handshaking?

I'll love some information about this topic or some link where I can read about it.

Thanks in advance.

Joop Eggen · Accepted Answer · 2013-05-17 22:26:01Z

1

Case Java + MySQL

Internally in Java String is text is Unicode encoded.
In a Java source text should have the same encoding that the java compiler uses. A wrong matching between editor and compiler would mess up string literals.
Java thus transfers a Unicode string to the JDBC driver, the database client library.
The MySQL connections string can indicate which encoding to use in the client library to communicate with the database server. useEncoding=UTF-8, so Unicode, would be a good international choice.
The database can set a default encoding.
As also any table.
As also per column (say one for Hindi one for Chinese).

Besides the encoding, also the collation (sorting order of strings) is language and encoding specific. And have to be considered too.

edited May 17, 2013 at 22:26

answered May 17, 2013 at 21:21

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paulo Bu Over a year ago

So basically, the entity that handles the connection is the one who needs to sync both sides (db and source). If you enforce a specific encoding in the connection, the database engine will have to translate the bytes from the connection to the database encoding?

Joop Eggen Over a year ago

IMHO the mysql client library (the JDB "driver") has simply an option too much. Java always passes a String. And the non-Java MySQL server should also accept Unicode. The transmission in a specific encoding means a conversion at the client driver in Java.

Collectives™ on Stack Overflow

Encoding in databases sql commands

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related