Обсуждение: Case-sensitivity of connection string parameters

Hi,

I use the psqlODBC 9.2.1 with unixODBC 2.2.14 on Ubuntu 14.04.
Should I expect names of connection string parameters to be case-sensitive?

For instance, if I try DSN-less connection with the following connection
string passed to the ODBC API call SQLDriverConnect:

"Driver={PostgreSQL ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"

I get this error:

[unixODBC][Driver Manager]Data source name not found, and no default
driver specified

whereas if I try this one, with the upper-case DRIVER:

"DRIVER={PostgreSQL ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"

I get successfully connected.

I've couldn't find any details on the case sensitivity in the FAQ,
release notes or https://odbc.postgresql.org/docs/config-opt.html

The only cue might be the connection string in the VB example
at the bottom of this page, where DRIVER is specified in upper-case:
https://odbc.postgresql.org/docs/config.html

Another cue that suggests case-sensitivity might be important is from
the connection strings database:
https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
and it says:
"Please note that sslmode=require is case sensitive, it should be
written in lower case letters."

But, it is confusing since the https://odbc.postgresql.org/docs/config-opt.html
lists this parameter as "SSLmode", but not asd "sslmode".

Finally, I have tried psqlODBC 9.3.4 on Windows and there parameters work
regardless of their case.

Could anyone explain what case of each of the parameters is required useon Unix?

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

On 2016/05/10 16:56, Mateusz Loskot wrote:
> Hi,
>
> I use the psqlODBC 9.2.1 with unixODBC 2.2.14 on Ubuntu 14.04.
> Should I expect names of connection string parameters to be case-sensitive?
>
> For instance, if I try DSN-less connection with the following connection
> string passed to the ODBC API call SQLDriverConnect:
>
> "Driver={PostgreSQL ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"
>
> I get this error:
>
> [unixODBC][Driver Manager]Data source name not found, and no default
> driver specified

What rejects the connection is unixODBC driver manager not psqlodbc driver.
Newer version of unixODBC may allow "Driver=....".

>
> whereas if I try this one, with the upper-case DRIVER:
>
> "DRIVER={PostgreSQL ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"
>
> I get successfully connected.
>
> I've couldn't find any details on the case sensitivity in the FAQ,
> release notes or https://odbc.postgresql.org/docs/config-opt.html
>
> The only cue might be the connection string in the VB example
> at the bottom of this page, where DRIVER is specified in upper-case:
> https://odbc.postgresql.org/docs/config.html
>
> Another cue that suggests case-sensitivity might be important is from
> the connection strings database:
> https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
> and it says:
> "Please note that sslmode=require is case sensitive, it should be
> written in lower case letters."
>
> But, it is confusing since the https://odbc.postgresql.org/docs/config-opt.html
> lists this parameter as "SSLmode", but not asd "sslmode".
>
> Finally, I have tried psqlODBC 9.3.4 on Windows and there parameters work
> regardless of their case.

The keywords of connection string are case-insensitive but the values
might be case-sensitive.

regards,
Hiroshi Inoue

On 10 May 2016 at 15:26, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:
> On 2016/05/10 16:56, Mateusz Loskot wrote:
>>
>> I use the psqlODBC 9.2.1 with unixODBC 2.2.14 on Ubuntu 14.04.
>> Should I expect names of connection string parameters to be
>> case-sensitive?
>>
>> For instance, if I try DSN-less connection with the following connection
>> string passed to the ODBC API call SQLDriverConnect:
>>
>> "Driver={PostgreSQL
>> ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"
>>
>> I get this error:
>>
>> [unixODBC][Driver Manager]Data source name not found, and no default
>> driver specified
>
>
> What rejects the connection is unixODBC driver manager not psqlodbc driver.
> Newer version of unixODBC may allow "Driver=....".

I see, though, confusingly, in the same environment unixODBC
accepts  "Driver=" for MySQL connection.
That is why I suspected psqlODBC.

Regards,
--
Mateusz Loskot, http://mateusz.loskot.net

On 05/10/2016 06:52 AM, Mateusz Loskot wrote:
> On 10 May 2016 at 15:26, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:
>> On 2016/05/10 16:56, Mateusz Loskot wrote:
>>>
>>> I use the psqlODBC 9.2.1 with unixODBC 2.2.14 on Ubuntu 14.04.
>>> Should I expect names of connection string parameters to be
>>> case-sensitive?
>>>
>>> For instance, if I try DSN-less connection with the following connection
>>> string passed to the ODBC API call SQLDriverConnect:
>>>
>>> "Driver={PostgreSQL
>>> ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"
>>>
>>> I get this error:
>>>
>>> [unixODBC][Driver Manager]Data source name not found, and no default
>>> driver specified
>>
>>
>> What rejects the connection is unixODBC driver manager not psqlodbc driver.
>> Newer version of unixODBC may allow "Driver=....".
>
> I see, though, confusingly, in the same environment unixODBC
> accepts  "Driver=" for MySQL connection.
> That is why I suspected psqlODBC.

