Обсуждение: 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@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
Вложения
Hi Team
Please let me know if any further information on this is required?
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@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.
From: Pandey, Krishnanand
Sent: 20 June 2016 09:33
To: 'pgsql-odbc@postgresql.org' <pgsql-odbc@postgresql.org>
Subject: 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@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
Вложения
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?
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@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@aklaver.com]
Sent: 20 June 2016 22:25
To: Pandey, Krishnanand <krishnanand.pandey@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Multilevel inserts issue with ODBC
On 06/20/2016 01:32 AM, krishnanand.pandey@accenture.com wrote:
> 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:
>
>
In what applications log are you seeing the below?:
>
> 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/
>
> cid:image001.png@01D05501.1577B7C0
> Newcastle Delivery Centre **
>
> UK Delivery Centre
> (Newcastle)
>
> 15A | 1 Quick Silver Way | Cobalt Business Park |Tyne and Wear| NE27
> 0QQ
>
> ( 07425606856
>
> * krishnanand.pandey@accenture.com
> <mailto:krishnanand.pandey@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.
> ______________________________________________________________________
> ________________
>
--
Adrian Klaver
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
Вложения
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@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@aklaver.com]
Sent: 21 June 2016 21:57
To: Pandey, Krishnanand <krishnanand.pandey@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Multilevel inserts issue with ODBC
On 06/21/2016 01:37 AM, krishnanand.pandey@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@production=# select * from odbc_test ;
id | fld_1
----+-------
1 | one
2 | two
3 | three
(3 rows)
>
>
>
> Thanks
>
>
>
>
>
>
>
> /Krishnanand Pandey/
>
--
Adrian Klaver
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
Вложения
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
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 CentreUK Delivery Centre (Newcastle)
15A | 1 Quick Silver Way | Cobalt Business Park |Tyne and Wear| NE27 0QQ
* krishnanand.pandey@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@aklaver.com]
Sent: 21 June 2016 21:57
To: Pandey, Krishnanand <krishnanand.pandey@accenture.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Multilevel inserts issue with ODBC
On 06/21/2016 01:37 AM, krishnanand.pandey@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@production=# select * from odbc_test ;
id | fld_1
----+-------
1 | one
2 | two
3 | three
(3 rows)
>
>
>
> Thanks
>
>
>
>
>
>
>
> /Krishnanand Pandey/
>
--
Adrian Klaver
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
