Professional Web Applications Themes

To generate the file on the client host - MySQL

Hi, If I have DB on my own local machine, I'm able to run this command, which modify outfile to csv-format: INTO OUTFILE '/Temp/statistic/payment_per_week.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' but If I'm running same command from client machine and my DB server is other machine I have to use this format to get that outfile to my machine: C:\>mysql --host="211.37.6.12" --port="3306" --database="testing" --local -u"myown" -p -e"source c:\temp\statistic\queries.sql"  Problem is: HowTo modify the last commands outfile to csv-format? BR John...

  1. #1

    Default To generate the file on the client host

    Hi,

    If I have DB on my own local machine, I'm able to run this command,
    which modify outfile to csv-format:

    INTO OUTFILE '/Temp/statistic/payment_per_week.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

    but

    If I'm running same command from client machine and my DB server
    is other machine I have to use this format to get that outfile to my
    machine:

    C:\>mysql --host="211.37.6.12" --port="3306" --database="testing"
    --local -u"myown" -p -e"source c:\temp\statistic\queries.sql" 

    Problem is: HowTo modify the last commands outfile to csv-format?

    BR
    John


    John Guest

  2. #2

    Default Re: To generate the file on the client host

    John Smith wrote: 
    >
    > Problem is: HowTo modify the last commands outfile to csv-format?
    >
    > BR
    > John
    >
    >[/ref]

    outfile and into outfile are by default commands that create files on
    the database server. you will not be able to send this cmd over the
    network and create a file locally. to overcome this you have 2 options:
    1-use the file that is created by outfile remotely through either ssh or
    psftp or similar tools
    2-use an output operation to dump the results of your query into a local
    file (this may be better fro your purposes). you can run something like
    this:

    mysql -h 10.10.10.10 -u john -p --database=test -e "select * from
    people;" > outputfile.csv

    the problem with this one is that you need to modify your query to
    actually create comma delimited values (not hard).

    hope this helps.
    lark Guest

  3. #3

    Default Re: To generate the file on the client host

    Lark wrote:

    "lark" <net> wrote in message
    news:Xgcii.16633$news.prodigy.net... 
    >>
    >> Problem is: HowTo modify the last commands outfile to csv-format?
    >>
    >> BR
    >> John[/ref]
    >
    > outfile and into outfile are by default commands that create files on the
    > database server. you will not be able to send this cmd over the network
    > and create a file locally. to overcome this you have 2 options:
    > 1-use the file that is created by outfile remotely through either ssh or
    > psftp or similar tools
    > 2-use an output operation to dump the results of your query into a local
    > file (this may be better fro your purposes). you can run something like
    > this:
    >
    > mysql -h 10.10.10.10 -u john -p --database=test -e "select * from people;" 
    >
    > the problem with this one is that you need to modify your query to
    > actually create comma delimited values (not hard).
    >
    > hope this helps.[/ref]

    This is hard:

    C:\>mysql --host="211.37.6.12" --port="3306" --database="testing"
    --local -u"myown" -p -e"source c:\temp\statistic\queries.sql"
    --disable-column-names >/temp/statistic/payment_per_week.csv

    And I should use these definitions which should convert client side outfile
    like
    payment_per_week.csv file which should ne compatible with Excel:

    --fields-terminated-by=',' --fields-enclosed-by='"' --fields-optionally-enclosed-by='"'
    --lines-terminated-by='\n'

    My queries.sql is:
    SELECT DISTINCT
    wk,
    ',',
    COUNT(DISTINCT bug) AS c
    FROM (
    SELECT
    CONCAT('wk', WEEK(day, 3), '/', YEAR(day)) AS wk,
    bug,
    day
    FROM software
    ) AS t
    GROUP BY t.wk
    ORDER BY day ASC;

    I need that outfile directly to client machine and I'm using Windows XP. Is
    it so that in MySql isn't any routines to this action?

    BR
    John


    John Guest

  4. #4

    Default Re: To generate the file on the client host

    John Smith wrote: 
    >> outfile and into outfile are by default commands that create files on the
    >> database server. you will not be able to send this cmd over the network
    >> and create a file locally. to overcome this you have 2 options:
    >> 1-use the file that is created by outfile remotely through either ssh or
    >> psftp or similar tools
    >> 2-use an output operation to dump the results of your query into a local
    >> file (this may be better fro your purposes). you can run something like
    >> this:
    >>
    >> mysql -h 10.10.10.10 -u john -p --database=test -e "select * from people;" 
    >>
    >> the problem with this one is that you need to modify your query to
    >> actually create comma delimited values (not hard).
    >>
    >> hope this helps.[/ref]
    >
    > This is hard:
    >
    > C:\>mysql --host="211.37.6.12" --port="3306" --database="testing"
    > --local -u"myown" -p -e"source c:\temp\statistic\queries.sql"
    > --disable-column-names >/temp/statistic/payment_per_week.csv
    >
    > And I should use these definitions which should convert client side outfile
    > like
    > payment_per_week.csv file which should ne compatible with Excel:
    >
    > --fields-terminated-by=',' --fields-enclosed-by='"' --fields-optionally-enclosed-by='"'
    > --lines-terminated-by='\n'
    >
    > My queries.sql is:
    > SELECT DISTINCT
    > wk,
    > ',',
    > COUNT(DISTINCT bug) AS c
    > FROM (
    > SELECT
    > CONCAT('wk', WEEK(day, 3), '/', YEAR(day)) AS wk,
    > bug,
    > day
    > FROM software
    > ) AS t
    > GROUP BY t.wk
    > ORDER BY day ASC;
    >
    > I need that outfile directly to client machine and I'm using Windows XP. Is
    > it so that in MySql isn't any routines to this action?
    >
    > BR
    > John
    >
    >[/ref]
    if you want the data to go to excel why don't you use the excels built
    in data import. create a dsn to the db server, use the dsn in excle data
    import to connect to the server and run this sql or any other sql to
    extract the data. this can be updated whenever the user opens up the
    excel file.

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  5. #5

    Default Re: To generate the file on the client host

    Hi,

    it seems that it's only possible way, I was just curious because in Sybase's
    client it's possible way.

    BR
    John

    "lark" <net> wrote in message
    news:4vQii.8097$news.prodigy.net... 
    >>
    >> This is hard:
    >>
    >> C:\>mysql --host="211.37.6.12" --port="3306" --database="testing"
    >> --local -u"myown" -p -e"source c:\temp\statistic\queries.sql"
    >> --disable-column-names >/temp/statistic/payment_per_week.csv
    >>
    >> And I should use these definitions which should convert client side
    >> outfile like
    >> payment_per_week.csv file which should ne compatible with Excel:
    >>
    >> --fields-terminated-by=',' --fields-enclosed-by='"' --fields-optionally-enclosed-by='"'
    >> --lines-terminated-by='\n'
    >>
    >> My queries.sql is:
    >> SELECT DISTINCT
    >> wk,
    >> ',',
    >> COUNT(DISTINCT bug) AS c
    >> FROM (
    >> SELECT
    >> CONCAT('wk', WEEK(day, 3), '/', YEAR(day)) AS wk,
    >> bug,
    >> day
    >> FROM software
    >> ) AS t
    >> GROUP BY t.wk
    >> ORDER BY day ASC;
    >>
    >> I need that outfile directly to client machine and I'm using Windows XP.
    >> Is it so that in MySql isn't any routines to this action?
    >>
    >> BR
    >> John[/ref]
    > if you want the data to go to excel why don't you use the excels built in
    > data import. create a dsn to the db server, use the dsn in excle data
    > import to connect to the server and run this sql or any other sql to
    > extract the data. this can be updated whenever the user opens up the excel
    > file.
    >
    > --
    > lark -- net
    > To reply to me directly, delete "despam".[/ref]


    John Guest

Similar Threads

  1. #39455 [NEW]: Can't generate different session id on same client
    By lizy at nlc dot gov dot cn in forum PHP Bugs
    Replies: 1
    Last Post: November 10th, 09:47 AM
  2. Capturing the CLIENT host name
    By Aegis Kleais in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 25th, 08:03 PM
  3. Replies: 0
    Last Post: August 1st, 02:30 PM
  4. Host ME, Client XP Home
    By Pat in forum Windows Networking
    Replies: 1
    Last Post: July 22nd, 06:49 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139