Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 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
 
Old May 17th, 2004, 05:34 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Convert from Image to Text

Hi,

I have a column defined as an Image data type. This field is used to store large amounts of text. Now why this field wasnt set as a text type I dont know, the database was designed by someone else.

I basically want this Image field to be converted to text so that I can more easily use it in reports. Any ideas on how to do this? SQL Server doesnt allow this conversion.

Thanks
Niall
 
Old May 17th, 2004, 05:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just an idea here, but how about pulling the image into an ADO object (VB or C or something), then writing it back into the text field? you'd probably have to 'manage' the conversion yourself - pass the output into variant or object variable before trying to write back to the db.
It's a million-to-one shot, but it just might work

Chris


There are two secrets to success in this world:
1. Never tell everything you know
 
Old May 17th, 2004, 08:21 AM
Registered User
 
Join Date: May 2004
Location: Antwerp, , Belgium.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried this (although without data in the fields) and didn't get any complaints:

1. right click on your db and choose export data.

2. export the old tabe to the new one: you'll get to indicate source and destination db, choose accordingly. The you'll get to a list of tables to export: choose your old table. In the same interface you''l be able to indicate the destination table, give a new name there. Also there is a button 'transformations'. There's the trick: click it and change the target fieldtype (source is fixed of course) to text.

Let me know if it works with data inside

I recon that perhaps the data will be mangled because image and text are stored differently, but you'll have to try to know....


cheers

 
Old May 17th, 2004, 09:32 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Thanks, that was a good try but it didnt work. It just left the new text field as NULL, it didnt transfer anything from the image field. It didnt even throw an errors which maybe you'd expect. I was expecting an error as this conversion is not allowed by sql.

Thanks anyways
Niall
 
Old May 18th, 2004, 08:10 AM
Registered User
 
Join Date: May 2004
Location: Antwerp, , Belgium.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Look in your msdn help for

TEXTPTR

you can declare a textptr to your image field, READTEXT it's context and WRITETEXT it back to a text field.

Since the data has been/is being put in an image field anyhow, it should work that way. I guess that SQL doesn't give errormessages in what you've tried or in my earlier suggestion because the actual data are one pointerhop further away and that the used methods ignore that hop.


 
Old May 18th, 2004, 08:59 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply.

I had initially tried this also with the following sql..

DECLARE @val varbinary(16)
SELECT @val = TEXTPTR(problemcomment)
FROM c_ticketactivity_ext
WHERE ticketactivityid = 'Q6UJ9A09W01D'
READTEXT c_ticketactivity_ext.problemcomment @val 0 0

writetext c_ticketactivity_ext.problemcomment2 @val

The problem is what to add to the end of the writetext statement? It needs to be the readtext value from the image field but the ReadText only returns a pointer, not the actually text the user typed into field. How can you capture this and pass it to the writetext command??

Thanks
Niall
 
Old May 18th, 2004, 10:51 AM
Authorized User
 
Join Date: Jun 2003
Location: dublin, , Ireland.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I almost have it.....


DECLARE @val varbinary(16),@val2 varbinary(16)
SELECT @val = TEXTPTR(problemcomment)
FROM c_ticketactivity_ext
WHERE ticketactivityid = 'Q6UJ9A02C6RC'
READTEXT c_ticketactivity_ext.problemcomment @val 0 0

SELECT @val2 = TEXTPTR(problemcomment2)
FROM c_ticketactivity_ext
WHERE ticketactivityid = 'Q6UJ9A02C6RC'
READTEXT c_ticketactivity_ext.problemcomment2 @val2 0 0

writetext c_ticketactivity_ext.problemcomment2 @val2 @val

the problem now is that what is written to the text field (problemcomment2) is just a meaningless string...e.g. þÿ¯¬

any ideas?
 
Old October 22nd, 2004, 12:48 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In case you haven't figured it out yet....

  -- insert as space as a placeholder
  -- into the new text col to get valid texptrs later
  update the_table
    set new_text_col = ' '

  -- get text ptrs for the row you'd like to update
  select
    @old_img_ptr = TEXTPTR(old_img_col),
    @new_text_ptr = TEXTPTR(new_text_col)
  from the_table
  where the_identity_col= @somevalue

  -- if we have a good textptr from the old img col
  -- update the new text col with the data,
  -- starting at offset 0, deleting our placeholder char
  if ( TEXTVALID('the_table.old_img_col', @old_img_ptr) = 1 )
    updatetext the_table.new_text_col @new_text_ptr 0 1 the_table.old_img_col @old_img_ptr
  -- if there was no data in the source text col, null out the img col
  else
    writetext the_table.new_text_col @new_text_ptr NULL

  -- show the resulting data lengths (for debug purposes only)
  select
    datalength(old_img_col) old_img_len,
    datalength(new_text_col) new_text_len
  from the_table
  where the_identity_col= @somevalue

You can put this in a while loop to process all rows in the table.
 
Old February 24th, 2007, 01:31 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I still get symbols when runing this code.

update paymentdetails2 set image_as_text = ' '
declare @old_img_ptr varbinary(16)
declare @new_text_ptr varbinary(16)
  -- get text ptrs for the row you'd like to update
  select
    @old_img_ptr = TEXTPTR(pay_slip_Image),
    @new_text_ptr = TEXTPTR(image_as_text)
  from paymentdetails2
  where id= '61757685-0A5B-4388-A415-0291DDDE039A'

  -- if we have a good textptr from the old img col
  -- update the new text col with the data,
  -- starting at offset 0, deleting our placeholder char
  if ( TEXTVALID('paymentdetails2.pay_slip_Image', @old_img_ptr) = 1 )
    updatetext paymentdetails2.image_as_text @new_text_ptr 0 1 paymentdetails2.pay_slip_Image @old_img_ptr
  -- if there was no data in the source text col, null out the img col
  else
    writetext paymentdetails2.image_as_text @new_text_ptr NULL

although the size of both match after this query
  select
    datalength(pay_slip_Image) old_img_len,
    datalength(image_as_text) new_text_len
  from paymentdetails2
  where id= '61757685-0A5B-4388-A415-0291DDDE039A'

The content of the text column is this = ÿØÿà

Any idea?

Thank you

 
Old February 24th, 2007, 02:17 PM
Registered User
 
Join Date: Feb 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

And sorry BTW;

That code has the same results that this code

update paymentdetails2
set image_as_text = convert(varchar(max), convert(varbinary(16), pay_slip_image))
where id= '61757685-0A5B-4388-A415-0291DDDE039A'

Any idea?





Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Text(Sql Server Text Field) to Image(JPG) srinivas72 ADO.NET 2 February 13th, 2009 06:31 PM
Convert datatype Text to image roopaksr ADO.NET 2 September 6th, 2008 03:02 PM
help me convert sql.image into string Maxxim ASP.NET 2.0 Professional 0 October 6th, 2007 03:58 AM
How to convert image to binary form deardp Python 0 September 26th, 2007 11:12 PM
Convert RTF Text to ASCII Text LouSchweichler Access 2 December 3rd, 2003 03:50 PM





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