Re: jdbc question

["Heineman, William P" <william_p_heineman at groton dot pfizer dot com>]

I use the following forms to wrap typical jdbc operations:

(define call-with-connection
  (lambda (url :: <string> user :: <string> password :: <string> 
	       proc :: <procedure>)
    (let ((connection :: <java.sql.Connection>
		      (invoke-static <java.sql.DriverManager> 
				     'getConnection 
				     (as <java.lang.String> url)
				     (as <java.lang.String> user)
				     (as <java.lang.String> password))))
      (try-finally
       (proc connection)
       (invoke connection 'close)))))


(define call-with-statement
  (lambda (connection :: <java.sql.Connection> proc :: <procedure>)
    (let ((statement :: <java.sql.Statement>
		     (invoke connection 'createStatement)))
      (try-finally
       (proc statement)
       (invoke statement 'close)))))

;; A simple example function using the call-with-statement function:

(define plate-registered?
   (lambda (plate-barcode)
     (call-with-statement 
      connection
      (lambda (statement)
	(let ((result-set :: <java.sql.ResultSet>
			  (invoke statement 'executeQuery
				  (string-append
				   "SELECT COUNT(*) FROM GCM_PLATE WHERE
GBL_PLATE_BAR_CODE = '"
				   plate-barcode
				   "'"))))
	  (invoke result-set 'next)
	  (> (invoke result-set 'getInt 1) 0))))))


The forms ensure proper release of resources.

Cheers,

Willy Heineman





LEGAL NOTICE
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.