rebol [
	Title: "ActiveX Data Objects"
	File: %ado.r
	Date: 6-Jul-2006
	Version: 1.0.2
	Progress: 0.7
	Status: "working"
	Needs: []
	Author: "Anton Rolls"
	Language: "English"
	Purpose: {ActiveX Data Objects read and manipulate data from a data source.}
	Usage: {}
	History: [
		1.0.0 [1-Jul-2006 {First version, AdoRead working} "Anton"]
		1.0.1 [3-Jul-2006 {AdoDump working} "Anton"]
		1.0.2 [6-Jul-2006 {AdoWrite working} "Anton"]
	]
	ToDo: {
	- catch error at  bEOF: GetInteger  and make sure to cleanup
	- format print outs exactly like ado.c ?
	- investigate strange path bug?: 
	  I am sure that I was able initially to connect to the data source without specifying the full path to whales.xls
	  Without the path, it looks in view-root.
	}
	Notes: {
		This script converted from ado.c from DispHelper's samples_c/ directory

		Note from disphelper:

		  To ensure that most people are able to view the contents of our 'database', we use an
		Excel spread sheet as our data source in these samples. However, Excel files do not
		provide a robust or fast database format and one should generally avoid using them
		as a database.

		  The preferred method is to use an Access file database or a server database such as
		MySql. A list of connection strings for data sources can be found at
		http://www.connectionstrings.com/ or http://www.able-consulting.com/ADO_Conn.htm
	}
]

if error? set/any 'error try [

	do/args %../COMLib.r [

		; Use the COMLib API functions here

		AdoRead: func ["Use ADO to obtain data from a recordset and print it out to the console."
			/local conn rs bEOF id species population status state
		][
			conn: CreateObject "ADODB.Connection"

			; Connect to the data source
			;CallMethod [conn ".Open(%s)" {Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Whales.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"}]
			CallMethod [conn ".Open(%s)" rejoin [{Provider=Microsoft.Jet.OLEDB.4.0;Data Source=} to-local-file clean-path %demos/Whales.xls {;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"}]]

			; Execute a select statement to retrieve a recordset
			rs: GetObject [conn  ".Execute(%s)" "SELECT ID, Species, Population, Status FROM [Whales$] ORDER BY ID"]

			; Loop through each row until EOF is TRUE
			;while (SUCCEEDED(dhGetValue(L"%b", &bEOF, rs, L".EOF")) && !bEOF)   ; <-- the C asks for a BOOL
			while [0 = bEOF: GetInteger [rs ".EOF"]][  ; <-- so Rebol just asks for an integer, with result value 0 or -1

				; Get the value of each field that we want
				id:			GetInteger [rs ".Fields(%s).Value" "ID"]
				species:	GetString [rs ".Fields(%s).Value" "Species"]
				population: GetString [rs ".Fields(%s).Value" "Population"]
				status:		GetString [rs ".Fields(%s).Value" "Status"]

				print [id mold species tab mold population tab mold status]

				; <-- note: rebol has no need to "Free returned strings", as the strings are copied into rebol memory
				; and freed on the C DLL side automatically by GetString

				CallMethod [rs ".MoveNext"]
			]

			; cleanup

			state: GetInteger [conn ".State"]
			if state <> 0 [ ; 0 = adStateClosed
				CallMethod [conn ".Close"] ; Closing the connection also closes active recordsets associated with it
			]

			release rs
			release conn
		]

		; Similar to AdoRead above, AdoDump differs by demonstrating
		; how to dump a recordset to output when you do not know its structure.

		AdoDump: func [
			/local conn rs bEOF state
		][
			conn: CreateObject "ADODB.Connection"

			CallMethod [conn ".Open(%s)" rejoin [{Provider=Microsoft.Jet.OLEDB.4.0;Data Source=} to-local-file clean-path %demos/Whales.xls {;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1;"}]]

			rs: GetObject [conn ".Execute(%s)" "SELECT * FROM [Whales$]"]
			
			while [0 = bEOF: GetInteger [rs ".EOF"]][ ; BOOL is an integer, with result 0 or -1

				; This is equivalent to the FOR_EACH below (and is essentially how FOR_EACH is implemented)
				{use [enum field][
					enum: EnumBegin [rs ".Fields"]
					while [0 <> field: EnumNextObject [enum]][
						name: GetString [field ".Name"]
						value: GetString [field ".Value"]
						print [name ":" mold value]
						release field
					]
				]}
				
				FOR_EACH [field rs ".Fields"][
					;?? field
					name: GetString [field ".Name"]
					;?? name
					value: GetString [field ".Value"]
					;?? value
					print [name ":" mold value]
				]


				print "-----------------------"
				;print ".MoveNext"
				CallMethod [rs ".MoveNext"]
			]

			state: GetInteger [conn ".State"]
			if state <> 0 [ ; 0 = adStateClosed
				CallMethod [conn ".Close"]
			]

			release rs
			release conn
		]

		AdoWrite: func [
			/local conn rs bEOF state
		][
			conn: CreateObject "ADODB.Connection"

			; Open the data source. Make sure we have write access.
			CallMethod [conn ".Open(%s)" rejoin [{Provider=Microsoft.Jet.OLEDB.4.0;Data Source=} to-local-file clean-path %demos/Whales.xls {;Extended Properties="Excel 8.0;HDR=Yes;IMEX=0;"}]]


			; Add some records using SQL statements
			CallMethod [conn "Execute(%s)" "INSERT INTO [AddressList$] VALUES ('Jane Doe', '1 Donkey St', 25)"]
			CallMethod [conn "Execute(%s)" "INSERT INTO [AddressList$] VALUES ('John Citizen', '1 Cheetah St', 74)"]

			; Alter a record using an SQL statement
			CallMethod [conn "Execute(%s)" "UPDATE [AddressList$] SET Address = '1 Elephant St' WHERE Name = 'John Citizen'"]

			; Add 5 to each age using an SQL statement
			CallMethod [conn "Execute(%s)" "UPDATE [AddressList$] SET Age = Age + 5"]
	

			; Using SQL is preferable but now we demonstrate how to update using a recordset

			; Create a recordset. The conn.Execute method can only return a read only recordset
			rs: CreateObject "ADODB.Recordset"

			; Open the recordset with write access
			CallMethod [rs "Open(%s, %o, %d, %d)" "[AddressList$]" conn 3 3]

			; Add a new record
			CallMethod [rs ".AddNew"]
			
				PutValue [rs ".Fields(%s).Value = %s" "Name" "Joe Smith"]
				PutValue [rs ".Fields(%s).Value = %s" "Address" "1 Dung Beetle Way"]
				PutValue [rs ".Fields(%s).Value = %d" "Age" 32]
			

			; Move to the first record and alter it
			CallMethod [rs ".MoveFirst"]
			
				PutValue [rs ".Fields(%s).Value = %s" "Address" "1 Kangaroo Rd"]
				PutValue [rs ".Fields(%s).Value = %d" "Age" 45]
			

			; Save changes to the last record we edited
			CallMethod [rs ".Update"]
			


			state: GetInteger [conn ".State"]
			if state <> 0 [ ; 0 = adStateClosed
				CallMethod [conn ".Close"]
			]

			release rs
			release conn
		]

		;print "Running AdoRead sample..."
		;AdoRead

		;ask "Press ENTER to run AdoDump sample..."
		;probe what-dir
		;AdoDump

		print "Running AdoWrite sample..."
		AdoWrite

		print "Finished."
	]

][
	print mold disarm error
]