Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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/libmsodbcsql-11.0.so.2270.0" 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.

In reply to Re: fail: Linux Perl-DBI to SQL Server by mje
in thread fail: Linux Perl-DBI to SQL Server by madmole

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others studying the Monastery: (7)
As of 2024-03-28 19:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found