' odbc_test.bas v1.0b29.23
' ScriptBasic ODBC module test program
' ------------------------------------------------------------------------
' 1.0b29 is the version of ScriptBasic (with ODBC module) to test
' subversion 23 identifies this particular version of odbc_test.bas 
' ------------------------------------------------------------------------
' Copyright (c) Gábor Pohl (2002)
' All rights reserved.
' 
' Redistribution and use in source and binary forms, with or without
' modification, are permitted provided that the following conditions
' are met:
' 1. Redistributions of source code must retain the above copyright
'    notice, this list of conditions and the following disclaimer.
' 2. Redistributions in binary form must reproduce the above copyright
'    notice, this list of conditions and the following disclaimer in the
'    documentation and/or other materials provided with the distribution.
' 3. The names of contributors may not be used to endorse or promote 
'    products derived from this software without specific prior written
'    permission.
' 
' THIS SOFTWARE IS PROVIDED ``AS IS'' AND
' ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
' IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
' ARE DISCLAIMED.  IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE
' FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
' DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
' OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
' HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
' LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
' OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
' SUCH DAMAGE.
' ------------------------------------------------------------------------

import odbc.bas

' *************************************************************************
'  odbctest config
' *************************************************************************

' ODBC data source parameters 
' DSN_? is the "Data Source Name" parameter of ODBC::Connect and ODBC::RealConnect
' USER_? and PASS_? are only used in ODBC::RealConnect

DSN[1]="odbctest"
USER[1]="odbctest"
PASS[1]="pwd"

DSN[2]="odbctest2"
USER[2]="odbctest"
PASS[2]="pwd"

NUM_OF_DATA_SOURCES = 2


' *************************************************************************
'  End of odbctest config
' *************************************************************************

'-------------------------------------------------------------------------
' START OF MAIN CODE
'-------------------------------------------------------------------------
print
print "\n#########################################################################\n"
print "! STARTING SCRIPTBASIC ODBC MODULE TEST PROGRAM v1.0b29.23"
print "\n#########################################################################\n"
print

print
print "\n#########################################################################\n"
print "! Connecting to ODBC data sourcees using ODBC::RealConnect..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> DB[",i,"] = ODBC::RealConnect(DSN[",i,"], USER[",i,"], PASS[",i,"])\n"
	DB[i] = ODBC::RealConnect(DSN[i], USER[i], PASS[i])
	print "DB[",i,"] = ",DB[i],"\n"
NEXT


print
print "\n#########################################################################\n"
print "! Creating table Person in all data sources..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"CREATE TABLE Person (LastName varchar(30), FirstName varchar(30), Age smallint, ID integer)\"\n"
	ODBC::Query DB[i],"CREATE TABLE Person (LastName varchar(30), FirstName varchar(30), Age smallint, ID integer)"
NEXT
print
print "\n#########################################################################\n"
print "! Creating table Phone in all data sources..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"CREATE TABLE Phone (ID int, Phone varchar(30))\"\n"
	ODBC::Query DB[i],"CREATE TABLE Phone (ID integer, Phone varchar(30))"
NEXT


print
print "\n#########################################################################\n"
print "! Populating table Person in all data sources..."
print "\n#########################################################################\n"
print

lname[1] = "Smith"
fname[1] = "Peter"
age[1] = 33

lname[2] = "Pan"
fname[2] = "Peter"
age[2] = 40

lname[3] = "Smith"
fname[3] = "John"
age[3] = 30

lname[4] = "Plymouth"
fname[4] = "Mary"
age[4] = 52

lname[5] = "McGill"
fname[5] = "Ann"
age[5] = 15

lname[6] = "McGill"
fname[6] = "Donald"
age[6] = 17

'id is assigned in the loop that populates the table

NUM_OF_PEOPLE = 6
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	FOR j=1 TO NUM_OF_PEOPLE STEP 1
	    on error goto ErrorHappened
		print "> ODBC::Query DB[",i,"],\"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('"&lname[j]&"', '"&fname[j]&"', "&age[j]&",  "&j&")\"\n"
		ODBC::Query DB[i],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('"&lname[j]&"', '"&fname[j]&"', "&age[j]&",  "&j&")"
		print "Number of affected rows = ",ODBC::AffectedRows(DB[i]),"\n"
	NEXT
NEXT


print
print "\n#########################################################################\n"
print "! Populating table Phone in all data sources..."
print "\n#########################################################################\n"
print
id[1] = 1
phone[1] = "12345678"

id[2] = 2
phone[2] = "23456789"

