George Weaver | 22 Jun 20:50 2016
Picon

Re: Multilevel inserts issue with ODBC


Hi Krishnanand,

I have done multi-level inserts (up to several thousand rows) from a VB.NET application using the psqlODBC driver - various versions - on XP, Win 7, and Win 10.

Hope this helps,
George

On 22/06/2016 4:20 AM, krishnanand.pandey <at> accenture.com wrote:
<!-- /* Font Definitions */ <at> font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;} <at> font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} <at> font-face {font-family:"Calibri Light"; panose-1:2 15 3 2 2 2 4 3 2 4;} <at> font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} <at> font-face {font-family:"Trebuchet MS"; panose-1:2 11 6 3 2 2 2 2 2 4;} <at> font-face {font-family:Consolas; panose-1:2 11 6 9 2 2 4 3 2 4;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;} a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;} p.MsoPlainText, li.MsoPlainText, div.MsoPlainText {mso-style-priority:99; mso-style-link:"Plain Text Char"; margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;} span.PlainTextChar {mso-style-name:"Plain Text Char"; mso-style-priority:99; mso-style-link:"Plain Text"; font-family:"Calibri",sans-serif;} .MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;} <at> page WordSection1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt;} div.WordSection1 {page:WordSection1;} -->

Hi Adrian

 

Same thing I am trying in a Windows XP 64bit machine from VB application it fails with the error (3137Missing semicolon (;) at end of SQL statement.).

And if the same multi-level insert statement from code executed from psql/pgadmin it works.

 

Could you please check the below statement from Windows odbc?

 

Thanks

 

 

Krishnanand Pandey


Newcastle Delivery Centre

UK Delivery Centre (Newcastle)                                                         

15A | 1 Quick Silver Way | Cobalt Business Park |Tyne and Wear| NE27 0QQ

* krishnanand.pandey <at> accenture.com

Confidential

 

This message is for the designated recipient only and may contain confidential, 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 this email by you is prohibited.

Communications with Accenture or any of its group companies (“Accenture Group”) including telephone calls and emails (including content), may be monitored by us for the purposes of security and the assessment of internal compliance with company policy. Accenture Group does not accept service by e-mail of court proceedings, other processes or formal notices of any kind.

Accenture means Accenture (UK) Limited (registered number 4757301), Accenture Services Limited (registered number 2633864), or Accenture HR Services Limited (registered number 3957974), all registered in England and Wales with registered addresses at 30 Fenchurch Street, London EC3M 3BD, as the case may be.

 

 

 

 

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver <at> aklaver.com]
Sent: 21 June 2016 21:57
To: Pandey, Krishnanand <krishnanand.pandey <at> accenture.com>; pgsql-odbc <at> postgresql.org
Subject: Re: [ODBC] Multilevel inserts issue with ODBC

 

On 06/21/2016 01:37 AM, krishnanand.pandey <at> accenture.com wrote:

> Hi Adrian

>

 

>

 

>

 

> We are seeing this error in the VB application logs where we have

> captured the error with the Err.Number and Err.Description in the code.

>

 

> Are there any limitations in the ODBC driver that it cannot handle

> multi-level inserts?

 

Not that I can see. Just got a chance to compile 09.05.0300 on my Linux machine. Going through the isql utility of unixODBC:

 

SQL> INSERT INTO odbc_test(id, fld_1) VALUES (1, 'one'), (2, 'two'), (3,

'three')

SQLRowCount returns 3

 

 

postgres <at> production=# select * from odbc_test ;

  id | fld_1

----+-------

   1 | one

   2 | two

   3 | three

(3 rows)

 

 

>

 

>

 

>

 

> Thanks

>

 

>

 

>

 

>

 

>

 

>

 

>

 

> /Krishnanand Pandey/

>

 

 

 

 

--

Adrian Klaver

adrian.klaver <at> aklaver.com



This message is for the designated recipient only and may contain privileged, proprietary, or otherwise confidential 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.
______________________________________________________________________________________

www.accenture.com

krishnanand.pandey | 20 Jun 10:32 2016
Picon

Multilevel inserts issue with ODBC

Hi Team

 

Our application is a Word VB front end with PostgreSQL 9.5 database.

