Niall - thanks. If I understand correctly - you use bash to do the preprocessing of the substitutions and submit the prepared statements to Sqlite.
On Fri, Jun 15, 2012 at 1:48 AM, Niall O'Reilly <[email protected]>wrote: > > On 14 Jun 2012, at 22:16, Udi Karni wrote: > > > Is there a way to run SQL scripts in the Shell with parameters? > > > > Something like this? > > > > set &YEAR = 2010 > > > > SELECT COUNT (*) FROM TABLE WHERE YEAR = &YEAR ; > > > > ??? > > I use bash and sqlite3, as in the fragment below. > > #!/bin/bash > > # query-script for netdb/SQL > > qtype=${2-node_by_name_or_alias} > dbfile=${3-default-network.db} > > case $qtype in > object_by_property) > qkey=${1-code=EE} > echo " Performing query '$qtype' for search argument '$qkey' in > database '$dbfile'" > echo > tag=`echo $qkey | sed -e 's/=.*//'` > val=`echo $qkey | sed -e 's/.*=//'` > /usr/bin/time /usr/local/bin/sqlite3 "$dbfile" <<EOF > -- tailor display > -- .mode tabs > .separator ' ' > > -- select memory for temporary storage > pragma temp_store = memory; > > create temporary table tmp_objects (object_ref integer); > > -- collect objects whose name or alias exactly matches the search key > insert into tmp_objects > select distinct object_ref from property where tag = '$tag' and > value = '$val'; > > -- show count > select count(), 'object(s) found' from (select distinct object_ref from > tmp_objects); > > -- collect linked objects (ranges, interfaces ...) > insert into tmp_objects > select origin_ref from tie where target_ref in (select distinct > object_ref from tmp_objects); > select id, '', class, '' from object where id in (select distinct > object_ref from tmp_objects) > union all > select origin_ref, ' ', class, target_ref from tie where > target_ref in (select object_ref from tmp_objects) > union all > select object_ref, ' ', tag, value from property where object_ref > in (select object_ref from tmp_objects) > order by object_ref asc; > > EOF > ;; > > # Other cases omitted ... > > *) > echo " Unknown query: '$qtype'" > ;; > esac > > > I hope this helps. > > > Best regards, > > Niall O'Reilly > University College Dublin IT Services > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
