How to odbc.ini and odbcinst.ini -- or how to Server=??? in DSN-Less connect

[Michael Higgins-6]

Hello list --

The problem I've had is that none of the DRIVER definitions seem to get
picked up in SQLDriverConnect via perl-dbi.

I use DBD::ODBC to connect. Using a defined DSN works, but I want a
distributable app.

I should be able to specify 'Driver={}' line in DBI->connect()... but
every time this has failed with the standard, not helpful ...
[...Manager]Data source name not found and no default driver specified.

(Maybe I really don't want to do that anyway, but what if I do?)

The [Default] is specified, but not considered if I include a
'Driver={}' line. Leaving that line out allows a connect. This makes
some sense, but issuing 'Driver={Default}' doesn't work either...
should it?

That's all fine, since I finally got it to work, however, what I can't
find is a man page (or similar) that explicitly defines what is supposed
to happen with these two files and how it matters to the connect
'Driver={}' call.

I think someone posted at some point that the odbc.ini config, say,
[BLAH]
Driver=/path/to/driver

is picked up when SQLDriverConnect sees "Driver={BLAH};". I find this
does NOT happen, but rather it just fails as above...

(I get the impression that the GUI proggy would possibly write these
files for me. That's a too windows-like for my taste and so not even
installed. I'd rather find an explanation that I can digest so to edit
correctly a working config.) '-)

/etc/unixODBC/odbc.ini

[Default]
Driver          = /usr/lib/libtdsodbc.so

If I remove this line (and only this), then the connect doesn't work.
So the config must matter somewhere, somehow. But, what if I had a
different driver I wanted to use? And on Windows I have to have that
line (Driver=SQL Server), so was hoping/expecting to be able to specify
a linux equivalent.

Can anyone post an actual working set of config (odbc.ini,
odbcinst.ini) files for unixODBC built against freeTDS (0.6x) with a
Driver={XXX} in a SQLDriverConnect call (via perl-dbi, ideally) that
works, that I can adopt for a test?

I'll post the config I had to go with in a separate mail, for reference
if anyone else goes down this "(Driver={};)" path, it might save a lot
of wasted time.

It seems like the How-To docs on the EasySoft site all specify some
"OOB" that doesn't exist on my system. It'd be nice if DNS-Less (sans
OOB) were more explicitly covered for unixODBC.

The docs on freeTDS make it look as if this (DNS-less connect) only
works with iODBC, since the equivalent info isn't in the unixODBC
section...

Maybe that's pretty much true, if the Driver={XXX} line always fails?
This is why I'm soliciting a working config with that Driver={} line
honored via the unixODBC config files.

Cheers,

--
 |\  /|        |   |          ~ ~  
 | \/ |        |---|          `|` ?
 |    |ichael  |   |iggins    \^ /
 michael.higgins[at]evolone[dot]org
_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support

[Nick Gorham]

Michael Higgins wrote:

>Hello list --
>
>The problem I've had is that none of the DRIVER definitions seem to get
>picked up in SQLDriverConnect via perl-dbi.
>
>I use DBD::ODBC to connect. Using a defined DSN works, but I want a
>distributable app.
>
>  
>

It should work as long as the Driver= line in odbc.ini points to a
driver in odbcinst.ini

so in odbcinst.ini

[MyDriver]
Driver = /path/to/my/driver.so

and in odbc,ini a entry that matches would be

[MyDsn]
Driver = My Driver

Then you could connect with

DSN=MyDsn

or

Driver={My Driver}

I don;t have a FreeTDS entry, but this is much the same and works using
the make test in the DBD module instalation

odbcinst.ini

[Easysoft ODBC-SQL Server]
Driver     = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
Setup       = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
Threading       = 0
FileUsage       = 1
DontDLClose     = 1
UsageCount      = 1

Values passed to perl

DBI_DSN="dbi:ODBC:DRIVER={Easysoft ODBC-SQL
Server};Server=192.168.250.71;Database=Test"
DBI_USER=test
DBI_PASS=test