We are communicating with PG via ODBC datasource with psqlodbc driver version psqlodbc_09_05_0300.

 

As a part of one of the performance issues we are trying to build a multi-level inserts.

 

i.e. insert statement similar to :

INSERT INTO films (code, title, did, date_prod, kind) VALUES

    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),

    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

 

 

But the multilevel insert on a table when executed from the application fails with below error:

 

3137 Missing semicolon (;) at end of SQL statement.

 

But the same insert statement when executed from pgadmin/psql executes with no error.

 

Looking at the error it looks like a syntax error in the insert statement but on our further investigations it seems like an issue with the odbc driver.

 

Could you please check and advice as a matter of urgency?

 

Many thanks

 

 

Krishnanand Pandey


Newcastle Delivery Centre

UK Delivery Centre (Newcastle)                                                         

15A | 1 Quick Silver Way | Cobalt Business Park |Tyne and Wear| NE27 0QQ

( 07425606856     

* krishnanand.pandey <at> accenture.com

Confidential

 

This message is for the designated recipient only and may contain confidential, 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 this email by you is prohibited.

Communications with Accenture or any of its group companies (“Accenture Group”) including telephone calls and emails (including content), may be monitored by us for the purposes of security and the assessment of internal compliance with company policy. Accenture Group does not accept service by e-mail of court proceedings, other processes or formal notices of any kind.

Accenture means Accenture (UK) Limited (registered number 4757301), Accenture Services Limited (registered number 2633864), or Accenture HR Services Limited (registered number 3957974), all registered in England and Wales with registered addresses at 30 Fenchurch Street, London EC3M 3BD, as the case may be.

 

 



This message is for the designated recipient only and may contain privileged, proprietary, or otherwise confidential 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.
______________________________________________________________________________________

www.accenture.com
Hiroshi Saito | 17 Jun 16:35 2016
Picon

psqlODBC 09.05.0300 Released

Hi, all.

We are release of psqlODBC 09.05.0300. this is a some bugfix release.
please see the notes at:
https://odbc.postgresql.org/docs/release.html

psqlODBC may be downloaded from in source, Windows Installer,
merge module, and basic zip file formats.

New Windows installer installs both 32-bit and 64-bit driver at once
on 64-bit OS. Both drivers may be needed on 64-bit OS because 32-bit
applications require 32-bit driver and 64-bit applications require
64-bit driver.

Please post any bug reports to the  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.

We are grateful to the help of many peoples. Thanks!

-- 
psqlODBC team.
email:   pgsql-odbc <at> postgresql.org
website: https://odbc.postgresql.org/

--

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

e.ratnakar.shetty | 16 Jun 18:21 2016
Picon

Issue with Save and Release points

Hi,

 

We are using psqlodbc_09_05_0100-x86 driver to connect to Postgres 9.4 version through a VB app. When we try running continuous inserts from the application, the performance is really slow (18 minutes for upto a lakh records). On monitoring the logs, we found that there are huge number of save release point executions happening.

 

duration: 0.000 ms

2016-06-16 17:06:40 BST LOG:  statement: RELEASE _EXEC_SVP_1B107E

2016-06-16 17:06:40 BST LOG:  duration: 0.000 ms

2016-06-16 17:06:40 BST LOG:  statement: SAVEPOINT _EXEC_SVP_1B1082F8

2016-06-16 17:06:40 BST LOG:  duration: 0.000 ms

 

We tried switching this off by setting the “level of Rollback on error” to Nop and also through the connection string in the application but the logs still show these executions. Any pointers as to what might be causing this issue?

 

Many Thanks

 

Regards,

Eisha Shetty

ACCENTURE | UK-NEWCASTLE

( +44 7741587433

* e.ratnakar.shetty <at> accenture.com

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.

 

 

 

Michael Paquier | 16 Jun 14:51 2016
Picon

Re: [PATCH] Remove unncessary localtime() calls during data type conversion

On Fri, Apr 29, 2016 at 9:15 AM, Michael Paquier
<michael.paquier <at> gmail.com> wrote:
> Thanks! If you need any help regarding those things, just don't
> hesitate to ping me. I can easily allocate even company time for this
> stuff.

I am really late at the party here... My two patches has been
committed as 34cdbd1f and c2794dbb. Thanks Inoue-san for pushing them!
-- 
Michael

--

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

ljwilson | 13 Jun 22:14 2016

psqlodbc 09.05.0210 commit "Don't do premature execution." Numeric Filter issue?

I've been using in production psqlodbc 09.02.0100 (unicode, 32-bit)  for the
last three years. A couple of weeks ago after upgrading a client's server to
the latest PostgreSQL 9.5.2 (32-bit) I thought I would test the latest
psqlodbc 09.05.0210 (unicode, 32-bit) on one of their machines. I hadn't
found any issues with it in my testing, but of course the client found one
straight away...

These are all Windows machines. And I've tested with PostgreSQL 9.4.6
(32-bit) too--no difference.

The issue is when exporting records. Where the older driver 09.02.0100 would
get two records with the given filter, the new driver 09.05.0210 would get
all the records (4419). Nothing different code or data-wise; just switching
the driver version made the difference.

So I went back to the git repository and by dividing and conquering
(download a version, compile, install, test) determined the exact commit
that changed the behavior:

"Don't do premature
execution."
https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=e5e048509bada06c61f05901904ddbdda070bd10
<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=e5e048509bada06c61f05901904ddbdda070bd10>

For testing, I took the latest from git
(
https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=f7d5623f538cb94c8e717e3447ab8be93b9cecfb
<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=f7d5623f538cb94c8e717e3447ab8be93b9cecfb> 
), and reverted from it the above commit ("Don't do premature execution.").
That made it easy to test, since doing that put back in the option to
allow/disable premature execution. By leaving "Disable Premature Execution"
unchecked all worked as before.

But note I would have the same issue with older drivers when I went back to
test with them by simply checking the "Disable Premature Execution" box.

I'm hoping someone can examine the two mylogs and see what the problem is. I
suspect a precision issue of some kind, since the filter to export the
records uses datetime columns expressed as a 12-digit pdecimal(12,0) on the
client side, which is stored as numeric(14,0) on the PostgreSQL side. I
tried changing it to numeric(12,0) on the PostgreSQL side, and decimal(12,0)
on the client side too, but no difference.

Compressed the two mylogs are almost 31MB--I can put them somewhere for
download. Each is created by doing a client session to export the records.
The version of the psqlodbc driver used to generate the mylogs is in the
filename.

mylog_psqlodbc_09_02_0100.log exports the two records (using the old
premature execution enabled by default)
mylog_psqlodbc_09_05_0210.log exports 4419 records instead of the expected 2
(premature execution no longer an option)

Thanks...jack

--
View this message in context: http://postgresql.nabble.com/psqlodbc-09-05-0210-commit-Don-t-do-premature-execution-Numeric-Filter-issue-tp5907625.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

PSequel Support | 6 Jun 08:26 2016

Escaping issues with queries using the `?` operator

Given a query like this:

SELECT t.val 
FROM (VALUES ('{"a": 1}'::jsonb), ('{"b": 2}'::jsonb)) t (val) 
WHERE t.val ? 'b';

I get this error:
> the # of binded parameters < the # of parameter markers
Cindy Taylor | 27 May 01:42 2016

ODBC connections

I’m setting up an ODBC connection from server A to server B.  When I click test, I get an error, screen shot below.

 

Using PostGre SQL 9.5, ODBC driver was 9.5.210 x64.  I believe I Need to edit the postgresql.conf file to include the tcpip connections, but I’m not finding the exact syntax to use.  It looks like I need to have –i somewhere in the conf file?

 

 

Cindy Taylor | MOSS ADAMS LLP
Database Administrator

999 Third Avenue, Suite 3300
Seattle, WA 98104
D (206) 302-6350 | C (425) 213-4633

 

<!-- /* Font Definitions */ <at> font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} <at> font-face {font-family:Georgia; panose-1:2 4 5 2 5 4 5 2 3 3;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri","sans-serif";} .MsoChpDefault {font-family:"Calibri","sans-serif";} .MsoPapDefault {margin-bottom:10.0pt; line-height:115%;} <at> page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.WordSection1 {page:WordSection1;} -->


CONFIDENTIALITY NOTICE
This e-mail and any attachments are for the sole use of the intended recipients and contain information that may be confidential or legally privileged. If you have received this e-mail in error, please notify the sender by reply e-mail and delete the message. Any disclosure, copying, distribution, or use of this communication by someone other than the intended recipient is prohibited.

 

Tsunakawa, Takayuki | 26 May 10:27 2016

Re: ADO CommandTimeout vs Postgres statement_timeout parameter

Hello, Richard, Inoue-san,

 

Richard, please post to pgsql-odbc ML as much as possible, because I’d like Inoue-san to have a look.  He is currently the only committer of psqlODBC, and I’m still an apprentice.

 

I implemented SQL_QUERY_TIMEOUT, which seems to work.  Please find the attached patch.

 

Inoue-san,

At first, I used CC_send_settings() to execute “SET statement_timeout” and refactored it.  After that, I realized I can use CC_send_query() and used it instead.  However, I’d like the refactored code to be applied too.

 

 

Regards

Takayuki Tsunakawa

 

From: Richard Hetherington [mailto:hetheringtonrichard <at> hotmail.com]
Sent: Friday, May 20, 2016 7:09 PM
To: Tsunakawa, Takayuki/
綱川 貴之
Subject: RE: [ODBC] ADO CommandTimeout vs Postgres statement_timeout parameter

 

Many thanks for you advice. I realised after receiving your email that I had made a fundamental error - I was logging from a website to my desktop folder which clearly isn't going to be writable by a Web Identity Account - oops.

Attached are 2 log files which I have cleaned up to remove all UID, PWD, Server values etc.

You should see that I set the SQL_QUERY_TIMEOUT via the ADO CommandTimeout = 60 seconds

This value has been logged but has no effect on the long running query.
[7212-0.181]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 60

I hope this is useful.

If you need anything esle, let me know.

Many thanks and have a good weekend,

RICHARD

From: tsunakawa.takay <at> jp.fujitsu.com
To: hetheringtonrichard <at> hotmail.com
Subject: RE: [ODBC] ADO CommandTimeout vs Postgres statement_timeout parameter
Date: Fri, 20 May 2016 02:39:30 +0000

Hello,

 

If SQL_QUERY_TIMEOUT is set on the connection or statement, mylog_xxx.log will have entries like this:

 

[140168204601088]PGAPI_SetConnectAttr for 0x8d5360: 0 0xa

[140168204601088]PGAPI_SetConnectOption: entering fOption = 0 vParam = 10

[140168204601088]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 10

...

[140168204601088][[SQLSetStmtAttr]] Handle=0x8e2110 0,10

[140168204601088]PGAPI_SetStmtAttr Handle=0x8e2110 0,10(0xa)

[140168204601088]PGAPI_SetStmtOption: entering...

[140168204601088]SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 10

 

I'm not sure about the cause of your logging failure, but perhaps you will be able to log to another directory by creating the following registry value:

 

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<your ODBC driver name>\Logdir

type: REG_SZ

value: directory path

 

Regards

Takayuki Tsunakawa

 

Attachment (query_timeout.patch): application/octet-stream, 14 KiB

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Tsunakawa, Takayuki | 26 May 04:40 2016

[patch] fix buffer overrun in logging, and code cleanup

Hello,

While I was troubleshooting some user problem and trying to figure out why mylog_xxx.log is not created, I
found a tiny bug.  The buffers for holding the full path for MyLog and CommLog are small -- only 80 bytes.  If
you specify a long path for Logdir, the buffer overrun occurs.  Please find the attached patch mylog_buffer_overrun.patch.

The other patch mylog_cleanup.patch removes code which are unused or unnecessary anymore.  I'd like to
make the code as lean as possible, so that it makes code reading and searching easier and faster.  This patch
removes about 120 lines.

Regards
Takayuki Tsunakawa

Attachment (mylog_buffer_overrun.patch): application/octet-stream, 859 bytes
Attachment (mylog_cleanup.patch): application/octet-stream, 8 KiB

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Parson, Damien | 20 May 22:20 2016
Picon

Dead openSSL link

Hello,

On the guide here https://odbc.postgresql.org/docs/win32-compilation.html the openSSL link is no longer valid do you have any other places I can download it from? or any other guides for the windows installer?

Damien Parson 
SysAdmin
SDSC IT Systems
(858) 534-9638


Gmane