SQLite Perl
SQLite3 用Perl使用Perl DBI模塊，這是一個Perl編程語言的數據庫訪問模塊可以集成。它定義了一套方法，變數和約定，提供一個標準的數據庫接口。
下麵是簡單的步驟，在Linux /UNIX的機器上安裝DBI模塊：
$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625 $ perl Makefile.PL $ make $ make install
如果需要安裝DBI的SQLite 驅動，那麼它可以安裝如下：
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz $ cd DBD-SQLite-1.11 $ perl Makefile.PL $ make $ make install
DBI 接口API
以下是DBI重要程序，可以滿足你的工作要求從Perl程序操作SQLite數據庫。如果正在尋找一個更複雜的應用程序，那麼可以看看 Perl DBI 官方文檔。
| S.N. | API & 描述 |
|---|---|
| 1 |
DBI->connect($data_source, "", "", \%attr) Establishes a database connection, or session, to the requested $data_source. Returns a database handle object if the connection succeeds. Datasource has the form like : DBI:SQLite:dbname='test.db' SQLite is SQLite driver name and test.db is the name of SQLite database file. If the filename is given as ':memory:', it will create an in-memory database in RAM that lasts only for the duration of the session. If filename is actual device file name, then it attempts to open the database file by using its value. If no file by that name exists then a new database file by that name gets created. You keep second and third paramter as blank strings and last parameter is to pass various attributes as shown below in the example. |
| 2 |
$dbh->do($sql) This routine prepares and executes a single SQL statement. Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. Here $dbh is a handle returned by DBI->connect() call. |
| 3 |
$dbh->prepare($sql) This routine prepares a statement for later execution by the database engine and returns a reference to a statement handle object. |
| 4 |
$sth->execute() This routine performs whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs. A successful execute always returns true regardless of the number of rows affected. Here, $sth is a statement handle returned by $dbh->prepare($sql) call. |
| 5 |
$sth->fetchrow_array() This routine fetches the next row of data and returns it as a list containing the field values. Null fields are returned as undef values in the list. |
| 6 |
$DBI::err This is equivalent to $h->err, where $h is any of the handle types like $dbh, $sth, or $drh. This returns native database engine error code from the last driver method called. |
| 7 |
$DBI::errstr This is equivalent to $h->errstr, where $h is any of the handle types like $dbh, $sth, or $drh. This returns the native database engine error message from the last DBI method called. |
| 8 |
$dbh->disconnect() This routine closes a database connection previously opened by a call to DBI->connect(). |
連接到數據庫
下麵的Perl代碼顯示了如何連接到一個現有的數據庫。如果數據庫不存在，那麼它就會被創建，終於將返回一個數據庫對象。
#!/usr/bin/perl use DBI; use strict; my $driver = "SQLite"; my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully ";
現在，讓我們運行上麵的程序在當前目錄中，創建數據庫test.db。按要求可以改變你的路徑。上麵的代碼在sqlite.pl文件並執行它，如下圖所示。如果數據庫創建成功，則它會給下麵的消息：
$ chmod +x sqlite.pl $ ./sqlite.pl Open database successfully
創建表
下麵的Perl程序將使用以前創建的數據庫中創建一個表：
#!/usr/bin/perl use DBI; use strict; my $driver = "SQLite"; my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully "; my $stmt = qq(CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);); my $rv = $dbh->do($stmt); if($rv < 0){ print $DBI::errstr; } else { print "Table created successfully "; } $dbh->disconnect();
上述程序執行時，它會創建表公司test.db，它會顯示以下消息：
Opened database successfully Table created successfully
注意：任何操作情況下，看到下麵的錯誤：
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
在這種情況下，打開 dbdimp.c 的文件可在DBD-SQLite的安裝，並找出sqlite3_prepare()函數，改變它的第三個參數為-1，而不是0。最後安裝DBD:: SQLite的使用make 和make install 來解決問題。
INSERT 操作
Perl程序，顯示我們如何能夠創造COMPANY在上麵的例子中創建表中的記錄：
#!/usr/bin/perl use DBI; use strict; my $driver = "SQLite"; my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully "; my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );); $rv = $dbh->do($stmt) or die $DBI::errstr; print "Records created successfully "; $dbh->disconnect();
上述程序執行時，它會創建COMPANY表中的記錄，並顯示以下兩行：
Opened database successfully Records created successfully
SELECT 操作
Perl 程序，表明我們如何能夠獲取並顯示COMPANY 表在上麵的例子中創建表的記錄：
#!/usr/bin/perl use DBI; use strict; my $driver = "SQLite"; my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully "; my $stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr; if($rv < 0){ print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . " "; print "NAME = ". $row[1] ." "; print "ADDRESS = ". $row[2] ." "; print "SALARY = ". $row[3] ." "; } print "Operation done successfully "; $dbh->disconnect();
當上述程序執行時，它會產生以下結果：
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
UPDATE 操作
Perl代碼顯示如何，我們可以使用UPDATE語句來更新任何記錄，然後獲取並顯示更新的記錄，從COMPANY 表：
#!/usr/bin/perl use DBI; use strict; my $driver = "SQLite"; my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully "; my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ){ print $DBI::errstr; }else{ print "Total number of rows updated : $rv "; } $stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr; if($rv < 0){ print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . " "; print "NAME = ". $row[1] ." "; print "ADDRESS = ". $row[2] ." "; print "SALARY = ". $row[3] ." "; } print "Operation done successfully "; $dbh->disconnect();
當上述程序執行時，它會產生以下結果：
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
DELETE 操作
Perl代碼表明我們如何能夠使用DELETE語句刪除任何記錄，然後獲取並顯示剩餘的記錄COMPANY 表：
#!/usr/bin/perl use DBI; use strict; my $driver = "SQLite"; my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database"; my $userid = ""; my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr; print "Opened database successfully "; my $stmt = qq(DELETE from COMPANY where ID=2;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ){ print $DBI::errstr; }else{ print "Total number of rows deleted : $rv "; } $stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr; if($rv < 0){ print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . " "; print "NAME = ". $row[1] ." "; print "ADDRESS = ". $row[2] ." "; print "SALARY = ". $row[3] ." "; } print "Operation done successfully "; $dbh->disconnect();
當上述程序執行時，它會產生以下結果：
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
