Project:
1. install MySQL DBMS (and DB/language connector) software on your machine,
2. create World database using MySQL (using command files - NOT a Setup PROGRAM)
3. write a Java or C# or ... program to access the DB.
Batch processing is used:
• queries (data retrieval & update requests) are in a transaction file
• output sent to a log file.
Steps:
A. Download & install MySQL on your machine.
B. Download & install appropriate connector for MySQL which allows Java or C# to interact with a MySQL database.
C. SETUP [NOT A PROGRAM]: Use MySQL and the script files provided (which need a slight alteration) to create the WorldDB consisting of Country and CountryLanguage tables (though NOT the City table).
D. UserApp PROGRAM with 1 (or more) additional class (in separate file)
1. program does batch processing (using "INPUT STREAM Processing Algorithm" [that is, loop til EOF { read 1 transaction, then deal with it completely } ].
2. Trans file & Log file handling are done in main program or a separate UI class
3. 4 local methods (in UserApp), SelectHandler, InsertHandler, DeleteHandler, UpdateHandler - each takes the transaction line (when it' called upon by Main) and builds (somehow) the appropriate sqlString.
4. 2 methods in a SEPARATE CLASS, DBAccess (in a physically separate file), do actual SQL DB interaction:
a. RetrieveData for handling SELECTs
b. ChangeData for handling INSERTs & DELETEs & UPDATEs
For each transaction:
1. Echo original transaction request (from transaction file)
2. For INSERTs & DELETEs (but not SELECTs or UPDATEs),
show actual SQL statement which your program BUILT, which it sent to the DBS for processing
3. Show results from executing the "query" - i.e.,
a. a "table" for a SELECT
b. a reassurance message for changes: OK, INSERT done
OK, DELETE done
OK, UPDATE done
4. Write out a blank line.
Each transaction on a new line - 1st column is transCode: S, I, D, U (always caps).
S(elect) transactions (RETRIEVE data from DB)
- transaction data is an actual SQL statement to be used "as is".
[This is not a common/proper programming approach, but time is short so...].
- Allow for 0 or 1 or Many ROWS to be returned to the program from the DB.
- rdr.FieldCount gives the number of COLUMNS there'll be for a result set
- "table" printed to TheLog file does NOT NEED TO:
- be in a box (e.g., like a typical interactive result in the command window)
- have column headings (but if it does,
DON'T use what's in my demo program &
DON'T hardcode the header labels (as my demo program does)
- be perfectly aligned since this SINGLE GENERIC METHOD doesn't know
what data type rdr[0] or rdr[1] or the other columns are.
U(pdate) transactions (CHANGE data in DB)
- transaction data is an actual SQL statement to be used "as is"
D(elete) transactions (CHANGE data in DB)
- transaction data is NOT an SQL statement
- your program has to construct it from the "parameters" supplied
- Basic format for a simple DELETE SQL statement:
DELETE FROM Country WHERE Name = 'Disneyland'
Transaction data (i.e., the parameters) would look like:
D Country|Name|'Disneyland'
I(nsert) transactions (CHANGE data in DB)
- transaction data is NOT an SQL statement
- your program has to construct it from the "parameters" supplied using various string-handling methods (e.g., Split, +, etc.)
- 2 basic formats for simple SQL INSERT statements:
1) all-columns INSERT (so column names NOT specified in SQL statement):
INSERT INTO CountryLanguage VALUES ('USA','C#','F',0.01)
Transaction data (parameters) looks like:
I CountryLanguage|'USA','C#','F',0.01
2) some-columns INSERT (so column names MUST BE specified in SQL stmt):
INSERT INTO Country(Code, Name) VALUES ('HEX','Hexland')
Transaction data (parameters) looks like:
I Country|(Code, Name)|'HEX','Hexland'