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.