Re: no DBD:MSSQL?
Hardy Merrill wrote: - What is DBD::ODBC? Why can't I connect? Do I need an ODBC driver? What is the ODBC driver manager? Where do I get an ODBC driver manager for Unix/Linux? How do I access a MS SQL Server database from Linux? For Unix -> Windows DB see Tom Lowery's write-up. http://tlowery.hypermart.net/perl_dbi_dbd_faq.html#HowDoIAccessMSWin dowsDB I took that page (actually the whole site) down a while ago. The document was very dated. However, I just completed the exercise evaluating solutions for connecting to a MS SQL Server database from Linux. When I've extra time and find the DBI FAQ again, I'll write you the methods below. I tested 4 methods, 3 based using unixODBC with FreeTDS ODBC driver, Easysoft Bridge, and DataDirect ODBC. The 4th was using DBD::Sybase. I had all 4 working within a 5 hours. The FreeTDS ODBC driver is buggy (haven't had time to write up test cases to submit). Perl seg faults when attempting to insert a string into integer column, the messages aren't very descriptive (but that could be Sql Server) however it does work. DBD::Sybase compiled against FreeTDS worked, however the driver doesn't support place holders ... major down fall. The Easysoft bridge was the simplest to install. IIRC, Easysoft contributes to the development of unixODBC (help those who help us). The cost of packages required is very reasonable. The downside was having to install software on both Unix/Linux and MS side and speed. The DataDirect was a tricker to install. The install script required csh, which I had to install, then the script failed. I ended up finding the tar ball and doing the install manually. Needed to edit some of the configuration files afterwards and (since I install datadirect drivers in a separate directory) add LD_LIBRARY_PATH. The driver is fast and solid, however very very pricey. With any of the drivers you need to add entries to odbcinst.ini and odbc.ini (either global or ~/.odbc.ini) Here are the steps to to install unixODBC with FreeTDS: Installing unixODBC with FreeTDS Fri Jul 23 13:12:48 EDT 2004 Download source code from unixODBC: http://www.unixodbc.org/download.html FreeTDS: ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz Installing unixODBC tar zxvf unixODBC-2.2.9.tar.gz creates directory unixODBC-2.2.9 cd unixODBC-2.2.9 For my local install, I used the following. configure --prefix=/usr/local/odbc --disable-gui make make check make install Create /etc/profile.d/odbc.sh to set the environmental variables. if [ -s "$LD_LIBRARY_PATH" ]; then LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/odbc/lib else LD_LIBRARY_PATH=/usr/local/odbc/lib fi ODBCHOME=/usr/local/odbc ODBCINI=/usr/local/odbc/etc export LB_LIBRARY_PATH ODBCHOME ODBCINI I created a template for PostgreSQL. create pg_template1.txt [PostgreSQL] Description = PostgreSQL driver for Linux & Win32 Driver = /usr/local/odbc/lib/libodbcpsql.so Setup = /usr/local/odbc/lib/libodbcpsqlS.so FileUsage = 1 [PostgreSQLThreads] Description = PostgreSQL driver for Linux & Win32 Driver = /usr/local/lib/libodbcpsql.so Setup = /usr/local/lib/libodbcpsqlS.so Threading = 2 Install the template into odbcinst.ini using the following sudo /usr/local/odbc/bin/odbcinst -i -d -f pg_template1.txt I created a template for PostgreSQL for odbc.ini [PostgreSQL] Description = Test to Postgres Driver = PostgreSQL Trace = Yes TraceFile = sql.log Database= database_name Servername = machine UserName= Password= Port= 5432 Protocol= 6.4 ReadOnly= No RowVersioning = No ShowSystemTables= No ShowOidColumn = No FakeOidIndex= No ConnSettings= sudo /usr/local/odbc/bin/odbcinst -i -s -f pg_template_ini1.txt Unpack the FreeTDS package. tar zxvf freetds-stable.tgz configure --help > myconfig.sh Creating the file myconfig.sh configure --prefix=/usr/local/odbc --with-unixodbc=/usr/local/odbc make make check sudo make install Create a template file for FreeTDS driver in odbcinst.ini [FreeTDS] Description = FreeTDS for Linux Driver = /usr/local/odbc/lib/libtdsodbc.so Setup = /usr/local/odbc/lib/libtdsS.so Create a template file for FreeTDS for odbc.ini [mssql_base] Driver = FreeTDS Description = Microsoft SQL Server Trace = No Server = machine TDS_Version = 8.0 Port= 1433 Database= database_name An entry for any SQL Server Database will need added to odbc.ini Next install DBD::ODBC With the ODBCINI, ODBCHOME, and LD_LIBRARY_PATH set (see above) Install DBD::ODBC using either CPAN or download the module CPAN: perl -MCPAN -e 'force install DBD::ODBC' Reason for the force, is the tests will fail because DBI_DSN, DBI_USER, and DBI_PASS are not set. Running the F
Re: no DBD:MSSQL?
Janet Goldstein wrote: Am I missing something? I searched CPAN for a DBI driver for Microsoft SQL Server and found only "DBIx::SQLEngine::Driver::MSSQL - Support DBD::ODBC with Microsoft SQL Server." Is there nothing out there for SQL Server as there is for Oracle (DBD::Oracle)? Must I go through ODBC to talk to a SQL Server database? Janet> there is DBD::Sybase module and using FreeTDS. The standard release of MS-SQL 7 can not be accessed using the Sybase libraries, but can be used using the FreeTDS libraries. There is a patch for MS-SQL 7 to allow Sybase clients to connect:
Re: no DBD:MSSQL?
Hardy Merrill wrote: > > Janet, I've got DBD::ODBC installed on my Windows XP machine. When I do > "perldoc DBD::ODBC" in a command prompt window I can see the excellent > documentation that is part of the DBD::ODBC module. Here are some of > the section titles in that perldoc: Thank you for your reply. I got DBD::ODBC to work on my Win2k machine with no trouble at all and rewrote my script to use DBI and DBD::ODBC instead of Win32::ODBC. I'm not convinced that performance is better, but it's nice to have the DBI interface. -- Janet Goldstein, Sr. Programmer/Analyst II Center for Inherited Disease Research Johns Hopkins University / Bayview Campus 333 Cassell Drive / Baltimore, MD 21224 [EMAIL PROTECTED] / 410-550-2819 / fax 410-550-3559 http://www.cidr.jhmi.edu/
Re: no DBD:MSSQL?
On Tue, 13 Jul 2004 15:47:21 -0400, Janet Goldstein wrote: >Thanks to all who replied. I ended up selling out and using >Win32::ODBC. Eh... why not DBD::ODBC? -- Bart.
RE: no DBD:MSSQL?
Janet, I've got DBD::ODBC installed on my Windows XP machine. When I do "perldoc DBD::ODBC" in a command prompt window I can see the excellent documentation that is part of the DBD::ODBC module. Here are some of the section titles in that perldoc: - What is DBD::ODBC? Why can't I connect? Do I need an ODBC driver? What is the ODBC driver manager? Where do I get an ODBC driver manager for Unix/Linux? How do I access a MS SQL Server database from Linux? For Unix -> Windows DB see Tom Lowery's write-up. http://tlowery.hypermart.net/perl_dbi_dbd_faq.html#HowDoIAccessMSWin dowsDB So for any module you have installed, at a command prompt you can do "perldoc [module name]" to see the documentation included with the module. You can also see the perldocs online - the main perldoc site is http://www.perldoc.com. This link might be some help: http://www.perldoc.com/perl5.6.1/lib/DBI/FAQ.html#3.1-What's-the-difference-between-ODBC-and-DBI- Hopefully this will give you some more info to go on. Hardy Merrill >>> Janet Goldstein <[EMAIL PROTECTED]> 07/13/04 09:58PM >>> On Tue, 13 Jul 2004, Tim Johnson wrote: > > Why not DBD::ODBC? because, I'm embarrassed to admit, I have no clue how ODBC works in Unix.. ;-}
RE: no DBD:MSSQL?
On Tue, 13 Jul 2004, Tim Johnson wrote: > > Why not DBD::ODBC? because, I'm embarrassed to admit, I have no clue how ODBC works in Unix.. ;-}
Re: no DBD:MSSQL?
> From: Janet Goldstein <[EMAIL PROTECTED]> > Date: 2004/07/13 Tue PM 07:47:21 GMT > > Thanks to all who replied. I ended up selling out and using > Win32::ODBC. If it ends up being too slow, I can always go back to > Unix and try DBD::Sybase. That, in my opinion, is not the best option. I wrote several scripts, and one small webapp, using Win32::ODBC, which _is_ a good module. However, I've had *much* better performance, and more importantly for what I'm doing, a higher degree of portability. I would like to suggest that you give it a try, using DBD::ODBC, instead of Win32::ODBC... v/r, amonotod -- `\|||/ amonotod@| subject line: (@@) charter.net | no perl, no read... ooO_(_)_Ooo _|_|_|_|_|_|_|_|
RE: no DBD:MSSQL?
Why not DBD::ODBC? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 12:47 PM Cc: [EMAIL PROTECTED] Subject: Re: no DBD:MSSQL? Thanks to all who replied. I ended up selling out and using Win32::ODBC. If it ends up being too slow, I can always go back to Unix and try DBD::Sybase."William R. Mussatto" wrote:
Re: no DBD:MSSQL?
Thanks to all who replied. I ended up selling out and using Win32::ODBC. If it ends up being too slow, I can always go back to Unix and try DBD::Sybase."William R. Mussatto" wrote:
Re: no DBD:MSSQL?
> From: Janet Goldstein <[EMAIL PROTECTED]>
> Date: 2004/07/13 Tue PM 02:00:09 GMT
>
> Am I missing something? I searched CPAN for a DBI driver for Microsoft
> SQL Server and found only "DBIx::SQLEngine::Driver::MSSQL - Support
> DBD::ODBC with Microsoft SQL Server." Is there nothing out there for
> SQL Server as there is for Oracle (DBD::Oracle)? Must I go through
> ODBC to talk to a SQL Server database?
I have been writing a script that connects to multiple sources, and use this to
determine which driver to use, and detect whether or not it is available
The ODBC for MSSQL (from a Windows 2000 client) has been working quite well for me,
including dropping/creating/altering tables, columns, indices; granting permissions,
dropping/adding roles, logins, and users...
###
# Check to see if the proper database module is available
sub CheckAvail {
my $retval = 1;
if ("\U$arg_db_type\E" eq "ORACLE") {
eval "use DBD::Oracle";
if ($@) { $retval = 0; }
} elsif ("\U$arg_db_type\E" eq "SYBASE") {
eval "use DBD::Sybase";
if ($@) { $retval = 0; }
}elsif ("\U$arg_db_type\E" eq "MSSQL") {
eval "use DBD::ODBC";
if ($@) { $retval = 0; }
}elsif (("\U$arg_db_type\E" eq "ACCESS97") or ("\U$arg_db_type\E" eq "ACCESS2K")) {
eval "use DBD::ADO";
if ($@) { $retval = 0; }
}
if (! $retval) { print LOGFILE "The module for this database could not be loaded;
failing\n" }
return $retval;
}
###
HTH,
amonotod
--
`\|||/ amonotod@| subject line:
(@@) charter.net | no perl, no read...
ooO_(_)_Ooo
_|_|_|_|_|_|_|_|
Re: no DBD:MSSQL?
On Tue, 2004-07-13 at 18:51, William R. Mussatto wrote: > Hardy Merrill said: > > Janet, I think David was saying that you might(?) be able to use the > > DBD::Sybase driver to connect to MS SQL Server ;-) > > > > Hardy Merrill > > > Janet Goldstein <[EMAIL PROTECTED]> 07/13/04 11:14AM >>> > > David N Murray wrote: > >> > >> google turns up http://search.cpan.org/search?module=DBD::ADO > >> ADO & ODBC are your only options, AFAIK, on Windows. > >> Maybe someone more informed can comment on using the Sybase driver. > > > > Thanks for your reply, Dave. Actually, I'm not limited to Windows; I > > would just as soon access the database from my Unix box. However, it's > > not Sybase I'm trying to get to here; it's Microsoft SQL Server. > A bit of background might explain things. MSSQL was derived from Sybase. > Therefore its way of talking to the rest of the world is similar so the > Sybase module is the normal method of connecting. Although it should be pointed out that backwards compatibility with Sybase's version of the protocol was broken in MS-SQL 2k, so using the Sybase *libraries* (i.e. Sybase OpenClient) does not work anymore. Hence the reference to using FreeTDS for the actual communication layer. Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.peppler.org/ Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.peppler.org/resume.html
Re: no DBD:MSSQL?
Hardy Merrill said: > Janet, I think David was saying that you might(?) be able to use the > DBD::Sybase driver to connect to MS SQL Server ;-) > > Hardy Merrill > Janet Goldstein <[EMAIL PROTECTED]> 07/13/04 11:14AM >>> > David N Murray wrote: >> >> google turns up http://search.cpan.org/search?module=DBD::ADO >> ADO & ODBC are your only options, AFAIK, on Windows. >> Maybe someone more informed can comment on using the Sybase driver. > > Thanks for your reply, Dave. Actually, I'm not limited to Windows; I > would just as soon access the database from my Unix box. However, it's > not Sybase I'm trying to get to here; it's Microsoft SQL Server. A bit of background might explain things. MSSQL was derived from Sybase. Therefore its way of talking to the rest of the world is similar so the Sybase module is the normal method of connecting. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061
Re: no DBD:MSSQL?
Janet, I think David was saying that you might(?) be able to use the DBD::Sybase driver to connect to MS SQL Server ;-) Hardy Merrill >>> Janet Goldstein <[EMAIL PROTECTED]> 07/13/04 11:14AM >>> David N Murray wrote: > > google turns up http://search.cpan.org/search?module=DBD::ADO > ADO & ODBC are your only options, AFAIK, on Windows. > Maybe someone more informed can comment on using the Sybase driver. Thanks for your reply, Dave. Actually, I'm not limited to Windows; I would just as soon access the database from my Unix box. However, it's not Sybase I'm trying to get to here; it's Microsoft SQL Server.
Re: no DBD:MSSQL?
David N Murray wrote: > > google turns up http://search.cpan.org/search?module=DBD::ADO > ADO & ODBC are your only options, AFAIK, on Windows. > Maybe someone more informed can comment on using the Sybase driver. Thanks for your reply, Dave. Actually, I'm not limited to Windows; I would just as soon access the database from my Unix box. However, it's not Sybase I'm trying to get to here; it's Microsoft SQL Server.
Re: no DBD:MSSQL?
On Tue, 2004-07-13 at 16:00, Janet Goldstein wrote: > Am I missing something? I searched CPAN for a DBI driver for Microsoft > SQL Server and found only "DBIx::SQLEngine::Driver::MSSQL - Support > DBD::ODBC with Microsoft SQL Server." Is there nothing out there for > SQL Server as there is for Oracle (DBD::Oracle)? Must I go through > ODBC to talk to a SQL Server database? The alternative is DBD::Sybase, with the FreeTDS libraries (see http://www.freetds.org). Or (on Unix/linux/MacOSX), DBD::ODBC using (again) the FreeTDS libs (the odbc bits, this time) Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.peppler.org/ Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.peppler.org/resume.html
Re: no DBD:MSSQL?
On Jul 13, Janet Goldstein scribed: > David N Murray wrote: > > > > google turns up http://search.cpan.org/search?module=DBD::ADO > > ADO & ODBC are your only options, AFAIK, on Windows. > > Maybe someone more informed can comment on using the Sybase driver. > > Thanks for your reply, Dave. Actually, I'm not limited to Windows; I > would just as soon access the database from my Unix box. However, it's > not Sybase I'm trying to get to here; it's Microsoft SQL Server. > Sybase 4.2 was the basis for SQL Server: http://search.cpan.org/~mewp/DBD-Sybase-1.04/Sybase.pm#Using_DBD::Sybase_with_MS-SQL It _should_ work, but I've never tried it.
Re: no DBD:MSSQL?
google turns up http://search.cpan.org/search?module=DBD::ADO ADO & ODBC are your only options, AFAIK, on Windows. Maybe someone more informed can comment on using the Sybase driver. hth, dave On Jul 13, Janet Goldstein scribed: > Am I missing something? I searched CPAN for a DBI driver for Microsoft > SQL Server and found only "DBIx::SQLEngine::Driver::MSSQL - Support > DBD::ODBC with Microsoft SQL Server." Is there nothing out there for > SQL Server as there is for Oracle (DBD::Oracle)? Must I go through > ODBC to talk to a SQL Server database? > > > > -- > Janet Goldstein, Sr. Programmer/Analyst II > Center for Inherited Disease Research > Johns Hopkins University / Bayview Campus > 333 Cassell Drive / Baltimore, MD 21224 > [EMAIL PROTECTED] / 410-550-2819 / fax 410-550-3559 > http://www.cidr.jhmi.edu/ >
