'
' Simple MySQL demo program - October 2010, PvE - GPL. Needs MySQL 5.x or higher.
'
'-------------------------------------------------------------------------------------------------------------------------------

INCLUDE "mysql.bac"

' Declare NULL pointer
DECLARE NUL TYPE STRING

' Declare pointer for results
DECLARE row TYPE long*

' Initialize DB pointer
db = mysql_init(0)

' Connect to DB
IF NOT(mysql_real_connect(db, "www.mydb.org", "username", "password", "dbname", 0, NUL, 0)) THEN \
    PRINT "Failed to connect to database: Error: ", mysql_error$(db)

' Show some info
PRINT "=========================================="
PRINT "Host info: ", mysql_get_host_info$(db)
PRINT "Client version: ", mysql_get_client_version()
PRINT "Server info: ", mysql_get_server_info$(db)
PRINT "=========================================="

' Create a table
IF mysql_query(db, "CREATE TABLE mytable(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, number INT, text VARCHAR(64));") THEN \
    PRINT "Failed to execute query: Error: ", mysql_error$(db)

' Insert values
IF mysql_query(db, CONCAT$("INSERT INTO mytable (number, text) VALUES (100, 'John');")) THEN \
    PRINT "Failed to execute query: Error: ", mysql_error$(db)
IF mysql_query(db, CONCAT$("INSERT INTO mytable (number, text) VALUES (200, 'Alex');")) THEN \
    PRINT "Failed to execute query: Error: ", mysql_error$(db)
IF mysql_query(db, CONCAT$("INSERT INTO mytable (number, text) VALUES (300, 'Peter');")) THEN \
    PRINT "Failed to execute query: Error: ", mysql_error$(db)

' Send query for hostnames
IF mysql_query(db, "SELECT number, text FROM mytable;") THEN PRINT "Failed to execute query: Error: ", mysql_error$(db)

' Tell we are going to use all rows at once
result = mysql_store_result(db)

REPEAT
    ' Get row
    row = mysql_fetch_row(result)

    ' Show text
    IF row THEN
        nr$ = (char*)row[0]
        text$ = (char*)row[1]
        PRINT nr$, " - ", text$
    END IF

UNTIL NOT(row)

PRINT "=========================================="
PRINT "We have " , mysql_num_rows(result), " results."
PRINT "=========================================="

' Free resources
mysql_free_result(result)

' Drop result table if it exists
IF mysql_query(db, "DROP TABLE IF EXISTS mytable;") THEN PRINT "Failed to execute query: Error: ", mysql_error$(db)

' Close connection
mysql_close(db)