RE: Any Examples of COM (and perhaps ADO)?

["Anton van Straaten" <anton@appsolutions.com>]

Alex,

To come back to what started this:

> Does anyone have any examples of COM use (with ADO would be nice).

With Paul Steckler's help, I've come up with the following example which
works, and iterates through a query using ADO.  It relies on Paul's
workaround for the MysterX/DrScheme Execute button interaction (see prior
list messages), plus a small modification to MysterX's com-get-property
function to support access to the ADO fields collection.

I've used a wrapper function, ado-fields, to simplify field access; more on
this below.  Here's a sample which executes a query and iterates through the
resultset:

	(require-library "mysterx.ss" "mysterx")

	; helper function for ADO field access
	(define (ado-fields recordset)
	  (let [(field-coll (com-get-property rs "Fields"))]
	    (com-add-ref fields) ; workaround for MysterX/DrScheme/Execute
interaction
	    (lambda (field-name)
      	(com-get-property (com-get-property field-coll "Item" field-name)
"Value"))))

	(define conn (cci/progid "ADODB.Connection"))
	(com-add-ref conn)   ; workaround

	(com-invoke conn "Open" "DSN=mydsn;UID=sa;PWD=###")

	(define rs (com-invoke conn "Execute" "SELECT * FROM SomeTableOrOther"))

	(define flds (ado-fields rs))

	(do () ((com-get-property rs "EOF"))
	  ; display ID and Name fields from resultset
	  (display (list (flds "ID") (flds "Name")))
	  (newline)
	  (com-invoke rs "MoveNext"))

	(com-invoke rs "Close")
	(com-invoke conn "Close")

For the above to work, a small change is required to MysterX, in the module
mysterxe.ss.  I renamed the existing com-get-property function to
com-get-property-chain (since it is designed to support retrieving a chain
of properties like obj.propa.propb.propc).  I then added an alias for the
native/C version of com-get-property, as follows:

	(define com-get-property mxprims:com-get-property)

This replaces the chaining version of com-get-property with the native one,
which supports the access to indexed properties required by the ADO Fields
collection.  Existing calls to com-get-property will continue to work,
unless they involve multiple property names.  (I'm not suggesting that this
should be the definitive change to MysterX - I'll leave that to Paul).
Here's a diff for the above change:

17a18
>   (define com-get-property mxprims:com-get-property)
77c78
<   (define (com-get-property obj . path)
---
>   (define (com-get-property-chain obj . path)

In addition, if you want to be able to use the com-get-property-chain
function, you'll need to add it to the unit signature in sigs.ss.

Finally, you need to make sure the above change is compiled, or DrScheme
will continue to use the original compiled code (I mention this because I
made that mistake originally, not being familiar with making changes to
DrScheme library code...)

The ado-fields wrapper function above was mainly intended to simplify field
access code such as:

	(com-get-property (com-get-property field-coll "Item" field-name) "Value"))

into expressions of the form: (field-coll field-name).  A few other simple
wrappers, e.g. for the ADO Recordset object,  would make ADO quite usable
from Scheme.  However, unless there's a specific reason to use ADO, it still
might make more sense to use SrPersist or SchemeQL instead, although I don't
have enough experience with any of these to know for sure.

This example works in DrScheme and can be Execute-d repeatedly without
generating errors.  However, until the garbage collection issue in MysterX
is resolved, using DrScheme for development of such code could be a bit
frustrating, especially since it's not clear exactly which objects need
extra com-add-ref calls to avoid causing errors on the next Execute.
MzScheme should work just fine, though (since it doesn't have an Execute
button :)

Anton