Perl ÁöµM¹ï¤å¦r¦³±j¤jªº³B²z¥\¯à¡A¦ýYn§¹¾ã¦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¡GPerl ¦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
- ²³æ©ö¥Î
- ¨ç¼Æ¥ÎªkÃþ¦ü ODBC API
- ¤ä´©¤j³¡¤Àªº ODBC ¨ç¼Æ
- ¥i¶Ç¦^§¹¾ãªº¿ù»~°T®§
- ª«¥ó¾É¦Vªº³]p
- ¹ï©ó¤£¦Pªº¸ê®Æ®w©M¥¥x¡A³£¦³«Ü°ªªº¥iÄâ©Ê¡]Portability¡^
DSN ¥»¨¤S¤À¨âºØÃþ§O¡G
- ¸ê®Æ®w¸ê°T¡]Ãþ§O¡B¦ì¸m¡BÀɦWµ¥¡^
- ¨Ï¥ÎªÌ±b¸¹
- ¨Ï¥ÎªÌ±K½X
- ³s±µ¦Ü¸ê®Æ®wªº¬ÛÃö¸ê°T
±ý²£¥Í·sªº System DSN¡A¥i¥Ñ¤U¦C¤è¦¡¨Ó¹F¦¨¡G
- ¨t²Î¸ê®Æ¨Ó·½¦WºÙ¡]User DSN¡^¡G¥u¤¹³\²£¥Í DSN ªº¨Ï¥ÎªÌ¨Ó¨Ï¥Î¡A¾A¥Î©óÓ¤H¨Ï¥Î
- ¨Ï¥ÎªÌ¸ê®Æ¨Ó·½¦WºÙ¡]System DSN¡^¡G¤¹³\ºô¸ô¤Wªº¨Ï¥ÎªÌ¨Ó¨Ï¥Î¡A¾A¥Î©ó CGI
¶}©l/³]©w/±±¨î¥x/ODBC¸ê®Æ¨Ó·½/¨t²Î¸ê®Æ¨Ó·½¦WºÙ/·s¼W ±µµÛ¿ï¨ú¸ê®Æ®wÃþ§O¤Î¦ì¸m¡A¨Ì§Ç§¹¦¨³]©w§Y¥i¡CY±ý²£¥Í·sªº User DSN¡A¥u»Ý±N¤Wzªº¡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
«Ø¥ß¸ê®Æ®w³sµ²ªº¨å«¬µ{¦¡½X¦p¤U¡G
- ¸g¥Ñ DSN ©M¸ê®Æ®w«Ø¥ß³sµ²
- °e¥X SQL ©R¥O
- ±µ¨ü¨Ã³B²z SQL ©R¥Oªºµ²ªG
- «½Æ¤Wz¨â¨BÆJ¡Aª½¨ì¦s¨ú¸ê®Æ§¹¦¨
- Ãö³¬¸ê®Æ®w³sµ²
$DSN = "Address"; $db = new Win32::ODBC($DSN); # «Ø¥ß¸ê®Æ®w³sµ² Y DSN ªº³]©w¥]§t¤F¨Ï¥ÎªÌªº±b¸¹©M±K½X¡A«h¤Wzµ{¦¡½XÀ³¥[¤J±b¸¹©M±K½X¡G$DSN = "DSN=Address;UID=Jang;PWD=my_passwd"; $db = new Win32::ODBC($DSN); # «Ø¥ß¸ê®Æ®w³sµ² ¦b¤Wz½d¨Ò¤¤¡A±b¸¹¬O¡uJang¡v¡A±K½X¬O¡umy_passwd¡v¡C¦b«Ø¥ß¸ê®Æ®w³sµ²ªº¹Lµ{¤¤¡AYµo¥Í¿ù»~¡A§ÚÌÀ³¸Ó¦L¥X¿ù»~°T®§¡A¦]¦¹¤Wzµ{¦¡½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¤Wzµ{¦¡¤ù¬q¤¤¡AY¸ê®Æ®w³sµ²¦¨¥\¡A«h·|¦^¶Ç¤@Óª«¥ó¡A¤Ï¤§¡A«h·|¦^¶Ç undef¡A¨Ã±N Win32::ODBC::Error() ¶Ç¦^ªº¿ù»~°T®§¦L¥X¡C³sµ²¸ê®Æ®w¦¨¥\«á¡A±µµÛ§ÚÌ´Nn°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¤Wzµ{¦¡½X¤¤¡AY SQL ©R¥O°õ¦æ¦¨¥\¡A«h $db->sql($sql) ·|¦^¶Ç undef¡A³o©M«ez³sµ²¸ê®Æ®wªº¦^¶Ç·N¸qè¦nÄAË¡CY 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 } ¤Wz 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¡CY¥u»Ýn¼ÆÓÄæ¦ì¡A§ÚÌ¥i§âÄæ¦ì¦WºÙ©ñ¤J $db->DataHash ªº¤Þ¼Æ¡A¨Ò¦p¡AY¥un¨ú±oÄæ¦ì Name ©M Positoin ªºÈ¡A¥i§ï¥Î¤U¦Cµ{¦¡½X¡G%Data = $db->DataHash("Name", "Position"); ¦b§¹¦¨¹ï©Ò¦³¸ê®Æªº³B²z«á¡A§O§Ñ¤Fn¨Ï¥Î¤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¥ç§Yn±N¦WºÙ¬° AddressBook ªº DSN ³]©w¦Ü¸ê®Æ®w example/test.mdb¡C¦¹½d¨Òµ{¦¡½X¡]listdb1.pl¡^¦p¤U¡GYn·s¼W¡B§R°£©Î×§ï¸ê®Æ®w¡A¥un´«¥Î¤£¦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¨º»ò´Nn¨Ï¥Î¨âÓ³æ¤Þ¸¹¡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¨ì¤Wzªº 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![]()