Reference no: EM1375654

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'