6-1 資æ?庫整??

Perl ÁöµM¹ï¤å¦r¦³±j¤jªº³B²z¥\¯à¡A¦ý­Y­n§¹¾ã¦aÀx¦s¤å¦r¡A³Ì¤è«Kªº¤èªkÁÙ¬O±N¤å¦r©ñ¤J¸ê®Æ®w¡]Databases¡^¡C´X¥G©Ò¦³ªº UNIX ¨t²Î³£´£¨Ñ¤@®MºÙ¬° DBM (Database Management) ªº¼Ð·Çµ{¦¡®w¡A¥]§t¤F¹ï¸ê®Æ®wªº«Ø¥ß¡BŪ¨ú¡B¼g¤J¡B§R°£µ¥¥\¯à¡A¦ý¨ä¸ê®Æ®w®æ¦¡¬O UNIX ±M¥Î®æ¦¡¡A¨ÃµLªk©M¤@¯ë¸ê®Æ®w¡]¦p Oracle ©Î MS Access¡^·¾³q¡CPerl ¥»¨­¤]¤ä´© DBM µ{¦¡®w¡A¦]¦¹¤]¥i¥H«Ø¥ß¦Û¤v±M¥Îªº¸ê®Æ®w¡A¦ý¥Ñ©ó®æ¦¡©M¤@¯ë Win32 ¸ê®Æ®w¡]¦p Access¡BSQL Server¡BFoxpro¡^µLªk·¾³q¡A©Ò¥H¸û¤£¤è«K¡C¦]¦¹¥»¸`­«ÂI±N¥H Perl ¦p¦ó¦s¨ú Win32 ªº¸ê®Æ®w¡]¦p Microsoft Access¡BSQL Serverµ¥¡^¬°¥D¡C

°ò¥»¤W¡APerl ¬O¸g¥Ñ ODBC (Open Database Connectivity) ªº¤¶­±©M¸ê®Æ®w·¾³q¡CODBC ¬O¥Ñ¼Æ­Ó¤j«¬¸ê®Æ®w¼t°Ó©Ò­q©wªº¼Ð·Ç¡A¨ä¥\¯à´N¬O´£¨Ñ¤@­Ó§¹¾ãªºÀ³¥Îµ{¦¡¤¶­±¡]API¡AApplication Program Interface¡^¡AÅý¨ä¥LÀ³¥Îµ{¦¡©Î³nÅé¯à°÷¸g¥Ñ¤@­Pªº¤¶­±¨Ó©M¥ô¦ó¥­¥x¡B¥ô¦ó¤ä´© ODBC ªº¸ê®Æ®w·¾³q¡C¨Æ¹ê¤W¡APerl Áٻݭn¤@®M¼Ò²Õ¡A¤~¯à©M ODBC ·¾³q¡A¦b Win32 ¥­¥x¤W¡A³Ì±`¥Îªº¼Ò²Õ¬O Win32::ODBC¡A³o¬O¥Ñ Dave Roth ©Ò³]­pªº¼Ò²Õ¡A±MªùÅý Perl ¦s¨ú Win32 ªº¸ê®Æ®w¡A¬ÛÃöºô­¶¥i¨£¡G

