Bartman88
asked on
VB.Net equivalent from VB6 CLOB Upload Procedure
I am trying to convert from VB6 to VB.Net and I am having a problem converting an upload procedure I used to use for reading large files (>>32k - .pdf, .xls, .zip, etc.) into CLOB fields in Oracle 10g tables. The script worked quite well in VB6 and Access97 and 2002 but I cannot get this to work. The biggest problem I am having is with:
chunksize = 32000
'Re adjust the buffer size
buffer = String$(chunksize, 32)
and
'Open the file.
Open st1 For Binary As #fnum
and most importantly
Get #fnum, , buffer
Any assistance would be greatly appreciated. I have attached to complete code below.
chunksize = 32000
'Re adjust the buffer size
buffer = String$(chunksize, 32)
and
'Open the file.
Open st1 For Binary As #fnum
and most importantly
Get #fnum, , buffer
Any assistance would be greatly appreciated. I have attached to complete code below.
Public Sub Upload2()
'Code for uploading any file into database.
Dim buffer As String
Dim chunksize, remainder As Long
Dim amount_written As Long
Dim ORide As Boolean
N1 = PID
n2 = POID
ORide = False
'Screen.MousePointer = 11
'strFilter = ahtAddFilterItem(strFilter, "PDF Files (*.PDF)", "*.pdf")
strFilter = ahtAddFilterItem(strFilter, "Any Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select file to upload...", _
Flags:=ahtOFN_HIDEREADONLY)
st1 = strInputFileName
'st2 = Left(strInputFileName, Len(strInputFileName) - Len(Dir(strInputFileName)))
st2 = Dir(strInputFileName)
If st1 <> "" Then
x1 = MsgBox("Upload: " & vbCrLf & vbCrLf & st2 & vbCrLf & vbCrLf & "into database?", vbYesNo, "File Upload")
Else
x1 = vbNo
End If
If x1 <> vbYes Then
ORide = False
MsgBox "No information uploaded", vbExclamation, "File Upload Cancelled."
Else
'Document2 = st2
DTitle2 = st2
Me.requery
Refresh
'Document2.Locked = False
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession.OpenDatabase(OracleSite, "USERNAME/PASSWORD", 0&)
'Create the OraDynaset Object
Set OraDynaset = OraDatabase.CreateDynaset("select * from po_link where proj_id = " & N1 & " and po_id = " & n2, 0&)
Set PartDesc = OraDynaset.Fields("Attachment_C").Value
chunksize = 32000
'Re adjust the buffer size
buffer = String$(chunksize, 32)
fnum = FreeFile
'Open the file.
Open st1 For Binary As #fnum
'Open "partdesc.dat" For Binary As #fnum
'set the offset and PollingAmount properties for piece wiseWrite operation
PartDesc.Offset = 1
PartDesc.PollingAmount = LOF(fnum)
remainder = LOF(fnum)
If (LOF(fnum) = 0) Then
MsgBox "File size is zero. Make sure that existence of File and its path are correct"
Exit Sub
End If
'Lock the row for write operation
OraDynaset.Edit
Get #fnum, , buffer
'Do first write operation
amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)
While PartDesc.Status = ORALOB_NEED_DATA
remainder = remainder - chunksize
If remainder < chunksize Then
piecetype = ORALOB_LAST_PIECE
chunksize = remainder
Else
piecetype = ORALOB_NEXT_PIECE
End If
Get #fnum, , buffer
amount_written = PartDesc.Write(buffer, chunksize, piecetype)
Wend
Close fnum
'call Update method to commit the transaction
OraDynaset.update
OraDynaset.Close
'Free LOB
Set PartDesc = Nothing
OraDatabase.Close
Set OraSession = Nothing
checkset
MsgBox "File: " & vbCrLf & vbCrLf & st2 & vbCrLf & vbCrLf & " uploaded successfully.", vbInformation, "Upload Complete"
End If
'Screen.MousePointer = 0
End Sub
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
aikimark,
Thanks for your comments. It looks like you may have found solutions for the first two conversions that were causing grief. Any thoughts on the last?
Get #fnum, , buffer
Thanks for your comments. It looks like you may have found solutions for the first two conversions that were causing grief. Any thoughts on the last?
Get #fnum, , buffer
have you looked at the posted URL about reading a binary file?
ASKER
I have tried to implement your suggestions but I cannot seem to get the code to work as it did in VB6.
please repost your code in a snippet.
Also, please let us know what statement(s) are failing and what error messages you are receiving.
Also, please let us know what statement(s) are failing and what error messages you are receiving.
ASKER
Here is my code.
Dim chunksize As Long
Dim AmountRead As Long
Dim buffer As Object
'Create the OraSession Object.
OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
OraDatabase = OraSession.OpenDatabase(oraWSITE, oraWPASS, 0&)
'Create the OraDynaset Object
OraDynaset = OraDatabase.CreateDynaset("select * from po_link where proj_id = " & projid & " and po_id = " & poid, 0&)
PartDesc = OraDynaset.Fields("engineering_notes").Value
chunksize = 32000
'Re adjust the buffer size
'buffer = String$(chunksize, 32)
Buffer = New String(" ", chunksize)
' fnum = FreeFile()
'Open the file.
'Open st2 For Binary As #FNum
Dim input As New FileStream(st2, FileMode.Open)
Dim fnum(CInt(input.Length - 1)) As Byte
'set the offset and PollingAmount properties for piece wise
'Write operation
PartDesc.offset = 1
PartDesc.PollingAmount = LOF(fnum)
remainder = LOF(fnum)
'Lock the row for write operation
OraDynaset.Edit()
'Get #FNum, , buffer
input.Read(fnum, 0, CInt(input.Length))
'Do first write operation
amount_written = PartDesc.Write(Buffer, chunksize, ORALOB_FIRST_PIECE)
While PartDesc.Status = ORALOB_NEED_DATA
remainder = remainder - chunksize
If remainder < chunksize Then
piecetype = ORALOB_LAST_PIECE
chunksize = remainder
Else
piecetype = ORALOB_NEXT_PIECE
End If
'Get #FNum, , buffer
input.Read(fnum, 0, CInt(input.Length))
amount_written = PartDesc.Write(Buffer, chunksize, piecetype)
End While
' FileClose(fnum)
'call Update method to commit the transaction
OraDynaset.Update()
ASKER
One correction
'Get #FNum, , buffer
input.Read(fnum, 0, CInt(input.Length))
should be
'Get #FNum, , buffer
input.Read(fnum, 0, buffer)
(I gave up this yesterday and tried something else that didn't work)
'Get #FNum, , buffer
input.Read(fnum, 0, CInt(input.Length))
should be
'Get #FNum, , buffer
input.Read(fnum, 0, buffer)
(I gave up this yesterday and tried something else that didn't work)
Basically, it looks ok. Here are a few questions:
1. I don't see where you define or assign a value to st2 (file name) variable.
2. What is the nature of this binary file? I would expect the entire contents of the file to be read with the first I/O operation. What is the looping structure for?
3. Does this code compile?
4. What happens at run time? (errors, good results, bad results, unknown results, etc.)
1. I don't see where you define or assign a value to st2 (file name) variable.
2. What is the nature of this binary file? I would expect the entire contents of the file to be read with the first I/O operation. What is the looping structure for?
3. Does this code compile?
4. What happens at run time? (errors, good results, bad results, unknown results, etc.)
ASKER
1. st2 is the filename and filepath I am trying to load into the CLOB field in the table.
2. The procedure is the same procedure used in VB6 to load large files into LOB fields. It is provided in the Oracle 10g documentation as a multiple-piece write operation at about 1/4 down on the web pagee:
http://youngcow.net/doc/oracle10g/win.102/b14309/advfeat.htm#BGBIBGHA
As mentioned earlier, I have used this code in vb6, access97 and access2002 and it was quite fast and effective.
3. The code compiles.
4. I am getting the error Conversion from string " " to type 'Integer' is not valid
2. The procedure is the same procedure used in VB6 to load large files into LOB fields. It is provided in the Oracle 10g documentation as a multiple-piece write operation at about 1/4 down on the web pagee:
http://youngcow.net/doc/oracle10g/win.102/b14309/advfeat.htm#BGBIBGHA
As mentioned earlier, I have used this code in vb6, access97 and access2002 and it was quite fast and effective.
3. The code compiles.
4. I am getting the error Conversion from string " " to type 'Integer' is not valid
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Visual Basic.NET
Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,
96K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Dim chunksize, remainder As Long
After doing some research it looks like the assignment is setting the size of the string variable to a fixed size (chunksize) with null values. It would appear that this variable defines the size of the buffer used for reading individual pieces of the file.