Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ODBC Connection

Posted on 2003-03-04
5
Medium Priority
?
263 Views
Last Modified: 2013-12-25
Hello all,
Im a newbie to PERL scripting. Im just trying to exectue some simple PERL scripts. The question is how to connect to a database using an ODBC connection (say i want to insert a row into an MS access table). I have not specifically installed PERL or APACHE . it came along with ORACLE installation and it is working fine. Do i need to install ActivePerl inorder for me to work with ODBC(I came across this comment on a website)
Secondly I tried to install ActivePerl and during the installation process it is asking for me to

Add PERL to the PATH Environment Variable
Create PERL file extension association

Im not sure what this is, i fear if i click yes, my existing connection with the Apache Web Server may lose.
Finally Im using Windows 2000, will it make a difference for any of the above conditions
Thanks
0
Comment
Question by:sktripuraneni
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8068207
First off, if you do add PERL to the path and add the extension that should *NOT* alter your existing web-connection.

Secondly, to connect using ODBC you have to:
1. Set up the ODBC connection (if using MS-Access make it a system DSN)
2. install DBI
If you use ActivePerl go to a dos prompt and type:
ppm install DBI
then
ppm install DBD-ODBC
and the two modules you need will be installed
3. write scripts to use DBI
For example to access an MS-Access table your script might look something like this:

use DBI;
use strict;

my $user = '';
my $pass = '';
my $connect = 'dbi:ODBC:testDB';

my $dbh = DBI->connect($connect, $user, $pass);
my $sql = 'SELECT employee FROM employees';
my $sth = $dbh->prepare($sql);

$sth->execute();

while (my $employee = $sth->fetchrow_array()) {
    print "$employee\n";
    }
0
 

Author Comment

by:sktripuraneni
ID: 8072078
Wow this works great, I have just another question, in the query you have selected just one field how to get data if there are multiple fields. That is if the query is
"Select * from employees" say employees has emp_id, emp_name. now in the while loop how to split it up or in other words capture in 2 different variables.

Finally will there be any difference if i move between Unix and Windows. Will the same code work for both.
0
 
LVL 2

Expert Comment

by:Itatsumaki
ID: 8074086
# Sure, just rewrite the query:
my $sql = 'SELECT employee, employee_name FROM employees';
# And call the fetchrow_array() in array context
while (my @row = $sth->fetchrow_array()) {
      print $row[0], "\t",  # employee
            $row[1], "\n";  # employee_name
      }

########################
# Thus putting it all together we get:
# Let the code begin:
use DBI;
use strict;

my $user = '';
my $pass = '';
my $connect = 'dbi:ODBC:testDB';

my $dbh = DBI->connect($connect, $user, $pass);
my $sql = 'SELECT employee, employee_name FROM employees';
my $sth = $dbh->prepare($sql);

$sth->execute();

while (my @row = $sth->fetchrow_array()) {
      print $row[0], "\t",  # employee
            $row[1], "\n";  # employee_name
   }
0
 
LVL 2

Accepted Solution

by:
Itatsumaki earned 200 total points
ID: 8077111
Oh, and yeah it will work *identically* between unix and windows, as long as you have the ODBC connectivity set up right.  That isn't trivial (and you're better off with a DBI mailing list on that than here, btw) at all.  On the other hand, the code is also pretty much database-independent so it should work virtually the same on mySQL, Oracle, MS-SQL, Access... whatever, as long as you set up ODBC for each DB.
0
 

Author Comment

by:sktripuraneni
ID: 8080559
Thanks Itstsumaki, Its really great and your code was perfect
Thanks for your help
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
This article will show, step by step, how to integrate R code into a R Sweave document
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question