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

why are you open rs twice to get two columns? can't you just "select problemcomment, problemcomment2 ..." in the same loop? Just an idea. I agree with you that the time lag here is in writing and reading the file.
Did you try to simply read problemcomment into a string and write it to problemcomment2?
VB knows how to convert a byte array into a String.
Marco

I will try that, thanks....

Before this code:

RSMaj.Open sql, hDB, adOpenStatic, adLockReadOnly, adCmdText

add

RSMaj.StayInSync = False

How badly do you need this to run faster?
If you must code in VB and don't mind using API, try the a memory copy. A graphic data is just an array of bytes so you can copy it to your string. Remember vb string is in unicode so you must pad your data you must of course also preallocate space for your string.

Have you checked out READTEXT in TSQL?

SQL Server does not support conversion of image to text data via TSQL (or any other means that I know of). ReadText is fine, I can access the data but it cannot be written to the text data field via UpdateText.

Also you say.."If you must code in VB and don't mind using API, try the a memory copy. A graphic data is just an array of bytes so you can copy it to your string. Remember vb string is in unicode so you must pad your data you must of course also preallocate space for your string".

I am not a pro VB programmer so I dont know what a memory copy is?
It sounds easier to just use the text file to copy out the text rather than "pad your data" and "preallocate space"?

MemCpy is a Windows API.

Try this on the pubs db on sql server

create table #tmp (a text)
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(logo)

FROM pub_info
WHERE pub_id = '0736'

insert into #tmp
select cast(cast(cast(substring(logo, 1, 1) as binary(1)) as integer) as varchar(2))
    + cast(cast(cast(substring(logo, 2, 1) as binary(1)) as
integer) as varchar(2))
 FROM pub_info WHERE pub_id = '0736'
select * from #tmp

drop table #tmp

what you get is 71 73 in decimal, convert this to hex and you get 47 49 which is the first 2 byte of logo 0x474946....... in char array it is GIF89a............

So,

Is there a way to do it via t-sql or not?

and with sql 2005?

any idea?

Thank you.