Pass Unicode To an Oracle Stored Procedure (Article)
Author: David Nishimoto
Version Compatibility: Visual Basic 6, ASP
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
|
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; /
|
<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 | |
|
|
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>