Results 1 to 2 of 2

Thread: Writetext

  1. #1
    Join Date
    Feb 2003
    Location
    Glasgow
    Posts
    4

    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!

    Regards

    Anna

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    BEGIN

    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)

    END

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

Posting Permissions

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