Results 1 to 2 of 2

Thread: Writetext

  1. #1
    Join Date
    Feb 2003

    Unanswered: Writetext

    Hi There,

    Can you help? I am trying to insert into 1 text field by selecting the value from 2nd text field in another table:

    Every example for WRITETEXT always shows the actual text e.g

    WRITETEXT TABLE.FIELD @txtptr 'This is the text'

    How do you use a 2nd text pointer for the text that is to be written?

    Hope you know what I am talking about - just about lost it! Thanking you if you can help!



  2. #2
    Join Date
    Aug 2003
    Andover, MA
    Okay, been a while but...

    I had to read a text file and stuff it into a field. The text files were typically 128k or so.

    I ended up INSERTing a new record to the destination table, putting a zero-length string into the text field. I then created a TEXTPTR to that new blank field. I used the last inserted ID to find it.

    I then set up a loop to read VARCHAR blocks of text from the binary file (in your case the source TEXT field), and write them to the new field using UPDATETEXT. You will need to keep track of your position in the text, and stop from reading beyond it. I used SUBSTRING to pull the blocks from the source TEXT field. Here's a not so clean code chuck I just put together...

    DECLARE @ptrval VARBINARY(16),
    @last_id INT,
    @last_pos INT,
    @max_pos INT,
    @read_len INT,
    @temp_data VARCHAR(8000)

    SELECT @max_pos = DATALENGTH(textfield1) FROM table1 WHERE id1=1
    SET @last_pos = 0

    INSERT INTO table2 (textfield2) VALUES ('')
    SELECT @last_id = IDENT_CURRENT('table2')
    SELECT @ptrval = TEXTPTR(textfield2) FROM table2 WHERE id2=@last_id

    WHILE @last_pos < @max_pos

    SET @read_len = @max_pos-@last_pos
    IF @read_len > 8000
    SET @read_len=8000

    SELECT @temp_data = SUBSTRING(table1.textfield1, @last_pos, @read_len) FROM table1 WHERE id1=@last_id

    UPDATETEXT table2.textfield2 @ptrval2 @last_pos 0 @temp_data

    SET @last_pos = @last_pos + LEN(@temp_data)


    You can probably do a better job monitoring/controlling the field positions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts