Perl Database Example Using ODBC Data Source Name DSN

The aim of this Perl tutorial is to use Perl to read and write to a database. In this example the database connection is an ODBC DSN attached to a Microsoft SQL Database. However, this can be used for any database. This perl example will open a connection to a database and create SQL queries to fetch and insert data.

First lets have a look at the example. It's really self explanatory.
 1. use DBI;
 2. 
 3. # Connect to database
 4. my $dbh = DBI->connect("dbi:ODBC:mydsn", "sa", "Admin-123",
 5.             {RaiseError => 1})
 6.             or die "Couldn't connect to database: " . DBI->errstr;
 7. 
 8. #Retrieve data
 9. my $sth = $dbh->prepare(<<SQL);
10.     select * from testtable
11.     where id < 1000
12. SQL
13. $sth->execute;
14. while (my @row = $sth->fetchrow_array()) {
15.    my ($id, $name, $address) = @row;
16.    print "$id $name $address\n";
17. }
18. 
19. #Insert data
20. $sth = $dbh->prepare("insert into callexpert (starttime, url) values( CONVERT(datetime, ?), ?)");
21. $sth->execute("08.09.2009  6:00:00 AM", "www.google.com");
22. $sth->execute("08.09.2009  6:24:00 AM", "www.facebook.com");
23. 
24. #Finish up    
25. $sth->finish();
26. $dbh->disconnect;
Hide line numbers

Line 3 We connect to the Data Source Name that is pointing to the SQL Server. For MySQL the connection url will look something like "dbi:mysql:MyDatabase;host=www.myhost.com"

Lines 8-17 Read data from testtable and display it on the screan

Lines 19-22 Insert some data into the callexpert table. Here we are using the MS SQL CONVERT function to convert a string into a datetime format.

2 comments:

vitamin k said...

The jdbc API was designed to keep simple things simple. This means that the JDBC makes everyday database tasks easy. This trail walks you through examples of using JDBC to execute common SQL statements, and perform other objectives common to database applications

Term Papers said...

I have been visiting various blogs for my term papers writing research. I have found your blog to be quite useful. Keep updating your blog with valuable information... Regards