ellyps.net ellyps.net
Using mysql

To make use of mysql, you need to load the file db.scm in your script. db.scm is a file that was created for you when you created your account.
It looks like this.

(use-modules (dbi dbi))

(define mysql (dbi-open "mysql" "iloveponies:PASSWORD:iloveponies:tcp:localhost:3306"))

Where PASSWORD is your mysql password.

To effectively use mysql you first need to setup tables and create data of interest. Access to mysql is done with the mysql client over an encrypted connection.

To login to mysql use the following command line:
#mysql -p -u iloveponies -h mysql.ellyps.net --ssl-ca=ellyps.pem iloveponies

This will ask for the password. You can get that from the file db.scm mentioned above.
You can get the ellyps.pem file here

This will establish an encrypted mysql connection to the database "iloveponies" as mysql-user "iloveponies" for you.
Now we put some example data into the database.

mysql> create table data (names varchar(100));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into data values ("Alice");
Query OK, 1 row affected (0.02 sec)
mysql> insert into data values ("Bob");
Query OK, 1 row affected (0.02 sec)

mysql> select * from data;
| names |
| Alice | 
| Bob   | 
2 rows in set (0.02 sec)

Now we can make use of that data in a dynamic webpage with the following script.

  1. #!/usr/bin/guile -s
  2. !#
  3. (load "../db.scm")
  4. (display "Content-type: text/html")(newline)(newline)
  5. (dbi-query mysql "select names from data")
  6. (let ((result (dbi-get_row mysql)))
  7. (while result
  8. (format #t "~a likes ponies too!<br>" (assoc-ref result "names"))
  9. (set! result (dbi-get_row mysql))))

Line 1 and 2 call the guile interpreter.

Line 4 loads the db.scm file that sets up our handle to the database. The handle is called "mysql".

Line 6 outputs a line the webserver expects. If you ever get the error "Premature end of script headers:" in the webserver error log, check this line first.

Line 8 makes a query to the database. This is just straight mysql as you would normally use it in the mysql client. You can leave out the closing ";"
Make sure to escape any quotes you might need inside the query (like "select * from data where names=\"Alice\"").

Line 9 lets a variable named "result" take on the value of what (dbi-get_row mysql) returns; a list of name-value pairs. If you would (display result) directly, it would show ((names . Alice))

Line 10 sets up a loop that repeats as long as result doesn't evaluate to #f (false in guile).

Line 11 formats the line (and prints it to standard out, since its first argument is #t), replacing the "~a" with the value of (assoc-ref result "names"). You can treat result as an associative array in guile since dbi-get_row returns a list of pairs.
You could replace (assoc-ref result "names") with (cdr(car result)) with the same output here to see its list-like nature.

Line 12 sets result to the next result from the mysql query, and to #f if there isn't any, exiting the loop.

You can see the results at http://ponies.ellyps.net/mysql.scm