| Author |
 Topic  |
|
h2o
Starting Member
46 Posts |
 Posted - 13 December 2000 : 15:01:03
|
Hey dust... where can I get these tools?
Hey, did that ASP PL/SQL procedure call work ok?
< |
 |
|
|
dust
Starting Member
Australia
18 Posts |
Posted - 17 December 2000 : 20:35:35
|
quote:
Hey dust... where can I get these tools?
Here it is : http://www.benthicsoftware.com
Go http://astalavista.box.sk/ to find the cracks.
quote:
Hey, did that ASP PL/SQL procedure call work ok?
it works 
--------------------------------------------
Programming is like sex : One mistake and you have to support for a lifetime.< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 20 February 2001 : 23:38:15
|
Guys,
Any news on this ?
I finally got Oracle 8i personal edition installed, the trigger stuff I got, creating tables shouldn't be that hard either, but were I'm stuck is the CLOB v.s. VARCHAR2 part.
I understand that using VARCHAR2(4000) might be the easiest option, but I rather not limit the length of a topic at all and use CLOB.
I wasn't able sofar however to find a working example of an ASP page that inserted text in a CLOB and retrieved it again.
dust,
You said you used CLOB instead of VARCHAR2, would you mind sharing that code ?
Pierre< |
|
|
|
h20
Starting Member
39 Posts |
Posted - 21 February 2001 : 22:53:06
|
Hey Gor, CLOB is the best way to go. If CLOBS are going to be used then Oracle's objects for OLE will have to be used and not Microsoft's OLDE for oracle. Oracle's objects for OLE has its own methods that allow you to create updateable recordsets. One such method is the write method which allows you to write text, pictures, or whatever you want to Oracles LOB fields (CLOB,BLOB, etc...) I am at home now... I will post code examples tomorrow from work. Good thing is that you should be able to use the same SQL code to update the tables (minus the triggers for the primary key )...
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 22 February 2001 : 02:16:43
|
Great
Pierre< |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 22 February 2001 : 12:12:20
|
I'm kinda geting antsy about this one :) it's sooooo exciting... :)
Reinsnitz (Mike)
><)))'>
"Therefore go and make disciples of all nations,..." Matthew 28:19a< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 22 February 2001 : 14:28:31
|
Remember that a lot still can go wrong....I couldn't get it to work with Interbase just because I don't know how to add Blobs in Interbase using ASP/Sql....so it kind of comes down to h20 at the moment to explain me how to handle them in Oracle.
But ofcourse when I could not get Oracle to properly run on my system...it was worse than it is now
Pierre< |
|
|
|
h20
Starting Member
39 Posts |
Posted - 22 February 2001 : 15:13:37
|
Man ... sorry it has taken me so long to respond... i just typed an extreamly long post and it is not showing... so I will try again.... whew... here we go again.
Ok.. here is a link to download Oracle's Objects for OLE (oo4o):
ftp://external-ftp.us.oracle.com/dev_tools/patchsets/mslang/oo4o/
Make sure that you download the RUNTIME files only. Also make sure you follow the text file instructions within the RUNTIME folder before you download. You want to make sure that you install the right oo4o version for your Oracle client version. I recommend that you have at least 8.1.6 client installed. this will allow you to install oo4o 8.1.7, Oracles newest OLE drivers. Ok on with the examples... again...
First example... this one reads a CLOB. Notice the OraSession, OraDatabase and OraClob datatypes in the Dim section. These are only accessable with Oracles OLE drivers and are must haves for communication between ASP and Oracle LOBs.
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim PartDesc As OraClob
Dim AmountRead As Long
Dim buffer As Variant
Dim buf As String
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb",
"scott/tiger", 0&)
'Add PartDesc as an Output parameter and set its initial value.
OraDatabase.Parameters.Add "PartDesc", Null, ORAPARM_OUTPUT
OraDatabase.Parameters("PartDesc").ServerType = ORATYPE_CLOB
'Execute the statement returning 'PartDesc'
OraDatabase.ExecuteSQL ("BEGIN select part_desc into :PARTDESC from
part where part_id = 1 for update NOWAIT; END;")
'Get 'PartDesc' from Parameters collection
Set PartDesc = OraDatabase.Parameters("PartDesc").Value
'Get a free file number
FNum = FreeFile
'Open the file.
Open "Desc.Dat" For Binary As #FNum
'Read entire CLOB value, buffer must be a Variant
AmountRead = PartDesc.Read(buffer)
'put will not allow Variant type
buf = buffer
Put #FNum, , buf
Close FNum
Here is another one. This example modifies a CLOB field. Again notice the Ora... datatypes.
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim PartDesc as OraClob
Dim buffer As String
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb",
"scott/tiger", 0&)
'execute the select statement
set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&)
set PartDesc = OraDynaset.Fields("part_desc").Value
'Open the file for reading
Open "partdesc.dat" For Binary As #FNum
'Allocate buffer to the size of file FNum and read the entire file
buffer = String$(LOF(FNum), 32)
Get #FNum, , buffer
'lock the row for write operation
OraDynaset.Edit
amount_written = PartDesc.Write(buffer)
'commit the operation and release the lock
OraDynaset.Update
Close FNum
rest of post below
Edited by - h20 on 22 February 2001 15:15:20< |
|
|
|
h20
Starting Member
39 Posts |
Posted - 22 February 2001 : 15:14:08
|
Here is the rest of my post:
Ok, last one. This one actually uses dynasets. There are some issues using this example with joined recordsets though. This example uses a BLOB field to store an image... the code sould be the same for CLOB fields.
Ok that is it... I hope these example help you out...
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim Part As OraDynaset
Dim PartImage as OraBLOB
Dim ImageChunk() As Byte
Dim amount_written As Long
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb",
"scott/tiger", 0&)
'Create a Dynaset containing a BLOB and a CLOB column
set part = OraDatabase.CreateDynaset ("select * from part",0)
set PartImage = part.Fields("part_image").Value
'First insert Empty LOB in the part_image column
part.AddNew
part.Fields("part").Value = 1234
part.Fields("part_image").Value = Empty
part.Update
'move to the newly added row
Part.MoveLast
'Open the file for reading PartImages
Open "part_picture.gif" For Binary As #FNum
'Re adjust the buffer size to hold entire file data
Redim ImageChunk(LOF(FNum))
'read the entire file and put it into buffer
Get #FNum, , ImageChunk
'call dynaset's Edit method to lock the row
part.Edit
amount_written = OraBlob.Write(ImageChunk)
part.Update
'close the file
Close FNum
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 22 February 2001 : 15:29:50
|
Printing...testing...will get back to you as soon as possible...
Pierre< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 23 February 2001 : 17:13:36
|
Here is another example.... the others were in straight VB... these are in script... notice the declaration of the connectin pool (Oracles version of transaction server)... fairly simple... this example is much easier to follow.
' ********************
' *** GLOBAL.ASA ***
' ********************
<OBJECT RUNAT=Server SCOPE=Application ID=OraSession
PROGID="OracleInProcServer.XOraSession"></OBJECT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
' Get an instance of the Connection Pooling object and create a pool of
' OraDatabase. Modify the username, password and database name for the
' database you will be using.
OraSession.CreateDatabasePool 1, 40, 200, "mydatabase", "scott/tiger", 0
End Sub
</SCRIPT>
- - - - - - - - - - - Part 2 of the Sample Code Begins Here - - - - - - - - - -
<%
' ************************
' *** RenderHTML.asp ***
' ************************
' Retrieve a connection from the pool
Set OraDatabase = OraSession.GetDatabaseFromPool(10)
' Select the HTML page you want to render
Set OraDynaset = OraDatabase.CreateDynaset ("select pagetext FROM html " &_
"WHERE pagename = 'Page 1'", 0)
' Retrieve the HTML data into an OO4O CLOB datatype
Set OraClob = OraDynaset.Fields("pagetext").Value
' Read the text into a buffer and return the number of characters read
AmountRead=OraClob.Read(Buffer)
' Render the HTML page after converting the CLOB datatype into a String
' and print out the number of total characters read
Response.Write CStr(Buffer)
Response.Write "<br>"
Response.Write "Total Characters Read: "
Response.Write AmountRead
Response.End
%>
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 23 February 2001 : 18:24:07
|
h20,
Thanks for the example scripts, though if you had just told me they were in the Oracle 8 documentation, it would have saved you a lot of typing.
I noticed the examples were in VB, and from time to time that makes a difference.
They also all use external files for input and output.
But I managed to translate the examples to ASP code.
Sofar I now am able to store a textstring into a CLOB field (after creating a new record) and retrieve the values again.
I also can delete them.
The thing I've spend most of my time on is to get the code used, looking as much as possible the way the forum uses it.
That means I want/need to use a combination of ODBC and OO4O.
It will make more sence when I show a few code samples (will do soon).
One more thing you might be able to help me with is the paging problem.
Do you know how to implement paging with Oracle, like i.e. is being used on the members.asp page (or the forum.asp page) ?
Pierre< |
|
|
|
h2o
Starting Member
46 Posts |
Posted - 23 February 2001 : 19:48:40
|
No wories, I copied and pasted all of that stuff...
About the paging...you mean how the members.asp page divides the members into 90+ pages.
Here is another example... this example uses straight SQL instead of the update method:
<%
dim num,oraclob
' Retrieve a connection from the pool
Set OraDatabase = OraSession.GetDatabaseFromPool(10)
' Select the HTML page you want to render
Set OraDynaset = OraDatabase.CreateDynaset ("select * FROM NEWS", 0)
num=OraDynaset.Recordcount
OraDatabase.DbExecuteSQL ("insert into NEWS(NEWSID,POSTDATE,BODY) values("&num+1&",'03/03/2003','<html><body><table><tr><td>"&num+1&"</td><td>Scott Murray is cool</td></tr><tr><td colspan=2><iframe name=me src=http://www.hp.com></iframe></td></tr></table></body></html>')")
OraDynaset.close
Set OraDynaset = OraDatabase.CreateDynaset ("select * FROM NEWS where NEWSID="&num+1, 0)
'Retrieve the HTML data into an OO4O CLOB datatype
Set OraClob = OraDynaset.Fields("BODY").Value
'Read the text into a buffer and return the number of characters read
AmountRead=OraClob.Read(Buffer)
' Render the HTML page after converting the CLOB datatype into a String
' and print out the number of total characters read
Response.Write CStr(Buffer)
Response.Write "<br>"
Response.Write "Total Characters Read: "
Response.Write AmountRead
Response.End
%>
< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 24 February 2001 : 03:13:41
|
quote:
About the paging...you mean how the members.asp page divides the members into 90+ pages.
Yes, but I think I've got it.
I think I've got all the basic functionality working in a way I can start integrating it in the existing forum code. It probably won't suprise that my main consern is that it works on all databases at once, and not just on Oracle.
Will be away from my computer for the next two days, but am going to start on it when I get back.
Pierre< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 07 March 2001 : 17:19:45
|
Locking this topic....still working on the conversion...but going to start new topics for the different questions I've got.
Pierre< |
|
|
|
Topic |
|
Forum Locked
Topic Locked
