MODULE SQLitePrepared;
(* Prepared statements, bound texts and a result read. *)

FROM SYSTEM IMPORT ADDRESS;
FROM CStr IMPORT CString, Copy;
FROM SQLite IMPORT
  SQLiteOk, SQLiteRow, SQLiteDone,
  sqlite3_open, sqlite3_close,
  sqlite3_prepare_v2, sqlite3_bind_int, sqlite3_bind_text,
  sqlite3_step, sqlite3_column_int, sqlite3_column_text,
  sqlite3_finalize, SQLiteTransient;

VAR
  ExitCode : INTEGER;
  db, stmt : ADDRESS;
  ctext : CString;
  buf : ARRAY [0..63] OF CHAR;
  rc : INTEGER;
  ok : BOOLEAN;

BEGIN
  db := NIL; stmt := NIL;
  ok := FALSE;
  rc := sqlite3_open(":memory:", db);
  IF (rc = SQLiteOk) AND (db # NIL) THEN
    rc := sqlite3_prepare_v2(db,
      "CREATE TABLE t(x INTEGER, s TEXT);", -1, stmt, NIL);
    IF (rc = SQLiteOk) AND (stmt # NIL) THEN
      rc := sqlite3_step(stmt);
      IF (rc = SQLiteDone) AND (sqlite3_finalize(stmt) = SQLiteOk) THEN
        stmt := NIL;
        rc := sqlite3_prepare_v2(db,
          "INSERT INTO t VALUES (?, ?);", -1, stmt, NIL);
        IF (rc = SQLiteOk) AND (stmt # NIL) THEN
          IF (sqlite3_bind_int(stmt, 1, 42) = SQLiteOk)
             AND (sqlite3_bind_text(stmt, 2, "hello", -1,
                                    SQLiteTransient()) = SQLiteOk) THEN
            rc := sqlite3_step(stmt);
            IF (rc = SQLiteDone) AND (sqlite3_finalize(stmt) = SQLiteOk) THEN
              stmt := NIL;
              rc := sqlite3_prepare_v2(db,
                "SELECT x, s FROM t;", -1, stmt, NIL);
              IF (rc = SQLiteOk) AND (stmt # NIL) THEN
                rc := sqlite3_step(stmt);
                IF rc = SQLiteRow THEN
                  ctext := sqlite3_column_text(stmt, 1);
                  IF (sqlite3_column_int(stmt, 0) = 42)
                     AND (ctext # NIL) THEN
                    IF Copy(ctext, buf) = 5 THEN
                      ok := (buf[0] = 'h') AND (buf[4] = 'o')
                            AND (buf[5] = CHR(0))
                    END
                  END;
                  IF (rc = SQLiteRow) AND (sqlite3_step(stmt) = SQLiteDone) THEN
                    ok := ok AND (sqlite3_finalize(stmt) = SQLiteOk);
                    stmt := NIL
                  END
                END
              END
            END
          END
        END
      END
    END
  END;
  IF stmt # NIL THEN
    IF sqlite3_finalize(stmt) # SQLiteOk THEN ok := FALSE END
  END;
  IF db # NIL THEN
    IF sqlite3_close(db) # SQLiteOk THEN ok := FALSE END
  END;
  IF ok THEN ExitCode := 42 ELSE ExitCode := 1 END
END SQLitePrepared.