?
Solved

ODBC Connection

Posted on 2003-03-04
5
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

This tutorial will discuss the log-in process using WhizBase. In this article I assume you already know HTML. I will write the code using WhizBase Server Pages, so you need to know some basics in WBSP (you might look at some of my other articles abo…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

765 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