How to store unicode character in oracle database

Question

I'm Trying to store Unicode data (Hindi Language character) in oracle database instead of storing in hindi character it stored in this format ????

I'm using Oracle 10g and i have set database character set to AL32UTF8 my table script

create table UTF
(
 ENGLISH_NAME VARCHAR2(10),
 HINDI_NAME   NVARCHAR2(10)
)

I'm using visual studio 13

I'm using google API for translation

<script>
function OnLoad() {
    var options = {
        sourceLanguage:
        google.elements.transliteration.LanguageCode.ENGLISH,
        destinationLanguage:
        [google.elements.transliteration.LanguageCode.HINDI],
        shortcutKey: 'ctrl+g',
        transliterationEnabled: true
    };

    var control = new google.elements.transliteration.TransliterationControl(options);
    control.makeTransliteratable(["txtHindi"]);
    var keyVal = 32; // Space key
    $("#txtEnglish").on('keydown', function (event) {
        if (event.keyCode === 32) {
            var engText = $("#txtEnglish").val() + " ";
            var engTextArray = engText.split(" ");
            $("#txtHindi").val($("#txtHindi").val() + engTextArray[engTextArray.length - 2]);

            document.getElementById("txtHindi").focus();
            $("#txtHindi").trigger({
                type: 'keypress', keyCode: keyVal, which: keyVal, charCode: keyVal
            });
        }
    });

    $("#txtHindi").bind("keyup", function (event) {
        setTimeout(function () { $("#txtEnglish").val($("#txtEnglish").val() + " "); document.getElementById("txtEnglish").focus() }, 0);
    });
} //end onLoad function

google.setOnLoadCallback(OnLoad);

C# .Net insertion code

public int insert(person d)
    {
        String Query;
        string ConnStr = new ConnectionClass().ConnStr();
        using (OleDbConnection conn = new OleDbConnection(ConnStr))
        {
            Query = "insert into UTF(ENGLISH_NAME, HINDI_NAME) VALUES('" + d.ENGLISH_NAME + "','" + d.HINDI_NAME + "')";
            OleDbCommand cmd = new OleDbCommand(Query, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

        }
        return 1;
    }

Do you have some code that you wrote to get data into your database? — Glenn Ferrie, Sep 26 '17 at 12:18
I'm nor familiar with that, can you show the part when you actually inserting row, your sql query? — SᴇM, Sep 26 '17 at 12:18
You're going to need this, if you do not have it: http://www.oracle.com/technetwork/developer-tools/developer-suite/downloads/101202winsoft-087370.html -- you're going to need the Oracle Native client and you're going to have to setup a connection. Are you familiar with TSNNames.ora? — Glenn Ferrie, Sep 26 '17 at 12:20
Can you please use CommandParameters instead of concatenating strings to make a sqlstatement ... see https://stackoverflow.com/a/5427980/578411 — rene, Sep 26 '17 at 12:25

SᴇM · Answer 1 · 2017-09-26T12:46:45.627

0

Change your Query to this (do not change, this part for knowledge purposes only):

Query = "insert into UTF(ENGLISH_NAME, HINDI_NAME) VALUES('" + d.ENGLISH_NAME + "',N'" + d.HINDI_NAME + "')";

You need prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.

But!

(Actual part what you need to do)

use Parameterized SQL queries. How and Why to Use Parameterized Queries

Query = "insert into UTF(ENGLISH_NAME, HINDI_NAME) VALUES( @ENGLISH_NAME, @HINDI_NAME)";
cmd.Parameters.Add(new OleDbParameter("@ENGLISH_NAME",d.ENGLISH_NAME));
cmd.Parameters.Add(new OleDbParameter("@HINDI_NAME",d.HINDI_NAME));

edited Sep 26 '17 at 12:46

answered Sep 26 '17 at 12:25

SᴇM

7,024
3
24
41

and that doesn't support named parameters – rene Sep 26 '17 at 12:31
_"OleDB doesn't recognize named parameters, but it apparently does recognize that you're trying to convey a named parameter, so you can use that to your advantage and make your SQL semantic and easier to understand. So long as they're passed in the same order, it'll accept a variable as a named parameter."_, ref : [This post](https://stackoverflow.com/questions/2675610/how-to-update-a-table-using-oledb-parameters). – SᴇM Sep 26 '17 at 12:34

How to store unicode character in oracle database

1 Answers1

But!