Thread: [psqlODBC 09.05.0100] could not insert SQLINTEGER to bytea column
Hi, First of all, many thanks for releasing version of 09.05.021 Which does not necessary to install VC++ 2013 runtime environment. So, we are trying to use version of 09.05.021 now, but have faced with another problem, could you confirm the belows for me? [problem] In 09.03.0400 we have no problem when insert into PostgreSQL bytea column with SQLINTEGER data type. But in 09.05.0210 it was returned with below error: ERROR: column "id" is of type bytea but expression is of type integer; [confirmation] (1) It is seem come from the below release, is that right? ---psqlODBC 09.05.0100 Release--- 10. Fix quoting bugs in sending integer query parameters to server The drivers used to assume that if a parameter's SQL type is SQL_INTEGER or SQL_SMALLINT, the value does not require quoting when its send to the server. For example, "SELECT ?", with parameter 123 was translated to "SELECT 123", when UseServerSidePrepare was not enabled. However, there was no check that the query parameter in fact contained a valid integer, when replacing the parameter markers with their values. Also, in a query like "SELECT 0-?", a negative value needs to have parens around it, as in "SELECT 0-(-123)". --- (2) Does the above release was made for below commit? http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=1ccd04c70b8037b268eefcaae09ce131c8c33b59 (3) But we found exactly the below commit made obove ERROR. As the release note, does this commit completely did the thing which not yet done in commit in (2) sir? http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=085e9324eb5a592f3b73f594311819cbf8dc5f2d Thanks and best regards, Dang Minh Huong NEC Solution Innovators, Ltd. http://www.nec-solutioninnovators.co.jp/en/
Hi Huong, Unfortunately I don't know details about commits referenced. However I'm suspicious if calling sqltype_to_bind_pgtype() is rather dangerous. regards, Hiroshi Inoue On 2016/04/25 15:50, Huong Dangminh wrote: > Hi, > > First of all, many thanks for releasing version of 09.05.021 > Which does not necessary to install VC++ 2013 runtime environment. > > So, we are trying to use version of 09.05.021 now, but have faced > with another problem, could you confirm the belows for me? > > [problem] > In 09.03.0400 we have no problem when insert into PostgreSQL bytea column with SQLINTEGER data type. > But in 09.05.0210 it was returned with below error: > > ERROR: column "id" is of type bytea but expression is of type integer; > > [confirmation] > (1) It is seem come from the below release, is that right? > > ---psqlODBC 09.05.0100 Release--- > 10. Fix quoting bugs in sending integer query parameters to server > The drivers used to assume that if a parameter's SQL type is SQL_INTEGER or SQL_SMALLINT, > the value does not require quoting when its send to the server. For example, "SELECT ?", > with parameter 123 was translated to "SELECT 123", when UseServerSidePrepare was not enabled. > However, there was no check that the query parameter in fact contained a valid integer, > when replacing the parameter markers with their values. Also, in a query like "SELECT 0-?", > a negative value needs to have parens around it, as in "SELECT 0-(-123)". > --- > > (2) Does the above release was made for below commit? > > http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=1ccd04c70b8037b268eefcaae09ce131c8c33b59 > > (3) But we found exactly the below commit made obove ERROR. > As the release note, does this commit completely did the thing which not yet done in commit in (2) sir? > > http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=085e9324eb5a592f3b73f594311819cbf8dc5f2d > > Thanks and best regards, > Dang Minh Huong > NEC Solution Innovators, Ltd. > http://www.nec-solutioninnovators.co.jp/en/
Hi Inoue-san, Thanks for your prompt response sir. > Hi Huong, > > Unfortunately I don't know details about commits referenced. I have confirmed that, sqltype_to_bind_pgtype() came to be called due to commit in (3). > However I'm suspicious if calling sqltype_to_bind_pgtype() is rather > dangerous. > For safety, we intend to change SQLINTEGER to a kind of text data type in APP or change the bytea to integer or a kind of text data type in PostgreSQL if possible. Thanks and best regards, Dang Minh Huong NEC Solution Innovators, Ltd. http://www.nec-solutioninnovators.co.jp/en/ > regards, > Hiroshi Inoue > > On 2016/04/25 15:50, Huong Dangminh wrote: > > Hi, > > > > First of all, many thanks for releasing version of 09.05.021 > > Which does not necessary to install VC++ 2013 runtime environment. > > > > So, we are trying to use version of 09.05.021 now, but have faced > > with another problem, could you confirm the belows for me? > > > > [problem] > > In 09.03.0400 we have no problem when insert into PostgreSQL bytea column > with SQLINTEGER data type. > > But in 09.05.0210 it was returned with below error: > > > > ERROR: column "id" is of type bytea but expression is of type integer; > > > > [confirmation] > > (1) It is seem come from the below release, is that right? > > > > ---psqlODBC 09.05.0100 Release--- > > 10. Fix quoting bugs in sending integer query parameters to server > > The drivers used to assume that if a parameter's SQL type is SQL_INTEGER > or SQL_SMALLINT, > > the value does not require quoting when its send to the server. For example, > "SELECT ?", > > with parameter 123 was translated to "SELECT 123", when UseServerSidePrepare > was not enabled. > > However, there was no check that the query parameter in fact contained a > valid integer, > > when replacing the parameter markers with their values. Also, in a query > like "SELECT 0-?", > > a negative value needs to have parens around it, as in "SELECT 0-(-123)". > > --- > > > > (2) Does the above release was made for below commit? > > > > > http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=1ccd04c70 > b8037b268eefcaae09ce131c8c33b59 > > > > (3) But we found exactly the below commit made obove ERROR. > > As the release note, does this commit completely did the thing which not > yet done in commit in (2) sir? > > > > > http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=085e9324e > b5a592f3b73f594311819cbf8dc5f2d > > > > Thanks and best regards, > > Dang Minh Huong > > NEC Solution Innovators, Ltd. > > http://www.nec-solutioninnovators.co.jp/en/