Re: Out of memory error on huge resultset

Barry Lind <barry(at)xythos(dot)com> writes:

> Nic Ferrier wrote:
>
> > So I'm simply transforming querys from:
> >
> >
> > SELECT x FROM y WHERE z;
> >
> >
> > into
> >
> > DECLARE jdbcXX CURSOR FOR $query ;
> > FETCH FORWARD $fetchSize jdbcXX;
> >
>
> And when $query is: "insert into foo values (...); select * from bar;"
>
> You will get:
> DECLARE jdbcXX CURSOR FOR insert into foo values (...);
> select * from bar;
> FETCH FORWARD $fetchSize jdbcXX;
>
> Which clearly isn't what you want.

That's a really good point Barry. I never do that so it's not
something I'd considered.

Isn't a solution to only do the to_cursor translation when the
statement given begins with "SELECT "?

I agree that it's far from perfect, but it will do for _most_ cases
won't it?

Interestingly, I don't think what you've speced above is even
possible with some databases is it? Another advantage of using
PostgreSQL is that you must properly terminate your SQL statements,
unlike with some commercial databases I could mention.

Nic