Part 2 - Working with MySQLIn part 2, I'm going to show how to access MySQL and display the result set in a table. The MySQL extension is a C library interface to the database engine. This example will show you how easy it is to work with a SQL database interface.
Test Table Definition
#!/usr/bin/scriba -c
INCLUDE cgi.bas
INCLUDE mysql.bas
OPTION cgi$Method cgi::Get
dbh = mysql::RealConnect("host","user","password","database")
cgi::Header 200,"text/html"
cgi::FinishHeader
PRINT """
<HTML>
<HEAD>
<title>MySQL Tutorial</title>
</HEAD>
<BODY>
<FONT face="Verdana, Arial, Helvetica, sans-serif">
<TABLE border="1" cellpadding="3">
"""
mysql::query(dbh,"SELECT * FROM contact")
WHILE mysql::FetchHash(dbh, col_name)
PRINT "<TR>\n"
PRINT "<TD>",col_name{"ID"},"</TD>\n"
PRINT "<TD>",col_name{"NAME"},"</TD>\n"
PRINT "<TD>",col_name{"ADDRESS"},"</TD>\n"
PRINT "<TD>",col_name{"CITY"},"</TD>\n"
PRINT "<TD>",col_name{"STATE"},"</TD>\n"
PRINT "<TD>",col_name{"ZIP"},"</TD>\n"
PRINT "<TD>",col_name{"PHONE"},"</TD>\n"
PRINT "<TD>",col_name{"EMAIL"},"</TD>\n"
PRINT "<TD>",col_name{"URL"},"</TD>\n"
PRINT "</TR>\n"
WEND
PRINT """
</TABLE>
</FONT>
</BODY>
</HTML>
"""
mysql::Close(dbh)
END
The mysql.bas include file declares the functions to access the MySQL library.
dbh = mysql::RealConnect("host","user","password","database")The MySQL library call makes the connection to the database. If the database is defined in the basic.conf file then you can use an abbreviated version of the function to connect to the MySQL interface.
mysql::Connect("conf_defined_name")This moves the login information out of the program and into a binary configuration file. The
dbh variable contains the connection handle and shouldn't be modified by the programmer. It is used to pass to the other mysql:: functions.
mysql::query(dbh,"SELECT * FROM contact")This function passes your SQL statement to MySQL. If a query is passed, then you could use the AffectedRows() function to return how many rows in the record set.
number_of_rows = mysql::AffectedRows(dbh)WHILE mysql::FetchHash(dbh, col_name)The WHILE directive will loop through the record set until the FetchHash() function returns false. This version of fetch uses an associative array to store the row from the record set. This allows you to reference the data by the column name. (case sensitive) There are times when this may not be practicle. (like a JOIN) In this case you would use the FetchArray() function to return the row from the result set.
WHILE mysql::FetchArray(dbh, array_var)
PRINT "<TR>\n"
PRINT "<TD>",array_var[0],"</TD>\n"
PRINT "<TD>",array_var[1],"</TD>\n"
PRINT "<TD>",array_var[2],"</TD>\n"
PRINT "<TD>",array_var[3],"</TD>\n"
PRINT "<TD>",array_var[4],"</TD>\n"
PRINT "<TD>",array_var[5],"</TD>\n"
PRINT "<TD>",array_var[6],"</TD>\n"
PRINT "<TD>",array_var[7],"</TD>\n"
PRINT "<TD>",array_var[8],"</TD>\n"
PRINT "</TR>\n"
WEND
PRINT "<TD>",col_name{"ID"},"</TD>\n"This will populate the cell with the current row "ID" column data.
WENDThis defines the end of the WHILE loop.
mysql::Close(dbh)This explicitly closes the MySQL connection. ScriptBasic will close all database connections on it's own upon exiting the script.
