SQLITE-m2/tests/test_prepare.mod

MODULE test_prepare ;

(*
   m2SQLITE smoke test: prepare/bind/step/column/finalize round trip,
   covering int, int64, double and text plus the SQLiteUtils
   TRANSIENT bind helpers.
*)

FROM SYSTEM IMPORT ADDRESS, ADR;
FROM SQLite IMPORT DbHandle, StmtHandle,
   SQLiteOk, SQLiteRow, SQLiteDone, SQLiteText,
   sqlite3_open, sqlite3_close,
   sqlite3_prepare_v2, sqlite3_step, sqlite3_finalize,
   sqlite3_bind_int, sqlite3_bind_int64, sqlite3_bind_double,
   sqlite3_bind_null,
   sqlite3_bind_parameter_count, sqlite3_bind_parameter_index,
   sqlite3_column_int, sqlite3_column_int64, sqlite3_column_double,
   sqlite3_column_text, sqlite3_column_type, sqlite3_column_count;
FROM SQLiteUtils IMPORT ExecSimple, BindTextCopy, CStrToM2, ErrMsg;
FROM libc IMPORT printf;

VAR db: DbHandle; stmt: StmtHandle;
    rc, n: INTEGER; buf: ARRAY [0..63] OF CHAR;
    i: INTEGER; li: LONGINT; d: REAL;
    tp: INTEGER;

BEGIN
   IF sqlite3_open(":memory:", db) # SQLiteOk THEN
      printf("FAIL test_prepare: open\n");
      HALT(1)
   END;
   IF ExecSimple(db, "CREATE TABLE t(a INTEGER, b INTEGER, c REAL, d TEXT);")
      # SQLiteOk THEN
      ErrMsg(db, buf);
      printf("FAIL test_prepare: create %s\n", buf);
      HALT(1)
   END;

   rc := sqlite3_prepare_v2(db, "INSERT INTO t(a,b,c,d) VALUES(?,?,?,?);",
                            -1, stmt, NIL);
   IF rc # SQLiteOk THEN
      ErrMsg(db, buf);
      printf("FAIL test_prepare: prep insert %s\n", buf);
      HALT(1)
   END;
   IF sqlite3_bind_parameter_count(stmt) # 4 THEN
      printf("FAIL test_prepare: param count\n");
      HALT(1)
   END;
   IF sqlite3_bind_int(stmt, 1, 42) # SQLiteOk THEN HALT(1) END;
   IF sqlite3_bind_int64(stmt, 2, VAL(LONGINT, 9000000001)) # SQLiteOk THEN
      printf("FAIL test_prepare: bind int64\n");
      HALT(1)
   END;
   IF sqlite3_bind_double(stmt, 3, 2.5) # SQLiteOk THEN HALT(1) END;
   IF BindTextCopy(stmt, 4, "hello") # SQLiteOk THEN HALT(1) END;
   rc := sqlite3_step(stmt);
   IF rc # SQLiteDone THEN
      printf("FAIL test_prepare: step insert rc=%d\n", rc);
      HALT(1)
   END;
   rc := sqlite3_finalize(stmt);
   IF rc # SQLiteOk THEN HALT(1) END;

   (* named parameter lookup *)
   rc := sqlite3_prepare_v2(db, "SELECT * FROM t WHERE a = :v;", -1,
                            stmt, NIL);
   IF rc # SQLiteOk THEN HALT(1) END;
   n := sqlite3_bind_parameter_index(stmt, ":v");
   printf("param :v index=%d\n", n);
   IF n # 1 THEN
      printf("FAIL test_prepare: param index=%d\n", n);
      HALT(1)
   END;
   IF sqlite3_bind_int(stmt, n, 42) # SQLiteOk THEN HALT(1) END;
   rc := sqlite3_step(stmt);
   IF rc # SQLiteRow THEN
      printf("FAIL test_prepare: step select rc=%d\n", rc);
      HALT(1)
   END;
   IF sqlite3_column_count(stmt) # 4 THEN
      printf("FAIL test_prepare: column count\n");
      HALT(1)
   END;
   i := sqlite3_column_int(stmt, 0);
   li := sqlite3_column_int64(stmt, 1);
   d := sqlite3_column_double(stmt, 2);
   tp := sqlite3_column_type(stmt, 3);
   CStrToM2(sqlite3_column_text(stmt, 3), buf);
   printf("row a=%d b=%ld c=%f d=%s t=%d\n", i, li, d, buf, tp);
   IF (i # 42) OR (li # VAL(LONGINT, 9000000001)) THEN
      printf("FAIL test_prepare: int values\n");
      HALT(1)
   END;
   IF (d < 2.49) OR (d > 2.51) THEN
      printf("FAIL test_prepare: double value\n");
      HALT(1)
   END;
   IF tp # SQLiteText THEN
      printf("FAIL test_prepare: text type=%d\n", tp);
      HALT(1)
   END;
   IF (buf[0] # 'h') THEN
      printf("FAIL test_prepare: text value\n");
      HALT(1)
   END;
   rc := sqlite3_step(stmt);
   IF rc # SQLiteDone THEN
      printf("FAIL test_prepare: drain rc=%d\n", rc);
      HALT(1)
   END;
   IF sqlite3_finalize(stmt) # SQLiteOk THEN HALT(1) END;

   (* NULL bind *)
   IF sqlite3_prepare_v2(db, "SELECT ?;", -1, stmt, NIL) # SQLiteOk THEN
      HALT(1)
   END;
   IF sqlite3_bind_null(stmt, 1) # SQLiteOk THEN HALT(1) END;
   IF sqlite3_step(stmt) # SQLiteRow THEN HALT(1) END;
   IF sqlite3_finalize(stmt) # SQLiteOk THEN HALT(1) END;

   IF sqlite3_close(db) # SQLiteOk THEN HALT(1) END;
   printf("PASS test_prepare\n")
END test_prepare.