Re: [sqlite] Parameterized SQL

[Don V Nielsen]

I use Ruby.  In the following example I use a replacement tag "#{lvl}" in
the scripts.  For each of the levels (indv,hhld,resi), I do a substitution
against the script and pass the script to sqlite.

dbname =
File.join('d:','cloveretl','projects','test_ii','data-out-97','test.db')
db = SQLite3::Database.new(dbname)
script_folder = File.join(prj.ps_project_folder,'scripts')

scripts_to_run = [
  '02_build_relationships.sql',
  '05_cdi_perspective.sql',
  '05_itrack_perspective.sql',
  '10_cdi_select',
  '10_itrack_select'
]

['indv','hhld','resi'].each {|lvl|
  puts "Executing level: #{lvl}"
  scripts_to_run.each {|sql|
puts "  script: #{sql}"
script = File.open(File.join(script_folder,sql)).readlines(sep=nil)[0]
db.execute_batch(script.gsub('#{lvl}',lvl))
  }
}


On Fri, Jun 15, 2012 at 5:01 AM, Niall O'Reilly wrote:

>
> On 15 Jun 2012, at 10:45, Udi Karni wrote:
>
> > Niall - thanks. If I understand correctly - you use bash to do the
> > preprocessing of the substitutions and submit the prepared statements to
> > Sqlite.
>
> Well, 'prepared' is not the term I would use, as it has a specific
>meaning in the context of SQLite (or other SQL implementations).
>
>Bash does make substitutions in the 'pre-scripted' (for want of a
>better term) block delimited by '<the modified text to sqlite3 as input.  I understand that other
>shells can do likewise, but bash is the one I'm familiar with.
>
>So, yes and no ... 8-)
>
>Good luck!
> Niall O'Reilly
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameterized SQL

[Niall O'Reilly]

On 15 Jun 2012, at 10:45, Udi Karni wrote:

> Niall - thanks. If I understand correctly - you use bash to do the
> preprocessing of the substitutions and submit the prepared statements to
> Sqlite.

Well, 'prepared' is not the term I would use, as it has a specific
meaning in the context of SQLite (or other SQL implementations).

Bash does make substitutions in the 'pre-scripted' (for want of a
better term) block delimited by '<

Re: [sqlite] Parameterized SQL

[Udi Karni]

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 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  = 2010
> >
> > SELECT COUNT (*) FROM TABLE WHERE 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" < -- 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameterized SQL

[Niall O'Reilly]

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  = 2010
> 
> SELECT COUNT (*) FROM TABLE WHERE 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" 

Re: [sqlite] Parameterized SQL

[Igor Tandetnik]

On 6/14/2012 5:16 PM, Udi Karni wrote:

Is there a way to run SQL scripts in the Shell with parameters?

Something like this?

set  = 2010

SELECT COUNT (*) FROM TABLE WHERE YEAR =  ;


Not to my knowledge.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Parameterized SQL

[Udi Karni]

Is there a way to run SQL scripts in the Shell with parameters?

Something like this?

set  = 2010

SELECT COUNT (*) FROM TABLE WHERE YEAR =  ;

???
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users