Thread: PostgreSQL: Inserting NULL values adds empty string.
Hi Folks, I am using PostgreSQL 9.4 odbc driver to talk to 9.4 postgreSQL server. I see that when inserting NULL values into DB is adding empty string in the database only in windows. The same insert is adding NULL values into database when linux odbc driver is used to connect to linux database. Is there any configuration in odbc driver end or in server to retain the null values or is it a bug? If it is not and is an expected behavior, how to fix it in the application? Thanks, Sekhar -----Original Message----- From: Venkatesan, Sekhar Sent: Friday, January 29, 2016 10:04 AM To: 'Joshua D. Drake'; 'pgsql-odbc@postgresql.org' Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! HI Joshua, I perform the following set of operations to set autocommit in postgreSQL through ODBC in windows. SQLSetConnectAttr(hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0) ret = SQLDriverConnect(_hdbc, NULL, (SQLCHAR*) dsn.GetChars(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); This doesn't enable autocommit. If I call SQLSetConnectAttr after SQLDriverConnect, autocommit is set. Any issue with this sequence? I assume It should be possible to set SQL_ATTR_AUTOCOMMIT before establishing connection. Can you please confirm on this behavior? Thanks, Sekhar -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Thursday, January 28, 2016 11:14 PM To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org Subject: Re: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!! On 01/28/2016 08:53 AM, Venkatesan, Sekhar wrote: > Is the above issue fixed? Also in my use-case, I want to enable > autocommit at odbc driver level but even that doesn't work. > > The same application works in Linux OS when unix odbc driver is used. > This seems to be specific to windows driver. > > Please shed some light on this. Do ask me further question if you have any. > By default PostgreSQL "autocommits". If it isn't in your case, your driver is starting a transaction and not committing it. Sincerely, JD -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development.
On 02/14/2016 05:13 AM, Venkatesan, Sekhar wrote: > Hi Folks, > > I am using PostgreSQL 9.4 odbc driver to talk to 9.4 postgreSQL server. Through what application? > I see that when inserting NULL values into DB is adding empty string in the database only in windows. > The same insert is adding NULL values into database when linux odbc driver is used to connect to linux database. How are you determining this? > Is there any configuration in odbc driver end or in server to retain the null values or is it a bug? > If it is not and is an expected behavior, how to fix it in the application? > > Thanks, > Sekhar > -- Adrian Klaver adrian.klaver@aklaver.com
It's a C++ application that talks to PostgreSQL DB. I checked the value in database directly. -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Sunday, February 14, 2016 10:19 PM To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org; pgsql-sql@postgresql.org Subject: Re: [ODBC] PostgreSQL: Inserting NULL values adds empty string. On 02/14/2016 05:13 AM, Venkatesan, Sekhar wrote: > Hi Folks, > > I am using PostgreSQL 9.4 odbc driver to talk to 9.4 postgreSQL server. Through what application? > I see that when inserting NULL values into DB is adding empty string in the database only in windows. > The same insert is adding NULL values into database when linux odbc driver is used to connect to linux database. How are you determining this? > Is there any configuration in odbc driver end or in server to retain the null values or is it a bug? > If it is not and is an expected behavior, how to fix it in the application? > > Thanks, > Sekhar > -- Adrian Klaver adrian.klaver@aklaver.com
Venkatesan, Sekhar schrieb am 14.02.2016 um 14:13: > > I am using PostgreSQL 9.4 odbc driver to talk to 9.4 postgreSQL server. > I see that when inserting NULL values into DB is adding empty string in the database only in windows. > The same insert is adding NULL values into database when linux odbc driver is used to connect to linux database. > Is there any configuration in odbc driver end or in server to retain the null values or is it a bug? > If it is not and is an expected behavior, how to fix it in the application? > > Thanks, > Sekhar The server most definitely will not convert a NULL value into an empty string out of the blue. So it's either the ODBC driver (which I highly doubt) or your application. Another possible reason is a trigger that converts nulls to empty strings. Can you show us the insert that is used to store the NULL values? Are you explicitely listing that column and providing a NULL value? Or are you maybe not passing a value for that column at all? In that case the column might be defined as DEFAULT ''
On 02/14/2016 10:08 AM, Venkatesan, Sekhar wrote: > It's a C++ application that talks to PostgreSQL DB. I checked the value in database directly. Just notice this has been cross-posted to --sql, it is generally considered good etiquette to not do that. What exact version of psqlodbc are you using? Does the C++ application you mention run on both Linux and Windows? Or to put it another way, what is the program flow that gets the data into the database from Windows versus Linux? Also are we talking about one Postgres database, or one on Windows and one on Linux? > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Sunday, February 14, 2016 10:19 PM > To: Venkatesan, Sekhar; pgsql-odbc@postgresql.org; pgsql-sql@postgresql.org > Subject: Re: [ODBC] PostgreSQL: Inserting NULL values adds empty string. > > On 02/14/2016 05:13 AM, Venkatesan, Sekhar wrote: >> Hi Folks, >> >> I am using PostgreSQL 9.4 odbc driver to talk to 9.4 postgreSQL server. > > Through what application? > >> I see that when inserting NULL values into DB is adding empty string in the database only in windows. >> The same insert is adding NULL values into database when linux odbc driver is used to connect to linux database. > > How are you determining this? > >> Is there any configuration in odbc driver end or in server to retain the null values or is it a bug? >> If it is not and is an expected behavior, how to fix it in the application? >> >> Thanks, >> Sekhar >> > > > -- Adrian Klaver adrian.klaver@aklaver.com