use bash commandline variables in mysql query

Question

Hi I have the following script:

#!/bin/sh
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A hg19 -D hg19 -e "select distinct c.name,c.transcript from  (select distinct kgID,genesymbol,refseq from kgXref where genesymbol = '"${1}"') a inner join (select * from knownGene) b on a.kgID = b.name inner join (SELECT distinct name, transcript,chromStart,chromEnd, substr(peptides,1,1) as ref_pep,substr(peptides,3,1) as mut_pep FROM snp141CodingDbSnp) c on a.refseq = c.transcript" > ${1}id.txt

where I use it like:

./snp_id_list.sh <myGene>

However I get:

./snp_id_list.sh: 3: Syntax error: Unterminated quoted string

to which I don't think I am escaping the double quotes correctly around the first instance of $1 (the one time I call it within the mysql syntax)

You may use simply '$1'.

Aleksei Golubev
– Aleksei Golubev

2015-10-06 15:24:38 +00:00
Commented Oct 6, 2015 at 15:24 — Aleksei Golubev
– Aleksei Golubev, Commented Oct 6, 2015 at 15:24

Fabian Schmengler · Accepted Answer · 2015-10-06 15:16:49Z

1

The double quotes are not needed at all. The following should work:

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A hg19 -D hg19 -e \
    "select distinct c.name,c.transcript from \
    (select distinct kgID,genesymbol,refseq from kgXref where genesymbol = '${1}') a \
    inner join (select * from knownGene) b on a.kgID = b.name \
    inner join (SELECT distinct name, transcript,chromStart,chromEnd, substr(peptides,1,1) as ref_pep,substr(peptides,3,1) as mut_pep \
    FROM snp141CodingDbSnp) c on a.refseq = c.transcript" > ${1}id.txt

(I made it a multi line statement for sake of readability)

answered Oct 6, 2015 at 15:16

Fabian Schmengler

24.6k9 gold badges82 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

use bash commandline variables in mysql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related