ruby-dbi-devel — ruby-dbi development list

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Sean Chittenden wrote:
> > > > > Have you tried the following? 
> > > > > 
> > > > >   dbh['AutoCommit'] = false
> > > > > 
> > > > > If this was set to false, no commit should be issued automatically!
> > 
> > Maybe, I've found a solution.
> > 
> > A new version of Pg.rb is appended. I cannot test it this week, so perhaps could you? 
> > 
> > I've thrown out the whole in_transaction/start_transaction stuff, and 
> > replaced it with a simple "SET AUTOCOMMIT TO ON|OFF" statement.
> > I hope this works correctly.
> 
> This looks and acts worlds better, thank you!  One quick suggestion
> given that 7.3's in beta right now.  In 7.3 you can turn off
> autocommit for the entire database on the backend with a new tunable
> 'autocommit = false' which forces the user to begin/commit.  On line
> 105 of Pg.rb, you're forcing autocommit on by default.  Could you
> delete that line and just leave it up to the DBA who configured the
> machine?  I can't think of a reason why it'd be worth while to send
> the extra autocommits to the server.  -sc

The reason was to stay compatible with the old Pg.rb one. 
But I guess, good code explicitly sets AutoCommit, so that we can remove
this line.  

Regards,

  Michael

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

> > > > Have you tried the following? 
> > > > 
> > > >   dbh['AutoCommit'] = false
> > > > 
> > > > If this was set to false, no commit should be issued automatically!
> 
> Maybe, I've found a solution.
> 
> A new version of Pg.rb is appended. I cannot test it this week, so perhaps could you? 
> 
> I've thrown out the whole in_transaction/start_transaction stuff, and 
> replaced it with a simple "SET AUTOCOMMIT TO ON|OFF" statement.
> I hope this works correctly.

This looks and acts worlds better, thank you!  One quick suggestion
given that 7.3's in beta right now.  In 7.3 you can turn off
autocommit for the entire database on the backend with a new tunable
'autocommit = false' which forces the user to begin/commit.  On line
105 of Pg.rb, you're forcing autocommit on by default.  Could you
delete that line and just leave it up to the DBA who configured the
machine?  I can't think of a reason why it'd be worth while to send
the extra autocommits to the server.  -sc

-- 
Sean Chittenden

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Michael Neumann wrote:
> Sean Chittenden wrote:
> > > Have you tried the following? 
> > > 
> > >   dbh['AutoCommit'] = false
> > > 
> > > If this was set to false, no commit should be issued automatically!

Maybe, I've found a solution.

A new version of Pg.rb is appended. I cannot test it this week, so perhaps could you? 

I've thrown out the whole in_transaction/start_transaction stuff, and 
replaced it with a simple "SET AUTOCOMMIT TO ON|OFF" statement.
I hope this works correctly.

Regards,

  Michael

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Sean Chittenden wrote:
> > Have you tried the following? 
> > 
> >   dbh['AutoCommit'] = false
> > 
> > If this was set to false, no commit should be issued automatically!
> 
> Ehh...  yes it does.
> 
> And in the error log:
> 
> 2002-09-18 01:33:54 LOG:  query: select getdatabaseencoding()
> 2002-09-18 01:33:54 LOG:  query: SELECT typname, typelem FROM pg_type
> 2002-09-18 01:33:54 LOG:  query: SELECT datname FROM pg_database WHERE datname = 'foo'
> 2002-09-18 01:33:54 LOG:  query: BEGIN WORK
> 2002-09-18 01:33:54 LOG:  query: DROP DATABASE foo
> 2002-09-18 01:33:54 ERROR:  DROP DATABASE: may not be called in a transaction block
> 2002-09-18 01:33:54 LOG:  statement: DROP DATABASE foo

Could you set in method Database#initialize (Pg.rb), @debug_level to 3 and run the example
again. What's now the output on stdout?


Regards,

  Michael

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

> Have you tried the following?=20
>=20
>   dbh['AutoCommit'] =3D false
>=20
> If this was set to false, no commit should be issued automatically!

