ODBC w/ MS SQL

Howdy List :)

I've been attempting to use DBD::ODBC to connect to MS SQL from a Linux box.

Unfortunately the only good DBD::ODBC/unixodbc -> MSSQL bridge costs 700
pounds
(http://www.easysoft.com/products/data_access/odbc_odbc_brid ge/index.html)
and the only DBD::ODBC -> MSSQL "how to" is from the same company.

Is there any good Unix Perl -> MSSQL "How to" (that is very clear and
doesn't require a driver that costs an arm and a leg) or system to use
that is either open source or at least reasonably priced?

I've looked and looked and can't seem to find any alternatives, so if
I've overlooked something or you know of a good URL, just let me know :)

TIA!

Re: ODBC w/ MS SQL

JupiterHost.Net wrote:
> Howdy List :)
>
> I've been attempting to use DBD::ODBC to connect to MS SQL from a Linux
> box.
>
> Unfortunately the only good DBD::ODBC/unixodbc -> MSSQL bridge costs 700
> pounds
> (http://www.easysoft.com/products/data_access/odbc_odbc_brid ge/index.html)
> and the only DBD::ODBC -> MSSQL "how to" is from the same company.
>
> Is there any good Unix Perl -> MSSQL "How to" (that is very clear and
> doesn't require a driver that costs an arm and a leg) or system to use
> that is either open source or at least reasonably priced?
>
> I've looked and looked and can't seem to find any alternatives, so if
> I've overlooked something or you know of a good URL, just let me know :)
>
> TIA!
>

Have you tried FreeTDS ?

http://www.freetds.org/

Dean Arnold
Presicient Corp.

Re: ODBC w/ MS SQL

> Have you tried FreeTDS ?
>
> http://www.freetds.org/
>
> Dean Arnold
> Presicient Corp.

Saweet! That appears to be just what I need, if this is what I need let
me just say: Dean you're a life saver :)

Thanks a bunch!!!

Re: ODBC w/ MS SQL

JupiterHost.Net wrote:
>> Have you tried FreeTDS ?
>>
>> http://www.freetds.org/
>>
>> Dean Arnold
>> Presicient Corp.
>
> Saweet! That appears to be just what I need, if this is what I need let
> me just say: Dean you're a life saver :)
>
> Thanks a bunch!!!
>

Glad to be of service.

FYI: There was an article about it in Sysadmin magazine
a few months ago, you might want to hunt it down for
some pointers on using it.

- Dean

Re: ODBC w/ MS SQL

> Have you tried FreeTDS ?
>
> http://www.freetds.org/

Little snafu, installing freetds and then DBD::ODBC against freetds

wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds -stable.tgz
cd freetds-0.63/
../configure --prefix=/opt/freetds
make
make install

wget http://search.cpan.org/CPAN/authors/id/J/JU/JURL/DBD-ODBC-1. 13.tar.gz
tar xzf DBD-ODBC-1.13.tar.gz
cd DBD-ODBC-1.13

# just want to get it built before I fiddle with freetds's MSSQL DSNs
export SYBASE=/opt/freetds
export ODBCHOME=/opt/
export DBI_DSN=dbi:ODBC:JDBC
export DBI_USER=guest
export DBI_PASS=sybase


root [~/DBD-ODBC-1.13]# perl Makefile.PL
Useless use of private variable in void context at Makefile.PL line 431.

Configuring DBD::ODBC ...

>>> Remember to actually *READ* the README file!
And re-read it if you have any problems.

Using DBI 1.50 (for perl 5.008007 on i686-linux) installed in
/usr/lib/perl5/site_perl/5.8.7/i686-linux/auto/DBI/
Using ODBC in /opt/

Hmm...I cannot find an ODBC driver manager that I recognize.
....And I know about these drivers:
Microsoft ODBC, adabas, easysoft, empress, esodbc, informix,
intersolve, iodbc, sapdb, solid, udbc, unixodbc
root [~/DBD-ODBC-1.13]#

Do I need to use DBD::OBDC-0.28 as per Example 6-4 at
http://freetds.org/userguide/perl.htm#PERLMODULES

or ??

Thanks!

Re: ODBC w/ MS SQL

You need an ODBC manager with the freetds driver. I suggest unixODBC.

One issue you may run into is that when a query fails to prepare, you
may only receive a "prepare failed..." message without additional
information as to why it failed. Also, I've had problems with place
holders not working.

If you're bulk loading or extracting take a look at freebcp. We've had
great success extracting data (6-7 million record tables), but have had
a problem with loading. Stops loading between 5-6 million records. We
divide our load files into files of million rows each to keep things
more manageable.

JupiterHost.Net wrote:
>
>> Have you tried FreeTDS ?
>>
>> http://www.freetds.org/
>
> Little snafu, installing freetds and then DBD::ODBC against freetds
>
> wget
> ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds -stable.tgz
> cd freetds-0.63/
> ./configure --prefix=/opt/freetds
> make
> make install
>
> wget http://search.cpan.org/CPAN/authors/id/J/JU/JURL/DBD-ODBC-1. 13.tar.gz
> tar xzf DBD-ODBC-1.13.tar.gz
> cd DBD-ODBC-1.13
>
> # just want to get it built before I fiddle with freetds's MSSQL DSNs
> export SYBASE=/opt/freetds
> export ODBCHOME=/opt/
> export DBI_DSN=dbi:ODBC:JDBC
> export DBI_USER=guest
> export DBI_PASS=sybase
>
>
> root [~/DBD-ODBC-1.13]# perl Makefile.PL
> Useless use of private variable in void context at Makefile.PL line 431.
>
> Configuring DBD::ODBC ...
>
> >>> Remember to actually *READ* the README file!
> And re-read it if you have any problems.
>
> Using DBI 1.50 (for perl 5.008007 on i686-linux) installed in
> /usr/lib/perl5/site_perl/5.8.7/i686-linux/auto/DBI/
> Using ODBC in /opt/
>
> Hmm...I cannot find an ODBC driver manager that I recognize.
> ...And I know about these drivers:
> Microsoft ODBC, adabas, easysoft, empress, esodbc, informix,
> intersolve, iodbc, sapdb, solid, udbc, unixodbc
> root [~/DBD-ODBC-1.13]#
>
> Do I need to use DBD::OBDC-0.28 as per Example 6-4 at
> http://freetds.org/userguide/perl.htm#PERLMODULES
>
> or ??
>
> Thanks!

MSAccess from SQL

Is there an example somewhere of accessing an MSAccess database from a SuSE
or RedHat machine via perl DBI?

Here is what I am presently using with ActiveState Perl to access the
MSAccess database on windows:

my $dbh = DBI->connect("dbi:ODBC:driver={Microsoft Access Driver
(*.mdb)};dbq=c:\\database.mdb","Admin","") or die "$DBI::errstr\\n";

What would I change to make this work on SuSE or RedHat?

Thanks,
Siegfried

Re: MSAccess from SQL

Siegfried Heintze wrote:

>Is there an example somewhere of accessing an MSAccess database from a SuSE
>or RedHat machine via perl DBI?
>
>Here is what I am presently using with ActiveState Perl to access the
>MSAccess database on windows:
>
>my $dbh = DBI->connect("dbi:ODBC:driver={Microsoft Access Driver
>(*.mdb)};dbq=c:\\database.mdb","Admin","") or die "$DBI::errstr\\n";
>
>What would I change to make this work on SuSE or RedHat?
>
>Thanks,
>Siegfried
>
>
Siegfried,
There are few options, unless MSAccess has come so far as being able to
be served on a port number as a server? I don't know that, I don't use
MSAccess. As far as I know, MSAccess is an "application" and doesn't
have the robustness of a Database Server, like PostgreSQL/Oracle/MS
SQL/Sybase/... other servers that can be accessed remotely by listening
on a determined IP/Port pair.

If you can get your MSAccess 'db' listening to a port number on your
Windows box, suffice it to say that you have the DBI driver installed on
the Linux (Suse/Redhat) box too for MSAccess, then you can use DBI to
connect to any remote DB server. I'm not sure if the DBI MSAccess
driver supports remote connections, again this is really a question of
"what can MS Access do?"

I'd recommend migrating your MS Access data over to the Linux box if
that's what you intend on using for the future. To do this, you'd have
several methods.

What are your plans?

Re: MSAccess from SQL

Siegfried Heintze wrote:
> Is there an example somewhere of accessing an MSAccess database from a SuSE
> or RedHat machine via perl DBI?
>
> Here is what I am presently using with ActiveState Perl to access the
> MSAccess database on windows:
>
> my $dbh = DBI->connect("dbi:ODBC:driver={Microsoft Access Driver
> (*.mdb)};dbq=c:\\database.mdb","Admin","") or die "$DBI::errstr\\n";
>
> What would I change to make this work on SuSE or RedHat?
>
> Thanks,
> Siegfried
>
>
>

Enabling ODBC support in Perl with Perl DBI and DBD::ODBC
http://www.easysoft.com/developer/languages/perl/dbi_dbd_odb c.html

Perl DBI/DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tu torial_part_1.html

Perl DBI/DBD::ODBC Tutorial Part 2 - Introduction to retrieving data
from your database.
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tu torial_part_2.html

Perl DBI/DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX or Linux to
Microsoft SQL Server
http://www.easysoft.com/developer/languages/perl/sql_server_ unix_tutorial.html
Although this is specifically for sql server it works for MS Access too.

Martin

Re: MSAccess from SQL

On Tue, 4 Apr 2006 23:40:27 -0600, Siegfried Heintze wrote:

>Is there an example somewhere of accessing an MSAccess database from a SuSE
>or RedHat machine via perl DBI?

An Access database is best served on a Windows PC. In my experience, you
don't even need to have Access installed for DBI to be able to work
with it. The Mdbtools thing is not yet fully reliable, in my experience.
(Blobs don't work well)

So what you can do, is employ DBI's Proxy Server on that PC.

Can you remotely access an ODBC database on a PC? I don't know, I4ve
never tried.

You don't want to run a database server on a PC? Then you definitely
don't want to be using MS Access.

--
Bart.

Re: MSAccess from SQL

Louis Gonzales schreef:

> As far as I know, MSAccess is an "application"
> and doesn't have the robustness of a Database Server

There is MsAccess and there is the Jet-Engine/Desktop-Engine.

See also the content and links on:
http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

--
Affijn, Ruud

"Gewoon is een tijger."