Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Re: fail: Linux Perl-DBI to SQL Server

by mje (Curate)
on May 16, 2013 at 08:24 UTC ( #1033786=note: print w/replies, xml ) Need Help??

in reply to fail: Linux Perl-DBI to SQL Server

I see you solved this for yourself but just a few notes which might help clarify things for any one else reading this.

The odbcinst.ini and odbc.ini are just mirror images of the system used on Windows where keys in the registry have those names. The odbcinst key/file defines your drivers and the odbc key/file defines your data sources (note you don't NEED the latter as there are DSN-less connections).

These keys/files are related so when you create/install a driver called "tul1system" in the odbcinst file and create a DSN in the odbc file the Driver keyword in the DSN should point to the name of the driver in the odbcinst file (the bit in []) and be "tul1system" not "/opt/microsoft/msodbcsql/lib64/" as you had although because of a quirk in the way unixODBC works the latter can still work.

Your odbc.ini file was incorrect as it should start with a DSN name in []. So it should have been:

[mydsnname] Description = Test Driver = tullsystem Trace = Yes TraceFile = /home/madmole/sql.log Database = Mosaic

whereas you created a DSN called "ODBC Driver 11 for SQL Server" and had some other keywords/attributes in there that were not associated with any DSN.

It makes no sense from what you posted for the isql command to work as you attempted to connect to the DSN call tul1system and you didn't appear to have one (unless it was defined in the system DSN file /usr/local/etc/odbc.ini or you just omitted "tul1system" from the start of your catted odbc.ini file when you pasted it).

When you attempted to connect in Perl with "dbi:ODBC:driver={ODBC Driver 11 for SQL Server};server=tul1system;database=Mosaic" what happens is DBI uses the 'dbi:ODBC' part to identify and load the DBD::ODBC driver and DBD::ODBC calls SQLDriverConnect with "driver={ODBC Driver 11 for SQL Server};server=tul1system;database=Mosaic". unixODBC parses that string to identify the driver (by the way I think you need to use DRIVER uppercase not lowercase), looks in the odbcinst.ini file for that driver, finds the shared object under the driver attribute, loads it and calls SQLDriverConnect in the driver shared object.

The so called curly brackets notation you refer to has nothing to do with DSN-less connections. ODBC connection strings have attributes separated by semicolons and the {} are a quoting system telling the driver manager etc to ignore special characters until the trailing } is seen. People conventionally do DRIVER={some driver} because it has spaces in but I don't believe you need to.

A DSN-less connection in Perl looks like "dbi:ODBC:DRIVER={some driver};attr1=value1;attr2=value2" where attrN are the attributes the driver defines and the same names as those you use in a DSN.

One last thing you should be aware of is some legacy support in DBD::ODBC for ODBC 2. Before ODBC 3 came along there was only an ODBC API SQLConnect which took 3 arguments - DSN name, username and password. The DSN name was limited to 32 characters. When ODBC 3 came along they added SQLDriverConnect which took a connection string which was not limited in size and could hold all sorts of attributes e.g., UID and PWD are the attributes for your username and password. Because loads of people had Perl code used to ODBC 2 DBD::ODBC calls SQLDriverConnect and if that fails SQLConnect. This matters since if you have a DSN called mydsn and attempt to connect with 'dbi:ODBC:mydsn' DBD::ODBC first calls SQLDriverConnect which will fail and then calls SQLConnect which will succeed and you wasted some time. If you'd used 'dbi:ODBC:DSN=mydsn' the first SQLDriverConnect would have worked. There are other good reasons for doing this - see ODBC - The Connection Process and look for Perl DBD::ODBC.

You may also find What is DBD::ODBC? and Linux/UNIX ODBC useful reading.

Replies are listed 'Best First'.
Re^2: fail: Linux Perl-DBI to SQL Server
by madmole (Novice) on May 16, 2013 at 15:31 UTC

    Thanks for the informative post. One comment: your speculation a paste error causing the missing first line of the catted .odbc.ini file was correct (I've updated the original post to add it). This is why the isql command succeeded.

    Furthermore, the original problem using the curly braces as quoting syntax was (again as you point out) the "DRIVER" keyword needs to be capitalized (one of the case variations I did not try). When I fixed that the original version of started working.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1033786]
and a kettle whistles...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2018-05-21 22:32 GMT
Find Nodes?
    Voting Booth?