| unable to save strings with crlf to table from ASP [message #128052] |
Thu, 14 July 2005 16:37 |
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
|
Member |
|
|
I have found something odd with the orawin924 odbc driver when using it in an ASP page. I am unable to pass strings to a stored procedure if the string contains a carriage return/line feed character.
I get the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Oracle][ODBC]Syntax error or access violation.
which I was able to track the problem down to the cr/lf combo. if the strings I pass in have no cr/lf, then the save works. If the strings I pass in have one or more cr/lf, then the save fails and gives me the above error.
I have not been able to figure out an easy way to escape out the cr/lf combos. I also am not able to use alternative means of sending the code to Oracle (ie creation of oledb commands, parameters, etc such) since there is extensive code that exists with this methodology and I cant change it.
Here is a test example of what I have done. Please note that I know that the test does not take into account things like strings with single quotes, etc. (this is just a test example).
Hopefully someone can help me figure out a way to save the crlf in strings.
1) create a simple test table:
CREATE TABLE TESTTABLE
(
EMPNO NUMBER,
DESCR VARCHAR2(512 BYTE)
OTHER VARCHAR2(10 BYTE)
);
2) create a simple stored procedure to populate the table:
PROCEDURE "SCOTT".PUTEMPINFO(
p_empno IN NUMBER DEFAULT NULL,
p_descr IN VARCHAR2 DEFAULT NULL,
p_other IN VARCHAR2 DEFAULT NULL)
IS
m_rowcount NUMBER := 0;
BEGIN
UPDATE testtable
SET descr = p_descr, other = p_other
WHERE empno = p_empno;
m_rowcount := SQL%ROWCOUNT;
IF m_rowcount = 0
THEN
INSERT INTO testtable
(empno, descr, other)
VALUES (p_empno, p_descr, p_other);
END IF;
COMMIT;
END;
3) create simple ASP page to update the table:
<!--#include virtual="/include/adovbs.asp"-->
<%
if len(request.form("empno")) > 0 then
connStr = "DSN=ORACLE9I; USER ID=scott; PASSWORD=tiger;"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connStr
strsql = "{ call putempinfo (" & request.form("empno") & ",'" & server.htmlencode(request.form("descr")) & "','" & request.form("other") & "') }"
set rs = objConn.Execute(strSQL)
end if
%>
<html>
<head>
<title>Untitled</title>
</head>
<body>
<form action="oracletest.asp" method="post">
<table>
<tr>
<td>Empno:</td>
<td><input type="text" name="empno" size="4" maxlength="4"></td>
</tr>
<tr>
<td>Descr:</td>
<td><textarea cols="50" rows="3" name="descr"></textarea></td>
</tr>
<tr>
<td>Other:</td>
<td><input type="text" name="other" size="10" maxlength="10"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Save Info"></td>
</tr>
</table>
</form>
</body>
</html>
4) to test, enter any value for empno and other (ie 1234 and asdf) and enter two separate lines (ie hit Enter between the lines) and submit the form.
any help appreciated.
[Updated on: Thu, 14 July 2005 17:31] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
