Forum: Ruby Problem with SQLite and Ruby

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
6d9bf78ca49a017e9e3e6b0357b6c59e?d=identicon&s=25 Peter Hickman (Guest)
on 2006-05-03 19:06
(Received via mailing list)
I'm having a little problem here. I have a version 2 SQLite database and
the following code:

#!/usr/bin/ruby

require 'sqlite'

db = SQLite::Database.new( "db/ars.db", 0644 )
db.results_as_hash = true

db.execute( "SELECT * FROM sources;" ) do |source|
        source.each do |k,v|
                puts "#{k} => #{v}"
        end
        puts
end

And I get:

0 => sshd
1 => SSH Daemon
id => sshd
description => SSH Daemon

0 => ipfw
1 => Firewall
id => ipfw
description => Firewall

0 => snort
1 => SNORT IDS
id => snort
description => SNORT IDS

0 => apache_error
1 => Apache Error Log
id => apache_error
description => Apache Error Log

But what I was expecting / wanted is:

id => sshd
description => SSH Daemon

id => ipfw
description => Firewall

id => snort
description => SNORT IDS

id => apache_error
description => Apache Error Log

What am I doing wrong?
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 unknown (Guest)
on 2006-05-03 19:07
(Received via mailing list)
On Wed, 3 May 2006, Peter Hickman wrote:

> db.execute( "SELECT * FROM sources;" ) do |source|
> id => sshd
> description => SSH Daemon

sqlite, by default, gives each tuple as a hash containing both the field
names
and field id as keys for each value.  a more convenient, imho, way to
use
sqlite is in conjunction with arrayfields:


   jib:~ > sqlite db '.tables'
   attributes  jobs


   jib:~ > cat a.rb
   require 'sqlite'
   require 'arrayfields'

   db = SQLite::Database::new 'db', 42
   db.use_array = true

   tuples = db.execute('select * from jobs limit 4')

   tuples.each do |tuple|
     fields = tuple.fields
     puts "---"
     puts "  class        : #{ tuple.class }"
     puts "  tuple        : #{ tuple.join ', ' }"
     puts "  fields       : #{ tuple.fields.join ', ' }"
     puts "  tuple[0]     : #{ tuple[0] }"
     puts "  tuple['jid'] : #{ tuple['jid'] }"
   end


   jib:~ > ruby a.rb
   ---
     class        : Array
     tuple        : 1, 0, finished, 2006-04-20 10:40:34.751516,
2006-04-20 10:40:49.817180, 2006-04-20 11:38:42.422901, 3472.605721,
jib.ngdc.noaa.gov, halibut.ngdc.noaa.gov, , stdout/1, stderr/1, 16036,
0, F152006_lc4_line_screened, true, cfjob
--src=ftp://cfd2/cfd2-2/data/vfl_4avg/F152006.d/lc4/
--dst=ftp://cfd2/cfd2-3/data/lc_composites/ -- wavg_dn
/dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list
F15_20060314_20060412.cloud2.light1.line_screened.tile10 -c
/dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile10.conf
     fields       : jid, priority, state, submitted, started, finished,
elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status,
tag, restartable, command
     tuple[0]     : 1
     tuple['jid'] : 1
   ---
     class        : Array
     tuple        : 2, 0, finished, 2006-04-20 10:40:34.751516,
2006-04-20 10:40:50.655100, 2006-04-20 11:40:46.114144, 3595.459044,
jib.ngdc.noaa.gov, halibut.ngdc.noaa.gov, , stdout/2, stderr/2, 16090,
0, F152006_lc4_line_screened, true, cfjob
--src=ftp://cfd2/cfd2-2/data/vfl_4avg/F152006.d/lc4/
--dst=ftp://cfd2/cfd2-3/data/lc_composites/ -- wavg_dn
/dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list
F15_20060314_20060412.cloud2.light1.line_screened.tile11 -c
/dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile11.conf
     fields       : jid, priority, state, submitted, started, finished,
elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status,
tag, restartable, command
     tuple[0]     : 2
     tuple['jid'] : 2
   ---
     class        : Array
     tuple        : 3, 0, finished, 2006-04-20 10:40:34.751516,
2006-04-20 10:40:51.674523, 2006-04-20 11:42:12.135216, 3680.460693,
jib.ngdc.noaa.gov, trout.ngdc.noaa.gov, , stdout/3, stderr/3, 16003, 0,
F152006_lc4_line_screened, true, cfjob
--src=ftp://cfd2/cfd2-2/data/vfl_4avg/F152006.d/lc4/
--dst=ftp://cfd2/cfd2-3/data/lc_composites/ -- wavg_dn
/dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list
F15_20060314_20060412.cloud2.light1.line_screened.tile12 -c
/dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile12.conf
     fields       : jid, priority, state, submitted, started, finished,
elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status,
tag, restartable, command
     tuple[0]     : 3
     tuple['jid'] : 3
   ---
     class        : Array
     tuple        : 4, 0, finished, 2006-04-20 10:40:34.751516,
2006-04-20 10:40:51.858822, 2006-04-20 11:44:41.847442, 3829.98862,
jib.ngdc.noaa.gov, trout.ngdc.noaa.gov, , stdout/4, stderr/4, 16057, 0,
F152006_lc4_line_screened, true, cfjob
--src=ftp://cfd2/cfd2-2/data/vfl_4avg/F152006.d/lc4/
--dst=ftp://cfd2/cfd2-3/data/lc_composites/ -- wavg_dn
/dmsp/reference/lunar_cycle_conf/F152006_lc4_prefix.list
F15_20060314_20060412.cloud2.light1.line_screened.tile13 -c
/dmsp/reference/lunar_cycle_conf/cloud2.light1.line_screened.tile13.conf
     fields       : jid, priority, state, submitted, started, finished,
elapsed, submitter, runner, stdin, stdout, stderr, pid, exit_status,
tag, restartable, command
     tuple[0]     : 4
     tuple['jid'] : 4


when you use arrayfields each tuple is an Array, but the array is
dynamically
altered such that keyword access is also supported.  you have 'normal'
hash-like things like ' tuple.values_at %w( jid priority ) '  etc.


regards.

-a
6d9bf78ca49a017e9e3e6b0357b6c59e?d=identicon&s=25 Peter Hickman (Guest)
on 2006-05-03 19:07
(Received via mailing list)
The problem for me is that the code I am writing needs to take an array
of results and convert the results hash for each record into a chunk of
XML for any set of results it is passed in. Thus it does not know, or
even need to know, what the keys for the table are. It just wants to
iterate over them and create some output. It does not take much for me
to only process the string based keys but it looked to me like I was
overlooking something obvious.

No big deal, just didn't work quite the way I was expecting.
Cb48ca5059faf7409a5ab3745a964696?d=identicon&s=25 unknown (Guest)
on 2006-05-03 19:07
(Received via mailing list)
On Wed, 3 May 2006, Peter Hickman wrote:

> The problem for me is that the code I am writing needs to take an array of
> results and convert the results hash for each record into a chunk of XML for
> any set of results it is passed in. Thus it does not know, or even need to
> know, what the keys for the table are. It just wants to iterate over them
> and create some output. It does not take much for me to only process the
> string based keys but it looked to me like I was overlooking something
> obvious.
>
> No big deal, just didn't work quite the way I was expecting.

indeed.  you can do

   fields = tuple.fields

   fields.each do |field|
     append_xml field, tuple[field]
   end

and preserve order.  or just the String keys and use random order.

regards.

-a
6d9bf78ca49a017e9e3e6b0357b6c59e?d=identicon&s=25 Peter Hickman (Guest)
on 2006-05-03 19:07
(Received via mailing list)
Ah, that looks exactly like what I want.

Thanks
This topic is locked and can not be replied to.