Re: ERROR with "Update ... where Current of"

This is the solution what i have found for my postioned update problem
It is implemented in Pascal/Delphi. To keep it short(er) i ommitted some
code (Error handling, setting String-length for returned data, type Info.
OK, "Update ... where Current of"  would have been more elegant, but the
mean thing is: it works.

Thanks goes to Mr. Inoue for the hint, that SQLSetStmtAttr(...
SQL_CONCUR_ROWVER ...) is needed.

----------------------- snip ---------------

Var aRes:Integer;
    hStmtSelect,hstmtUpdate,fEnvHandle,fConnectHandle:SQLHandle;
    szName:ShortString;
    cbName:SQLInteger;
    aScroll,aSQLSmallInt:SQLSmallInt;
    aConnectString:String;
    aRow:Cardinal;

// -UpdatableCursors=1/0 makes no difference
// -using SQL_CURSOR_Static : Update works. DB2 Says NO.

Begin

 fEnvHandle := 0;
 fConnectHandle := 0;

 aRes := SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, fEnvHandle);
 aRes := SQLAllocHandle(SQL_HANDLE_DBC, fEnvHandle, fConnectHandle);

RowCount && UseDeclareFetch Performance

I am trying to optimize performance on larger selects sets:

If i connect with *...UseDeclareFetch=0...* SQLGetDiagField(...
SQL_DIAG_CURSOR_ROW_COUNT...) or SQLRowCount deliver the number of the rows
which where found by the SELECT. Reading rowcount is needed by my ODBC
wrapper. 

BUT: The SELECT needs MUCH more time(vs.UseDeclareFetch=1) and a lot of
memory(~100MByte) is eaten up until the cursor ist closed. So far as i have
understand, the result set is read by the client, which may also a problem
on slow connections to the server. Therefore the amount of cached rows
should be controlled by *Fetch=XXX* assigned to the connection string. But
whatever i assigned to "Fetch", the return time for the SELECT and memory
usage is nearly the same.

Now i have tried *...UseDeclareFetch=1...*. The return time is MUCH better. 

BUT: Now SQLRowCount returns -1. 

One idea: Executing Select ...,(Select count(*) where {MyConditions}) as
__ROWCOUNT where {MyConditions}. I suppose that would double the time on a
complicate evaluation.
Any ideas how to get the RowCount? 
Or to optimize Selects with UseDeclareFetch=0.

I'm quite a newbie to PostgreSQL and i am nearly sure that i have overlook
something.

Any help would be greatly welcome!                         

Re: RowCount && UseDeclareFetch Performance

I recently went through same issue. Here are my observations.

When "UseDeclareFetch=0" used and you are dealing with large result
sets, then memory usage of driver goes way up and contributes to
slowness. The response back to client application happens after a long
time. The reason for this is, the driver fetches all the rows in the
resultset to client host machine before returning a single row to the
calling application. i.e. the driver is either storing the results in
memory or in some kind of temp file until it gathers all the results.

When "UseDeclareFetch=1" and "Fetch=xxx" defined, then it uses postgres
cursor for queries, then fetches "xxx" result rows at time, and returns
the results to the calling application. Thus the client application gets
initial set of results faster. So, here since the driver is fetching
incrementally it does not know the row count.

I used "UseDeclareFetch=1" with prepare statement to get to the some of
the resultset metadata, then re-executed with non-prepared to make use
of the cursors to fetch the data. Use "Fetch=10000" or some thing large
so that you are not making too many round trips for data. Also make sure
there is absolutely no debug/trace logs turned on.

Hope this helps.

Ramesh..

On Tue, 2011-07-05 at 09:44 -0700, BGoebel wrote:
> I am trying to optimize performance on larger selects sets:
> 
> If i connect with *...UseDeclareFetch=0...* SQLGetDiagField(...

Re: RowCount && UseDeclareFetch Performance

Many thanks for your message + help.

I have made some tests, which exactly confirm your information. Seeing
things a bit clearer now. The prepared statement you suggested not has made
a big difference, but i will have that in mind.

Conclusion: To limit memory usage, execution time and bandwidth with large
result sets (Columns X Rows) i HAVE to use "UseDeclareFetch=1" and
"Fetch=xxx".  
"Fetch=xxx" with  "UseDeclareFetch=0" does not help.

I still hope there is a way to get RowCount without ReConnecting,
ReSelecting and halve the performance.

regards

--
View this message in context: http://postgresql.1045698.n5.nabble.com/RowCount-UseDeclareFetch-Performance-tp4553904p4554370.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

psqlODBC 09.00.0310 Released

We are pleased to announce the release of psqlODBC 09.00.0310.

Changes and updates in the new release include:

1.) Let SC_forget_unnamed() clear the result in case the statement is
not executed yet. It would fix the bug reported by Silvio Brandani.
2.) Fix the bug that strings are copied to a null pointer.
3.) Don't clear the parsed plan in SC_recycle_statement().
4.) Now SQLDescribeCol() can detect the changes of column type or size.
5.) Handle *with or without oids* correctly.
6.) Take the environment variable into account.
7.) Use SOCK_get_next_n_bytes() instead of SOCK_get_next_byte().
8.) Add some driver specific options for SQLSetConnectAttr().
9.) Fix compilation errors in case ODBC 2.5.
10.) Fix compilation error on different ODBCVER.
11.) Correct the behavior of SQLSetConnectAttr() for the driver specific
options.

