通过使用脚本语言和编译型系统语言(例如 C),将数据库集成到Linux应用就可能相当容易。可免费获得的MySQL(在GNU Public License下发行)数据库提供了一系列复杂的SQL功能,并易于集成到应用中。MySQL是快速、多线程的,并支持ANSI和ODBC SQL标准。加上第三方软件,MySQL就支持用于事务处理应用的事务安全的表。
要构建集成数据库访问的网站,需要编写CGI脚本来根据数据库状态生成动态结果。Web服务器启动CGI脚本,然后将适当格式化的HTML输出到它们的标准输出流中。Web服务器捕捉到HTML后将它发送回客户机,如同请求是对静态HTML页面进行的那样。 在生成 HTML 的过程中,脚本可以修改数据库,也可以查询并将结果合并到它们的输出中。
#This code prints out all products in the database # that are below a specified price (assumed to have been determined # beforehand, and stored in the variable targetPrice) # The output is in HTML table format, appropriate for CGI output
#load the SQL shared object library. the Tcl interpreter could also #have been compiled with the library, making this line unnecessary load /home/aroetter/tcl-sql/sql.so
#these are well defined beforehand, or they could #be passed into the script set DBNAME "clientWebSite"; set TBLNAME "products"; set DBHOST "backend.company.com" set DBUSER "mysqluser" set DBPASSWD "abigsecret"
set targetPrice 200;
#connect to the database set handle [sql connect $DBHOST $DBUSER $DBPASSWD] sql selectdb $handle $DBNAME ;# get test database
#run a query using the specified sql code sql query $handle "select * from $TBLNAME where price <= $targetPrice"
#print out html table header puts "
" puts "
Product Id
Description
Price (\$)"
#output table rows - each fetchrow retrieves one result #from the sql query while {[set row [sql fetchrow $handle]] != ""} { set prodid [lindex $row 0] set descrip [lindex $row 1] set price [lindex $row 2] puts "
$prodid
$descrip
$price" }
puts "
"
#empty the query result buffer - should already be empty in this case sql endquery $handle #close the db connection - in practice this same connection #is used for multiple queries sql disconnect $handle
下面的代码是使用正式MySQL C++ API MySQL++以C++编写的等价脚本。该版本的优势在于它是编译型的,因此比解释语言更快。经常用在特定站点的数据库代码应该以C或C++编写,然后由脚本或直接由Web服务器访问,以改进整体运行时间。
int main() { try { //open the database connection and query Connection con(DBNAME, DBHOST, DBUSER, DBPASSWD); Query query = con.query();
//write valid sql code to the query object query <<"select * from " < //run the query and store the results Result res = query.store();
//write out the html table header cout <<"
\n"; cout <<"
Product Id
Description" <<"
Price ($)" < Result::iterator curResult; Row row;
//iterate over each result and put it into an html table for (curResult = res.begin(); curResult != res.end(); curResult++) { row = *curResult; cout <<"