Wednesday, December 14, 2005

 

Sybase and Perl

Question: I've got a Perl script that collects information every night that I'd like to store in the database. What is the simplest way to do that?

Answer: Well that depends which is easiest for you, there are many ways.

The most pedestrian way using Perl:
1. Output your SQL to a file.
2. Use "system" (or back-ticks) to run a command on the shell, and within:
3. Run "isql" to connect to the database, with the file you created as the input file.

If you wanted to be more interactive, you could re-direct the output a file, and read it in, and parse the results.

But there is no reason to do it that way.

Instead, you can use the Perl DBI module. In its simplest form, here is what you could do:

use DBI;

$dbh->connect('DBI:Sybase:server=servername;database=dbname', 'user', 'password');
$dbh->do($sql);
$dbh->disconnect();

To be more interactive, you'd use $dbh->fetchrow. Here is more on the Sybase DBI from Perl: http://www.perl.com/pub/a/1999/10/DBI.html

There is one more option I'd like to highlight, SybPerl. Specifically, the CTlib module. In its simplest form, our example now looks like this:

use Sybase::CTLib;

$dbh = new Sybase::CTlib 'user', 'password', 'SERVER';
$dbh->ct_execute($sql);

Here, Not only could you use $dbh->ct_results along with $dbh->ct_fetch to be more interactive, but, better yet:
@rows = $dbh->ct_sql($sql);

Michael Peppler's page has more information on SybPerl: http://www.peppler.org/~mpeppler/

This page is powered by Blogger. Isn't yours?