More then a year ago I asked about reading DB2 EBCDIC data in a .Net program. The amazingly helpful answer included a routine for reading VARCHAR columns.
I am now faced with reading CLOB columns.
Is their internal structure similar in that the first word contains the actual size? Or is it the whole word, considering the possible length of the CLOB is much larger?
Thanks!
Not the mainframe but DB2/400 (midrange) appears to store CLOB fields externally from the table itself:
CREATE TABLE QTEMP/T1 (F1 CLOB(512))
INSERT INTO QTEMP/T1 (F1) VALUES ('TEST')
SELECT * FROM QTEMP/T1
F1
*POINTER
DSPPFM QTEMP/T1
* . . . + . . . . 1 . . . . + . . . . 2 . . . . + . . . . 3 . .
00000000 00000000 00000000 00000000 5CD7D6C9 D5E3C5D9 40404040 40404040
*...+....1....+....2....+....3..
*POINTER
Is there any particular reason you're not accessing the database using a DB/2 driver to handle all of these details? It's like reading from SQL server by trying to parse the .MDF files.
See IBM Redbooks | Large Objects with DB2 for z/OS and OS/390 chapter 4.4.1 LOB table space organization for more information.
This is a much more detail explanation of how to do so http://ripalsoni.wordpress.com/2008/07/24/how-do-i-read-write-oracle-clob-data-in-aspnet-or-vbnet/
Step 1: Add a reference – Oracle.Dataaccess.dll ( found in ODP.NET )
Step 2: Imports following namespaces
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Step 3: Create a connection string
Public ReadOnly connectionstring = "data source = oradb;user id = rmsoni;password=rmsoni99"
Step 4: Create Following Public Methods
Public Sub ReadLOBData()
Dim con As New OracleConnection(connectionstring)
con.Open()
Dim sql As String = "select CLOBTEXTFIELD from TestCLOB where ID=1"
Dim cmd As OracleCommand = New OracleCommand(sql, con)
Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()
Dim blob As OracleClob = dr.GetOracleClob(0)
txtOutput.Text = blob.Value()
blob.Close()
dr.Close()
con.Close()
End Sub
Complete Source Code –
Public Sub WriteLOBData()
Dim connection As New OracleConnection(connectionstring)
connection.Open()
Dim strSQL As String = "INSERT INTO TestCLOB (ID,CLOBTEXTFIELD) VALUES (1,:TEXT_DATA) "
Dim paramData As New OracleParameter
paramData.Direction = ParameterDirection.Input
paramData.OracleDbType = OracleDbType.Clob
paramData.ParameterName = "TEXT_DATA"
paramData.Value = txtInput.Text
Dim cmd As New OracleCommand
cmd.Connection = connection
cmd.Parameters.Add(paramData)
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
paramData = Nothing
cmd = Nothing
connection.Close()
End Sub