--
Nick Gorham
Easysoft Limited
http://www.easysoft.com, http://www.unixODBC.org

_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support

[Lukasz Szybalski]

On Mon, Jun 2, 2008 at 11:48 AM, Nick Gorham <nick.gorham@...> wrote:

> Michael Higgins wrote:
>
>> Hello list --
>>
>> The problem I've had is that none of the DRIVER definitions seem to get
>> picked up in SQLDriverConnect via perl-dbi.
>>
>> I use DBD::ODBC to connect. Using a defined DSN works, but I want a
>> distributable app.
>>
>>
>
> It should work as long as the Driver= line in odbc.ini points to a driver in
> odbcinst.ini
>
> so in odbcinst.ini
>
> [MyDriver]
> Driver = /path/to/my/driver.so
>
> and in odbc,ini a entry that matches would be
>
> [MyDsn]
> Driver = My Driver
>
> Then you could connect with
>
> DSN=MyDsn
>
> or
>
> Driver={My Driver}
>
> I don;t have a FreeTDS entry, but this is much the same and works using the
> make test in the DBD module instalation
>
> odbcinst.ini
>
> [Easysoft ODBC-SQL Server]
> Driver     = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
> Setup       = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
> Threading       = 0
> FileUsage       = 1
> DontDLClose     = 1
> UsageCount      = 1
>
> Values passed to perl
>
> DBI_DSN="dbi:ODBC:DRIVER={Easysoft ODBC-SQL
> Server};Server=192.168.250.71;Database=Test"
> DBI_USER=test
> DBI_PASS=test
>
> --
> Nick Gorham
> Easysoft Limited
> http://www.easysoft.com, http://www.unixODBC.org
>
> _______________________________________________
> unixODBC-support mailing list
> unixODBC-support@...
> http://mail.easysoft.com/mailman/listinfo/unixodbc-support
>

The way I connect to sqlserver on linux in python.

1. I use dsnless connection but still need a driver installed.
2. You need to install driver http://lucasmanual.com/mywiki/unixODBC
on windows that is done for you and driver name is "sql server" On
linux you have to install it. I usually call it "TDS" but I guess you
could call it "SQL Server" to make things easier.
3. Provide driver and tds version on a connection string in python.
4. In case this is helpful see:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/36fd2e935b165d70

Lucas

Lucas
_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support

[Michael Higgins-6]

On Mon, 02 Jun 2008 17:48:47 +0100
Nick Gorham <nick.gorham@...> wrote:

> Michael Higgins wrote:
>
> >Hello list --
> >
> >The problem I've had is that none of the DRIVER definitions seem to
> >get picked up in SQLDriverConnect via perl-dbi.
> >
> >I use DBD::ODBC to connect. Using a defined DSN works, but I want a
> >distributable app.
> >
>
> It should work as long as the Driver= line in odbc.ini points to a
> driver in odbcinst.ini
>
> so in odbcinst.ini
>
> [MyDriver]
> Driver = /path/to/my/driver.so

So, this should be okay:

cat /etc/unixODBC/odbcinst.ini

[Default]
/usr/lib/libtdsodbc.so

[FreeTDS]
/usr/lib/libtdsodbc.so

I want to call my driver FreeTDS.

> and in odbc,ini a entry that matches would be
>
> [MyDsn]
> Driver = My Driver
>
> Then you could connect with
>
> DSN=MyDsn

Which I want to avoid, users needing a DSN entry. So, this file is not
needed at all for my purpose, no?

>
> or
>
> Driver={My Driver}

Which would require users have some... something (not DSN?) configured.