id[3] = 2
phone[3] = "23456788"

id[4] = 2
phone[4] = "23456787"

id[5] = 3
phone[5] = "34567890"

id[6] = 4
phone[6] = "45678901"

id[7] = 5
phone[7] = "5678"

id[8] = 6
phone[8] = "5678"

NUM_OF_PHONES = 8

FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	FOR j=1 TO NUM_OF_PHONES STEP 1
	    on error goto ErrorHappened
		print "> ODBC::Query DB[",i,"],\"INSERT INTO Phone (ID,Phone) VALUES ("&id[j]&", '"&phone[j]&"')\"\n"
		ODBC::Query DB[i],"INSERT INTO Phone (ID,Phone) VALUES ("&id[j]&", '"&phone[j]&"')"
		print "Number of affected rows = ",ODBC::AffectedRows(DB[i]),"\n"
	NEXT
NEXT


print
print "\n#########################################################################\n"
print "! Selecting all data from table Person in all data sources...\n"
print "! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...)"
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT * FROM Person\"\n"
	ODBC::Query DB[i],"SELECT * FROM Person"
	CALL PrintTable(DB[i])
NEXT


print
print "\n#########################################################################\n"
print "! Selecting only 3 columns from table Person in all data sources...\n"
print "! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...)"
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT LastName,FirstName,ID FROM Person\"\n"
	ODBC::Query DB[i],"SELECT LastName,FirstName,ID FROM Person"
	CALL PrintTable(DB[i])
NEXT


print
print "\n#########################################################################\n"
print "! Listing each person's name and phone number\n"
print "! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...)"
print "\n#########################################################################\n"
print

FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT LastName, FirstName, Phone FROM Person, Phone WHERE Person.ID = Phone.ID\"\n"
	ODBC::Query DB[i],"SELECT LastName, FirstName, Phone FROM Person, Phone WHERE Person.ID = Phone.ID"
	CALL PrintTable(DB[i])
NEXT


print
print "\n#########################################################################\n"
print "! Doing a SELECT that returns an empty table...\n"
print "! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...)"
print "\n#########################################################################\n"
print

FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT * FROM Phone WHERE ID = 987654321\"\n"
	ODBC::Query DB[i],"SELECT * FROM Phone WHERE ID = 987654321"
	CALL PrintTable(DB[i])
NEXT


print
print "\n#########################################################################\n"
print "! Testing COUNT... \n"
print " Select will return the number of people over 18 in table Person."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT COUNT(*) FROM Person WHERE Age >= 18\"\n"
	ODBC::Query DB[i],"SELECT COUNT(*) FROM Person WHERE Age >= 18"
	CALL PrintTable(DB[i])
NEXT



print
print "\n#########################################################################\n"
print "! Testing UPDATE... (1)\n"
print "! Making every person a year older in table Person."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"UPDATE Person SET Age = Age + 1\"\n"
	ODBC::Query DB[i],"UPDATE Person SET Age = Age + 1"
	print "Number of affected rows = ",ODBC::AffectedRows(DB[i]),"\n"
	
	print "Printing table after update.\n"
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT * FROM Person\"\n"
	ODBC::Query DB[i],"SELECT * FROM Person"
	CALL PrintTable(DB[i])
NEXT

print
print "\n#########################################################################\n"
print "! Testing UPDATE... (2)\n"
print "! Making every person under 50 a year older in table Person. "
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"UPDATE Person SET Age = Age + 1 WHERE Age < 50\"\n"
	ODBC::Query DB[i],"UPDATE Person SET Age = Age + 1 WHERE Age < 50"
	print "Number of affected rows = ",ODBC::AffectedRows(DB[i]),"\n"
	
	print "Printing table after update.\n"
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT * FROM Person\"\n"
	ODBC::Query DB[i],"SELECT * FROM Person"
	CALL PrintTable(DB[i])
NEXT


print
print "\n#########################################################################\n"
print "! Closing data sources... (We will reconnect to them.)" 
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print ">ODBC::Close DB[",i,"]\n"
	ODBC::Close DB[i]
NEXT

print
print "\n#########################################################################\n"
print "! Reconnecting to ODBC data sourcees using ODBC::Connect..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> DB[",i,"] = ODBC::Connect(DSN[",i,"])\n"
	DB[i] = ODBC::Connect(DSN[i])
	print "DB[",i,"] = ",DB[i],"\n"
NEXT

print
print "\n#########################################################################\n"
print "! Reconnecting using ODBC::Connect without closing open connections ..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> DB[",i,"] = ODBC::Connect(DSN[",i,"])\n"
	DB[i] = ODBC::Connect(DSN[i])
	print "DB[",i,"] = ",DB[i],"\n"