Ehh...  yes it does.

#<DBI::DatabaseHandle:0x81cf860 @trace_mode=3D2, @handle=3D#<DBI::DBD::Pg::=
Database:0x81cf838 @coerce=3D#<DBI::DBD::Pg::PgCoerce:0x81cf52c>, @connecti=
on=3D#<PGconn:0x81cf568>, @in_transaction=3Dfalse, @type_map=3D{2205=3D>:as=
_str, 1083=3D>:as_str, 27=3D>:as_str, 16=3D>:as_bool, 1562=3D>:as_str, 869=
=3D>:as_str, 704=3D>:as_str, 22=3D>:as_str, 0=3D>:as_str, 2206=3D>:as_str, =
1700=3D>:as_str, 28=3D>:as_str, 17=3D>:as_bytea, 600=3D>:as_str, 1266=3D>:a=
s_str, 650=3D>:as_str, 23=3D>:as_int, 628=3D>:as_str, 1184=3D>:as_str, 700=
=3D>:as_float, 601=3D>:as_str, 29=3D>:as_str, 18=3D>:as_str, 2202=3D>:as_st=
r, 24=3D>:as_str, 1790=3D>:as_str, 602=3D>:as_str, 1042=3D>:as_str, 30=3D>:=
as_str, 19=3D>:as_str, 701=3D>:as_float, 2203=3D>:as_str, 1114=3D>:as_times=
tamp, 25=3D>:as_str, 718=3D>:as_str, 1560=3D>:as_str, 1186=3D>:as_str, 702=
=3D>:as_str, 603=3D>:as_str, 20=3D>:as_int, 1043=3D>:as_str, 790=3D>:as_str=
, 2204=3D>:as_str, 1082=3D>:as_date, 829=3D>:as_str, 26=3D>:as_str, 1033=3D=
>:as_str, 604=3D>:as_str, 703=3D>:as_str, 21=3D>:as_int}, @debug_level=3D0,=
 @autocommit=3Dfalse, @attr=3D{"AutoCommit"=3D>false}>, @trace_output=3D#<I=
O:0x8073218>>

And in the error log:

2002-09-18 01:33:54 LOG:  query: select getdatabaseencoding()
2002-09-18 01:33:54 LOG:  query: SELECT typname, typelem FROM pg_type
2002-09-18 01:33:54 LOG:  query: SELECT datname FROM pg_database WHERE datn=
ame =3D 'foo'
2002-09-18 01:33:54 LOG:  query: BEGIN WORK
2002-09-18 01:33:54 LOG:  query: DROP DATABASE foo
2002-09-18 01:33:54 ERROR:  DROP DATABASE: may not be called in a transacti=
on block
2002-09-18 01:33:54 LOG:  statement: DROP DATABASE foo

The logic in lib/dbd_pg/Pg.rb around line 459 seems broken to me.
Thoughts?  -sc


--=20
Sean Chittenden

Re: [Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Michael N. <uu...@rz...>]

Sean Chittenden wrote:
> I've got a pretty interesting problem in that I'm dynamically creating
> a database using Ruby.  I've got something of a problem though with
> the way that transactions are handled with the execute() methods in
> that they always prepend BEGIN WORK; if you're outside of a
> transaction.  There are a couple of workarounds for this, but none of
> them elegant or correct, IMHO.
> 
> *) What I'm doing now:
> 
>    dbh.do('ABORT; #{sql}; COMMIT; BEGIN;')
> 
>    It's super ugly, but it works insofar as it keeps the database in
>    sync with DBI.
> 
> *) A possible hack would be to add :in_transaction as an accessor:
> 
>    # make sure you're currently outside of a transaction
>    dbh.in_transaction = true
>    dbh.do(sql)
>    dbh.in_transaction = false
> 
> *) Another option would be to create something parallel to #do, only
>  have it skip the write-through transaction bits.
> 
>  dbh.do_exec(sql)
> 
> *) The most elegant and likely correct solution would be to add
>  another options tunable that would let #do omit adding a 'BEGIN
>  WORK;' to every command.
> 
>  dbh['AutoBegin'] = false
>  dbh.do(sql)
>  dbh.commit