http://www.roth.net/odbc/
Perlµ{¦¡½X¡BWin32::ODBC ¼Ò²Õ¡A¥H¤Î¸ê®Æ®w ODBC Driver ªºÃö«Y¥i¥Î¥k¹Ï¨Óªí¥Ü¡A¨ä¤¤ Perl ¥ý©I¥s Win32::ODBC ¤¤ªº¦UºØ¤èªk¨Ó¡AWin32 ¦A±N¬ÛÃöªº SQL (Strucutre Query Language) ©R¥O°e¨ì¸ê®Æ®wªº ODBC Drivers Win32::ODBC ¼Ò²Õ¨ã¦³¤U¦CÀuÂI¡G Perl ¦b©M¸ê®Æ®w¶i¦æ·¾³q¤§«e¡A§Ú­Ì¥²¶·¥ý³]©w¡u¸ê®Æ¨Ó·½¦WºÙ¡v¡]Data Source Name¡A²ºÙ DSN¡^¡A³o¬O¤@­Ó³s±µ¦Ü¸ê®Æ®wªº¥N¸¹¡A¥]§t¤U¦C¸ê°T¡G DSN ¥»¨­¤S¤À¨âºØÃþ§O¡G ±ý²£¥Í·sªº System DSN¡A¥i¥Ñ¤U¦C¤è¦¡¨Ó¹F¦¨¡G
¶}©l/³]©w/±±¨î¥x/ODBC¸ê®Æ¨Ó·½/¨t²Î¸ê®Æ¨Ó·½¦WºÙ/·s¼W
±µµÛ¿ï¨ú¸ê®Æ®wÃþ§O¤Î¦ì¸m¡A¨Ì§Ç§¹¦¨³]©w§Y¥i¡C­Y±ý²£¥Í·sªº User DSN¡A¥u»Ý±N¤W­zªº¡u¨t²Î¸ê®Æ¨Ó·½¦WºÙ¡v§ï¦¨¡u¨Ï¥ÎªÌ¸ê®Æ¨Ó·½¦WºÙ¡v§Y¥i¡C

¤@¥¹³]©w DSN «á¡A§Y¥i¹ï¸ê®Æ®w¶i¦æÅª¼gªº°Ê§@¡C¤@¯ë¦Ó¨¥¡APerl ¹ï¸ê®Æ®wŪ¼gªº°Ê§@¥i¥H»¡©ú¦p¤U¡G

  1. ¸g¥Ñ DSN ©M¸ê®Æ®w«Ø¥ß³sµ²
  2. °e¥X SQL ©R¥O
  3. ±µ¨ü¨Ã³B²z SQL ©R¥Oªºµ²ªG
  4. ­«½Æ¤W­z¨â¨BÆJ¡Aª½¨ì¦s¨ú¸ê®Æ§¹¦¨
  5. Ãö³¬¸ê®Æ®w³sµ²