psqlODBC may be downloaded from
http://www.postgresql.org/ftp/odbc/versions/ in source, Windows
Installer, merge module, and basic zip file formats.

Please post any bug reports to the pgsql-odbc <at> postgresql.org mailing list.

I'd like to take this opportunity to thank all those involved with the
development, testing and bug fixing of the updated driver.

--
psqlODBC team.

Compiling 09.00.0300 on OS X Snow Leopard

Re: Compiling 09.00.0300 on OS X Snow Leopard

Hi Brian-san.

Please try the following,

1. relation is investigated by ldd.
ldd /usr/local/lib/psqlodbcw.so

When it looks normal.
2. please build the 09.00.0310
It is correcting a little uneasy portion.

although verification is impossible by me. sorry, I don't have MAC 
machine. However, More information may be able to obtain someone's 
suggestion.

Regards,
Hiroshi Saito

(2011/07/09 6:01), Brian Panulla wrote:
> I'm trying to use psqlODBC on OS X 10.6. I compiled from source
> with configure arguments
>
>    --with-iodbc --with-libpq=/opt/local/lib/postgresql90
>
> I installed PG9 via MacPorts.
>
> Using the iODBC driver test script gives me the errors:
>
>    1: SQLDriverConnect = [iODBC][Driver
> Manager]dlopen(/usr/local/lib/psqlodbcw.so, 6): no suitable image found.
>   Did find:
> /usr/local/lib/psqlodbcw.so: mach-o, but wrong architecture (0)
> SQLSTATE=00000
> 2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not
> be loaded (0) SQLSTATE=IM003
>
> The Makefile appears to have correctly determined my architecture
> (x86_64-apple-darwin10.8.0).
>
> Any sugestions? Is there a better way to get the ODBC driver for OS X?
> It didn't appear to be included with any of the various packages in
> MacPorts.
>
> -B

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: psqlODBC 09.00.0310 and oids

On Friday 08 July 2011 21:08:21 Hiroshi Saito wrote:
> Changes and updates in the new release include:
> 5.) Handle *with or without oids* correctly.

Thank you very much.
However, the behaviour is still no exactly what I expected:

I have a legacy excel 97 database, poorly designed, with about 100 tables, 1000 queries, complex forms...
You get the picture.
I used a recent mdbtools snapshot from git to convert the database to postgres (index, default values,
constaints, this rocks!).
However, I had a lot of tables without a primary keys.
What I did is adding oids for these tables, and created a primary key (on oid).

Now, I still have some legacy code from access I'd like to use.
I changed the access tables to psql linked tables, and I'm opening the connection with that:
ODBC;CA=r;A6=;A7=100;A8=4096;B0=255;B1=8190;B9=0;BI=0;C2=dd_;CX=1b102bb;A1=8.4;DSN=india;SERVER=10.10.10.146;DATABASE=india;PORT=5432;UID=jvourger;PASSWORD=xxxxxxxx
(This is CX with BIT_SHOWOIDCOLUMN)
But when open these tables with a primary key on oid, I don't see the oid column and I can't append data.

I also tried CX=1d102bb (BIT_FAKEOIDINDEX). Same results.

Does anyone has any idea about what is going on?
Is that an excel problem? Or is there some change I could make in psqlodc to have these linked tables work with
legacy access modules?

--

-- 
- - Nirgal Vourgère ☮ GP France ☮ tel:+33.180969724
-   xmpp:nirgal <at> nirgal.com ☮ gpg 0x4760b41db292ab13 ☮ skype:nirgal_v
    nirgal.com:qotd ☮ Be the change you want to see in the world -- Gandhi

Re: psqlODBC 09.00.0310 and oids

Hi Nirgal-san.

Sorry very late reaction.

Umm, I can't explore your problem....
Office 97 is very old which does not reach it for me.
although I use 2003, I don't look at a problem.
For example,
http://winpg.jp/~saito/psqlODBC/check/FakeIndex.png

Regards,
Hiroshi Saito

(2011/07/12 18:26), Nirgal Vourgère wrote:
> On Friday 08 July 2011 21:08:21 Hiroshi Saito wrote:
>> Changes and updates in the new release include:
>> 5.) Handle *with or without oids* correctly.
>
> Thank you very much.
> However, the behaviour is still no exactly what I expected:
>
> I have a legacy excel 97 database, poorly designed, with about 100 tables, 1000 queries, complex forms...
You get the picture.
> I used a recent mdbtools snapshot from git to convert the database to postgres (index, default values,
constaints, this rocks!).
> However, I had a lot of tables without a primary keys.
> What I did is adding oids for these tables, and created a primary key (on oid).
>
> Now, I still have some legacy code from access I'd like to use.
> I changed the access tables to psql linked tables, and I'm opening the connection with that:
> ODBC;CA=r;A6=;A7=100;A8=4096;B0=255;B1=8190;B9=0;BI=0;C2=dd_;CX=1b102bb;A1=8.4;DSN=india;SERVER=10.10.10.146;DATABASE=india;PORT=5432;UID=jvourger;PASSWORD=xxxxxxxx
> (This is CX with BIT_SHOWOIDCOLUMN)
> But when open these tables with a primary key on oid, I don't see the oid column and I can't append data.
>
> I also tried CX=1d102bb (BIT_FAKEOIDINDEX). Same results.
>
> Does anyone has any idea about what is going on?
> Is that an excel problem? Or is there some change I could make in psqlodc to have these linked tables work
with legacy access modules?
>

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Compiling 09.00.0300 on OS X Snow Leopard