Re: Issue with Save and Release points

Thanks Hiroshi,

Is there anywhere I can do some reading up about the SQLStatistics() function and how to cache its results so that ODBC doesn’t need to run these repeatedly for each statement? Will I be able to call SQLStatistics() from VBA?

On a separate note – another way to tackle this might be to issue bulk insert statements but my approach so far has been to issue bulk statements using the postgres syntax from vba

i.e. to populate a string with an insert statement like

INSERT INTO tblA(field1, field2) VALUES
(1, 999),
(2, 888);

And to issue via the driver using following:

ThisDB.Execute strSQL, dbFailOnError

ThisDB is created using :

Set ThisDB = OpenDatabase("", False, False, DBName)

But I keep getting a syntax error for missing ; even though this exact query works on pgAdmin

Regards
Dan

________________________________
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

From: Inoue, Hiroshi [mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp]
Sent: 21 June 2016 00:25
To: Machet, Daniel <daniel(dot)machet(at)accenture(dot)com>
Cc: tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com; Ratnakar Shetty, E. <e(dot)ratnakar(dot)shetty(at)accenture(dot)com>; pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Issue with Save and Release points

Hi Daniel,

Unfortunately it needs an implementation change.
If you hope, I would try to cache the result of SQLStatistics().

regards,
Hiroshi Inoue
On 2016/06/20 22:58, daniel(dot)machet(at)accenture(dot)com<mailto:daniel(dot)machet(at)accenture(dot)com> wrote:
Hi Hiroshi,

This is repeated about 2 times for each INSERT statement, how would I go about changing the driver to cache the result? (I tried switching on connection pooling for the Postgres Unicode driver with a 60 second timeout but those queries didn’t reduce).

Regards
Dan

________________________________
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

From: Inoue, Hiroshi [mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp]
Sent: 20 June 2016 14:33
To: Machet, Daniel <daniel(dot)machet(at)accenture(dot)com><mailto:daniel(dot)machet(at)accenture(dot)com>
Cc: tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com<mailto:tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>; Ratnakar Shetty, E. <e(dot)ratnakar(dot)shetty(at)accenture(dot)com><mailto:e(dot)ratnakar(dot)shetty(at)accenture(dot)com>; pgsql-odbc(at)postgresql(dot)org<mailto:pgsql-odbc(at)postgresql(dot)org>
Subject: Re: [ODBC] Issue with Save and Release points

Hi Daniel,

Looks like the log is a part of SQLStatistics call.
If SQLStatistics calls for a table are repeated, maybe the driver had better cache the result.

regards,
Hiroshi Inoue
On 2016/06/20 17:38, daniel(dot)machet(at)accenture(dot)com<mailto:daniel(dot)machet(at)accenture(dot)com> wrote:
Hi Hiroshi,

Please find attached the mylog from when the job runs (have only switched on for 1 run and then switched off due to the time cost from this)

Regards
Dan

________________________________
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.

From: Inoue, Hiroshi [mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp]
Sent: 17 June 2016 23:55
To: Machet, Daniel <daniel(dot)machet(at)accenture(dot)com><mailto:daniel(dot)machet(at)accenture(dot)com>
Cc: tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com<mailto:tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>; Ratnakar Shetty, E. <e(dot)ratnakar(dot)shetty(at)accenture(dot)com><mailto:e(dot)ratnakar(dot)shetty(at)accenture(dot)com>; pgsql-odbc(at)postgresql(dot)org<mailto:pgsql-odbc(at)postgresql(dot)org>
Subject: Re: [ODBC] Issue with Save and Release points

Hi Daniel,

Doesn't your connection string contain 'Protocol=7.4-2' option?
If so, please change it to 'Protocol=7.4-1'.

regards,
Hiroshi Inoue
On 2016/06/17 23:28, daniel(dot)machet(at)accenture(dot)com<mailto:daniel(dot)machet(at)accenture(dot)com> wrote:
Thanks Takayuki,

We’ve tried with Transaction but are still seeing save points and release points in the postgres logs. Also on executing the same query on localhost (from my laptop) am getting slow performance (around 4 records per second).

We don’t see same performance drawback on inserting directly on the client

Regards
Daniel Machet
________________________________
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the e-mail by you is prohibited. Where allowed by local law, electronic communications with Accenture and its affiliates, including e-mail and instant messaging (including content), may be scanned by our systems for the purposes of information security and assessment of internal compliance with Accenture policy.

Accenture means Accenture (UK) Limited (registered number 4757301), registered in England and Wales with registered address at 30 Fenchurch Street, London EC3M 3BD.