NEXT


print
print "\n#########################################################################\n"
print "! Testing DELETE... (2)\n"
print "! Deleting all McGills from table Person"
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"DELETE FROM Person WHERE LastName = 'McGill'\"\n"
	ODBC::Query DB[i],"DELETE FROM Person WHERE LastName = 'McGill'"
	print "Number of affected rows = ",ODBC::AffectedRows(DB[i]),"\n"
	
	print "Printing table after delete.\n"
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT * FROM Person\"\n"
	ODBC::Query DB[i],"SELECT * FROM Person"
	CALL PrintTable(DB[i])
NEXT


print
print "\n#########################################################################\n"
print "! NOW ERROR CODES ARE EXAMINED, SO IT IS ALL RIGHT IF YOU SEE ERRORS."
print "\n#########################################################################\n"
print

' *************************************************************************
' THE FOLLOWING CODE CAUSES A SERIOUS ERROR 
' (scriba.exe is terminated by the OS)
' *************************************************************************
'
'print
'print "\n#########################################################################\n"
'print "! Trying to connect to an unconfigured connectinon using ODBC::Connect"
'print "\n#########################################################################\n"
'print
'
'on error goto ErrorHappened2
'print "> DB_N = ODBC::Connect(\"DSN_not_in_config\")\n"
'DB_N = ODBC::Connect("DSN_not_in_config") 
'
' *************************************************************************
' END OF CODE CAUSING SERIOUS ERROR
' *************************************************************************



print
print "\n#########################################################################\n"
print "! Trying to connect to an unconfigured connectinon using ODBC::RealConnect"
print "\n#########################################################################\n"
print
on error goto ErrorHappened2
print "> DB_N = ODBC::RealConnect(\"DSN_not_in_config\", \"user\", \"pass\")\n"
DB_N = ODBC::RealConnect("DSN_not_in_config", "user", "pass") 



print
print "\n#########################################################################\n"
print "! Trying to connect to badly configured connectinon using ODBC::Connect"
print "\n! The resource is not registered in the ODBC Driver Manager "
print "\n#########################################################################\n"
print
on error goto ErrorHappened2
print "> DB_N = ODBC::Connect(\"badodbctest\")\n"
DB_N = ODBC::Connect("badodbctest") 



' *************************************************************************
' THE FOLLOWING CODE CAUSES A SERIOUS ERROR 
' (scriba.exe is terminated by the OS)
' *************************************************************************
'
'print
'print "\n#########################################################################\n"
'print "! Trying to connect to badly configured connectinon using ODBC::Connect"
'print "\n! The resource configuration is not valid in scriba.conf "
'print "\n#########################################################################\n"
'print
'on error goto ErrorHappened2
'print "> DB_N = ODBC::Connect(\"badodbctest2\")\n"
'DB_N = ODBC::Connect("badodbctest2") 
'
' *************************************************************************
' END OF CODE CAUSING SERIOUS ERROR
' *************************************************************************


print
print "\n#########################################################################\n"
print "! Testing query execution error on all connceted resources (1)"
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"CREATE LIGHT\"\n"
	ODBC::Query DB[i],"CREATE LIGHT"
NEXT


print
print "\n#########################################################################\n"
print "! Testing query execution error on all connceted resources (2)"
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"SELECT nice_girls FROM Person WHERE Age>=18 AND Age<=22\"\n"
	ODBC::Query DB[i],"SELECT nice_girls FROM Person WHERE Age>=18 AND Age<=22"
NEXT


print
print "\n#########################################################################\n"
print "! Trying to call FetchArray with an argument that does not evaluate to lvalue."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::FetchArray(DB[i], 1)\n"
	ODBC::FetchArray(DB[i], 1)
NEXT

print
print "\n#########################################################################\n"
print "! Trying to call FetchHash with an argument that does not evaluate to lvalue."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::FetchHash(DB[i], 1)\n"
	ODBC::FetchHash(DB[i], 1)
NEXT

print
print "\n#########################################################################\n"
print "! Closing data sources... (We will reconnect to them.)" 
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print ">ODBC::Close DB[",i,"]\n"
	ODBC::Close DB[i]
NEXT

print
print "\n#########################################################################\n"
print "! Reconnecting to ODBC data sourcees using ODBC::Connect..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> DB[",i,"] = ODBC::Connect(DSN[",i,"])\n"
	DB[i] = ODBC::Connect(DSN[i])
	print "DB[",i,"] = ",DB[i],"\n"
