Pass Unicode To an Oracle Stored Procedure (Article)

Category:

ASP, HTML and XML

Type:

Snippets

Difficulty:

Intermediate

Author: David Nishimoto

Version Compatibility: Visual Basic 6, ASP

More information:

This article explains how to pass unicode code points to an oracle stored procedure. The stored procedure uses the REF CURSOR type to return a resultset back to an ADO recordset. See also http://www.listensoftware.com/searching_unicode.html.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Setting up the Oracle Data Source Name:8.1.7.2 Oracle Driver

NLS_LANG must be change to AMERICAN_AMERICA.UTF8 in the Windows registry.
The 8.1.7.2 Oracle ODBC Driver must be download from Oracle and installed on the Server Machine. You must signup as a member of the Oracle Technology Network (OTN) to download the ODBC driver.
From the ODBC Driver tabs, select the Workaround item, check "force SQL_WCHAR Support" If you don't check this box the ODBC driver will return garabage.
Setup a ODBC Data Source Name (DSN) referencing the 8.1.7.2 Oracle driver.

Oracle unicode_test table
Column	Description
unicode_number	This field is a varable length string with maximum length of 20 characters. The purpose of this field is to store the 2-byte number associated with the Unicode code point.
code_point	This fields is a variable length string with a maximum length of 200 character. The purpose of this field is to store the code point sequences.

create table unicode_test
(
	unicode_number not null varchar2(20),
	code_point varchar2(200)
);

With the release of Microsoft Data Access Component (MDAC) 2.5, ADO can be used to return an Oracle cursor through the use of the REF CURSOR type.
The both the Oracle 8.1.7.2 ODBC driver and Microsoft OLEDB provider for Oracle support the REF CURSOR type.
The unicode_pkg has one procedure, getCodePoint which receives unicode code point criteria and returns a Oracle cursor. The connection.execute method does not work with some Unicode code point within the SQL statement. The solution is to use a ADO command object and bind the search criteria fields as a NULL delimited Unicode strings. This will be explained below.

create or replace package unicode_pkg as type lookup_cur IS REF CURSOR; PROCEDURE getCodePoint(p_code_point in varchar2, p_cursor OUT lookup_cur); end unicode_pkg; / create or replace package body unicode_lookup_pkg as procedure getCodePoint(p_code_point in varchar2, p_cursor OUT lookup_cur) IS BEGIN if p_code_point='ALL' then OPEN p_cursor for select * from unicode_test; else OPEN p_cursor for select * from unicode_test where code_point like p_code_point; end if; end getCodePoint; end unicode_lookup_pkg; /

From Unicode.html the user enters a Unicode code point that has been stored in the database and presses search.
The search occurs in unicode_display.asp

	<script language=javascript>
	function submitPage()
	{
		document.frmMain.action='unicode_display.asp';
		document.frmMain.method='POST';
		document.frmMain.submit();
	}
	</script>

	<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8">

	<FORM NAME=frmMain>
	<pre>
	Unicode Code Point:<INPUT TYPE="text" NAME="code_point" VALUE="ALL">
	</pre>
	<br>
	<INPUT TYPE="button" VALUE="Search Unicode" onClick="submitPage();">
	</FORM>

Active Server Page: unicode_display.asp
The unicode_pkg has one procedure, getCodePoint which receives unicode code point criteria and returns a Oracle cursor. The connection.execute method does not work with some Unicode code point within the SQL statement. The solution is to use a ADO command object and bind the search criteria fields as a NULL delimited Unicode strings.
cmd.CreateParameter _ ("@p_code_point",adVarWChar,adParamInput,200,sCode_Point) This command parameter call @p_code_point binds an Unicode Null delimited string as an input parameter for the unicode_pkg.getCodePoint Procedure. This allows the user to submit Unicode code points to be search in the Database.

<%@Language=VBScript%>
<!--#INCLUDE FILE="adovbs.inc"-->
<%Response.Buffer = True

session.CodePage=65001 'UTF-8 Codepage supported only in iis5.0
Server.ScriptTimeout=12000

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open "your_8.1.7.2_dsn", "user_name", "password"

dim lCount, sCode_Point

set rstUnicode=nothing

		sCode_Point=Request("code_point")
	
		set cmd=Server.CreateObject("ADODB.Command")
		cmd.ActiveConnection=cnn
		cmd.CommandText="unicode_pkg.getLocality"
		cmd.CommandType=adCmdStoredProc
		cmd.Parameters.append _
   cmd.CreateParameter("@p_code_point",adVarWChar,adParamInput,200,sCode_Point)

		Set rstUnicode = cmd.Execute


%>
<HTML>
<HEAD>
	<LINK REL="stylesheet" HREF="rnd_unicode.css">
	<TITLE>TR Unicode Results</TITLE>
	<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8">
</HEAD>

<BODY style="font-family: arial unicode ms">
	<SPAN class="custH1">Unicode  </SPAN><BR>
	
	<FORM NAME="frmMain" METHOD="post">

	<%
	if not rstUnicode is nothing then
		lCount=0
		do while not rstUnicode.EOF%>
		<%
		lCount=lCount+1
		
		'if lCount > 100 then
		'	exit do
		'end if
		%>
		<TABLE BORDER=1 width=500>
		<TR>
			<td width=25%><SMALL><%=lCount%></SMALL></TD>
			<TD width=50%>
				<%=rstUnicode("code_point")%>
			</TD>
		</TR>
		</TABLE>
			<%rstUnicode.movenext%>
		<%
		response.flush
		loop%>
	<%end if%>
	
	

	</FORM>


<%
if not rstUnicode is nothing then
	rstUnicode.close
end if
cnn.close
Set cnn = Nothing%>

</BODY>
</HTML>