#---
#
# to build MySQL/DBI support into Susie, you have to create an environment
# variable called MYSQLPATH and set it to the path of your MySQL include
# and lib directoryies. for example, my include is in /Library/MySQL/include.
# so my MYSQLPATH is /Library/MySQL. once this is defined, just 'make' Susie
#
#
# you will need to change the table,database,userid,password to match
# your needs.
#
#---


"--- load the optional DBI classes "
File new fileIn: 'src/DBI.st'.

"--- make a connection to the test database "
conn := DBI connectWith: 'mysql' to: 'localhost' using: 'database' user: 'userid' pass: 'password'.
Transcript show: 'conn:' + (conn connection asString);cr.

"--- (try to) delete a specific row in a table "
items := conn do: 'DELETE FROM tablename where userid = 99'.
Transcript show: 'items:' + (items asString);cr.

"--- an invalid SQL statement just to test returning errors "
items := conn do: 'DELETE FROM tablename where useridd = ?' with: (Array with: 99).
Transcript show: 'items:' + (items asString);cr.
(items isNil) ifTrue: [
   Transcript show: 'errno:' + (conn errno asString);cr.
   Transcript show: 'error:' + (conn error);cr.
   ].

"--- try a SELECT that is supposed to return an empty resultSet "
rSet := conn execute: 'SELECT * from tablename where userid = 1'.
Transcript show: 'rClass:' + (rSet class);cr.
(rSet isNil)
   ifTrue: [
      Transcript show: 'error:' + (conn error);cr.
      ]
   ifFalse: [
      Transcript show: 'rows:' + (rSet rows asString);cr.
      "rSet free."
      ].

"--- try a SELECT that is supposed to return a resultSet "
rSet := conn execute: 'SELECT * from tablename where userid = 2'.
Transcript show: 'rClass:' + (rSet class);cr.
(rSet isNil)
   ifTrue: [
      Transcript show: 'error:' + (conn error);cr.
      ]
   ifFalse: [
      rData := rSet fetch.
      Transcript show: 'host:' + (rSet host);cr.
      Transcript show: 'rows:' + (rSet rows asString);cr.
      Transcript show: 'data:' + rData;cr.
      "rSet free."
      ].

"--- all done. close up shop "
conn disconnect.
Transcript show: 'conn:' + (conn connection asString);cr.

!