Have you tried the following? 

  dbh['AutoCommit'] = false

If this was set to false, no commit should be issued automatically!

You can also disable AutoCommit from the beginning on:

 dbh = DBI.connect("dbi:xxxx", "user", "pw", "AutoCommit" => false)


Hope this helps!


Regards,

  Michael

[Ruby-DBI-devel] Performing SQL actions that can't be done inside of a transaction...

[Sean C. <se...@ch...>]

I've got a pretty interesting problem in that I'm dynamically creating
a database using Ruby.  I've got something of a problem though with
the way that transactions are handled with the execute() methods in
that they always prepend BEGIN WORK; if you're outside of a
transaction.  There are a couple of workarounds for this, but none of
them elegant or correct, IMHO.

*) What I'm doing now:

   dbh.do('ABORT; #{sql}; COMMIT; BEGIN;')

   It's super ugly, but it works insofar as it keeps the database in
   sync with DBI.

*) A possible hack would be to add :in_transaction as an accessor:

   # make sure you're currently outside of a transaction
   dbh.in_transaction = true
   dbh.do(sql)
   dbh.in_transaction = false

*) Another option would be to create something parallel to #do, only
 have it skip the write-through transaction bits.

 dbh.do_exec(sql)

*) The most elegant and likely correct solution would be to add
 another options tunable that would let #do omit adding a 'BEGIN
 WORK;' to every command.

 dbh['AutoBegin'] = false
 dbh.do(sql)
 dbh.commit

I'm personally a fan of the last option as it'd give the developer
fine grained control over when a transaction should begin or end and
would let people execute administrative SQL commands that can't be
executed inside of a transaction (create/drop database, etc).  Anyone
else have an opinion on this?  -sc

-- 
Sean Chittenden

[Ruby-DBI-devel] Method aliases and API...

[Sean C. <se...@ch...>]

Howdy.  Attached is a dead simple patch that aliases:

	DBI::DatabaseHandle#rollback    -> abort
	DBI::DatabaseHandle#transaction -> begin