«Ø¥ß¸ê®Æ®w³sµ²ªº¨å«¬µ{¦¡½X¦p¤U¡G $DSN = "Address"; $db = new Win32::ODBC($DSN); # «Ø¥ß¸ê®Æ®w³sµ² ­Y DSN ªº³]©w¥]§t¤F¨Ï¥ÎªÌªº±b¸¹©M±K½X¡A«h¤W­zµ{¦¡½XÀ³¥[¤J±b¸¹©M±K½X¡G $DSN = "DSN=Address;UID=Jang;PWD=my_passwd"; $db = new Win32::ODBC($DSN); # «Ø¥ß¸ê®Æ®w³sµ² ¦b¤W­z½d¨Ò¤¤¡A±b¸¹¬O¡uJang¡v¡A±K½X¬O¡umy_passwd¡v¡C

¦b«Ø¥ß¸ê®Æ®w³sµ²ªº¹Lµ{¤¤¡A­Yµo¥Í¿ù»~¡A§Ú­ÌÀ³¸Ó¦L¥X¿ù»~°T®§¡A¦]¦¹¤W­zµ{¦¡½Xªº²Ä¤G¦C¥i­×§ï¦¨¸û§¹¾ãªº¼gªk¡A¦p¤U¡G

if (!($db = new Win32::ODBC($dsn))) { print "Error in opening DSN \"$dsn\"!\n"; print "Reason: " . Win32::ODBC::Error(). "\n"; exit; } ½Ðª`·N¦b¤W­zµ{¦¡¤ù¬q¤¤¡A­Y¸ê®Æ®w³sµ²¦¨¥\¡A«h·|¦^¶Ç¤@­Óª«¥ó¡A¤Ï¤§¡A«h·|¦^¶Ç undef¡A¨Ã±N Win32::ODBC::Error() ¶Ç¦^ªº¿ù»~°T®§¦L¥X¡C³sµ²¸ê®Æ®w¦¨¥\«á¡A±µµÛ§Ú­Ì´N­n°e¤J SQL ©R¥O¡A¥i¥Î sql() ¤èªk¨Ó¹F¦¨¡A¨å«¬µ{¦¡½X¦p¤U¡G $sql = "SELECT * FROM table1"; # ¿ï¥X¸ê®Æªí table1 ¤¤ªº©Ò¦³Äæ¦ì if ($db->Sql($sql)) { print "Error in SQL query: \"$sql\"!\n"; print "Reason: " . $db->Error() . "\n"; $db->Close(); exit; } ½Ðª`·N¦b¤W­zµ{¦¡½X¤¤¡A­Y SQL ©R¥O°õ¦æ¦¨¥\¡A«h $db->sql($sql) ·|¦^¶Ç undef¡A³o©M«e­z³sµ²¸ê®Æ®wªº¦^¶Ç·N¸q­è¦nÄA­Ë¡C­Y SQL ©R¥O°õ¦æ®Éµo¥Í¥ô¦ó¿ù»~¡A$db->Error() ·|¶Ç¦^¬ÛÃöªº¿ù»~°T®§¡C

¦¨¥\°e¤J SQL ©R¥O«á¡A±µµÛ§Ú­Ì­n±N¦^¶Ç¸ê®Æ¤@µ§¤@µ§¶i¦æ³B²z¡A¨å«¬µ{¦¡½X¦p¤U¡G

while ($db->FetchRow()) { %Data = $db->DataHash(); ¡K process result ¡K } ¤W­z while-°j°é·|¤@¦A°õ¦æ¡A¨C¦¸¦¨¥\¨ú±o¤@µ§¸ê®Æ®É¡A$db->FetchRow() §Y¶Ç¦^ 1¡A¦p¦¹¨Ì§Ç³B²z¸ê®Æ¡Aª½¨ì©Ò¦³¸ê®Æ³£³Q³B²z§¹²¦¬°¤î¡C¦b¨C¦¸°j°é¤¤¡A%Data ·|¨ú±o¨Cµ§¸ê®Æ¡A¨Ã¥H hash ªº¸ê®Æ«¬ºA¨ÓÀx¦s¦¹µ§¸ê®Æ¡A¨ä¤¤¸ê®Æ®wªºÄæ¦ì¦WºÙ´NÅܦ¨ %Data ªº key¡A¦ÓÄæ¦ì­È´NÅܦ¨ %Data ªº value¡C­Y¥u»Ý­n¼Æ­ÓÄæ¦ì¡A§Ú­Ì¥i§âÄæ¦ì¦WºÙ©ñ¤J $db->DataHash ªº¤Þ¼Æ¡A¨Ò¦p¡A­Y¥u­n¨ú±oÄæ¦ì Name ©M Positoin ªº­È¡A¥i§ï¥Î¤U¦Cµ{¦¡½X¡G %Data = $db->DataHash("Name", "Position"); ¦b§¹¦¨¹ï©Ò¦³¸ê®Æªº³B²z«á¡A§O§Ñ¤F­n¨Ï¥Î¤U¦Cµ{¦¡½X¨Ó²M°£©M¸ê®Æ®wªº³sµ²¡G $db->Close(); ¥H¤U¬O¤@­Ó§¹¾ãªº¨Ò¤l¡A¦¹½d¨Ò·|±N example/test.mdb ªº¸ê®Æ®w³vµ§¦L¥X¡A¦b¹Á¸Õ¦¹½d¨Ò¤§«e¡A½Ð°O±o¥ý­n³]©w DSN¡A¥ç§Y­n±N¦WºÙ¬° AddressBook ªº DSN ³]©w¦Ü¸ê®Æ®w example/test.mdb¡C¦¹½d¨Òµ{¦¡½X¡]listdb1.pl¡^¦p¤U¡G

­ì©lÀÉ¡]listdb1.pl¡^¡G¡]¦Ç¦â°Ï°ì«ö¨â¤U§Y¥i«þ¨©¡^
# Display a table in a database using PERL ODBC module

use Win32::ODBC;

$dsn = "AddressBook";
$table = "Namelist";

# ====== Check if the database can be opened correctly
if (!($db = new Win32::ODBC($dsn))) {
	print "Error in opening DSN \"$dsn\"!\n";
	print "Error: " . Win32::ODBC::Error(). "\n";
	exit;
}

# ====== Select all fields from the given table
$sql = "select * from $table;";
$sql = "select * from $table order by Category;";

# ====== Check if the SQL query is correct
if ($db->Sql($sql)) {
	print "Error in SQL query: \"$sql\"!\n";
	print "Error: " . $db->Error() . "\n";
	$db->Close();
	exit;
}

# ====== Print field data
print "Content-type: text/html\n\n";
print "<html><body><pre>\n";
$count = 1;
@FieldNames = $db->FieldNames();
while ($db->FetchRow()) {
	print "Record $count\n";
#	($newsSiteID, $classID) = $db->Data("newsSiteID", "classID");
#	undef %Data;
	%Data = $db->DataHash();
	foreach $x (@FieldNames) {
		print "$x: $Data{$x}\n";
	}
	print "\n";
	$count++;
}
$db->Close();
print "</pre></body></html>\n";

­Y­n·s¼W¡B§R°£©Î­×§ï¸ê®Æ®w¡A¥u­n´«¥Î¤£¦Pªº SQL ©R¥O´N¥i¥H¤F¡A½Ð°Ñ¾\¦³Ãö SQL ªº³¹¸`¡C

¦b°e¤J SQL ©R¥O®É¡A¦r¦êÄæ¦ì­È­n¥Î³æ¤Þ¸¹¨í¦í¡A¨Ò¦p¡G

$sql = "select * from table1 where NAME = 'Roger'"; ¦ý¦pªG¬O¼Æ­ÈÄæ¦ì¡A´N¤£»Ý­n¨Ï¥Î³æ¤Þ¸¹¡A¨Ò¦p¡G $sql = "select * from table1 where AGE = 38"; ¦pªG¬O¦r¦êÄæ¦ì¡A¦Ó¥B¨äÄæ¦ì­ÈÁÙ¥]§t³æ¤Þ¸¹¡A¨º»ò´N­n¨Ï¥Î¨â­Ó³æ¤Þ¸¹¡A¨Ò¦p¡G $sql = "select * from table1 where NAME = 'Roger''s fish'"; ¦b¤W¨Ò¤¤¡AÄæ¦ì­È¬O¡uRoger's fish¡v¡A±N³æ¤Þ¸¹­«½Æ¤@¦¸«á¡A´N±o¨ì¤W­zªº SQL ©R¥O¡C

¥t¤@­Ó±`¸I¨ìªº°ÝÃD¬O¡G¦p¦ó¨Ï¥Î¡u|¡v©óÄæ¦ì­È¡H¦]¬°¡u|¡v¦b SQL ¸Ì­±¦³¯S®íªº·N¸q¡A¦]¦¹¤U­±¬O¤@­Ó¿ù»~ªº¼gªk¡G

$sql = "select * from table1 where NAME = 'P|Q'"; ¥¿½Tªº¼gªk¡A«h¬O¨Ï¥Î¡u|¡vªº ASCII ¤º½X¡A±N 'P|Q' Âà´«¦¨ 'P' & chr(124) & 'Q'¡A©Ò¥H¥¿½T¼gªk¦p¤U¡G $sql = "select * from table1 where NAME = 'P' & chr(124) & 'Q'";
Perl