반응형
'create objects
Set Conn = Server.CreateObject("ADODB.Connection")
set recordset = server.createobject("ADODB.Recordset")
'open connection object to database
conn.open "DSN=theDatabase;User ID=userid;password=password"
sql="select CLOBField from CLOBFieldtable"
'open recordset object
recordset.Open sql, conn
'create the Field object for the CLOB field
clobFieldObject = recordset.Fields("CLOBField")
'Use the GetChunk Method to read the data.
'Pass the size of the data you want to retrieve
'in this case, we are retrieving 50K
strCLOBstring = clobFieldObject.GetChunk(50000)
Editing CLOB fields are a little more complicated because I have to
use the empty_clob() Oracle function to create an empty CLOB field,
and then use the AppendChunk method to write the binary data to the
empty field. The code looks like this:
'use the same connection and recordset objects as above
sql="Update CLOBFieldtable set CLOBField = empty_clob() where CLOBid =
1"
'no need to use a recordset here
conn.execute(sql)
'get your empty clob field in a recordset to update it.
sql="select CLOBField from CLOBFieldtable where CLOBid = 1"
recordset.Open sql, conn
'Get the field object we want to write to
CLOBFieldObj = recordset.Fields("CLOBField")
'Now use the AppendChunk Method to write your data
CLOBFieldObj.AppendChunk("Some Huge Long String to write to CLOB
field")
'Commit the transaction
recordset.update
=========================================================
Example 1 : Saving the Data in a SQL Server Image Column to a File on the Hard Disk
The code in this example opens a recordset on the pub_info table in the pubs database and saves the binary image data stored in the logo column of the first record to a file on the hard disk, as follows: 1. Open a new Standard EXE Visual Basic project.
2. On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.
3. Place a CommandButton control on Form1.
4. Make the following declarations in the form's General declarations section: Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
5. Cut and paste the following code into the Click event of the CommandButton that you added to the form: Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"
Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite
rs.Close
cn.Close
6. Save and run the Visual Basic project.
7. Click the CommandButton to save the binary data in the logo column of the first record to the file c:\publogo.gid. Look for this file in Windows Explorer and open it to view the saved image.
The code in this example declares an ADODB Stream object and sets its Type property to adTypeBinary to reflect that this object will be used to work with Binary data. Following this, the binary data stored in the logo column of the first record in the pub_info table is written out to the Stream object by calling its Write method. The Stream object now contains the binary data that is saved to the file by calling its SaveToFile method and passing in the path to the file. The adSaveCreateOverWrite constant passed in as the second parameter causes the SaveToFile method to overwrite the specified file if it already exists.
Example 2 : Transfer the Image Stored in a .gif File to an Image Column in a SQL Server Table
The code in this example saves an image stored in a .gif file to the logo column in the first record of the pub_info table by overwriting its current contents, as follows: 1. Open a new Standard EXE Visual Basic project.
2. On the Project menu, click to select References, and then set a reference to the Microsoft ActiveX Data Objects 2.5 Object Library.
3. Place a CommandButton on Form1.
4. Make the following declarations in the form's General declarations section:Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
5. Cut and paste the following code in the Click event of the CommandButton that you added to the form:Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"
Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "<path to .gif file>"
rs.Fields("logo").Value = mstream.Read
rs.Update
rs.Close
cn.Close
6. Save and run the Visual Basic project.
7. Click on the CommandButton to run the code to stream the contents of the .gif file to the ADO Stream object, and save the data in the Stream to the logo column in the first record of the recordset.
8. Verify that the image in the logo column has been modified by using the code in Example 1.
반응형