Stephane Perennes (71 91, Mascotte)
On 8 Feb 2003, Jeyaraj wrote: > I am new to this environment.... I don't know how to establish connection > to SQL Server from Linux machine using Perl.... Pls help me in this > issue.... > > Expecting your favourable answers.... > Thanx in advance..... > > Regards, > Jeyaraj > Your question is answered in many FAQs, see : http://perl.about.com/gi/dynamic/offsite.htm?site=http%3A%2F%2Fwww.extropia.com%2Ftutorials%2Fsql%2Fdbi_api.html but it's not a very good tutorial i would even say it's bad .. Since i use now php, i have only old examples, at that time i was using perl just intuitively (i still have to read an manual one day ) use DBI; $dsn = "DBI:$driver:host=$host;port=$port"; This more or less tell that you connect to a mysql database on host $host and port $port, the port is usually the standard one for mysqld. Host can be either the IP or the name of the host. $dbh = DBI->connect($dsn, $user, $password) ; You can probably specify here the database too, here it's omitted This establishes the connection with user and password. Indeed it creates a Data Base handle that you will use to talk to the database. So yu have 2 steps : $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; $dbh = DBI->connect($dsn, $user, $password); That can be made as one my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host;port=$port", $user, $password,); database=$database can be omittted. ans usually host and port too (assuming that host is localhost and the port mysqld default port) Now you have the DBH (data base handle) test if it's ok if (! ($dbh)) { SQLERROR("Connection de $user depuis $host refuse par le serveur"); return ( (0>1)); } olog("Nouvelle connection Reussie"); return(1); $dbh has some error checking functions : err(), state() , errstr() $dbh->errstr retunr the sql error text, err() is an error code, state() must be documented somewhere. Now you can use $dbh to perform a request simply call some crappy function like EASYDO($req) sub EASYDO { my $req= shift; if ( (!dbh) || (!defined($dbh)) ) { treat the error , } $u= $dbh->do($req) ; if ($u) { it's ok } else { trouble, dbh->errstr contains an error message ); } In the case before you don't need the result of the request, you just need to be sure that the request was done. You can also prepare the request before doing it, it then works like Mysql, you just need to take some care about special characters when writing requests. You do something like EASYDO("use MYDatabase"); unless you specified the database when connecting. $req="Select ID Name from Peoples " $sth = $dbh->prepare($req); $sth->execute then $sth contains the request result (you can test that things are ok by testing $sth) { my $ref; while( $ref = $sth->fetchrow_arrayref ) { print " ID is $$ref[0] Name is is $$ref[1]\n " } } Here $sth->fetchrow_arrayref return the next row of the resulting table as an array. $ref refers to it. You can also get associative array, and also the name of the resulting table fields, using fetchrow_hashref() $sth->execute(); while (my $ref = $sth->fetchrow_hashref()) { print "Found a row: id = $ref->{'ID'}, name = $ref->{'NAME'}\n"; } There is much more, see CPAN DBI module reference. $sth->rows; (numer of rows returned or affected) $sth->{'NUM_OF_FIELDS'}; (number of fields is the resulting table) $sth->{"Names'} Names of the columns of the resulting table.
