######################################################################### ! STARTING SCRIPTBASIC ODBC MODULE TEST PROGRAM v1.0b29.23 ######################################################################### ######################################################################### ! Connecting to ODBC data sourcees using ODBC::RealConnect... ######################################################################### > DB[1] = ODBC::RealConnect(DSN[1], USER[1], PASS[1]) DB[1] = 1 > DB[2] = ODBC::RealConnect(DSN[2], USER[2], PASS[2]) DB[2] = 2 ######################################################################### ! Creating table Person in all data sources... ######################################################################### > ODBC::Query DB[1],"CREATE TABLE Person (LastName varchar(30), FirstName varchar(30), Age smallint, ID integer)" > ODBC::Query DB[2],"CREATE TABLE Person (LastName varchar(30), FirstName varchar(30), Age smallint, ID integer)" ######################################################################### ! Creating table Phone in all data sources... ######################################################################### > ODBC::Query DB[1],"CREATE TABLE Phone (ID int, Phone varchar(30))" > ODBC::Query DB[2],"CREATE TABLE Phone (ID int, Phone varchar(30))" ######################################################################### ! Populating table Person in all data sources... ######################################################################### > ODBC::Query DB[1],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Smith', 'Peter', 33, 1)" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Pan', 'Peter', 40, 2)" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Smith', 'John', 30, 3)" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Plymouth', 'Mary', 52, 4)" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('McGill', 'Ann', 15, 5)" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('McGill', 'Donald', 17, 6)" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Smith', 'Peter', 33, 1)" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Pan', 'Peter', 40, 2)" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Smith', 'John', 30, 3)" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('Plymouth', 'Mary', 52, 4)" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('McGill', 'Ann', 15, 5)" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Person (LastName,FirstName,Age,ID) VALUES ('McGill', 'Donald', 17, 6)" Number of affected rows = 1 ######################################################################### ! Populating table Phone in all data sources... ######################################################################### > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (1, '12345678')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (2, '23456789')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (2, '23456788')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (2, '23456787')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (3, '34567890')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (4, '45678901')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (5, '5678')" Number of affected rows = 1 > ODBC::Query DB[1],"INSERT INTO Phone (ID,Phone) VALUES (6, '5678')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (1, '12345678')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (2, '23456789')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (2, '23456788')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (2, '23456787')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (3, '34567890')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (4, '45678901')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (5, '5678')" Number of affected rows = 1 > ODBC::Query DB[2],"INSERT INTO Phone (ID,Phone) VALUES (6, '5678')" Number of affected rows = 1 ######################################################################### ! Selecting all data from table Person in all data sources... ! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...) ######################################################################### > ODBC::Query DB[1],"SELECT * FROM Person" ------------------------------------------------------------------------- LASTNAME | FIRSTNAME | AGE | ID ------------------------------------------------------------------------- Smith | Peter | 33 | 1 Pan | Peter | 40 | 2 Smith | John | 30 | 3 Plymouth | Mary | 52 | 4 McGill | Ann | 15 | 5 McGill | Donald | 17 | 6 ------------------------------------------------------------------------- > ODBC::Query DB[2],"SELECT * FROM Person" ------------------------------------------------------------------------- LastName | FirstName | Age | ID ------------------------------------------------------------------------- Smith | Peter | 33 | 1 Pan | Peter | 40 | 2 Smith | John | 30 | 3 Plymouth | Mary | 52 | 4 McGill | Ann | 15 | 5 McGill | Donald | 17 | 6 ------------------------------------------------------------------------- ######################################################################### ! Selecting only 3 columns from table Person in all data sources... ! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...) ######################################################################### > ODBC::Query DB[1],"SELECT LastName,FirstName,ID FROM Person" ------------------------------------------------------------------------- LASTNAME | FIRSTNAME | ID ------------------------------------------------------------------------- Smith | Peter | 1 Pan | Peter | 2 Smith | John | 3 Plymouth | Mary | 4 McGill | Ann | 5 McGill | Donald | 6 ------------------------------------------------------------------------- > ODBC::Query DB[2],"SELECT LastName,FirstName,ID FROM Person" ------------------------------------------------------------------------- LastName | FirstName | ID ------------------------------------------------------------------------- Smith | Peter | 1 Pan | Peter | 2 Smith | John | 3 Plymouth | Mary | 4 McGill | Ann | 5 McGill | Donald | 6 ------------------------------------------------------------------------- ######################################################################### ! Listing each person's name and phone number ! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...) ######################################################################### > ODBC::Query DB[1],"SELECT LastName, FirstName, Phone FROM Person, Phone WHERE Person.ID = Phone.ID" ------------------------------------------------------------------------- LASTNAME | FIRSTNAME | PHONE ------------------------------------------------------------------------- Smith | Peter | 12345678 Pan | Peter | 23456789 Pan | Peter | 23456788 Pan | Peter | 23456787 Smith | John | 34567890 Plymouth | Mary | 45678901 McGill | Ann | 5678 McGill | Donald | 5678 ------------------------------------------------------------------------- > ODBC::Query DB[2],"SELECT LastName, FirstName, Phone FROM Person, Phone WHERE Person.ID = Phone.ID" ------------------------------------------------------------------------- LastName | FirstName | Phone ------------------------------------------------------------------------- Smith | Peter | 12345678 Pan | Peter | 23456789 Pan | Peter | 23456788 Pan | Peter | 23456787 Smith | John | 34567890 Plymouth | Mary | 45678901 McGill | Ann | 5678 McGill | Donald | 5678 ------------------------------------------------------------------------- ######################################################################### ! Doing a SELECT that returns an empty table... ! Printing tables using both ODBC::FetchArray(...) and ODBC::FetchHash(...) ######################################################################### > ODBC::Query DB[1],"SELECT * FROM Phone WHERE ID = 987654321" No result. ------------------------------------------------------------------------- > ODBC::Query DB[2],"SELECT * FROM Phone WHERE ID = 987654321" No result. ------------------------------------------------------------------------- ######################################################################### ! Testing COUNT... Select will return the number of people over 18 in table Person. ######################################################################### > ODBC::Query DB[1],"SELECT COUNT(*) FROM Person WHERE Age >= 18" ------------------------------------------------------------------------- COUNT(*) ------------------------------------------------------------------------- 4 ------------------------------------------------------------------------- > ODBC::Query DB[2],"SELECT COUNT(*) FROM Person WHERE Age >= 18" ------------------------------------------------------------------------- COUNT(*) ------------------------------------------------------------------------- 4 ------------------------------------------------------------------------- ######################################################################### ! Testing UPDATE... (1) ! Making every person a year older in table Person. ######################################################################### > ODBC::Query DB[1],"UPDATE Person SET Age = Age + 1" Number of affected rows = 6 Printing table after update. > ODBC::Query DB[1],"SELECT * FROM Person" ------------------------------------------------------------------------- LASTNAME | FIRSTNAME | AGE | ID ------------------------------------------------------------------------- Smith | Peter | 34 | 1 Pan | Peter | 41 | 2 Smith | John | 31 | 3 Plymouth | Mary | 53 | 4 McGill | Ann | 16 | 5 McGill | Donald | 18 | 6 ------------------------------------------------------------------------- > ODBC::Query DB[2],"UPDATE Person SET Age = Age + 1" Number of affected rows = 6 Printing table after update. > ODBC::Query DB[2],"SELECT * FROM Person" ------------------------------------------------------------------------- LastName | FirstName | Age | ID ------------------------------------------------------------------------- Smith | Peter | 34 | 1 Pan | Peter | 41 | 2 Smith | John | 31 | 3 Plymouth | Mary | 53 | 4 McGill | Ann | 16 | 5 McGill | Donald | 18 | 6 ------------------------------------------------------------------------- ######################################################################### ! Testing UPDATE... (2) ! Making every person under 50 a year older in table Person. ######################################################################### > ODBC::Query DB[1],"UPDATE Person SET Age = Age + 1 WHERE Age < 50" Number of affected rows = 5 Printing table after update. > ODBC::Query DB[1],"SELECT * FROM Person" ------------------------------------------------------------------------- LASTNAME | FIRSTNAME | AGE | ID ------------------------------------------------------------------------- Smith | Peter | 35 | 1 Pan | Peter | 42 | 2 Smith | John | 32 | 3 Plymouth | Mary | 53 | 4 McGill | Ann | 17 | 5 McGill | Donald | 19 | 6 ------------------------------------------------------------------------- > ODBC::Query DB[2],"UPDATE Person SET Age = Age + 1 WHERE Age < 50" Number of affected rows = 5 Printing table after update. > ODBC::Query DB[2],"SELECT * FROM Person" ------------------------------------------------------------------------- LastName | FirstName | Age | ID ------------------------------------------------------------------------- Smith | Peter | 35 | 1 Pan | Peter | 42 | 2 Smith | John | 32 | 3 Plymouth | Mary | 53 | 4 McGill | Ann | 17 | 5 McGill | Donald | 19 | 6 ------------------------------------------------------------------------- ######################################################################### ! Closing data sources... (We will reconnect to them.) ######################################################################### >ODBC::Close DB[1] >ODBC::Close DB[2] ######################################################################### ! Reconnecting to ODBC data sourcees using ODBC::Connect... ######################################################################### > DB[1] = ODBC::Connect(DSN[1]) DB[1] = 3 > DB[2] = ODBC::Connect(DSN[2]) DB[2] = 4 ######################################################################### ! Reconnecting using ODBC::Connect without closing open connections ... ######################################################################### > DB[1] = ODBC::Connect(DSN[1]) DB[1] = 5 > DB[2] = ODBC::Connect(DSN[2]) DB[2] = 6 ######################################################################### ! Testing DELETE... (2) ! Deleting all McGills from table Person ######################################################################### > ODBC::Query DB[1],"DELETE FROM Person WHERE LastName = 'McGill'" Number of affected rows = 2 Printing table after delete. > ODBC::Query DB[1],"SELECT * FROM Person" ------------------------------------------------------------------------- LASTNAME | FIRSTNAME | AGE | ID ------------------------------------------------------------------------- Smith | Peter | 35 | 1 Pan | Peter | 42 | 2 Smith | John | 32 | 3 Plymouth | Mary | 53 | 4 ------------------------------------------------------------------------- > ODBC::Query DB[2],"DELETE FROM Person WHERE LastName = 'McGill'" Number of affected rows = 2 Printing table after delete. > ODBC::Query DB[2],"SELECT * FROM Person" ------------------------------------------------------------------------- LastName | FirstName | Age | ID ------------------------------------------------------------------------- Smith | Peter | 35 | 1 Pan | Peter | 42 | 2 Smith | John | 32 | 3 Plymouth | Mary | 53 | 4 ------------------------------------------------------------------------- ######################################################################### ! NOW ERROR CODES ARE EXAMINED, SO IT IS ALL RIGHT IF YOU SEE ERRORS. ######################################################################### ######################################################################### ! Trying to connect to an unconfigured connectinon using ODBC::RealConnect ######################################################################### > DB_N = ODBC::RealConnect("DSN_not_in_config", "user", "pass") ************************************************************************* An error occured. Error code: 0x81003 ************************************************************************* ######################################################################### ! Trying to connect to badly configured connectinon using ODBC::Connect ! The resource is not registered in the ODBC Driver Manager ######################################################################### > DB_N = ODBC::Connect("badodbctest") ************************************************************************* An error occured. Error code: 0x81003 ************************************************************************* ######################################################################### ! Testing query execution error on all connceted resources (1) ######################################################################### > ODBC::Query DB[1],"CREATE LIGHT" ************************************************************************* An error occured. Error code: 0x81004 [Oracle][ODBC][Ora]ORA-00901: nem megengedett CREATE parancs ************************************************************************* > ODBC::Query DB[2],"CREATE LIGHT" ************************************************************************* An error occured. Error code: 0x81004 [MySQL][ODBC 3.51 Driver][mysqld-3.23.49-nt]You have an error in your SQL syntax near 'LIGHT' at line 1 ************************************************************************* ######################################################################### ! Testing query execution error on all connceted resources (2) ######################################################################### > ODBC::Query DB[1],"SELECT nice_girls FROM Person WHERE Age>=18 AND Age<=22" ************************************************************************* An error occured. Error code: 0x81004 [Oracle][ODBC][Ora]ORA-00904: nem megengedett oszlopnév ************************************************************************* > ODBC::Query DB[2],"SELECT nice_girls FROM Person WHERE Age>=18 AND Age<=22" ************************************************************************* An error occured. Error code: 0x81004 [MySQL][ODBC 3.51 Driver][mysqld-3.23.49-nt]Unknown column 'nice_girls' in 'field list' ************************************************************************* ######################################################################### ! Trying to call FetchArray with an argument that does not evaluate to lvalue. ######################################################################### > ODBC::FetchArray(DB[i], 1) ************************************************************************* An error occured. Error code: 0x81006 ************************************************************************* > ODBC::FetchArray(DB[i], 1) ************************************************************************* An error occured. Error code: 0x81006 ************************************************************************* ######################################################################### ! Trying to call FetchHash with an argument that does not evaluate to lvalue. ######################################################################### > ODBC::FetchHash(DB[i], 1) ************************************************************************* An error occured. Error code: 0x81006 ************************************************************************* > ODBC::FetchHash(DB[i], 1) ************************************************************************* An error occured. Error code: 0x81006 ************************************************************************* ######################################################################### ! Closing data sources... (We will reconnect to them.) ######################################################################### >ODBC::Close DB[1] >ODBC::Close DB[2] ######################################################################### ! Reconnecting to ODBC data sourcees using ODBC::Connect... ######################################################################### > DB[1] = ODBC::Connect(DSN[1]) DB[1] = 7 > DB[2] = ODBC::Connect(DSN[2]) DB[2] = 8 ######################################################################### ! Trying to call FetchArray without a preceeding query execution. ######################################################################### > ODBC::FetchArray(DB[i],arr) ************************************************************************* An error occured. Error code: 0x81005 [MySQL][ODBC 3.51 Driver]Unsupported option: 1750 to SQLGetInfo ************************************************************************* > ODBC::FetchArray(DB[i],arr) ************************************************************************* An error occured. Error code: 0x81005 ************************************************************************* ######################################################################### ! Trying to call FetchHash without a preceeding query execution. ######################################################################### > ODBC::FetchHash(DB[i],arr) ************************************************************************* An error occured. Error code: 0x81005 ************************************************************************* > ODBC::FetchHash(DB[i],arr) ************************************************************************* An error occured. Error code: 0x81005 ************************************************************************* ######################################################################### ! END OF ERROR CODE TESTING ######################################################################### ######################################################################### ! Dropping table Person in all data sources... ######################################################################### > ODBC::Query DB[1],"DROP TABLE Person" > ODBC::Query DB[2],"DROP TABLE Person" ######################################################################### ! Dropping table Phone in all data sources... ######################################################################### > ODBC::Query DB[1],"DROP TABLE Phone" > ODBC::Query DB[2],"DROP TABLE Phone" ######################################################################### ! END OF SCRIPTBASIC ODBC MODULE TEST PROGRAM ######################################################################### I suggest you redirect the output of this program to a text file for easier examination. To do so run the following command: scriba odbctest.bas > odbctest_result.txt