Rubyists,
Given a table in a database defined by the following:
create table test ( test DATE );
how can I insert the current time using Ruby/DBI? Given a DBI::DatabaseHandler
"dbh" I’d expect that you can do the following:
dbh.do “insert into test values (#{Time.now})”
That is, I expect the DBI library to convert a Time object into the database’s
DATE data type. It appears that this is not the case. The above code causes
an error.
It doesn’t convert the String ‘2002-10-04 18:30:00’ into a DATE either.
For now, I am resorting to storing my date information as strings in the
database. Does anyone know about the logistics of database-independent data
handling?
Cheers,
Gavin
···
–
Gavin Sinclair Software Engineer
Sydney, Australia Soyabean Software Pty Ltd
I use DBI with Mysql and prepared statements:
stm = dbh.prepare(“intert into test values (?)”)
stm.execute(Time.now)
-billy.
···
On Thu, Oct 17, 2002 at 04:54:17PM +0900, Gavin Sinclair wrote:
Rubyists,
Given a table in a database defined by the following:
create table test ( test DATE );
how can I insert the current time using Ruby/DBI? Given a DBI::DatabaseHandler
“dbh” I’d expect that you can do the following:
dbh.do “insert into test values (#{Time.now})”
That is, I expect the DBI library to convert a Time object into the database’s
DATE data type. It appears that this is not the case. The above code causes
an error.
It doesn’t convert the String ‘2002-10-04 18:30:00’ into a DATE either.
For now, I am resorting to storing my date information as strings in the
database. Does anyone know about the logistics of database-independent data
handling?
–
Meisterbohne Söflinger Straße 100 Tel: +49-731-399 499-0
eLösungen 89077 Ulm Fax: +49-731-399 499-9
This worked for me (using the PostgreSQL driver) …
db.do("insert into test values(?)", Time.now)
IIRC, each individual driver handles conversions to its own native data
types, so your driver might not handle that directly, If so, then you
can try …
db.do("insert into test values(?)", DBI::Date.new(Time.now))
···
On Thu, 2002-10-17 at 03:54, Gavin Sinclair wrote:
Rubyists,
Given a table in a database defined by the following:
create table test ( test DATE );
how can I insert the current time using Ruby/DBI? Given a DBI::DatabaseHandler
“dbh” I’d expect that you can do the following:
dbh.do “insert into test values (#{Time.now})”
“Beware of bugs in the above code; I have only proved it correct,
not tried it.” – Donald Knuth (in a memo to Peter van Emde Boas)
For now, I am resorting to storing my date information as strings in the
database. Does anyone know about the logistics of database-independent date
handling?
I use DBI with Mysql and prepared statements:
stm = dbh.prepare(“intert into test values (?)”)
stm.execute(Time.now)
-billy.
Hmmm… I can’t test right now, but I’ll give it a try. Just so this post is
not a totally information-free zone, let me pass on a curious result.
create table test_date (test DATE);
insert into test_date values (‘01-OCT-2002’);
% ruby <<EOF
require “dbi”
DBI.connect(…) do |dbi|
date = dbi.select_one(“select * from test_date”).to_h[“TEST”]
puts date
puts date.class
end
EOF
01-OCT-02
String
···
From: “Philipp Meier” meier@meisterbohne.de
===
So, DATE information in the database in returned through either DBI or the
Oracle driver as a String. I’ll try the method you posted shortly, but I’m not
confident. (It’s probably the Oracle driver.)
Thanks,
Gavin
Rubyists,
Given a table in a database defined by the following:
create table test ( test DATE );
how can I insert the current time using Ruby/DBI? Given a
DBI::DatabaseHandler
“dbh” I’d expect that you can do the following:
dbh.do “insert into test values (#{Time.now})”
This worked for me (using the PostgreSQL driver) …
db.do("insert into test values(?)", Time.now)
IIRC, each individual driver handles conversions to its own native data
types, so your driver might not handle that directly, If so, then you
can try …
db.do("insert into test values(?)", DBI::Date.new(Time.now))
– Jim Weirich jweirich@one.net http://w3.one.net/~jweirich
Thanks Jim. I didn’t even know about DBI::Date, and I’ve spent ages reading
the docu^H^H^H^Hcode. I’m hoping to contribute some documentation to that
project, becuase it really is a great package. You’ve given me a whole new
frontier to explore.
Gavin
···
From: “Jim Weirich” jweirich@one.net
On Thu, 2002-10-17 at 03:54, Gavin Sinclair wrote:
Yes, indeed. DBI returns every column as String. You can use
dbh.columns(table)[0].sql_type to determine the sql type an convert it
yourself.
-billy.
···
On Thu, Oct 17, 2002 at 11:53:19PM +0900, Gavin Sinclair wrote:
From: “Philipp Meier” meier@meisterbohne.de
For now, I am resorting to storing my date information as strings in the
database. Does anyone know about the logistics of database-independent date
handling?
I use DBI with Mysql and prepared statements:
stm = dbh.prepare(“intert into test values (?)”)
stm.execute(Time.now)
-billy.
Hmmm… I can’t test right now, but I’ll give it a try. Just so this post is
not a totally information-free zone, let me pass on a curious result.
create table test_date (test DATE);
insert into test_date values (‘01-OCT-2002’);
% ruby <<EOF
require “dbi”
DBI.connect(…) do |dbi|
date = dbi.select_one(“select * from test_date”).to_h[“TEST”]
puts date
puts date.class
end
EOF
01-OCT-02
String
===
So, DATE information in the database in returned through either DBI or the
Oracle driver as a String. I’ll try the method you posted shortly, but I’m not
confident. (It’s probably the Oracle driver.)
–
Meisterbohne Söflinger Straße 100 Tel: +49-731-399 499-0
eLösungen 89077 Ulm Fax: +49-731-399 499-9
From: “Philipp Meier” meier@meisterbohne.de
For now, I am resorting to storing my date information as strings in the
database. Does anyone know about the logistics of database-independent
date
handling?
I use DBI with Mysql and prepared statements:
stm = dbh.prepare(“intert into test values (?)”)
stm.execute(Time.now)
-billy.
I tried that code and got the following error:
…/site_ruby/1.6/DBD/Oracle/Oracle.rb:290:in `execute’: ORA-01861: literal
does not match format string (DBI::DatabaseError)
I guess the driver’s just not built for it 
Gavin
···
From: “Gavin Sinclair” gsinclair@soyabean.com.au
silly question, but have you tried quoting the date string?
stm = dbh.prepare(“insert into test values (‘?’)”)
i have not used the dbi, or oracle, but i need the quote the output of
Time.now when i use it in postgresql - so i thought i’d throw that out
there…
-a
···
On Fri, 18 Oct 2002, Gavin Sinclair wrote:
From: “Gavin Sinclair” gsinclair@soyabean.com.au
From: “Philipp Meier” meier@meisterbohne.de
For now, I am resorting to storing my date information as strings in the
database. Does anyone know about the logistics of database-independent
date
handling?
I use DBI with Mysql and prepared statements:
stm = dbh.prepare(“intert into test values (?)”)
stm.execute(Time.now)
-billy.
I tried that code and got the following error:
…/site_ruby/1.6/DBD/Oracle/Oracle.rb:290:in `execute’: ORA-01861: literal
does not match format string (DBI::DatabaseError)
I guess the driver’s just not built for it
–
====================================
Ara Howard
NOAA Forecast Systems Laboratory
Information and Technology Services
Data Systems Group
R/FST 325 Broadway
Boulder, CO 80305-3328
Email: ahoward@fsl.noaa.gov
Phone: 303-497-7238
Fax: 303-497-7259
====================================
stm = dbh.prepare(“intert into test values (?)”)
stm.execute(Time.now)
-billy.
I tried that code and got the following error:
…/site_ruby/1.6/DBD/Oracle/Oracle.rb:290:in `execute’: ORA-01861: literal
does not match format string (DBI::DatabaseError)
I guess the driver’s just not built for it
silly question, but have you tried quoting the date string?
stm = dbh.prepare(“insert into test values (‘?’)”)
i have not used the dbi, or oracle, but i need the quote the output of
Time.now when i use it in postgresql - so i thought i’d throw that out
there…
Thanks ‘a’ 
I’ll try that at work on Monday. It seems like a good idea. I can’t predict
whether it will work, though…
Gavin
···
From: “ahoward” ahoward@fsl.noaa.gov