SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problem with READ INTO OUTFILE and NULL values

    Hi guys,

    I'm producing a file every couple of minutes that needs to be processed by a very old (1984) SunOS machine. The file has to be presented in a flat format where individual fields need to be padded out to a particular length. I've had a couple of problems with this which I've solved, but the big one at the moment is values that are NULL, I'm getting the whole field reduced to \N instead of being padded out.

    Another problem is that when I force in a NULL value I'm getting the result padded out further than I should:
    Code MySQL:
    SET @sql_text = 
       CONCAT (
            "SELECT       
                CAST(LPAD(4, 2, '0') AS UNSIGNED)
            ,    CAST(LPAD(12345, 6, '0') AS UNSIGNED)
            ,    RPAD('W1',3,' ')
            ,    RPAD(NULL, 3, ' ')
            ,    CAST(RPAD(1, 1, '1') AS UNSIGNED)
            into outfile 'TEST-"
            ,    DATE_FORMAT( NOW(), '%Y%m%d%H%i%s')
            ,    ".txt' FIELDS TERMINATED BY '' LINES TERMINATED BY '\n';"
        );        
     
    PREPARE s1 FROM @sql_text;
    EXECUTE s1;
    DROP PREPARE s1;
    Results in:

    Code:
    4 12345 W1                1
    121234561231231
    (The bottom line is to demonstrate where I would expect values to be)

    Can anyone help? Do I need to put an IF/ELSE statement on every line that could be a NULL value in the SELECT part of the query?

    By the way, the reason that a lot of those values are CAST is because I kept getting hex values returned that I thought were causing other problems so I was just being thorough.

    MySQL v5.1.30 by the way

    Cheers

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    For now I've basically added this to any lines that could potentially be null:

    Code MySQL:
    CASE WHEN val IS NULL
                    THEN  SPACE(50)
                    ELSE RPAD(val, 50, ' ')  END
    Works, but not the most elegant solution I'm sure you'll agree


Bookmarks

Posting Permissions

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