So what does the MySQL connection look like in its entirety?

Also what are the *.ini file entries for the respective drivers?

>
> Regards,
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/05/16 15:40, Adrian Klaver wrote:
> On 05/10/2016 06:52 AM, Mateusz Loskot wrote:
>> On 10 May 2016 at 15:26, Inoue, Hiroshi <h-inoue@dream.email.ne.jp>
>> wrote:
>>> On 2016/05/10 16:56, Mateusz Loskot wrote:
>>>>
>>>> I use the psqlODBC 9.2.1 with unixODBC 2.2.14 on Ubuntu 14.04.
>>>> Should I expect names of connection string parameters to be
>>>> case-sensitive?
>>>>
>>>> For instance, if I try DSN-less connection with the following
>>>> connection
>>>> string passed to the ODBC API call SQLDriverConnect:
>>>>
>>>> "Driver={PostgreSQL
>>>> ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"
>>>>
>>>> I get this error:
>>>>
>>>> [unixODBC][Driver Manager]Data source name not found, and no default
>>>> driver specified
>>>
>>>
>>> What rejects the connection is unixODBC driver manager not psqlodbc
>>> driver.
>>> Newer version of unixODBC may allow "Driver=....".
>>
>> I see, though, confusingly, in the same environment unixODBC
>> accepts  "Driver=" for MySQL connection.
>> That is why I suspected psqlODBC.
>
> So what does the MySQL connection look like in its entirety?
>
> Also what are the *.ini file entries for the respective drivers?

 From the current unixODBC change log

26th-November-2011
2.3.1

     * The keyword matching for DRIVER=, DSN= etc was case sensitive.
Make it insensitive now

--
Nick

On 10 May 2016 at 16:40, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 05/10/2016 06:52 AM, Mateusz Loskot wrote:
>>
>> On 10 May 2016 at 15:26, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:
>>>
>>> On 2016/05/10 16:56, Mateusz Loskot wrote:
>>>>
>>>>
>>>> I use the psqlODBC 9.2.1 with unixODBC 2.2.14 on Ubuntu 14.04.
>>>> Should I expect names of connection string parameters to be
>>>> case-sensitive?
>>>>
>>>> For instance, if I try DSN-less connection with the following connection
>>>> string passed to the ODBC API call SQLDriverConnect:
>>>>
>>>> "Driver={PostgreSQL
>>>> ANSI};Server=127.0.0.1;Database=test;UID=test;PWD=test;"
>>>>
>>>> I get this error:
>>>>
>>>> [unixODBC][Driver Manager]Data source name not found, and no default
>>>> driver specified
>>>
>>>
>>>
>>> What rejects the connection is unixODBC driver manager not psqlodbc
>>> driver.
>>> Newer version of unixODBC may allow "Driver=....".
>>
>>
>> I see, though, confusingly, in the same environment unixODBC
>> accepts  "Driver=" for MySQL connection.
>> That is why I suspected psqlODBC.
>
>
> So what does the MySQL connection look like in its entirety?

Driver=MySQL;Server=localhost;Database=nanodbc_tests;User=root;Password=;Option=3;


Sample Travis CI build job where this MySQL connection string is
successfully used:
https://travis-ci.org/lexicalunit/nanodbc/jobs/128260733

Sample job where PostgreSQL connection string with capitalised Driver=
is failing:
https://travis-ci.org/lexicalunit/nanodbc/jobs/128260734

and PostgreSQL job worked after changing to DRIVER=
https://travis-ci.org/lexicalunit/nanodbc/jobs/129121683

> Also what are the *.ini file entries for the respective drivers?

All .ini files are left as installed by Ubuntu 14.04 packages

$ cat /etc/odbc.ini

$ cat /etc/odbcinst.ini

[SQLite]
Description = SQLite ODBC Driver
Driver = /usr/lib/odbc/libsqliteodbc.so
Setup = /usr/lib/odbc/libsqliteodbc.so
UsageCount = 1

[SQLite3]
Description = SQLite3 ODBC Driver
Driver = /usr/lib/odbc/libsqlite3odbc.so
Setup = /usr/lib/odbc/libsqlite3odbc.so
UsageCount = 1

[PostgreSQL ANSI]
Description = PostgreSQL ODBC driver (ANSI version)
Driver = psqlodbca.so
Setup = libodbcpsqlS.so
Debug = 0
CommLog = 1
UsageCount = 2

[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver (Unicode version)
Driver = psqlodbcw.so
Setup = libodbcpsqlS.so
Debug = 0
CommLog = 1
UsageCount = 2


Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net