www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] Shell Script, SQL output, format help

  1. #1
    Join Date
    Jul 2007
    Posts
    10

    resolved [RESOLVED] Shell Script, SQL output, format help

    I'm using MySQL 5.0.27 on redhat-linux, Fedora Core 4.

    I am attempting to write a Bash Shell script that extracts data from a MySQL database and puts it in a text file (that I can use for other scripts).

    I am able to get the data output, but I don't know how to correctly format it. I need the data in rows (one item per row) and without column headers or the dashes and lines . Code below:

    #!/bin/bash
    ### MySQL Setup ###
    MUSER="user1"
    MPASS="mypassword"
    MHOST="localhost"
    MYSQL="$(which mysql)"

    #Read the IP addresses from the MySQL database
    DATA="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -t networkscan -e 'select ipaddress from data WHERE datetime<NOW();')"

    echo $DATA > File

    *****

    The resulting file looks like this:

    +--------------+ | ipaddress | +--------------+ | 192.168.1.56 | | 192.168.1.7 | | 192.168.1.3 | | 192.168.1.74 | | 192.168.1.12 | | 192.168.1.56 | +--------------+

    I need it to look like this:

    192.168.1.56
    192.168.1.7
    192.168.1.3
    192.168.1.74
    192.168.1.12


    I've read that there are various parameters for formatting, such as -E that creates a separate line for each entry, but I must not be adding that in the correct place, because I can't get it to work. Any help is appreciated.

  2. #2
    Join Date
    Jul 2004
    Posts
    300
    maybe something like:

    select char(10) + char(13) + ipaddress as ipaddr

    would work? i dunno. give it a try and let me know.

  3. #3
    Join Date
    Jul 2007
    Posts
    10

    resolved

    I found out that using the -sN parameter suppresses the column headings.

    In addition, to get rid of the funny dashes and lines, place the SQL query in a batch file that gets called from the Shell script. (It sounds complicated but it's not.) So my script now reads:

    #!/bin/bash

    #Read the IP addresses from the MySQL database

    mysql -u user1 -h localhost -pmypassword -sN < batchfile > target.out


    The batchfile is:

    use networkscan;
    select ipaddress from data;


    The result in target.out looks like this:

    192.168.1.12
    192.168.1.56
    192.168.1.7
    192.168.1.3
    192.168.1.74
    192.168.1.12

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles