'Example of how to use functions.

''******************************************************************************************
' Example of how to use DSN created for the database of sample Flight application.
''******************************************************************************************
SQL="SELECT * FROM ORDERS"
connection_string="QT_Flight32"

isConnected = db_connect ( curConnection ,connection_string )
If isConnected = 0 then
	' Execute the basic SQL statement
	set myrs=db_execute_query( curConnection , SQL )
	' Report the query and the connection string
	Reporter.ReportEvent micInfo ,"Executed query and created recordset ","Connection_string is ==> " & connection_string & " SQL query is ===> " & SQL
	' Show the number of rows in the table using a record set
	msgBox " Quantity of rows in queried DB ( db_get_rows_count )==> " & db_get_rows_count( myrs )
	' Show the number of rows in the table using a new SQL statement
	msgBox " Quantity of rows in queried DB (db_get_rows_count_SQL ) ==> " & db_get_rows_count_SQL( curConnection , "SELECT COUNT(*) FROM ORDERS" )
	' Change a value of a field in an existing row
	rc = db_set_field_value (curConnection, "ORDERS" , "Agents_Name" , "test", "Agents_Name", "AGENT_TESTER")

	' Examples of how to retrieve values from the table
	msgBox "val row 0 col 0: " & db_get_field_value( myrs , 0 , 0 )
	msgBox "val row 0 col 1: " & db_get_field_value( myrs , 0 , 1 )
	msgBox "val row 1 col Name: " & db_get_field_value( myrs , 1 , "Agents_Name" )
	msgBox "val SQL row 1 col Name: " & db_get_field_value_SQL( curConnection , "ORDERS" , 1 , "Agents_Name" )

	db_disconnect curConnection
End If

''******************************************************************************************
' Database Functions library
''******************************************************************************************
'db_connect
' ---------------
' The function creates a new connection session to a database.
' curSession - The session name (string)
' connection_string - A connection string
' for example the connection_string can be "DSN=SQLServer_Source;UID=SA;PWD=abc123"
''******************************************************************************************
Function db_connect( byRef curSession ,connection_string)
	dim connection
	on error Resume next
	' Opening connection
	set connection = CreateObject("ADODB.Connection")
	If Err.Number <> 0 then
		db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
		err.clear
		Exit Function
	End If

	connection.Open connection_string
	If Err.Number <> 0 then
		db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
		err.clear
		Exit Function
	End If
	set curSession=connection
	db_connect=0
End Function

''******************************************************************************************
' db_disconnect
' ---------------------
' The function disconnects from the database and deletes the session.
' curSession - the session name (string)
''******************************************************************************************
Function db_disconnect( byRef curSession )
	curSession.close
	set curSession = Nothing
End Function

''******************************************************************************************
' db_execute_query
' ---------------------------
' The function executes an SQL statement.
' Note that a db_connect for (arg1) must be called before this function
' curSession - The session name (string)
' SQL - An SQL statement
''******************************************************************************************
Function db_execute_query ( byRef curSession , SQL)
	set rs = curSession.Execute( SQL )
	set db_execute_query = rs
End Function
''******************************************************************************************
' db_get_rows_count
' ----------------------------
' The function returns the number of rows in the record set
' curRS - Variable, containing a record set, that contains all values that retrieved from the database by query execution
''******************************************************************************************
Function db_get_rows_count( byRef curRS )
	dim rows
	 = 0
	curRS.MoveFirst
	Do Until curRS.EOF
		rows = rows+1
		curRS.MoveNext
	Loop
	db_get_rows_count = rows
End Function

''******************************************************************************************
' db_get_rows_count_SQL
' ------------------------------------
' The function returns the number of rows that are the result of a given SQL statement
' curSession - The session name (string)
' CountSQL - SQL statement
''******************************************************************************************
Function db_get_rows_count_SQL( byRef curSession ,CountSQL )
	dim cur_rs
	set cur_rs = curSession.Execute( CountSQL )
	db_get_rows_count_SQL = cur_rs.fields(0).value
End Function

''******************************************************************************************
' db_get_field_value_SQL
' -----------------------------------
' curSession - Variable that denotes the current active connection
' tableName - Name of the table, from which the value should be retrieved
' rowIndex - Row number
' colName - The column name
''******************************************************************************************
Function db_get_field_value_SQL( curSession , tableName , rowIndex , colName )
	dim rs
	SQL = " select " & colName & " from " & tableName
	set rs = curSession.Execute( SQL )
	rs.move rowIndex
	db_get_field_value_SQL = rs.fields(colName).value
