Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: Redirect mysql query result into a file

    I am writing a shell script in which I want to redirect the output of a mysql query result into a file. I have experimented with a sample help I got from the web but did not help :

    #!/bin/sh
    mysql --user=root --password=ibj -h localhost <<!!
    USE mydb;


    SELECT * FROM shino WHERE dbflag=0;
    quit
    !!


    Actually , I have never used the syntax "<< ......... quit !! " before and I do not have documentation to read about it.

    So when I execute the script as it is , it dispalys the result of the query on the screen. Now when I changed the select statement to

    "SELECT * FROM shino WHERE dbflag =0;">>/tmp/$$.tempfile
    it gave me a syntax error ,likewise with
    mysql -e "SELECT * FROM shino" >>/tmp/$$.tempfile

    Can someone give me the correct syntax to be able to put the result of a mysql query into a file.

    Thanks ,
    Ibro
    Last edited by ibrotj; 05-31-04 at 07:30. Reason: mistake in the sample script-sorry for that

  2. #2
    Join Date
    May 2004
    Posts
    4
    Hello All,

    I have been able to solve my problem. In case it will be useful for somebody,let me explain.

    Use the following script :

    #!/bin/sh

    mysql -u [username ] -p[password] [db_name] -e " SELECT * FROM [tbl_name] ">/path_to_file/file_name

    That is all and the output of the file_name will contain the query result of SELECT command.

    if you do not specify username and password and even the database name ,you will not be able to this will not work. Also I later realise that the option -e to the mysql command means execute in mysql> command prompt. so you can execute anything using that format as if you are in mysql> comand prompt.

    Again, it is required that /path_to_file directory be world writeable(drwxr-xrwx) eg by using chmod 757 command. /tmp is ideal for this as it has this property already.
    These are the mistakes I was making for the past 3 days until I was able to solve the problem.

    I hope this is useful for somebody.

    -Ibro
    i

  3. #3
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28
    I have also used the spool command with the following code. This works quite well too.

    #-----------------------------------
    FILE=/home/mydir/my_file

    sqlplus -s /<<!
    set pages 9999
    spool ${FILE}
    select FIELD1, FIELD2
    from MY_TABLE
    /
    exit
    /
    !

    #-----------------------------------

    Basically it inputs anything between the “!” exclamation points directly into sqlplus. The forward slash “/ “creates a CR.

Posting Permissions

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