Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 20th, 2004, 09:22 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Improve on this code...?

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
  #2 (permalink)  
Old May 20th, 2004, 12:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Alameda, ca, USA.
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old May 21st, 2004, 03:59 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I will try that, thanks....
  #4 (permalink)  
Old May 21st, 2004, 03:53 PM
Authorized User
 
Join Date: May 2004
Location: Irving, TX, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Before this code:

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

add

RSMaj.StayInSync = False

  #5 (permalink)  
Old June 16th, 2004, 05:25 PM
tnd tnd is offline
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

  #6 (permalink)  
Old June 17th, 2004, 10:49 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"?
  #7 (permalink)  
Old June 17th, 2004, 12:53 PM
tnd tnd is offline
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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............

  #8 (permalink)  
Old February 24th, 2007, 01:47 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So,

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

and with sql 2005?

any idea?

Thank you.



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to improve this C++ code to accept spaces in i code_lover C++ Programming 1 July 23rd, 2008 01:42 AM
Please help me to improve this ADO.NET code. edurazee ADO.NET 6 July 22nd, 2008 09:55 PM
how to improve this code? dhaval229 SQL Language 0 February 20th, 2006 04:38 AM
how to improve this code? dhaval229 SQL Language 0 February 20th, 2006 04:36 AM
How to Improve Myself in ASP ranuji BOOK: Access 2003 VBA Programmer's Reference 4 April 22nd, 2005 08:53 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.