I correct myself making that mistake probably every few days and even
after using DBI for a year now.  :-/ This adds a degree of parallelism
between DBI and psql (not to mention they're shorter).  -sc

-- 
Sean Chittenden

Re: [Ruby-DBI-devel] DBI silently catching exceptions?

[James F.H. <jf...@ci...>]

On Wed, 28 Aug 2002 09:48:53 -0400
James F.Hranicky <jf...@ci...> wrote:

> Is DBI silently catching exceptions? 

I found it. DBD::Oracle was catching but not handling all but one execption
in DBI::DBD::Oracle::parse.

------------------------------------------
*** Oracle.rb   Thu Aug 29 10:45:23 2002
--- Oracle.rb.mod       Thu Aug 29 10:45:17 2002
***************
*** 339,344 ****
--- 339,345 ----
        @handle.parse(statement)
      rescue OCIError => err
        retry if err.to_i == 3123  ## block
+       raise DBI::DatabaseError.new(err.message, err.to_i)
      end   
  
      colnr = 1
------------------------------------------

HTH.

----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin                   UF/CISE Department |
| E314D CSE Building                            Phone (352) 392-1499 |
| jf...@ci...                      http://www.cise.ufl.edu/~jfh |
----------------------------------------------------------------------

"Given a choice between a complex, difficult-to-understand, disconcerting
 explanation and a simplistic, comforting one, many prefer simplistic
 comfort if it's remotely plausible, especially if it involves blaming
 someone else for their problems."
                                                -- Bob Lewis, _Infoworld_

[Ruby-DBI-devel] Anyone done anything like an SQL "select statement maker" class?

[Stephen D. <st...@da...>]

Hi,

I'm using ruby-fltk with DBI and dbd-pg to implement a user interface for
a video recorder project.

I want to pass the browser the "bare bones" of an SQL select, and then
implement some direct-manipulation interface where the user can add and
remove columns from the displayed browser, change column widths, adjust
the "order by", "where" clauses etc etc.  Imagine something like Excel's
autofilter and other data handling facilities.

Has anyone done anything along these lines that I can look to get me
on the way?

Thanks,
Steve Davies

[Ruby-DBI-devel] DBI silently catching exceptions?

[James F.H. <jf...@ci...>]

Is DBI silently catching exceptions? 

The following code throws an exception and exits:
 
    conn = Oracle.new("sys", "....")
    begin
        conn.exec("drop table foo")
        print "table test droped\n"
    end

    /usr/local/lib/ruby/site_ruby/1.7/oracle.rb:101:in `parse': 
    ORA-00942: table or view does not exist (OCIError)

        from /usr/local/lib/ruby/site_ruby/1.7/oracle.rb:101:in `parse'
        from /usr/local/lib/ruby/site_ruby/1.7/oracle.rb:93:in `initialize'
        from /usr/local/lib/ruby/site_ruby/1.7/oracle.rb:37:in `new'
        from /usr/local/lib/ruby/site_ruby/1.7/oracle.rb:37:in `exec'
        from oratest.rb:8

While this code silently finishes:

    dbh = DBI.connect("dbi:Oracle:oradb", "sys", "....")
    dbh.prepare("drop table foo") { |sth|
        ret = sth.execute
    }

Also, sqlsh.rb simply says "No rows affected" . 

Is there another way to catch errors in DBI?

----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin                   UF/CISE Department |
| E314D CSE Building                            Phone (352) 392-1499 |
| jf...@ci...                      http://www.cise.ufl.edu/~jfh |
----------------------------------------------------------------------

"Given a choice between a complex, difficult-to-understand, disconcerting
 explanation and a simplistic, comforting one, many prefer simplistic
 comfort if it's remotely plausible, especially if it involves blaming
 someone else for their problems."
                                                -- Bob Lewis, _Infoworld_

[Ruby-DBI-devel] Resend: Patch to sqlsh.rb to hide DB password

[James F.H. <jf...@ci...>]

Oops -- I prompted the user for an "Oracle password" -- fixed to prompt
for the correct driver.

Jim

[Ruby-DBI-devel] Patch to sqlsh.rb to hide DB password

[James F.H. <jf...@ci...>]

Here's a small patch to sqlsh.rb to hide the password. Requires
the Termios module.

----------------------------------------------------------------------
| Jim Hranicky, Senior SysAdmin                   UF/CISE Department |
| E314D CSE Building                            Phone (352) 392-1499 |
| jf...@ci...                      http://www.cise.ufl.edu/~jfh |
----------------------------------------------------------------------

"Given a choice between a complex, difficult-to-understand, disconcerting
 explanation and a simplistic, comforting one, many prefer simplistic
 comfort if it's remotely plausible, especially if it involves blaming
 someone else for their problems."
                                                -- Bob Lewis, _Infoworld_

[Ruby-DBI-devel] patch to Pg.rb

[Brad H. <bh...@vp...>]

Hi,

I have a patch for Pg.rb which fixes problems handling backslashes in
strings.  Could someone please review/commit it?

Note: the second diff is necessary for proper quoting of placeholder
values.  The default quote() method in sql.rb was being used without
that change.

Thanks,
-Brad


--- Pg.rb       Wed Jul  3 13:11:23 2002
+++ /usr/local/lib/ruby/site_ruby/1.6/DBD/Pg/Pg.rb      Thu Aug 22
16:03:15 2002
@@ -304,6 +304,15 @@
          @connection.exec(sql)
        end
        
+       def quote(value)
+         case value
+         when String
+               "'#{ value.gsub(/\\/){ '\\\\' }.gsub(/'/){ '\\\'' } }'"
+         else
+               super
+         end
+       end
+       
        private # ----------------------------------------------------
 
        def initialize_autocommit
@@ -411,7 +420,7 @@
 
        def initialize(db, sql)
          @db  = db
-          @prep_sql = DBI::SQL::PreparedStatement.new(self, sql)
+          @prep_sql = DBI::SQL::PreparedStatement.new(@db, sql)
          @result = nil
          @bindvars = []
        end

Re: [Ruby-DBI-devel] pg driver quoting

[Brad H. <bh...@vp...>]

On Sat, 2002-08-10 at 09:59, KAWAJI Shinya wrote:
> > The pg driver quotes it as:
> > 
> > '\'
> Because, dbd_mysql define an own quote method (MySQL's API is used),
> but dbd_postgres does not (Postgres's API dosen't have 'quote').
> 
> Try this patch.(untested)

Sorry to take so long to reply.  The patch solves the '\' problem, but
doesn't escape single quotes.  I had to add a quote method like:

        def quote(value)
          case value
          when String
                "'#{ value.gsub(/\\/){ '\\\\' }.gsub(/'/){ '\\\'' } }'"
          else
                super
          end
        end

That solved the quoting problems for me.

It would be great if someone could apply the patch to Pg.rb, or better
yet, if the ruby-postgres driver could be patched to use the internal
quoting mechanism provided by the postgres client libraries.

Thanks,
-Brad

Re: [Ruby-DBI-devel] pg driver quoting

[Gabriel E. <gem...@au...>]

I emailed Noboru Saitou earlier about quoting; PostgreSQL does in fact ha=
ve a=20
quoting mechanism via PQescapeString().  He says he will add it to the=20
ruby-postgres wrapper, after which time perhaps the PostgreSQL dbd can be=
=20
adapted to take advantage of it.

--Gabriel Emerson

>
> Because, dbd_mysql define an own quote method (MySQL's API is used),
> but dbd_postgres does not (Postgres's API dosen't have 'quote').
>
> Try this patch.(untested)
>

Re: [Ruby-DBI-devel] pg driver quoting

[KAWAJI S. <ka...@ho...>]

Brad Hilton <bh...@vp...> wrote:

> The mysql driver quotes \ as:
> 
> '\\'
> 
> The pg driver quotes it as:
> 
> '\'


Because, dbd_mysql define an own quote method (MySQL's API is used),
but dbd_postgres does not (Postgres's API dosen't have 'quote').

Try this patch.(untested)


--- Pg.rb.bak   Sun Aug 11 01:54:34 2002
+++ Pg.rb       Sun Aug 11 01:54:34 2002
@@ -304,6 +304,15 @@
          @connection.exec(sql)
        end

+       def quote(value)
+         case value
+         when String
+           "'#{ value.gsub(/\\/){ '\\\\' } }'"
+         else
+           super
+         end
+       end
+
        private # ----------------------------------------------------

        def initialize_autocommit


SEE ALSO: Perl's DBD::Pg
http://search.cpan.org/author/JBAKER/DBD-Pg-1.13/Pg.pm#quote

--
Shinya Kawaji

[Ruby-DBI-devel] pg driver quoting

[Brad H. <bh...@vp...>]

Hi,

Using ruby-dbi 0.0.16, ruby-postgres 0.6.5, and postgres 7.2.1, quoting 
of '\' characters seems broken.

The mysql driver quotes \ as:

'\\'

The pg driver quotes it as:

'\'

which causes an sql syntax error because the closing quote is eaten up.

I've been double checking my results because I'm surprised nobody else 
has seen this problem.  Can anyone duplicate this?

Thanks,
-Brad

[Ruby-DBI-devel] patch fro BTS[ 586785 ] DBD::Pg back-slash quote bug

[MoonWolf <moo...@mo...>]

http://sourceforge.net/tracker/index.php?func=detail&aid=586785&group_id=43737&atid=437251
http://developer.postgresql.org/docs/postgres/sql-syntax.html#SQL-SYNTAX-CONSTANTS

Index: Pg.rb
===================================================================
RCS file: /cvsroot/ruby-dbi/src/lib/dbd_pg/Pg.rb,v
retrieving revision 1.23
diff -r1.23 Pg.rb
306c306,317
<
---
>
>         def quote(value)
>           case value
>           when String
>             value = value.gsub(/'/, "''") # ' (for ruby-mode)
>             value.gsub!(/\\/, '\\\\\\')
>             "'#{value}'"
>           else
>             super
>           end
>         end
>
499a511,520
>         def quote(value)
>           case value
>           when String
>             value = value.gsub(/'/, "''") # ' (for ruby-mode)
>             value.gsub!(/\\/, '\\\\\\')
>             "'#{value}'"
>           else
>             super
>           end
>         end

MoonWolf <moo...@mo...>

[Ruby-DBI-devel] thoughts on extending Ruby/DBI

[Elias K. <ek...@so...>]

Hello everybody,

I have some proposals for the DB2 driver as well as the Ruby DBI/DBD 
API, and I would be grateful to hear your opinions. In detail:

1)BLOB/CLOB support
I work on a project that will be using DB2 as a backend to store ruby 
objects (via Marshall.store) in VARCHAR columns and I am hitting on the 
database limit of pagesize (4096 bytes default). Extending the pagesize 
at db creation up to 32K will not solve the problem, but will only 
postpone it for a later time, so the logical solution seems to use large 
objects (LOB).
Looking at existing work, I see that only the postgres driver for ruby 
implements LOB functionality, but then it is hidden from the DBI. Class 
DBI::Binary seems a good starting point, so my first thought is to add 
functionality like the one found on java.sql.[B|C]lob interfaces. I 
think that JDBC is very well designed (although  its datatype strictness 
really gets on my nerves) and we can borrow many ideas and function 
calls. Or, we can borrow ideas from Python/Perl DBI implementations. Or 
better, we can keep it simple and use Ruby in a clever manner to hide 
all this complexity from the user.

2) Parameter binding
Right now, parameter binding uses ugly string concatenation to produce 
ad-hoc SQL statements, which cancels all performance gains from prepared 
(precompiled) statements. Apart from that, it doesn't support 
input/output and output parameters, which means that most existing 
legacy databases that use stored procedures are useless under Ruby. 
Therefore, we need to define some sort of attribute to let the db driver 
know what kind of parameter we use. We could be using 'attribs' 
parameter in DBI::Statement.bind_param to do this. For instance:

statement.bind_param(1, value1, { 'direction' => 'in', 'type' => SQL_BLOB})
statement.bind_param(2, value2, { 'direction' => 'out', 'type' => 
SQL_INTEGER})
statement.bind_param(3, value3, { 'direction' => 'inout', 'type' => 
SQL_VARCHAR})

In this case:
a)  direction => 'in' would mean that parameter is bound for input (no 
expected result from the db statement)
b) 'out' means that parameter is bound for output (value generated by 
database, ruby variable initialized & populated at statement execution time)
c) 'inout' means the same as 'out' except that we use the ruby 
variable's value at execution, dispose the value and populate a new 
value with the statement results

attribute 'type' must be supplied because the low-level driver needs to 
know what kind of data to expect from the database. Therefore, all 
returned ('out'/'inout') values should be instantiated from a base class 
that matches closely the specified SQL datatype.

Furthermore, for BLOB/CLOB functionality, value1 could be:
a) read directly from memory,
b) if it was a string it could represent a filename, then we could bind 
the parameter to the file itself. DB2 and ODBC have SQLBindFileTo(Col|Param)

3) DB2 driver
I admit I am confused as to where to draw the separation line between 
database handling logic and the ruby DBD implementation. Looking at the 
ODBC driver (~120k!) I see that all low-level work is done in C and the 
driver uses Ruby to act as a bridge to ODBC functions. This decision 
means that we have to take care of memory management, garbage collection 
etc. in C. What is your opinion?

See you,

Elias

Re: [Ruby-DBI-devel] Timestamp without time zone typeid problems...

[Sean C. <se...@ch...>]

> > I've verified that this is a fix for the problem.  I've also
> > gotten to the point of being able to reproduce it reliably:
> > 
> > echo "CREATE TABLE dbi_test ( t TIMESTAMP WITH OUT TIMEZONE NOT NULL );" | psql

Grrr... typing too fast.

echo "CREATE TABLE dbi_test ( t TIMESTAMP WITHOUT TIME ZONE NOT NULL );" | psql

> Sorry, but I can't create this table using PostgreSQL 7.2.1.
> If I omit "WITH OUT TIMEZONE", then it works.

I needed to transpose a space.  ::sigh::

> > # in dbi_test.rb
> > require 'dbi'
> > 
> > DBI.connect('dbi:Pg:test') do |dbh|
> >   dbh.select_all('SELECT t FROM dbi_test') do |row|
> >     puts row.inspect
> >   end
> > end
> > 
> > 
> > That'll throw it 100% of the time.  Any ideas on a fix for this?  -sc
> 
> What tells the raised exception?

/usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:289:in `convert': Unsupported Type (typeid=1114) (DBI::InterfaceError)
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:567:in `fill_array'
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:566:in `each_with_index'
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:566:in `each'
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:566:in `each_with_index'
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:566:in `fill_array'
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:519:in `fetchrow'
        from /usr/local/lib/ruby/site_ruby/1.7/DBD/Pg/Pg.rb:456:in `fetch'
        from /usr/local/lib/ruby/site_ruby/1.7/dbi/dbi.rb:742:in `fetch'
        from /usr/local/lib/ruby/site_ruby/1.7/dbi/dbi.rb:767:in `each'
        from /usr/local/lib/ruby/site_ruby/1.7/dbi/dbi.rb:595:in `select_all'
        from /usr/local/lib/ruby/site_ruby/1.7/dbi/dbi.rb:593:in `execute'
        from /usr/local/lib/ruby/site_ruby/1.7/dbi/dbi.rb:593:in `select_all'
        from tmp/dbi.rb:6
        from tmp/dbi.rb:5:in `connect'
        from /usr/local/lib/ruby/site_ruby/1.7/dbi/dbi.rb:332:in `connect'
        from tmp/dbi.rb:5

-sc

-- 
Sean Chittenden

Re: [Ruby-DBI-devel] Timestamp without time zone typeid problems...

[Michael N. <uu...@rz...>]

On Sun, Jul 07, 2002 at 07:58:26PM -0700, Sean Chittenden wrote:
> > > Which is correct because typid 1114 doesn't exist as a typeid in
> > > the database.  The problem is, it doesn't exist in the database
> > > and I don't know where the value 1114 is coming from.  Any
> > > thoughts?  I can send code/schema to anyone who's interested.  -sc
> > 
> > Can you send it me?
> > 
> > Another similar problem is:
> > 
> >   select 'h'
> > 
> > raises the following exception 
> > 
> >   Unsupported Type (typeid=705)
> > 
> > whereas
> > 
> >   select 'h'::text
> > 
> > gives the correct result.
> 
> I've verified that this is a fix for the problem.  I've also gotten to
> the point of being able to reproduce it reliably:
> 
> echo "CREATE TABLE dbi_test ( t TIMESTAMP WITH OUT TIMEZONE NOT NULL );" | psql

Sorry, but I can't create this table using PostgreSQL 7.2.1.
If I omit "WITH OUT TIMEZONE", then it works.

> # in dbi_test.rb
> require 'dbi'
> 
> DBI.connect('dbi:Pg:test') do |dbh|
>   dbh.select_all('SELECT t FROM dbi_test') do |row|
>     puts row.inspect
>   end
> end
> 
> 
> That'll throw it 100% of the time.  Any ideas on a fix for this?  -sc

What tells the raised exception?

Regards,

  Michael 

[Ruby-DBI-devel] Re: RPC and Postgres Reprise

[Gabriel E. <ga...@de...>]

>
> I've now changed method Statement#rows (which is called and returned by
> Database#do) to return the Row Processed Count (affected rows).
> Old behaviour is still available through method Statement#['pg_row_count'].
>
> Both changes are in -current (as well as license change from GNU GPL to BSD).
>
> Regards,
>

Yahoo!  Thanks!  Now I can run our ruby apps with stock DBI...

Re: [Ruby-DBI-devel] Locking problem

[Matthew B. <ma...@by...>]

On Friday 05 July 2002 14:37, Michael Neumann wrote:
> Matthew Bloch wrote:
> > Hello;
> >
> > I've just noticed an undesirable interaction between multithreaded Ruby
> > applications and the MySQL C API (if not other DB APIs); my web spider
> > application was running along fine but suddenly after an unpredictable
> > number of minutes, hours etc. it would stop dead.  I think the problem is
> > that I use a "lock tables" statement which, if used with Ruby threads,
> > blocks the whole Ruby process until the table is unlocked.  Trouble is if
> > the 'thread' holding the lock is an internal Ruby thread in the same
> > process it'll never get run to unlock it :-/
> >
> > Is there a general way around this problem with the mysql_ API?  My
> > impression is no, since I can't find any way of telling mysql to use
> > non-blocking semantics through its C API.  Anyhow even if we leave it
> > like this it's probably worth adding a note about this pitfall to the
> > docs.
>
> If only your Ruby application accesses the Mysql database and you want
> to lock a table only for one thread, use a Mutex instead of locking the
> whole table. But if you lock a table because other processes (outside the
> Ruby world) should not modify the table while one Ruby thread modifies it,
> I see no other chance than using processes (start new Ruby interpreter)
> instead of threads.

I'm using multiple Ruby processes over multiple machines talking to one 
database, so the database really should be the co-ordination point for 
synchronization.  In the end this solution seemed to work quite nicely:

    private
    def lock_central
      begin
        # A bit of a bodge, but a necessary one since calling blocking Mysql
	# statements blocks *all* of our threads.
	#
	timeout(60) {
          backoff = 0.25
	  begin
            row = @dbi_lock.select_one("select get_lock('central', 1)")
	    sleep backoff
	    backoff = backoff < 5 ? backoff + rand : 0.25
	  end while row[0].to_i == 0
	}
	yield
      ensure
        @dbi_lock.select_one("select release_lock('central')")
      end
    end

Where @dbi_lock is a database connection separate from the one that accesses 
the data.  So for an overhead of an extra database connection per process and 
a slightly mucky spin lock, I think this is the best way of fixing the 
problem.

-- 
Matthew Bloch                 Bytemark Computer Consulting
                                http://www.bytemark.co.uk/
                                  tel. +44 (0) 8707 455026

Re: [Ruby-DBI-devel] Locking problem

[Michael N. <uu...@rz...>]

Matthew Bloch wrote:
> Hello;
> 
> I've just noticed an undesirable interaction between multithreaded Ruby 
> applications and the MySQL C API (if not other DB APIs); my web spider 
> application was running along fine but suddenly after an unpredictable number 
> of minutes, hours etc. it would stop dead.  I think the problem is that I use 
> a "lock tables" statement which, if used with Ruby threads, blocks the whole 
> Ruby process until the table is unlocked.  Trouble is if the 'thread' holding 
> the lock is an internal Ruby thread in the same process it'll never get run 
> to unlock it :-/
> 
> Is there a general way around this problem with the mysql_ API?  My 
> impression is no, since I can't find any way of telling mysql to use 
> non-blocking semantics through its C API.  Anyhow even if we leave it like 
> this it's probably worth adding a note about this pitfall to the docs.

If only your Ruby application accesses the Mysql database and you want
to lock a table only for one thread, use a Mutex instead of locking the
whole table. But if you lock a table because other processes (outside the Ruby world) 
should not modify the table while one Ruby thread modifies it, I see no other
chance than using processes (start new Ruby interpreter) instead of threads. 

Or have you tried to use an own connection (DBI.connect) for each thread?

Regards,

  Michael