Class: DBI::DatabaseHandle

Inherits:
Object
  • Object
show all
Defined in:
lib/mydbi.rb

Instance Method Summary collapse

Instance Method Details

#ascii_query(sql, *values) ⇒ Object

prints the query results (columns names and values) much like the mysql commandline

+----+---------+---------------------+
| id | song_id | played_at           |
+----+---------+---------------------+
| 3  | 713     | 2007-12-01 00:44:44 |
| 4  | 174     | 2007-12-01 00:44:44 |
+----+---------+---------------------+



161
162
163
164
165
166
167
 
# File 'lib/mydbi.rb', line 161

def ascii_query(sql,*values)
  sth = self.query(sql,*values)
  rows = sth.fetch_all
  col_names = sth.column_names
  sth.finish
  DBI::Utils::TableFormatter.ascii(col_names, rows)
end

#query(sql, *values) ⇒ Object

execute a query

SELECT:

Either the StatementHandle (sth) is returned or if you pass it a block it will iterate across the results yielding the row

sth = query("select * from songs")
puts sth.rows
while( row = sth.fetch )
  p row
end
sth.finish

or

query("select * from songs") do |row|
  p row
end

INSERT:

Will return the last_insert_id. Warning! If you provide a bulk insert you’ll only see get back the id of the first insert (with Mysql 5.0.45-Debian_1ubuntu3-log anyway).

last_insert_id = query("insert into songs values (?,?,?,?)",nil,artist,album,song)
=> 1

last_insert_id = query("insert into songs values (?,?,?,?)",nil,artist,album,song)
=> 2

UPDATE:

Will return the affected_rows_count

affected_row_count = query("update songs set artist=? where song_id = ?",new_artist, song_id)
=> 1

default:

returns sth after preparing and executing



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
 
# File 'lib/mydbi.rb', line 116

def query(sql,*values)
  case sql
  when /^\s*select/i
    sth = self.prepare(sql)
    sth.execute(*values)
    if block_given?
      while row = sth.fetch do
        yield(row)
      end
      sth.finish
    else
      return sth
    end

  when /^\s*update/i
    return self.do(sql,*values); # returns affected_rows_count
  
  when /^\s*insert/i
    # automatically getting the last_insert id is really only meant
    # to work when inserting a single record. bulk inserts ?!
    rows_inserted = self.do(sql,*values);
    last_id = nil
    sql.squeeze(" ").match(/insert into ([^ ]*) /) # grab the table
    query("select last_insert_id() from #{$1} limit 1"){|row|
      last_id = row[0];
    }
    return last_id

  else # create, drop, truncate, show, ...
    sth = self.prepare(sql)
    sth.execute(*values)
    return sth

  end
end