dynamic query question

[leileili]

Hello,
Hello,

I'm new in dynamic SQL query and I have a problem need help.

For example: I have a SQL query like this:

select hw.family,hw.id where hw.id="1234567";

and I have a txt file holding more than ten thousands id.
I need feed the ids from the file to the SQL query, inside
the "???", please let me know how can I do this?

then I put the SQL query in a file and run it from another .csh
file. Inside the .csh file, I have something like this:

mysql -u usrname dataname< /directory/sqlfile

/directory/outputfile/id_???

how can I have the output files created with different names according
with the id inputs in the run time?

Thanks a lot.

[Bill Karwin]

le******@hotmail.com wrote:

For example: I have a SQL query like this:

select hw.family,hw.id where hw.id="1234567";

and I have a txt file holding more than ten thousands id.
I need feed the ids from the file to the SQL query, inside
the "???", please let me know how can I do this?
Probably the most efficient way to do this is to load your txt file into
a temporary table, and then perform a join between that table and your
hw table.

CREATE TEMPORARY TABLE tmp (id CHAR(20));
LOAD DATA INFILE 'idlist.txt' INTO TABLE tmp;

SELECT hw.family,hw.id
FROM hw JOIN tmp ON hw.id = tmp.id;

That's just an example. I don't know if it conforms to your text file
format, or if the CHAR is long enough, etc. Adjust to suit your needs,
and read the docs on LOAD DATA:
http://dev.mysql.com/doc/mysql/en/load-data.html

Another alternative is to loop through your txt file in your shell
script and execute SQL queries one by one, capturing the output of each
one to a separate output file. This executes the queries less
efficiently than the method above, but it's easier to separate the
output. See lower in this post for an example.

BTW, one should use sh instead of csh for shell programming. See
http://www.faqs.org/faqs/unix-faq/shell/csh-whynot/ for the classic
explanation.
then I put the SQL query in a file and run it from another .csh
file. Inside the .csh file, I have something like this:

mysql -u usrname dataname< /directory/sqlfile

/directory/outputfile/id_???

how can I have the output files created with different names according
with the id inputs in the run time?

Here's an example in sh (I think it's been over 15 years since I wrote
anything in csh -- gulp!):

cat idlist.txt | while read id
do
mysql -u username dataname -e "select hw.family, hw.id from hw
where hw.id = '$id'" > /directory/outputfile/id_$id
done

Regards,
Bill K.

[Felix Geerinckx]

On 19/05/2005, le******@hotmail.com wrote:

Inside the .csh file, I have something like this:

mysql -u usrname dataname< /directory/sqlfile

>/directory/outputfile/id_???

how can I have the output files created with different names according
with the id inputs in the run time?

The small Perl program below will do what you want.
Run it as: foo.pl file_with_ids.txt

#! /usr/bin/perl
# foo.pl - save query results to files based on id
use strict;
use warnings;
my $dp = "/path/to/destination/directory";
my $cmd = "/path/to/mysql/mysql -uuser -ppass db >$dp/id_%s";
my $qry = "select * from foo where id='%s'";

while (<>) {
chomp;
open CMD, sprintf("| $cmd", $_) or die "Cannot create pipe: $!\n";
print CMD sprintf($qry, $_);
close CMD;
}

--
felix

[leileili]

Cool, this works just great. Save me lots of time.
Thank you very much.

lei