| Author |
 Topic  |
|
|
gor
Retired Admin
Netherlands
5511 Posts |
 Posted - 09 March 2001 : 13:44:05
|
While porting the forum to Oracle I ran into a problem trying to convert the search.asp page. Any help would be very much appreciated.
I used CLOB as datatype for T_MESSAGE in FORUM_TOPICS and T_REPLY in FORUM_REPLY.
But that means that the message isn't really stored in the those two fields, they only hold a pointer to the (for me unknown) location of the real values.
Writing and retrieving them goes like this:
strSql2 = "SELECT T_MESSAGE FROM " & strTablePrefix & "TOPICS WHERE "
strSql2 = strSql2 & " TOPIC_ID = " & Request.QueryString("TOPIC_ID") & " "
set rs2 = my_Conn_Oracle.CreateDynaset(strSql2, 0)
amount_read = rs2.fields("T_MESSAGE").value.Read(strMessage)
rs2.close
with my_Conn_Oracle being a OO4O connection to the database.
But code like this in the searchpage:
strSql = strSql & " (" & strTablePrefix & "REPLY.R_MESSAGE LIKE '%" & ChkString(Request.Form("Search"), "SQLString") & "%'"
strSql = strSql & " OR " & strTablePrefix & "TOPICS.T_MESSAGE LIKE '%" & ChkString(Request.Form("Search"), "SQLString") & "%') "
throws an error (ofcourse).
Sofar I haven't been able to find info on how to search a CLOB from within a select statement.
The only thing I found was that you could open a CLOB and use a INSTR() to check to see if the searchstring was in it. Not an option in case of our search-page ofcourse.
One other option I see is disabling the search in the message body, but that would be really bad.
Another option would be to store an abstract of the message body using a fixed length in an extra field (in case of Oracle) and use that for the search. But that would make the database in Oracle (much) larger than in other database types.
Pierre< |
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 09 March 2001 : 14:09:01
|
I'm 99% sure you can't do a LIKE on a CLOB. I think there is a way around it, but for the life of me, I can't remember what it was (can tell it's Friday....)
Dave Maxwell
--------------
Proud to be a "World Class" Knucklehead < |
 |
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 09 March 2001 : 14:49:34
|
Well, for now even that is good news.
Any ideas on where to look for the workaround ?
Pierre< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 09 March 2001 : 15:51:52
|
Hello gor... to answer your question, Yes you can search a CLOB field... using oo4o. In fact I have an example somewhere on my pc.. I will look for it now. You may have to greate anoth block of code spicific for Oracle CLOB search... but from what I remember the code needed is not to heavy... brb with example.
< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 09 March 2001 : 16:05:38
|
*************************************************************
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
*************************************************************
PURPOSE
-------
This document shows you how to search for a string in a CLOB column in SQLPlus.
SCOPE & APPLICATION
-------------------
This is for any one who does not want to use PLSQL or other programs to view values
saved in a CLOB datatype. This article is not applicable to any other LOB types as
they are not single byte characters.
How To Search Within A CLOB Column
----------------------------------
To the Oracle8 Server, a LOB's value is unstructured and cannot be queried against.
It is required to use the DBMS_LOB package to access the data. Most of the time, it
will involve writing PL/SQL or other program code. However, there is an exception to
the CLOB datatype. The following is an example of how to use a CLOB datatype in the
where clause of a select statement.
1. Create a table containing a CLOB datatype and insert a row to the table.
SQL> create table clob1 (clobcol clob);
Table created.
SQL> insert into clob1 values ('abcdefghiji');
1 row created.
(Note that I don' t need to use the DBMS_LOB package to insert to the CLOB column.)
SQL> select * from clob1;
CLOBCOL
--------------------------------------------------------------------------------
abcdefghiji
(Note that I don' t need to use the DBMS_LOB package to query the CLOB column.)
2. Query the table by searching for a particular string in the CLOB datatype will fail with
ORA-932 because this is not a varchar or char datatype.
SQL> select * from clob1 where
2 clobcol like '%cde%';
clobcol like '%cde%'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes
3. Query the table by using the DBMS_LOB.INSTR(<column name>,
'<string to be searched>') procedure. If the specified string is in the CLOB column,
the procedure will return the position of the first appearance of the string. So if the
output value for the procedure is >0, the string exist in the CLOB column.
SQL> select * from clob1 where
2 dbms_lob.instr(clobcol, 'abc')>0;
CLOBCOL
--------------------------------------------------------------------------------
abcdefghiji
I hope this helps... here is the LIKE version dave was talking about.. pretty straight forward. i got this from oracle's metalink site. Since this is PLSQL you would have to use oo4o and use the Execute method of the Oracle database object to run this PLSQL against the oracle database only. This should work ok however. I read through your other examples.. why are you using ODBC for the other pages. If you have already declared an Oracle database connection pool oject in the global.asa files as i mention in the other examples i posted you would only have to reference that object to make a connection... no more messy Oracle connection strings... I know that there are so many issues with ODBC and oracle... I use oo4o in all of my Oracle ASP app. and I display integers all over the place with no problem...
Oracle design oo4o to be houes almost all of the properties and methods that microsoft put into their OLE drivers... so the code really would not have to be changed...
Let me know if these examples work out for you.
Edited by - h2o on 09 March 2001 16:12:33< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 09 March 2001 : 16:09:43
|
I take that back, this example does not use PLSQL... this should work out fine... if not let me know and I will locate a PLSQL example for you....
you still may want to use a separate string with this exact sql statements for oracle... oracle sql is a little different than SQL sql... hehe that was not confusing... good luck.
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 09 March 2001 : 16:28:30
|
Thanks h20 I'll try the code you supplied.
To answer your question about using ODBC instead of OO4O whenever I can:
One important problem with using OO4O is that if I want to create a Recordset I can't use:Set rs = Server.CreateObject("ADODB.RecordSet")
or
my_ConnC.execute(strSql)
used by the script right now (and all the other ODBC based databases), but would have to use:set rs = my_Conn_Oracle.CreateDynaset(strSql, 0) and my_Conn.ExecuteSql(strSql)
That would mean that every sql statement would have to be duplicated: once for Oracle and once for all the other databases.
The way I'm coding it now, the only changes needed are some rewrites of a couple of sql-statements so the conform to a even more mutuall format i.e. the way the self-joins are written needs a bit of rewrite, and adding extra code-sections to the sql-statements that use CLOB fields so that in case strDBType="oracle" the CLOB value is also retrieved/stored/deleted (using a OO4O connection for that, but contained in 1 function added to inc_functions.asp).
This way the code for the forum stays clean and the other databases are still supported.
I don't mind using OO4O for the search.asp because that is only one page with one sql statement.
Pierre< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 09 March 2001 : 16:54:02
|
I see.. good thinking...
If you have a problem with ODBC then use oo4o... i agree totally... i was just reading on CLOBS... you can store up to 4gb in a CLOB... that is huge...
anyway... good luck gor...
i have nt been able to check the forums everyday becase of an overwhelming workload... if you get stuck and need help right away or want me to fetch some examples from oracle's metalink database email me at:
smurray@rccu.org
anyway... let me know how it works out.
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 09 March 2001 : 17:18:30
|
Ok, another problem fixed. The code h2o supplied did the trick.
I didn't even need OO4O for that, so I could use 1 Sql statement for all databases 
I ended up needing only 8 (well 14 if you count the if..then..else) extra lines of code in search.asp to get it to work with Oracle also.
I reshuffled the existing SQL-statement a bit and added the extra Oracle code.
Here are some of the new lines:
if Request.Form("andor") = "phrase" then
if strDBType = "oracle" then
strSql = strSql & " ( dbms_lob.instr(" & strTablePrefix & "REPLY.R_MESSAGE, '" & ChkString(Request.Form("Search"), "SQLString") & "') > 0 "
strSql = strSql & " OR dbms_lob.instr(" & strTablePrefix & "TOPICS.T_MESSAGE, '" & ChkString(Request.Form("Search"), "SQLString") & "') > 0 "
else
strSql = strSql & " (" & strTablePrefix & "REPLY.R_MESSAGE LIKE '%" & ChkString(Request.Form("Search"), "SQLString") & "%'"
strSql = strSql & " OR " & strTablePrefix & "TOPICS.T_MESSAGE LIKE '%" & ChkString(Request.Form("Search"), "SQLString") & "%' "
end if
strSql = strSql & " OR " & strTablePrefix & "FORUM.F_DESCRIPTION LIKE '%" & ChkString(Request.Form("Search"), "SQLString") & "%'"
strSql = strSql & " OR " & strTablePrefix & "TOPICS.T_SUBJECT LIKE '%" & ChkString(Request.Form("Search"), "SQLString") & "%')"
else
I did the same for the else part (the search using keywords) and that was that.
Thanks for the help !
Pierre< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 09 March 2001 : 17:25:40
|
quote:
If you have a problem with ODBC then use oo4o... i agree totally... i was just reading on CLOBS... you can store up to 4gb in a CLOB... that is huge...
How much can you tell in 4GB ? 
quote:
anyway... good luck gor...
i have nt been able to check the forums everyday becase of an overwhelming workload... if you get stuck and need help right away or want me to fetch some examples from oracle's metalink database email me at:
smurray@rccu.org
anyway... let me know how it works out.
Thank you, if I run into more problems, I'll post them here and send you a link to the topic.
I finished the inc_create_forum_oracle.asp so it creates the tables/sequences/triggers and default category/forum/topic. I can add Categories/Forums/Topics/Replies
I can use Paging in forum.asp and topic.asp
I can search
Need to add the paging to Members.asp and the CLOB handling for the M_BIO / M_NEWS / M_QUOTE fields (shouldn't be a problem), and a few other things that I'll post in new topics.
Pierre< |
|
|
| |
Topic |
|
Forum Locked
Topic Locked
