<H2><A NAME="using dbd::odbc with web servers under win32.">Using DBD::ODBC with web servers under Win32.</A></H2>
<DL>
<DT><STRONG><A NAME="item_General_Commentary_re_web_database_access">General Commentary re web database access</A></STRONG><BR>
<DD>
This should be a DBI faq, actually, but this has somewhat of an
Win32/ODBC twist to it.
<P>Typically, the Web server is installed as an NT service or a Windows
95/98 service. This typically means that the web server itself does
not have the same environment and permissions the web developer does.
This situation, of course, can and does apply to Unix web servers.
Under Win32, however, the problems are usually slightly different.</P>
<P></P>
<DT><STRONG><A NAME="item_Defining_your_DSN_%2D%2D_which_type_should_I_use%3">Defining your DSN -- which type should I use?</A></STRONG><BR>
<DD>
Under Win32 take care to define your DSN as a system DSN, not as a user
DSN. The system DSN is a ``global'' one, while the user is local to a
user. Typically, as stated above, the web server is ``logged in'' as a
different user than the web developer. This helps cause the situation
where someone asks why a script succeeds from the command line, but
fails when called from the web server.
<P></P>
<DT><STRONG><A NAME="item_Defining_your_DSN_%2D%2D_careful_selection_of_the_">Defining your DSN -- careful selection of the file itself is important!</A></STRONG><BR>
<DD>
For file based drivers, rather than client server drivers, the file
path is VERY important. There are a few things to keep in mind. This
applies to, for example, MS Access databases.
<P>1) If the file is on an NTFS partition, check to make sure that the Web
<STRONG>service</STRONG> user has permissions to access that file.</P>
<P>2) If the file is on a remote computer, check to make sure the Web
<STRONG>service</STRONG> user has permissions to access the file.</P>
<P>3) If the file is on a remote computer, try using a UNC path the file,
rather than a X:\ notation. This can be VERY important as services
don't quite get the same access permissions to the mapped drive letters
<STRONG>and</STRONG>, more importantly, the drive letters themselves are GLOBAL to
the machine. That means that if the service tries to access Z:, the Z:
it gets can depend upon the user who is logged into the machine at the
time. (I've tested this while I was developing a service -- it's ugly
and worth avoiding at all costs).</P>
<P>Unfortunately, the Access ODBC driver that I have does not allow one to
specify the UNC path, only the X:\ notation. There is at least one way
around that. The simplest is probably to use Regedit and go to
(assuming it's a system DSN, of course)
HKEY_LOCAL_USERS\SOFTWARE\ODBC\``YOUR DSN'' You will see a few settings
which are typically driver specific. The important value to change for
the Access driver, for example, is the DBQ value. That's actually the
file name of the Access database.</P>
<P></P>
<DT><STRONG>Connect without DSN
The ability to connect without a full DSN is introduced in version 0.21.</STRONG><BR>
<DD>
Example (using MS Access):
my $DSN = 'driver=Microsoft Access Driver
(*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb';
my $dbh = DBI->connect(``dbi:ODBC:$DSN'', '','')
or die ``$DBI::errstr\n'';
<P>The above sample uses Microsoft's UNC naming convention to point to the MSAccess
file (\\\\cheese\\g$\\perltest.mdb). The dbq parameter tells the access driver
which file to use for the database.
</P>
<PRE>
Example (using MSSQL Server):
my $DSN = 'driver={SQL Server};Server=server_name;
database=database_name;uid=user;pwd=password;';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";</PRE>
<P></P></DL>
<P>
<H2><A NAME="random links">Random Links</A></H2>
<P>These are in need of sorting and annotating. Some are relevant only
to ODBC developers (but I don't want to loose them).</P>
<H2><A NAME="frequently asked questions answers to common dbi and dbd::odbc questions:">Frequently Asked Questions
Answers to common DBI and DBD::ODBC questions:</A></H2>
<DL>
<DT><STRONG><A NAME="item_How_do_I_read_more_than_N_characters_from_a_Memo_%">How do I read more than N characters from a Memo | BLOB | LONG field?</A></STRONG><BR>
<TR><TD><TD>$sth = $dbh->prepare(``select long_col from big_table'');
<TR><TD><TD>$sth->execute;
<TR><TD><TD>etc</TABLE></P>
<P></P>
<DT><STRONG><A NAME="item_What_is_DBD%3A%3AODBC%3F_Why_can%27t_I_connect%3F_">What is DBD::ODBC? Why can't I connect? Do I need an ODBC driver? What is the ODBC driver manager?</A></STRONG><BR>
<DD>
These, general questions lead to needing definitions.
<P>1) ODBC Driver - the driver that the ODBC manager uses to connect
and interact with the RDBMS. You DEFINITELY need this to
connect to any database. For Win32, they are plentiful and installed
with many applications. For Linux/Unix, some hunting is required, but
<P>2) ODBC Driver Manager - the piece of software which interacts with the drivers
for the application. It ``hides'' some of the differences between the
drivers (i.e. if a function call is not supported by a driver, it 'hides'
that and informs the application that the call is not supported.
DBD::ODBC needs this to talk to drivers. Under Win32, it is built in
to the OS. Under Unix/Linux, in most cases, you will want to use freeODBC,
unixODBC or iODBC. iODBC is bundled with DBD::ODBC.</P>
<P>3) DBD::ODBC. DBD::ODBC uses the driver manager to talk to the ODBC <CODE>driver(s)</CODE> on
your system. You need both a driver manager and driver installed and tested
before working with DBD::ODBC. You need to have a DSN (see below) configured
*and* TESTED before being able to test DBD::ODBC.</P>
<P>4) DSN -- Data Source Name. It's a way of referring to a particular database by any
name you wish. The name itself can be configured to hide the gory details of
which type of driver you need and the connection information you need to provide.
For example, for some databases, you need to provide a TCP address and port.
You can configure the DSN to have use information when you refer to the DSN.</P>
<P></P>
<DT><STRONG><A NAME="item_Where_do_I_get_an_ODBC_driver_manager_for_Unix%2FL">Where do I get an ODBC driver manager for Unix/Linux?</A></STRONG><BR>
<DD>
DBD::ODBC comes with one (iODBC). In the DBD::ODBC source release is a directory named iodbcsrc.
There are others. UnixODBC, FreeODBC and some of the drivers will come with one of these managers.
For example Openlink's drivers (see below) come with the iODBC driver manager.
<P></P>
<DT><STRONG><A NAME="item_How_do_I_access_a_MS_SQL_Server_database_from_Linu">How do I access a MS SQL Server database from Linux?</A></STRONG><BR>
<DD>
Try using drivers from <A HREF="http://www.openlinksw.com">http://www.openlinksw.com</A>
The multi-tier drivers have been tested with Linux and Redhat 5.1.
<P></P>
<DT><STRONG><A NAME="item_How_do_I_access_an_MS%2DAccess_database_from_Linux">How do I access an MS-Access database from Linux?</A></STRONG><BR>
<DD>
I believe you can use the multi-tier drivers from <A HREF="http://www.openlinksw.com,">http://www.openlinksw.com,</A> however, I have
not tested this. Also, I believe there is a commercial solution from <A HREF="http://www.easysoft.com.">http://www.easysoft.com.</A> I
have not tested this.
<P>If someone does have more information, please, please send it to me and I will put it in this
FAQ.</P>
<P></P>
<DT><STRONG><A NAME="item_Almost_all_of_my_tests_for_DBD%3A%3AODBC_fail%2E_T">Almost all of my tests for DBD::ODBC fail. They complain about not being able to connect
or the DSN is not found.</A></STRONG><BR>
<DD>
Please, please test your configuration of ODBC and driver before trying to test DBD::ODBC. Most
of the time, this stems from the fact that the DSN (or ODBC) is not configured properly. iODBC
comes with a odbctest program. Please use it to verify connectivity.
<P></P>
<DT><STRONG><A NAME="item_Windows">For Unix -> Windows DB see Tom Lowery's write-up.</A></STRONG><BR>