NEXT


print
print "\n#########################################################################\n"
print "! Trying to call FetchArray without a preceeding query execution."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::FetchArray(DB[i],arr)\n"
	ODBC::FetchArray(DB[i],arr)
NEXT




print
print "\n#########################################################################\n"
print "! Trying to call FetchHash without a preceeding query execution."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::FetchHash(DB[i],arr)\n"
	ODBC::FetchHash(DB[i],arr)
NEXT


print
print "\n#########################################################################\n"
print "! END OF ERROR CODE TESTING"
print "\n#########################################################################\n"
print


print
print "\n#########################################################################\n"
print "! Dropping table Person in all data sources..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"DROP TABLE Person\"\n"
	ODBC::Query DB[i],"DROP TABLE Person"
NEXT



print
print "\n#########################################################################\n"
print "! Dropping table Phone in all data sources..."
print "\n#########################################################################\n"
print
FOR i=1 TO NUM_OF_DATA_SOURCES STEP 1
	on error goto ErrorHappened
	print "> ODBC::Query DB[",i,"],\"DROP TABLE Phone\"\n"
	ODBC::Query DB[i],"DROP TABLE Phone"
NEXT



' *************************************************************************
' THE FOLLOWING CODE CAUSES A SERIOUS ERROR 
' (scriba.exe is terminated by the OS)
' *************************************************************************
'
'print
'print "\n#########################################################################\n"
'print "! Trying to query a closed connection"
'print "\n#########################################################################\n"
'print
'on error goto ErrorHappened
'print "> ODBC::Query 1,\"DROP TABLE Person\"\n"
'ODBC::Query 1,"DROP TABLE Person"
'
' *************************************************************************
' END OF CODE CAUSING SERIOUS ERROR
' *************************************************************************


print
print "\n#########################################################################\n"
print "! END OF SCRIPTBASIC ODBC MODULE TEST PROGRAM"
print "\n#########################################################################\n"
print
print "I suggest you redirect the output of this program to a text file \n"
print "for easier examination.\n"
print "To do so run the following command:\n"
print "scriba odbctest.bas > odbctest_result.txt\n"



END
'-------------------------------------------------------------------------
' END OF MAIN CODE
'-------------------------------------------------------------------------





'-------------------------------------------------------------------------
' ERROR HANDLER
'-------------------------------------------------------------------------
ErrorHappened:
print "\n*************************************************************************\n"
print "An error occured.\n"
print "Error code: 0x",hex(error()),"\n"
FOR error_index=1 TO NUM_OF_DATA_SOURCES STEP 1
	If DB[error_index] <> undef then
		print chomp(ODBC::Error(DB[error_index])),"\n"
	End If
NEXT
print "*************************************************************************\n"
Resume next
'-------------------------------------------------------------------------


'-------------------------------------------------------------------------
' ERROR HANDLER FOR TESTING ERROR CODES ON CONNECTION "DB_N"
'-------------------------------------------------------------------------
ErrorHappened2:
print "\n*************************************************************************\n"
print "An error occured.\n"
print "Error code: 0x",hex(error()),"\n"
If DB_N <> undef then
	print chomp(ODBC::Error(DB_N)),"\n"
End If
print "*************************************************************************\n"
Resume next
'-------------------------------------------------------------------------


'-------------------------------------------------------------------------
' SUB PrintTable(DB) prints out the result of a select statement
'-------------------------------------------------------------------------
SUB PrintTable(DB)
	local arr
	local R
	local i
	local u
	local column

	R = ODBC::FetchHash(DB,arr)
	If (R = 0) then 
		print "No result.\n"
	Else
		'getting column names
		u = ubound(arr)	
		FOR i = 0 TO u STEP 2
			column[i/2] = arr[i]
		NEXT
		
		'printing header
		print "-------------------------------------------------------------------------\n"
		u = ubound(column)
		FOR i = 0 TO u-1 STEP 1
			print column[i], " | "
		NEXT
		print column[u], "\n"
		print "-------------------------------------------------------------------------\n"
		'printing first raw
		u = ubound(column)
		FOR i = 0 TO u-1 STEP 1
			print arr{column[i]}, " | "
		NEXT
		print arr{column[u]}, "\n"
				
		'printing all other raws using ODBC::FetchArray(DB,arr)
		Do While ODBC::FetchArray(DB,arr)
			print join(" | ",arr),"\n"
		Loop
	End If
	print "-------------------------------------------------------------------------\n\n"
END SUB