(in-package :orapy)
(use-package :oli)



(defvar *python-path* "C:/python35/python.exe")
(defvar *oracle-sql-python-script* (format nil "~a/oracle_sql.py" (directory-namestring *load-truename*)))



(defun sql-statement (&key sql-statement host port sid user password)

 "
   Executes an SQL statement on an Oracle databases using python and the cx_Oracle module for python.
   
    Example:
    (sql-statement 
     :sql-statement 'select * from table'
     :host 'mmpdb';
     :port '1527'
     :sid 'MMP'
     :user 'username'
     :password 'password')
     
   Prerequisites:
    Python installation
    Installation of cx_Oracle module for Python.
    
   Remark:
    Script files oracle_sql.py and oracle_sql.lsp needs to be in the same directory.
    The lisp variable orapy:*python-path* needs to point to python.exe.
 "

 (let ((tmp-sql-statement-result (sd-gen-unique-filename (sd-inq-temp-dir))))

  (if (probe-file *python-path*)
   (if (probe-file *oracle-sql-python-script*)
    (progn
      (sd-sys-exec 
        (sd-convert-filename-to-platform 
          (format nil "~a ~a -s \"~a\" -c \"~a\" -p \"~a\" -i \"~a\" -u \"~a\" -w \"~a\" -r \"~a\"" 
            *python-path* 
            *oracle-sql-python-script*
            sql-statement
            host port sid user password
            tmp-sql-statement-result)))
            
      (if (probe-file tmp-sql-statement-result)
       (let (error-state (result (list)))
         (with-open-file (read-stream tmp-sql-statement-result :direction :input)
           (do 
             ((line (read-line read-stream nil 'eof) (read-line read-stream nil 'eof))) 
             ((eql line 'eof))
              (if (sd-string-match-pattern-p "###@ERROR*" line)
               (progn
                (setq error-state t)
                (push (subseq line 10) result))
              (push line result))))
         (if error-state
          (sd-display-error (format nil "~{~a~^~%~}" result))
          result))
        (sd-display-error (format nil "Statement result file (~a) not found." tmp-sql-statement-result))))
    (sd-display-error (format nil "File not found. ~a" *oracle-sql-python-script*)))
   (sd-display-error (format nil "File not found. ~a" *python-path*)))))




; Example call:
(display
  (sql-statement 
     :sql-statement "select * from wm_elements where rownum <= 1"
     :host "mmpdb"                                                  
     :port "1527"                                                   
     :sid "MMP"                                                     
     :user "username"                                               
     :password "password"))