I'm not quite sure you're understanding what form the output will be in when
it gets into your script, but one question at a time:
The first question will vary slightly from dbd to dbd. However, in all cases
you will need to install DBI. After DBI, install the DBD for the the type of
connection you are going to be using (Example, for ODBC you need DBD::ODBC,
you can use DBD::MySQL for MySQL. you can use either DBD::ODBC or a DBD made
specifically for a certain DBLibrary for pretty much any DBMS.) These are
all available on CPAN, or if you use PPM you can do:
PPM install DBI
PPM install DBD::ODBC
or PPM install DBD::MySQL
or whatever you are using.
Now, connecting, I'll give two examples. Connection strings differ a little
between DBD's, and are usually available in perldoc dbd after you have
installed your dbd.
MySQL:
#connect to calltrac database
use DBI;
my $database_name = 'intra_data';
my $location = 'localhost';
my $port_num = '3306'; # This is default for mysql
# define the location of the sql server.
my $database = "DBI:mysql:$database_name:$location:$port_num";
my $db_user = "sa";
my $db_password = "sa";
# connect to the sql server.
my $dbh = DBI->connect($database,$db_user,$db_password);
my $sql_statement = "SELECT call_no FROM calltrac ORDER BY call_no DESC
LIMIT 1";
############## more on how to use the output later.
For DBD::ODBC it would be like this:
use DBI;
my $dsn = "northwind";
# define the location of the sql server.
my $database = "DBI:ODBC:$dsn";
my $db_user = "sa";
my $db_password = "";
# connect to the sql server.
my $dbh = DBI->connect($database,$db_user,$db_password, {AutoCommit=>0});
in either case, you have to have the security account set up in your dbms
before you can connect. Also, parameters like AutoCommit can be see in
perldoc DBI.
Now for how to get the output and work with it (including your split with
you can get details on using perldoc -f split). There are several ways to
get the output, but the fastest and least memory intensive is to use a
bind_columns. This is something you will do with your statement handle. Your
statement handle will be created by dereferencing a method in the database
handle you created when you connected. Untested example:
my $select = qq{SELECT CustomerID, Name FROM Customers};
my $selecth = $dbh->prepare($select); #now your statement handle is
$selecth
open (outfile, ">/somedirectory/somefile.txt"); # open your file for input
$selecth->execute() || die "Can't execute\n$select\n$dbh::errstr";
######## now let's get the output into our script
my ($row, $custid, $custname);
$selecth->bind_columns(undef,\($custid, $custname));
while ($row = $selecth->fetchrow_arrayref) {
print outfile qq{Customer Name is $custname\t\tCustomer ID is $custid};
}
###############
now you can open /somedirectory/somefile.txt and view the output. However,
if you're writing to an outfile just for paging through your data, there
might be a better way, depending on your DBMS. You'll have to check its docs
for specifics on that, but a couple of examples from different dbms's are
the limit statement like I used in the MySQL connect example, or with MS SQL
7 or higher, you might imbed something like:
SELECT TOP 10 Somecolumn FROM Sometable
WHERE Someothercolumn NOT IN
(SELECT TOP 100 Someothercolumn FROM Sometable
ORDER BY Someothercolumn)
ORDER BY Someothercolumn
(returns rows number 101-110 of the whole result)
In Sybase, or older versions of MS you could set rowcout. Oracle uses a
virtual count column... Those examples will take a load off your network by
controlling the number of rows the DBMS gives you.
Now, I'm not sure how split will fit into what you're asking but just to
give an example:
my $example = qq{this, is, the, way, I, want, to, give, an, example};
my @array = split /, /, $example;
print "$_\n" foreach (@array);
Hope this gets you going.
Steve H.
-----Original Message-----
From: Messervy, Joe M [mailto:[EMAIL PROTECTED]]
Sent: Saturday, July 07, 2001 7:57 AM
To: [EMAIL PROTECTED]
Subject: connecting to sql database
Hello,
I have been tasked with writing a script that will prompt the user for a
ticket number,some other info, and a sql query. The script should create a
directory based on ticket number (easy enough) create files in the directory
that contains info based on second query, and then and here is where I am
stuck...
The script must connect to the sqldatabase (ie sqlplus [EMAIL PROTECTED]
), turn on spooling, then run the query that was entered by user in step 1,
disconnect from the database and continue. Any clues on how this can be
accomplished?
One last question while I am here, I am looking to split the output of the
above query into separate files containing 10000 entry lines. This can be
done using the unix split command "split -l 10000 fallout.txt" Looking
through the info I have using the perl split there is no format like this.
I assume though,. that this can be done...what would the format be?
Thanks for any help in advance.