End Function

''******************************************************************************************
' db_get_field_value
' --------------------------
' The function returns the value of a single item of an executed query.
' Note that a db_execute_query for (arg1) must called before this function
' curRecordSet - Variable, containing a record set, that contains all values retrieved from the database by query execution
' rowIndex - The row index number (zero-based)
' colIndex - The column index number (zero-based) or the column name.
' returned values
' -1 - Requested field index more than exists more than once in record set
''******************************************************************************************
Function db_get_field_value( curRecordSet , rowIndex , colIndex )
	dim curRow

	curRecordSet.MoveFirst
	count_fields = curRecordSet.fields.count-1
	If ( TypeName(colIndex)<> "String" ) and ( count_fields < colIndex ) then
		db_get_field_value = -1 'requested field index exists more than once in recordset
	Else
		curRecordSet.Move rowIndex
		db_get_field_value = curRecordSet.fields(colIndex).Value
	End If
End Function

''******************************************************************************************
' db_set_field_value
' ---------------------------
' The function changes the value of a field according to a search criteria.
' We search for a certain row according to a column name and the desired vale, then we change a value in that row according
' to a desired columns
' curConnection - The session name (string)
' tableName - Name of the table from which the value should be retrieved
' colFind - The column which to search for the criteria
' colFindValue - The value for which to search in the column
' colChange - The column in which we want to change the value
' colChangeValue - The new value
' returned values
' -1 - Requested field index that does not exist in the recordset
''******************************************************************************************
Function db_set_field_value(curConnection, tableName , colFind , colFindValue, colChange, colChangeValue)
	dim curRow
	dim updateSQL
	dim checkSQL

	checkSQL = "select * from Details"
	set myrs1 = db_execute_query( curConnection , SQL )
	myrs1.MoveFirst
	count_fields = myrs1.fields.count
	If ( TypeName(colFind)<> "String" ) or ( TypeName(colChange)<> "String" ) then
		db_set_field_value = -1 'requested field index that does not exists in the record set
	Else
		updateSQL = "UPDATE " & tableName & " SET " & colChange & "='" & colChangeValue & "' WHERE " & colFind & "='" & colFindValue & "'"
		set myrs1 = db_execute_query( curConnection , updateSQL )
		db_set_field_value = 1 'operation succeeded
	End If
End Function

''******************************************************************************************
' db_add_row
' -----------------
' The function adds a new row to the desired table
' curConnection - Variable, containing a recordset, that contains all the values to be retrieved from the database by query execution
' tableName - Name of the table, from which the value should be retrieved
' values - Array that contains values to be entered in a new row to the table
' Note: The function must receive values for all the columns in the table.
' returned value.
' -1 - The number of values to be entered to the table does not match the number of columns
' 1 - Execution of the query succeed and the data was entered to the table
''******************************************************************************************
Function db_add_row(curConnection, tableName , byRef values)
	dim i
	dim updateSQL
	dim myrs1

	updateSQL = "INSERT INTO " & tableName & " VALUES ("
	arrLen = UBound (values) - LBound (values) + 1

	set myrs1=db_execute_query( curConnection , SQL )
	myrs1.MoveFirst
	count_fields = myrs1.fields.count
	' Check whether the number of values match the number of columns
	If arrLen <> count_fields then
		db_add_row = -1
	Else
		For i = 0 to arrLen-1
			updateSQL = updateSQL & values (i)
			If i <> arrLen-1 then
				updateSQL = updateSQL & ","
			End If
		Next
		updateSQL = updateSQL & ")"
		set myrs1 = db_execute_query( curConnection , updateSQL )
		db_add_row = 1
	End If
End Function

''******************************************************************************************
' represent_values_of_RecordSet
' ---------------------------------------------
' The function reports all the values of fields in a record set
' curRS - Variable, containing the recordset, that contains all the values that were retrieved from the database by the query execution
''******************************************************************************************
Function represent_values_of_RecordSet( myrs)
	dim curRowString
	myrs.MoveFirst
	reporter.ReportEvent 4,"Fields quantity" , myrs.fields.count
	count_fields = myrs.fields.count-1
	curRow=0
	Do Until myrs.EOF
		curRowString= ""
		curRow = curRow+1
		For ii=0 to count_fields
			curRowString = curRowString& "Field " &"==> " & myrs.fields(ii).Name &" : Value ==>" & myrs.fields(ii).Value & vbCrLf
		Next
		myrs.MoveNext
		reporter.ReportEvent 4,"Current row"& curRow , curRowString
	Loop
End Function