MODULE hello_db ;

(*
   m2SQLITE example: create a table, insert two rows with bound
   parameters, then read them back with prepare/step/column.
*)

FROM SYSTEM IMPORT ADDRESS;
FROM SQLite IMPORT DbHandle, StmtHandle,
   SQLiteOk, SQLiteRow, SQLiteDone,
   sqlite3_open, sqlite3_close,
   sqlite3_prepare_v2, sqlite3_step, sqlite3_finalize,
   sqlite3_bind_int, sqlite3_column_int, sqlite3_column_text;
FROM SQLiteUtils IMPORT ExecSimple, BindTextCopy, CStrToM2, ErrMsg;
FROM libc IMPORT printf;

VAR db: DbHandle; stmt: StmtHandle;
    rc: INTEGER; buf: ARRAY [0..63] OF CHAR;
    name: ARRAY [0..63] OF CHAR; id: INTEGER;

PROCEDURE check (rc: INTEGER; what: ARRAY OF CHAR);
VAR e: ARRAY [0..255] OF CHAR;
BEGIN
   IF rc # SQLiteOk THEN
      ErrMsg(db, e);
      printf("FAIL %s: %s\n", what, e);
      HALT(1)
   END
END check;

BEGIN
   check(sqlite3_open(":memory:", db), "open");
   check(ExecSimple(db, "CREATE TABLE hi(id INTEGER, name TEXT);"),
         "create");
   check(sqlite3_prepare_v2(db, "INSERT INTO hi(id,name) VALUES(?,?);",
                            -1, stmt, NIL), "prep insert");
   check(sqlite3_bind_int(stmt, 1, 1), "bind1");
   check(BindTextCopy(stmt, 2, "modula"), "bind2");
   IF sqlite3_step(stmt) # SQLiteDone THEN HALT(1) END;
   check(sqlite3_finalize(stmt), "fin1");
   check(sqlite3_prepare_v2(db, "INSERT INTO hi(id,name) VALUES(?,?);",
                            -1, stmt, NIL), "prep insert2");
   check(sqlite3_bind_int(stmt, 1, 2), "bind3");
   check(BindTextCopy(stmt, 2, "sqlite"), "bind4");
   IF sqlite3_step(stmt) # SQLiteDone THEN HALT(1) END;
   check(sqlite3_finalize(stmt), "fin2");

   check(sqlite3_prepare_v2(db, "SELECT id, name FROM hi ORDER BY id;",
                            -1, stmt, NIL), "prep select");
   LOOP
      rc := sqlite3_step(stmt);
      IF rc = SQLiteDone THEN EXIT END;
      IF rc # SQLiteRow THEN
         printf("FAIL step rc=%d\n", rc);
         HALT(1)
      END;
      id := sqlite3_column_int(stmt, 0);
      CStrToM2(sqlite3_column_text(stmt, 1), name);
      printf("row id=%d name=%s\n", id, name)
   END;
   check(sqlite3_finalize(stmt), "fin3");
   check(sqlite3_close(db), "close");
   printf("hello_db OK\n")
END hello_db.