# Problem with SQLite and Ruby **URL:** \ **Category:** Ruby\ **Created:** [May 3, 2006, 7:06pm UTC](https://www.ruby-forum.com/t/problem-with-sqlite-and-ruby/59450 "2006-05-03T19:06:59Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![tekhne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/90ced4/32.png) [@tekhne](https://www.ruby-forum.com/u/tekhne)\ **Post date:** [May 3, 2006, 7:06pm UTC](https://www.ruby-forum.com/t/problem-with-sqlite-and-ruby/59450/1 "2006-05-03T19:06:59Z")
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? ---
**Author:** ![tekhne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/90ced4/32.png) [@tekhne](https://www.ruby-forum.com/u/tekhne)\ **Post date:** [May 3, 2006, 7:07pm UTC](https://www.ruby-forum.com/t/problem-with-sqlite-and-ruby/59450/2 "2006-05-03T19:07:20Z")
On Wed, 3 May 2006, Peter H. 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](http://jib.ngdc.noaa.gov), [halibut.ngdc.noaa.gov](http://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](http://jib.ngdc.noaa.gov), [halibut.ngdc.noaa.gov](http://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](http://jib.ngdc.noaa.gov), [trout.ngdc.noaa.gov](http://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](http://jib.ngdc.noaa.gov), [trout.ngdc.noaa.gov](http://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 ---
**Author:** ![tekhne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/90ced4/32.png) [@tekhne](https://www.ruby-forum.com/u/tekhne)\ **Post date:** [May 3, 2006, 7:07pm UTC](https://www.ruby-forum.com/t/problem-with-sqlite-and-ruby/59450/3 "2006-05-03T19:07:24Z")
On Wed, 3 May 2006, Peter H. 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 ---
**Author:** ![tekhne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/90ced4/32.png) [@tekhne](https://www.ruby-forum.com/u/tekhne)\ **Post date:** [May 3, 2006, 7:07pm UTC](https://www.ruby-forum.com/t/problem-with-sqlite-and-ruby/59450/4 "2006-05-03T19:07:22Z")
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. ---
**Author:** ![tekhne](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/90ced4/32.png) [@tekhne](https://www.ruby-forum.com/u/tekhne)\ **Post date:** [May 3, 2006, 7:07pm UTC](https://www.ruby-forum.com/t/problem-with-sqlite-and-ruby/59450/5 "2006-05-03T19:07:28Z")
Ah, that looks exactly like what I want. Thanks