Problem In Inserting Unicode Data in Stored Procedures
Posted by: Vinay KD
Date: August 15, 2011 09:47AM

Hello
I am trying to insert some unicode data (hindi language) in a table.
Everything is ok, and data correctly get inserted in table if I am sending the data from a Query.
Problem raises if i am using the stored procedures.


Connection String defined in Web.Config

<add name="DBConnection" connectionString="server=localhost; userid=localuser; password=1234568987; database=DBUNICODEDATA; CharSet=utf8; pooling=false;"/>

CREATE TABLE `ztesthindi`
(
`rid` int(10) unsigned NOT NULL,
`engcontent` varchar(500) NOT NULL,
`hindicontent` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(in this table 'hindicontent' column will contain the unicode data)

'----------------------- THIS ASP.NET 3.5 CODE WORKING PERFECTLY ------------------
Dim Qstr As String
Qstr = "INSERT INTO ztesthindi(rid, engcontent, hindicontent) values (110, '" & TextBox1.Text & "', '" & TextBox2.Text & "')"
Constring = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
dbCon = New MySqlConnection(Constring)
dbCon.Open()
Dim MySqlCommand As New MySqlCommand("", dbCon)
MySqlCommand.CommandText = "SET character_set_results=utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET NAMES utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET character_set_client=utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET character_set_connection=utf8"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = "SET collation_connection=utf8_general_ci"
MySqlCommand.ExecuteNonQuery()
MySqlCommand.CommandText = Qstr
MySqlCommand.ExecuteNonQuery()
'-----------------------

The above asp.net 3.5 code is executing perfectly and inserting the data as well in the above table.

Now i created a stored procedure to insert the data in above table

CREATE DEFINER=`localuser`@`%` PROCEDURE `zAdd_ContentRow`(
IN p_engcontent VARCHAR(500),
IN p_hindicontent TEXT
)
BEGIN
DECLARE p_rid Integer;
Select Max(rid)+1 into p_rid From ztesthindi;
IF p_rid IS NULL THEN
SET p_rid = 101;
END IF;
SET character_set_results=utf8;
SET NAMES utf8;
SET character_set_client=utf8;
SET character_set_connection=utf8;
SET collation_connection=utf8_general_ci;
INSERT INTO ztesthindi VALUES(p_rid, p_engcontent, p_hindicontent);
END

The following code is returning error while trying to insert the row in above table.
"Incorrect string value: '\xE0\xA4\x90 \xE0\xA4...' for column 'p_hindicontent' at row 1"

Try
dbCon = New MySqlConnection
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
dbCmd = New MySqlCommand
dbCmd.Connection = dbCon
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "zAdd_ContentRow"
dbCmd.Parameters.AddWithValue("p_engcontent", TextBox1.Text)
dbCmd.Parameters.AddWithValue("p_hindicontent", TextBox2.Text)
dbCon.Open()
dbCmd.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.Message)
End Try

I tried to update the insert statement in stored proceudre
INSERT INTO ztesthindi VALUES(p_rid, p_engcontent, p_hindicontent);
with
INSERT INTO ztesthindi VALUES(p_rid, p_engcontent, convert(p_hindicontent using utf8));

but still getting the same error.

Please help me in this issue that how can i sortout the porblem.

Thank you.

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem In Inserting Unicode Data in Stored Procedures
8844
August 15, 2011 09:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.