kwabbernoot
Cacti User
Joined: 13 Oct 2003
Posts: 99
Location: Zottegem, Belgium
|
 Posted: Fri Aug 27, 2004 3:40 am Post subject: [HOWTO] Interrogating data stored into a MS SQL DB from Unix |
 |
|
Hi,
Maybe most of you already know this information, if so good for you, but I can imagine that there are others.
Because of the fact that I needed this functionality and lost some great deal of time on my quest for information it could come in handy for the guys/girls that do not yet found it within the huge world of the Internet.
Using Cacti on *nix ?
Wanting to graph information stored into an MS SQL Db ?
Using Perl ?
Then this Howto could come in handy for you.
This method used a direct connection to the Db, it does not make usage of ODBC stuff.
Requirements:
- SYBASE environment variable
- Freetds software (http://www.freetds.org/)
- Perl modules "DBI" and "DBD::Sybase"
To proceed do the following:
1. Create the SYBASE and TDSVER environment variable on your */nix system. The TDSVER variable is not really needed, it just specifes a default
| Code: |
export SYBASE=/your_freetds_install_dir
export TDSVER=4.2
|
2. Compile and install the freetds software
3. After installation edit the "freetds.conf" file and add your MS SQL Server configuration
| Code: | *this is for MS SQL 2000*
[Descriptive_name]
host = MLS_SQL_Server_IP_Address
port = TCP_Port_on_which_server_runs (Default = 1433)
tds version = 8.0
|
4. Install the perl module "DBD::Sybase", you have to make sure that the SYBASE environment value is defined while installing this module.
Remark: If using Webmin to install the module define the SYBASE variable in the "Webmin Configuration/Operating System and Environment"
5. Develop your own Perl script that connects to the MS SQL DB and retrieves the Data
To connect to the DB:
| Code: |
use DBI;
###############
# Open MS SQL Db
# Use freetds Configuration to interrogate remote MS SQL Db
$ENV{'SYBASE'} = '/your_freetds_install_dir';
my ($user_id,$pass,$dsh,$sth,$dbh);
$dsh = 'dbi:Sybase:server=FreeTDS_config_descriptive_name';
$user_id = 'your_MS_SQL_UserID';
$pass = 'your_MS_SQL_password';
# Connect to the MS SQL database
$dbh = DBI->connect($dsh, $user_id,$pass);
|
To retrieve Data from the MS SQL Server
| Code: |
$SqlSelect = "Your_select_query";
$dbh->do('use your_MS_SQL_table');
$sth = $dbh->prepare($SqlSelect) || die print $DBI::errstr."\n";
$sth->execute;
my @row;
my $i;
while (@row = $sth->fetchrow_array)
{ for $i (0..$#row)
{ print "$row[$i]\t";
}
print "\n";
}
######################
# Close and disconnect from MS SQL databases
$sth->finish();
$dbh->disconnect();
|
Hope this helps,
Kwabbernoot |
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
