Thread: faq?

Hi,
I can't seem to find the faq. I ask this because after having seen our postgres/pgsql-odbc project nearly fail (the boss even suggested we convert to access!!!) because I failed to realise that for big tables not using the option "Use Declare/Fetch" meant pgsql-odbc would bring our server and network to a standstill. That should certainly be in the FAQ!
BTW, could someone tell me why that is not a good option to have enabled by default? I assume that most dbs have at least a couple of tables where there are lots of rows, and seeing as a lot of odbc (vb6 anyway) users probably do inserts the way we do (ie, not via execute, but by opening a table and then doing AddNew) it might be something to look at... anyway, the boss told us to go home early seeing as I turned our project from a dog back into something decent!
Cheers
Antoine

--
This is where I should put some witty comment.

> I can't seem to find the faq. I ask this because after having seen our
> postgres/pgsql-odbc project nearly fail (the boss even suggested we convert
> to access!!!) because I failed to realise that for big tables not using the
> option "Use Declare/Fetch" meant pgsql-odbc would bring our server and
> network to a standstill. That should certainly be in the FAQ!

I agree with you. But we don't have even enough time for development.
Could you maintain the documentation? Or is here someone who could
maintain the documentation?
There is config.html in CVS (I hope Dave put it into releases too)
so you could study from it.

> BTW, could someone tell me why that is not a good option to have enabled by
> default? I assume that most dbs have at least a couple of tables where there
> are lots of rows, and seeing as a lot of odbc (vb6 anyway) users probably do

It was broken some time ago. And no every user need it. When you access
whole table it doesn't matter if you use "use declare/fetch" or not.
Then you have turned on "use declare/fetch" you don't know number
of returned rows until you reach end of result.
It isn't so clear to enable it by default.

> inserts the way we do (ie, not via execute, but by opening a table and then
> doing AddNew) it might be something to look at... anyway, the boss told us

Hey, does I miss something? There is no support for updateable cursors
in 08.01 psqlodbc driver. And it is marked as experimental feature in
07.03 version.

Regards,

Luf

On 27/1/06 22:03, "Ludek Finstrle" <luf@pzkagis.cz> wrote:

>> I can't seem to find the faq. I ask this because after having seen our
>> postgres/pgsql-odbc project nearly fail (the boss even suggested we convert
>> to access!!!) because I failed to realise that for big tables not using the
>> option "Use Declare/Fetch" meant pgsql-odbc would bring our server and
>> network to a standstill. That should certainly be in the FAQ!
>
> I agree with you. But we don't have even enough time for development.
> Could you maintain the documentation? Or is here someone who could
> maintain the documentation?
> There is config.html in CVS (I hope Dave put it into releases too)
> so you could study from it.

The FAQ is included in the documentation that ships in the source tarball
and the Windows Installer packages. It can also be found in CVS at:

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/psqlodbc/psqlodbc/docs/faq.html?
rev=1.3&content-type=text/x-cvsweb-markup

As for adding the question above to it, it's not really a candidate for
inclusion as its not a frequently asked question. As Luf points out though,
it is already documented in config.html which is also included with the
source and Windows distributions.

>> inserts the way we do (ie, not via execute, but by opening a table and then
>> doing AddNew) it might be something to look at... anyway, the boss told us
>
> Hey, does I miss something? There is no support for updateable cursors
> in 08.01 psqlodbc driver. And it is marked as experimental feature in
> 07.03 version.

You don't need updateable cursors or declare/fetch to use AddNew in ADO/VB6.
When you create your recordset, restrict it to relevant records only by
using SQL. Avoid creating recordsets straight from tables - that is very
much an Access'ism, which whilst it might work well there with a single
user, or small multi-user database, it definitely does not work well with
much bigger data sets on large client/server databases. If all you are doing
is adding new records, then you might even be able to add a 'WHERE 1=2'
clause to your query to create an empty recordset of the right structure
which you can then fill up to your hearts content.

Regards, Dave.

Fri, Jan 27, 2006 at 10:34:12PM +0000, Dave Page napsal(a):
> On 27/1/06 22:03, "Ludek Finstrle" <luf@pzkagis.cz> wrote:
> >> inserts the way we do (ie, not via execute, but by opening a table and then
> >> doing AddNew) it might be something to look at... anyway, the boss told us
> >
> > Hey, does I miss something? There is no support for updateable cursors
> > in 08.01 psqlodbc driver. And it is marked as experimental feature in
> > 07.03 version.
>
> You don't need updateable cursors or declare/fetch to use AddNew in ADO/VB6.

Hmm. I don't know ADO/VB6->ODBC enough :-( I have to stop answer such
questions.

> much bigger data sets on large client/server databases. If all you are doing
> is adding new records, then you might even be able to add a 'WHERE 1=2'

It could be faster to use LIMIT on pgsql. But WHERE 1=2 is more
universal across different RDBMS.

Regards,

Luf