(Why can't I just pass the path to the .so, I wonder?)

> I don;t have a FreeTDS entry, but this is much the same and works
> using the make test in the DBD module instalation
>
> odbcinst.ini
>
> [Easysoft ODBC-SQL Server]
> Driver     = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
> Setup       = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
> Threading       = 0
> FileUsage       = 1
> DontDLClose     = 1
> UsageCount      = 1

Where are these last five options documented with the installation? Or
are they specific to this other driver you use for example?

>
> Values passed to perl
>
> DBI_DSN="dbi:ODBC:DRIVER={Easysoft ODBC-SQL
> Server};Server=192.168.250.71;Database=Test"
> DBI_USER=test
> DBI_PASS=test
>

. . .

Well, I went through this step-by-step. No joy, period. The only thing
that works is having this:

cat /etc/unixODBC/odbc.ini
[Default]
Driver          =  /usr/lib/libtdsodbc.so

And NOT specifying the driver in my connect string. (Which seems
totally in conflict with what you posted.)

To illustrate:

# mv /etc/unixODBC/odbc.ini /etc/unixODBC/odbc.ini.bak
# mv /etc/unixODBC/odbcinst.ini /etc/unixODBC/odbcinst.ini.bak
# vim /etc/unixODBC/odbc.ini
# cat /etc/unixODBC/odbc.ini
[Default]
Driver          =  /usr/lib/libtdsodbc.so

locate odbc |grep .ini
cat /home/myuser/.odbc.ini
[empty]

And in my script:

$DSN="Server=ip.add.of.svr;Port=1433;Database=thedb;UID=_user;PWD=_pass;";

$DB->connect("dbi:ODBC:$DSN",'','', { AutoCommit
=> 1, LongTruncOk=>1 }, { quote_char => '"',
          name_sep => '.' }
         );

So, I'm stumped. I wasted many frustrated hours following all the
how-tos and hints I found on the web, only to come upon this...
solution(?) quite by trial-and-error.

Now what? Should we try to discover the root of the conflicting
information, or should the unixODBC how-tos be updated, somehow? I was
about to uninstall it completely when I found it works, but just not
"as advertised". '-)

It appears like there may be some different assumptions with FreeTDS
and unixODBC. I just looked again at this:

http://www.freetds.org/userguide/odbcinionly.htm
------------
ODBC-only configuration

An ODBC-only configuration relies solely on odbc.ini
------------

So, how does this correspond/correlate with the docs for unixODBC? Were
you aware that this was a possible configuration for unixODBC?

And this seems important as well:

http://www.freetds.org/userguide/prepodbc.htm
-----------------
With version 0.60, the FreeTDS ODBC library started to see fuller
implementation. The driver was made able to read the connection
attributes directly from odbc.ini, rather than leaning on freetds.conf.
For installations that don't need db-lib and ct-lib, this ODBC-only
setup is simpler.
------------------

So. How about,
# mv /etc/freetds.conf /etc/freetds.conf.bak

Script still works. I'm totally baffled at this point. Do I even need
unixODBC?

DBIx::Class::Storage::DBI::ensure_connected(): DBI Connection failed:
install_driver(ODBC) failed: Can't load
'/usr/lib/perl5/vendor_perl/5.8.8/i586-linux/auto/DBD/ODBC/ODBC.so' for
module DBD::ODBC: libodbc.so.1: cannot open shared object file: No such
file or directory at /usr/lib/perl5/5.8.8/i586-linux/DynaLoader.pm line
230.  at (eval 208) line 3
Compilation failed in require at (eval 208) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 839

I guess so, in order to build DBD::ODBC??? So, what's the solution? Is
it possible to get FreeTDS and unixODBC docs into some concordance? Or
is this a Gentoo bug, for example? I just noticed a proposed DBD-ODBC
ebuild is on the coming bugday list. Is this something that should be
addressed in that context, at the least?

Just a quick look at the web in this context led me to this:

http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31307.html

How long has this been going on? This looks awfully similar to the
problem I had.

From that post:

"In reality, on UNIX no one writes a default section in their odbc.ini
file so the misdirection to a
DEFAULT driver nearly always confuses."

Um, that's exactly how my configuration works, though I'm not
specifying Default, it would appear the [Default] section is read. From
that file.

