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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
