From: Tom Sawyer Date: 2002-08-07T04:26:58+09:00 Subject: Re: Getting column types with DBI --=-C+71ueWoqBk8RZSkq6D9 Content-Type: text/plain Content-Transfer-Encoding: 7bit On Tue, 2002-08-06 at 09:12, Philipp Meier wrote: > Hello, > > does DBI support getting the column types for row? I digged the source > and found that DBD::Mysql does something on it but I don't know, how get > actually use it. > > Thanks, > -billy. > -- take a look at this attachment. it's my general library for accessing a database. you'll notice that in the DBConnection class there is a method called load_meta. in it you'll see @connection.columns() this pulls down the meta-info including types into a hash. hope that helps. ~transami --=-C+71ueWoqBk8RZSkq6D9 Content-Disposition: attachment; filename=database.rb Content-Transfer-Encoding: quoted-printable Content-Type: text/x-ruby; name=database.rb; charset=ANSI_X3.4-1968 # TomsLib - Database Library # Copyright (c)2002 Thomas Sawyer, All Rights Reserved # # Modules: # DBize # # Classes: # DBConnection # require 'dbi/dbi' module TomsLib # Mixin to automatically make and object database connected # Object requires: # #table - method to return table name # #record - method to return record number module DBize =20 # # Module Methods # =20=0D def DBize.connect(dsn, user, pass) @@dbi =3D DBConnection.new(dsn, user, pass) end =20 def DBize.setup(id_field=3Dnil) if id_field @@id_field =3D id_field else if @@dbi possible_ids =3D @@dbi.meta_names & ['id', 'recordno', 'recno', '= record'] if possible_ids.empty? raise 'inderminate id field' else @@id_field =3D possible_ids[0] end else raise 'inderminate id field' end end end =20 def DBize.close @@dbi.close end =20 def DBize.dbi @@dbi end =20 # # Instance Methods # =20 def load_from_database sql =3D "SELECT * FROM #{table} WHERE #{@@id_field}=3D#{record}" r =3D @@dbi.connection.select_one(sql) raise 'record not found' if not r r.each_with_name do |value, name| send("#{name}=3D".intern, value) end end =20 def save_to_database sql =3D "SELECT * FROM #{table} WHERE #{@@id_field}=3D#{record}" r =3D @@dbi.connection.select_one(sql) if r rc =3D update_database(r) else rc =3Dinsert_into_database end return rc end =20 def update_database(r=3Dnil) if not r sql =3D "SELECT * FROM #{table} WHERE #{@@id_field}=3D#{record}" r =3D @@dbi.connection.select_one(sql) raise 'can not update because record not found' if not r end fields =3D [] r.each_with_name do |value, name| new_value =3D send(name.intern) if new_value !=3D value fields << [name, new_value] end end sql =3D "UPDATE #{table} SET " << fields.collect{ |pair| pair.join('= =3D') }.join(',') << " WHERE #{@@id_field}=3D#{record}" @@dbi.connection.transaction do rc =3D $dbi.connection.do(sql) end return rc end =20 def insert_into_database fields =3D [] @@dbi.meta_name.each do |name| new_value =3D send(name.intern) fields << [name, new_value] end sql =3D "INSERT INTO #{table} " << fields.collect{ |pair| pair[0] }.j= oin(',') << ' VALUES (' << fields.collect{ |pair| pair[1] }.join(',') << ')= ' @@dbi.connection.transaction do rc =3D @@dbi.connection.do(sql) end return rc end def delete_from_database sql =3D "DELETE FROM #{table} WHERE #{@@id_field}=3D#{record}" @@dbi.connection.transaction do rc =3D @@dbi.connection.do(sql) end end end # DBize =20 =20 # Common class for accessing a database # Provides some extra functionality class DBConnection attr_reader :connection attr_reader :tables attr_reader :meta attr_reader :meta_names attr_reader :meta_types # initialize opens the connection to the database, prepares variables and= calls meta (currently AutoCommit is set to false) def initialize(dsn, user, pass) @connection =3D DBI.connect(dsn, user, pass, 'AutoCommit' =3D> false) @tables =3D [] @meta =3D {} @meta_names =3D {} @meta_types =3D {} load_meta # load database meta-information end # close method closes the database connection def close @connection.disconnect end # meta method collects meta information for the database def load_meta @tables =3D @connection.tables # .select { |table| table !~ /^pg_/ } #= this only works with postgresql to remove system tables @tables.each do |table| @meta[table] =3D @connection.columns(table) @meta_names[table] =3D [] @meta_types[table] =3D {} @meta[table].each do |column| @meta_names[table] << column['name'] = # make an array of column names @meta_types[table].update({ column['name'] =3D> column['type_name'] }= ) # make a hash of column names =3D> column types end end end # Returns a field value formatted for sql statments according to the data= base meta information # Essentially it deals with quoting strings def sql_format(table, field_name, field_value) if not @meta_types.has_key?(table) raise "invalid table: #{table}" end if not @meta_types[table].has_key?(field_name) raise "invalid field name: #{field_name}" end case @meta_types[table][field_name].downcase when /int/, /serial/ if type !=3D 'interval' and type !=3D 'point' typified_value =3D field_value end when /float/, /double/, /money/, /numeric/, /decimal/ typified_value =3D field_value when /bool/ typified_value =3D field_value when /timestamp/, /date/ if field_value.to_s.strip.empty? typified_value =3D 'NULL' else typified_value =3D sql_escape(field_value.to_s.strip).quote(true) end when /var/, /char/, /text/ typified_value =3D sql_escape(field_value.to_s.strip).quote(true) end return typified_value end =09 # sql_escape escapes apostrophes in character string types def sql_escape(str) return str.gsub(/[']/,"''") # doubles apostrophes end =09 # typecast's a value according to database meta information def typecast(table, field_name, field_value, honor_func=3Dfalse) if @meta_types.has_key?(table) if @meta_types[table].has_key?(field_name) case @meta_types[table][field_name].downcase when /int/, /serial/ if type !=3D 'interval' and type !=3D 'point' # these type are= not supported if honor_func and field_value.to_s.strip =3D~ /^\w+\(/ typecast_value =3D field_value else typecast_value =3D field_value.to_i end else typecast_value =3D field_value.to_s.strip end when /float/, /double/, /money/, /numeric/, /decimal/ if honor_func and field_value.to_s.strip =3D~ /^\w+\(/ typecast_value =3D field_value else typecast_value =3D field_value.to_f end when /bool/ if honor_func and field_value.to_s.strip =3D~ /^\w+\(/ typecast_value =3D field_value else typecast_value =3D field_value.to_b end when /timestamp/, /date/ typecast_value =3D field_value.to_s.strip when /var/, /char/, /text/ typecast_value =3D field_value.to_s.strip else typecast_value =3D field_value.to_s.strip end else # pass through any field not found? typecast_value =3D field_value #raise "table column, #{field_nam= e}, does not exist" end else # pass through if table not found? typecast_value =3D field_value #raise "typecast table, #{table}, d= oes not exist" end return typecast_value end =20 end # DBConnection =20 end # TomsLib --=-C+71ueWoqBk8RZSkq6D9--