Hi,
I have developed a small piece of code to transfer data from an image column to a text column in the same table. It works fine.The database is SQL Server.
I was wondering if anyone can suggest a "better" way to do this, what improvements can be made to the code below. In "better" I mean faster (assuming it works!)
I am not an expert
VB programmer but know enough to get things to work although they may not be the generally accepted way of doing it. The code below reads the image data from the ProblemComment field and stores it out into a text file which is then read back into another adodb.stream object and is written back to the new text field ProblemComment2. Hopefully you can read the code and it will make some sense.
I have about 400K records to update this way so it takes about 60mins with this code. Its VB6 too.
There is no SQL Server TSQL way of doing this, I have tried. You cant change an image field to a text field and you cannot convert image to text via TSQL. You must use something else like ADODB.
I would like to remove the need to store each value out into the text file and then read it back in but I couldn't get it to work anyway.
Any thoughts on what to change below to speed this up? Remember, all I want to do is transfer the image data into a new text column.
Thanks
Niall
'************************
Dim rs As ADODB.Recordset
Dim s As ADODB.Stream
Dim RSMaj As ADODB.Recordset
Dim sql As String
Set s = New ADODB.Stream
Set RSMaj = New ADODB.Recordset
sql = "select ticketid from c_ticket_ext where problemcomment is not null"
RSMaj.Open sql, hDB, adOpenStatic, adLockReadOnly, adCmdText
Set rs = New ADODB.Recordset
Do While RSMaj.EOF = False
sql = "select problemcomment from c_ticket_ext where ticketid = '" & RSMaj.Fields("ticketid").Value & "'"
rs.Open sql, hDB, adOpenKeyset, adLockReadOnly, adCmdText
With s
.Open
.Type = adTypeBinary
.Write rs.Fields("problemcomment").Value
.SaveToFile "c:\nialltext.txt", adSaveCreateOverWrite
End With
rs.Close
s.Close
Set s = New ADODB.Stream
With s
.Charset = "ascii"
.Open
.LoadFromFile "c:\nialltext.txt"
.Position = 0
End With
sql = "select problemcomment2 from c_ticket_ext where ticketid = '" & RSMaj.Fields("ticketid").Value & "'"
rs.Open sql, hDB, adOpenDynamic, adLockOptimistic, adCmdText
rs.Fields("problemcomment2").Value = s.ReadText
rs.Update
rs.Close
s.Close
RSMaj.MoveNext
Loop
Set rs = Nothing
Set s = Nothing
RSMaj.Close
Set RSMaj = Nothing