Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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....
Reply With Quote
  #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

Reply With Quote
  #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?

Reply With Quote
  #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"?
Reply With Quote
  #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............

Reply With Quote
  #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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:05 AM.


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