http://www.nntp.perl.org/group/perl.dbi.users/2007/04/msg31300.html

Is this a good outcome?

The only answers he seems to have got was to use DBD::Gopher or
DBD::Sybase.

Hope this helps someone keep the ODBC option available.

Cheers,

--
 |\  /|        |   |          ~ ~  
 | \/ |        |---|          `|` ?
 |    |ichael  |   |iggins    \^ /
 michael.higgins[at]evolone[dot]org
_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support

[Michael Higgins-6]

On Mon, 2 Jun 2008 13:29:31 -0500
"Lukasz Szybalski" <szybalski@...> wrote:

> > Michael Higgins wrote:
> >
> >> Hello list --
> >>
> >> The problem I've had is that none of the DRIVER definitions seem
> >> to get picked up in SQLDriverConnect via perl-dbi.
> >>
> >> I use DBD::ODBC to connect. Using a defined DSN works, but I want a
> >> distributable app.

[...]

> 3. Provide driver and tds version on a connection string in python.

Hmmm. I'm sure I did that.

> 4. In case this is helpful see:
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/36fd2e935b165d70

Very informative thread.

Since dsn-less connect doesn't touch freetds.conf, would you mind
sharing the relevant bits of odbcinst.ini and odbc.ini? I found that my
setup apparently respects/needs only the setting in odbc.ini that
points to the driver library object directly, but fails when set up
with odbcinst.ini pointing to the driver library and odbc.ini
Driver= pointing to the entry in odbcinist.ini. Or fails whenever I
specify Driver={} in the connect().

I'm rebuilding the entire chain at the moment. Maybe some behavior will
change.

Cheers,

--
 |\  /|        |   |          ~ ~  
 | \/ |        |---|          `|` ?
 |    |ichael  |   |iggins    \^ /
 michael.higgins[at]evolone[dot]org
_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support

[Dan Wierenga]

Hi Michael,

> -----Original Message-----
> From: unixodbc-support-bounces@... [mailto:unixodbc-support-
> bounces@...] On Behalf Of Mich
> Can anyone post an actual working set of config (odbc.ini,
> odbcinst.ini) files for unixODBC built against freeTDS (0.6x) with a
> Driver={XXX} in a SQLDriverConnect call (via perl-dbi, ideally) that
> works, that I can adopt for a test?
>

With odbcinst.ini defined as

[FreeTDS]
Description  = FreeTDS driver for MS SQL Server or Sybase databases
Driver       = /usr/lib/libtdsodbc.so.0

Use:

my $dbh =
DBI->connect('dbi:ODBC:Driver=FreeTDS;Server=some.server.com;port=1433',
$user, $password);


HTH,
Dan

_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support

[Nick Gorham]

Michael Higgins wrote:

>On Mon, 02 Jun 2008 17:48:47 +0100
>Nick Gorham <nick.gorham@...> wrote:
>
>  
>
>>Michael Higgins wrote:
>>
>>    
>>
>>>Hello list --
>>>
>>>The problem I've had is that none of the DRIVER definitions seem to
>>>get picked up in SQLDriverConnect via perl-dbi.
>>>
>>>I use DBD::ODBC to connect. Using a defined DSN works, but I want a
>>>distributable app.
>>>
>>>      
>>>
>>It should work as long as the Driver= line in odbc.ini points to a
>>driver in odbcinst.ini
>>
>>so in odbcinst.ini
>>
>>[MyDriver]
>>Driver = /path/to/my/driver.so
>>    
>>
>
>So, this should be okay:
>
>cat /etc/unixODBC/odbcinst.ini
>
>[Default]
>/usr/lib/libtdsodbc.so
>
>[FreeTDS]
>/usr/lib/libtdsodbc.so
>
>I want to call my driver FreeTDS.
>  
>

No, you need

[FreeTDS]
Driver = /usr/lib/libtdsodbc.so

