;;;=head1 Test code for Oracle
;;;
;;;This program gives more insight about how to use SQLI/SQLD It is a small test program
;;;that is distributed with the SQLI/SQLD package. Works for both bigloo and mzscheme.
;;;To prepare it for a scheme dialect, you need C<staste>.
;;;
;;;=head2 Code explained
;;;
;;;You want to have a look at L<the Test program for PostgreSQL first|sqli_sample_psql>.
;;;We begin with importing or requiring the right modules. You can see that here
;;;the right driver (SQLite) is loaded.
;;;
;;;=verbatim scm,8
;;#+ bigloo
;(module test
;	(import sqli)
;	(import sqld-oracle)
;	(main main))
;#+ mzscheme
(module oracle-test mzscheme
	(require (lib "time.ss" "srfi" "19"))
	(require (planet "sqli.scm" ("oesterholt" "sqlid.plt" 1 2)))
	(require (planet "sqld-oracle.scm" ("oesterholt" "sqlid.plt" 1 2)))
;##

(define (displayp . args)
  (define (p args)
    (if (null? args)
	(newline)
	(begin
	  (display (car args))
	  (p (cdr args)))))
  (p args))

(define (main . argv)
;;;=verbatim
;;;
;;;See here the difference between the Oracle version of this test program and the
;;;PostgreSQL version. B<This is allmost the only place where the oracle version
;;;of this program differs from the PostgreSQL version>
;;;
;;;=verbatim scm,8
  (let* (
;#+ mzscheme
	 (connect_string (if (null? argv) "scott/tiger" (car argv)))
;;#+ bigloo
;	 (connect_string (if (null? (cdr argv)) "scott/tiger" (cadr argv)))
;##
	 (sqld (sqld-oracle-new connect_string))
;;;=verbatim
;;;
;;;The rest is allmost the same and won't be explained. Except for the CREATE TABLE statement.
;;;One can see that Oracle has some limitations for varchars.
;;;
;;;=verbatim scm,8
	 (sqli (sqli-connect sqld))
	 (results (sqli-query sqli "CREATE TABLE test (name varchar(2000),age numeric(4),nice numeric(1),dt timestamp)"))
	 (i 0))
;;;=verbatim
;;;
;;;But the rest is really the same.
;;;
;;;=verbatim scm,8

    ; Look at result

    (displayp results)
    (displayp (sqli-error? sqli) " - " (sqli-error-message sqli))

    ; version and name

    (displayp (sqli-version) " - " (sqli-driver-name sqld) " - " (sqli-driver-version sqld))
    
    ; First test: Select * from test.

    (do 
	((a (sqli-fetchrow sqli) (sqli-fetchrow sqli)))
	((eq? a #f) #t)
      (display a))
    (displayp)

    ; Transaction of insert statements.
    
    (sqli-begin sqli)

    (do 
	((i 1 (+ i 1)))
	((> i 10) #t)
      (sqli-query sqli "INSERT INTO test VALUES ($1, $2, $3, $4)" (string-append "row'" (number->string i)) i #t (current-date)))

    (sqli-commit sqli)

    ; Select from database

    (set! results (sqli-query sqli "SELECT * FROM test"))
    (displayp (sqli-error? sqli) " - " (sqli-error-message sqli))
    (displayp (sqli-fetchall sqli))
    (displayp)

    ; Wrong SQL syntax.
    
    (sqli-begin sqli)
    (sqli-query sqli "INSERT INTO test VALUES ('joost',42,$1,$2);SELEKT * FROM test" 1 (current-date))
    (displayp (sqli-error? sqli) " - " (sqli-error-message sqli))
    (if (sqli-error? sqli)
	(sqli-rollback sqli)
	(sqli-commit sqli))

    ; Register a query

    (sqli-register sqli 'select 
		   "SELECT $1 FROM test WHERE age $2 $3"
		    (let ((c 0))
		     (lambda (a) 
		       (begin
			 (set! c (+ c 1))
			 (list c (car a))))))
    (displayp sqli)
    (displayp (sqli-exec sqli 'select 'name '> 2))
    (displayp (sqli-fetch sqli 10))

    ; Register an other query

    (sqli-register sqli 'select-convert
		   "SELECT * FROM test WHERE age=10"
		   'string 'integer 'boolean 'date)
    (displayp sqli)
    (displayp (sqli-exec sqli 'select-convert))
    (displayp (sqli-fetchall sqli))

    ; Use closure.

    (let ((clos (sqli-closure sqli)))
      (displayp (clos 'exec 'select 'age '<= 2))
      (displayp (clos 'fetchall))
      (clos 'register 'count "SELECT COUNT(name) FROM test"
	    (lambda (row) (car row)))
      (clos 'exec 'count);
      (displayp (clos 'fetchrow))

      (clos 'register 'and "SELECT nice FROM test" 
	    (lambda (row) 
	      (let ((b (clos 'convert (car row) 'boolean)))
		(list b))))
      
      (displayp (clos 'exec 'and))
      (displayp (clos 'fetchall))

      (clos 'query "INSERT INTO test VALUES ('false',0,$1)" #f)

      (displayp (clos 'exec 'and))
      (displayp (clos 'fetchall))

      (clos 'hithere "errors"))

    (displayp sqli)
    (displayp (sqli-exec sqli 'and))
    (displayp (sqli-fetchall sqli))

  (sqli-disconnect sqli)

  0))


;#+ mzscheme
(provide main))
;##
;;;=verbatim
;;;
;;;That's all to it!
;;;
;;;=head2 Info   
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;S<C<Package   : >> psql-test.scmE<lb>
;;;S<C<Author    : >> Hans Oesterholt-Dijkema.E<lb>
;;;S<C<Copyright : >> HOD 2004/2005.E<lb>
;;;S<C<License   : >> The Elemental Programming Artistic License.E<lb>
;;;S<C<CVS       : >> $Id: oracle-test.scm,v 1.3 2005/06/11 22:58:23 HansOesterholt Exp $E<lb>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;=cut