option explicit
 
 ' uncomment the string for the driver desired...
 Dim connstr
    connstr = "DRIVER={MySQL ODBC 3.51 Driver};" & _
		"SERVER=localhost;DATABASE=ado" & _
		";USER=root;PASSWORD=mypass;OPTION=3;"
		
'	connstr = "DRIVER={MySQL Connector/ODBC v5};" & _
'		"SERVER=localhost;DATABASE=ado" & _
'		";UID=root;PWD=mypass;OPTION=3;"


' Copyright 2007 - MySQL AB

Const adUseServer = 2
Const adUseClient = 3
Const adLockOptimistic = 3
Const adOpenStatic = 3
Const adCmdText = &H0001
Const testFile = "setTest.csv"

Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Dim f:


Sub setup()
	Dim connection1, strSQL, data
	Set connection1 = CreateObject("ADODB.Connection")
	
	' get rid of test table if it's there
	strSQL = "DROP TABLE IF EXISTS set_test"
	connection1.ConnectionString = connstr
	connection1.Open
	connection1.Execute(strSQL)

	' insert data into test table
	Set f = fso.OpenTextFile("../common/" & testFile,1)
	strSQL = f.readline
	connection1.Execute(strSQL)

	while NOT f.AtEndOfStream
		data = f.readline
		strSQL = "INSERT INTO set_test(C1) VALUES (" & data & ")"
		connection1.Execute(strSQL)
	wend

	connection1.Close
	Set connection1 = Nothing
End Sub

Sub teardown
exit sub	' DEBUG put this in to leave test table intact at end of test
	Dim connection1, strSQL
	Set connection1 = CreateObject("ADODB.Connection")
	
	' get rid of test table
	strSQL = "DROP TABLE set_test"
	connection1.ConnectionString = connstr
	connection1.Open
	connection1.Execute(strSQL)
	connection1.Close
	Set connection1 = Nothing
End Sub

Sub Cleanup(conn, recset)
	Set recset.ActiveConnection = Nothing
	recset.Close
	Set recset = Nothing
	conn.Close
	Set conn = Nothing
End Sub


Sub TestExecuteValidImmediateClient()
	' NOTE: THIS TESTS ONLY THE SET DATA TYPE
	' Tests immediate updates with adLockOptimistic cursor lock.  adLockPessimistic  is not supported 
	' for client side cursor.  Updates to the DB should happen immediately after an update statement is made
	
	On Error Resume Next
    Dim connection1, recset1, fld, strSQL, rowCount, fldName, strSet
	Set connection1 = CreateObject("ADODB.Connection")
	Set recset1 = CreateObject("ADODB.RecordSet")
	
	' open connection
	connection1.ConnectionString = connstr
	connection1.CursorLocation = CInt(adUseClient)
	connection1.Open
	strSQL = "SELECT * FROM set_test"
	strSet = "number,1"
		   
   ' open recordset
    recset1.Open strSQL, connection1, adOpenStatic, _
             adLockOptimistic, adCmdText
	If Not recset1.EOF Then
		recset1.MoveFirst
	Else
		Call Cleanup (connection1, recset1)
		On Error Goto 0
		Assert.Failure "No records were returned for this query"
		Exit Sub
	End If

		If recset1.Supports(adUpdate) Then
		rowCount = 1
		While Not recset1.EOF
			For Each fld In recset1.Fields
				fldName = fld.Name
				If fldName = "C1" Then
			
			'	fld.Value = strSet
			'		recset1.Update
					
					recset1.Update fldName, strSet
							
					' verify the change
					If Not Trim(fld.OriginalValue) = strSet Then
						msgbox "failed " & Trim(fld.OriginalValue) & "::" & strSet
						Call Cleanup (connection1, recset1)
						On Error Goto 0
						Assert.Failure "Row: " & rowCount & " Col: " & fldName & " wasn't updated"
					Else
						msgbox "Pass - OLDVAL:" & Trim(fld.OriginalValue) & "::NEWVAL:" & strSet
					End If
					
				End If
			Next
			rowcount = rowcount + 1
			if rowcount = 10 then		'  CHANGE THIS LINE TO INCREASE THE NUMBER OF RECORDS MODIFIED
				call Cleanup (connection1, recset1)
				exit sub
			end if
			recset1.MoveNext
		wend
	End If
End Sub