>  
>
>>and in odbc,ini a entry that matches would be
>>
>>[MyDsn]
>>Driver = My Driver
>>
>>Then you could connect with
>>
>>DSN=MyDsn
>>    
>>
>
>Which I want to avoid, users needing a DSN entry. So, this file is not
>needed at all for my purpose, no?
>
>  
>

No.

>>or
>>
>>Driver={My Driver}
>>    
>>
>
>Which would require users have some... something (not DSN?) configured.
>
>(Why can't I just pass the path to the .so, I wonder?)
>
>  
>

I think you can, check the code in DriverManager/SQLDriverConnect.c, its
some time simce I wrote it.

>>I don;t have a FreeTDS entry, but this is much the same and works
>>using the make test in the DBD module instalation
>>
>>odbcinst.ini
>>
>>[Easysoft ODBC-SQL Server]
>>Driver     = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
>>Setup       = /usr/local/easysoft/sqlserver/lib/libessqlsrvS.so
>>Threading       = 0
>>FileUsage       = 1
>>DontDLClose     = 1
>>UsageCount      = 1
>>    
>>
>
>Where are these last five options documented with the installation? Or
>are they specific to this other driver you use for example?
>  
>

FileUsage and UsageCount is documented in the MS docs, Threading and
DontDLClose are documented in the unixODBC code, but normally are not
needed. Look at the archives, you will see several copies of the
comemnts in __handle.c that describes the setting.

>  
>
>>Values passed to perl
>>
>>DBI_DSN="dbi:ODBC:DRIVER={Easysoft ODBC-SQL
>>Server};Server=192.168.250.71;Database=Test"
>>DBI_USER=test
>>DBI_PASS=test
>>
>>    
>>
>
>. . .
>
>Well, I went through this step-by-step. No joy, period. The only thing
>that works is having this:
>
>cat /etc/unixODBC/odbc.ini
>[Default]
>Driver          =  /usr/lib/libtdsodbc.so
>
>And NOT specifying the driver in my connect string. (Which seems
>totally in conflict with what you posted.)
>
>To illustrate:
>
># mv /etc/unixODBC/odbc.ini /etc/unixODBC/odbc.ini.bak
># mv /etc/unixODBC/odbcinst.ini /etc/unixODBC/odbcinst.ini.bak
># vim /etc/unixODBC/odbc.ini
># cat /etc/unixODBC/odbc.ini
>[Default]
>Driver          =  /usr/lib/libtdsodbc.so
>
>locate odbc |grep .ini
>cat /home/myuser/.odbc.ini
>[empty]
>
>And in my script:
>
>$DSN="Server=ip.add.of.svr;Port=1433;Database=thedb;UID=_user;PWD=_pass;";
>
>$DB->connect("dbi:ODBC:$DSN",'','', { AutoCommit
>=> 1, LongTruncOk=>1 }, { quote_char => '"',
>          name_sep => '.' }
>         );
>
>So, I'm stumped. I wasted many frustrated hours following all the
>how-tos and hints I found on the web, only to come upon this...
>solution(?) quite by trial-and-error.
>  
>

Thats working as you are not specifying a DSN= or a DRIVER=  in the
connection string, so unixODBC is using the defaut section in odbc.ini.
The best documention for this is the Microsoft one, as this is what I
tried to make the driver manager copy word for word.

>Now what? Should we try to discover the root of the conflicting
>information, or should the unixODBC how-tos be updated, somehow? I was
>about to uninstall it completely when I found it works, but just not
>"as advertised". '-)
>  
>
Well, thats always a option, but remember, I am talking about what
unixODBC does, and I showed it working how I believe it should with a
driver that I know works in the same way, I can't say either way how
another driver will operate, thats a question for the writers of that
driver, but I believe, that the example I gave shows unixODBC is doing
what it should in this situation, and given this is on the unixODBC
support list, thats all I can talk about with any confidance.

--
Nick
_______________________________________________
unixODBC-support mailing list
unixODBC-support@...
http://mail.easysoft.com/mailman/listinfo/unixodbc-support