| Author |
 Topic  |
|
|
akbar
Starting Member
USA
1 Posts |
 Posted - 15 January 2002 : 10:47:40
|
I have been working on an Oracle port along with one other developer, for a couple sites we are putting together.
So far we seem to have everything working, including searches (case insensitive, I will go into how in a sec) and admin stuff.
The only things left to do are
1) Figure out a good, cheap way of doing large inserts (for posts that are longer then 4000 char's) ( I would like to do it in a way that does not involve chunking the data )
2) Get all the setup stuff working (including moving all our pl/sql scripts into an inc_create_forum_oracle.asp
Before I go into how we got this far, I want to thank the others who posted on their solutions/methods for an oracle port in this forum, the stuff posted was invaluable.
Ok we are using oo4o, and I will put link/help stuff in the readme.txt (or maybe make an readme_oracle.txt?) about how to set it up. I tried to make the changes as unobtrusive as possible, i.e. limiting changes wherever possible and wrapping others in strDBType="oracle" conditions. The port is being done of 4.0b3 with patch 001 applied.
I would love to send the code back to the main developers, so they could integrate it.
Some details on what/how we have gotten where we are. For most connections we used an OLE DB provider to handle the database connection, and used ADO to handle the data.
We ran across three problems with this method:
1) It does not like ';' at the end of a SQL statement (odd eh?)
2) type conversion issues
3) Transact SQL and PL/SQL differences
1 and 2 were pretty easy solves (I did the solutions in such a way that if you use a non oracle database it should be effectively identical to what you had before).
To solve 3, I had to rewrite a couple SQL statements, I did do an if strDBType = "oracle" branch, but there are other solutions and would welcome any input  .
Now we hit the problem of case sensitive searches. To do this we used the method suggested by the developers below (but questioned due to it's theoretical higher cpu cost on the DB server). We split the CLOB into bytes (sized 32,767) and check those bytes out. (The search actually goes from offset 1 to the length of the CLOB in steps of 32767 - search criteria length (while still searching 32,767 bytes), and stops at the first hit). This results in a maximum search criteria size of 32,766 characters (which I hope is not an issue).
I will be testing the code I have against both an access database as well as SQL server database (to make sure I have not broken anything there). I will not currently able to test it against a MySQL database.
Ok sorry for the long post, but what I am mainly after is any suggestions for my problem listed way above (#1). Currently I have tried using both oo4o methods and ado -> ole db methods and have had some (but different) problems with both. I am trying to avoid splitting the message into smaller pieces and moving those up, but will do that if necessary.
Thanks for any input/help
Edited by - akbar on 15 January 2002 10:57:34< |
|
|
h20
Starting Member
39 Posts |
Posted - 13 February 2002 : 00:08:55
|
Hey akbar,
Sounds like you are on the right track. Are you performing the search and CLOB parsing in a PL/SQL procedure? If so I would not worry about any performance issues. I think the performance issues spoken of before were ASP/ADO related.
I have another idea for Oracle... using the variable character data type... I am in class now and will post the idea tomorrow.
Late!
< |
 |
|
|
h20
Starting Member
39 Posts |
Posted - 13 February 2002 : 15:24:39
|
We really need to get away from using CLOBS... or any other Oracle related binary fields. Their parsing and handling is far too proprietary. I have spoken with Oracle directly several times about managing Oracle binary fields and the consensus is that although you can manage Oracle’s binary fields on a very rudimentary level… any advanced manipulation on Oracle binary fields would require the purchase of other Oracle tools that would allow for fully featured binary field management. It sounds like you have come a long way with using CLOBS and as I mentioned before… it sounds like you are using a PL/SQL procedure to perform your search (which would be the only way to feasibly do a search like this). Anyway, enough about CLOBS… I am sure we have all experienced enough frustration with those dam things.
Ok.. Here is the idea I had last night...
Let’s first start off with a question...
How many posts are actually over 4000 characters?
I would bet that no more than 10% are. That being the case... here is my idea.
We could make another table to store the message content. We would still have the message table to store all of the unique data… (i.e. messageid, username, replycount…) but we could have a 1:N relationship to another table that actually stores the message content. If and when someone posts a message that is > 4000 characters we could just create another record in the new table. The primary key would be a composite key of two fields. These two fields could be the messageid field and a counter field. And since we would not be combining the values of these two fields together then we do not have to worry about duplicating their combined value. For example, if a message had 18000 characters then there would be a total of 5 records storing the message content in this new table. This of course would allow us to store the message content in a character field which would allow us to feasibly perform the case search without having to use a PL/SQL procedure… which would make the implementation of Snitz/Oracle much more attractive.
Anyway… let me know what you think… I sounds like you have already done a lot of work with CLOBS… It may be a bit hard to turn cheek now… but it may be in the best interest of the ALL MIGHTY SNITZ!
Scott
< |
|
|
|
jeffery
Junior Member
USA
176 Posts |
Posted - 24 July 2003 : 12:27:30
|
| Ive been asked about integrating this with an existing Oracle user database. akbar, are you willing to share the code you have?< |
www.thomasforum.com |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 25 July 2003 : 14:46:59
|
akbar,
If you could provide documentation on how you did this integration, we can put this into the base code and thus prevent you from having to re-do all this wonderful work when you need to upgrade in the future.< |
Reinsnitz (Mike) |
|
|
|
sikandar
Junior Member
Pakistan
135 Posts |
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 21 October 2003 : 08:44:01
|
Any Chance of actualy having you share the implimentation? We've wanted to get an officialy Oracle support in the product for a long time, but didn't have the right people to actualy develop it.
The idea being to actualy build in Oracle support out of the box.< |
Reinsnitz (Mike) |
|
|
|
shannon
Starting Member
11 Posts |
Posted - 25 July 2006 : 15:12:26
|
I have seen posts out there saying that Oracle support has been planned for a while ...
http://www.dmxzone.com/ShowDetail.asp?NewsId=1268
Has anyone ever successfully implemented this? If so are you willing to share with the Snitz developers so that support can be included out of the box?
Thanks for the info,
Shannon< |
|
|
| |
Topic |
|
Forum Locked
