 
-- Program name: DBCheckerCounty20051208.sql
-- Created:  by Gwo Shyu, ASD/EPA, 12/8/2005 5:44:45 PM.
--        :  This SQL program, auto-generated by a Microsoft Access program from 
--        :  EPA's NMIM ERWin schema/design, is intended to be used to check the NMIM database.
--        :  Listed below are items checked. 
--        :   1. All fields in all tables, e.g., wrong and/or missing field names, etc.
--        :   2. Empty Tables. 
--        :   3. NULL fields that are actually required.
--        :   4. Foreign key/referential integrity between parent & child tables,
--        :      e.g., missing record(s) in parent or child table, etc.
--        :   5. NMIM constraints, validation rules if embedded in the NMIM ERWin database design/schema.
 
-- Special DBChecker Errors: (number range 9000 - 9599) 
--   Error 9008: Data definition error - Primary key error.
--   Error 9010: Data definition error - number of fields not match that of your MySQL NMIM table.
--   Error 9012: Data definition error - field(s) not found in your MySQL NMIM table.
--   Error 9014: Data definition error - table structure error.
--   Error 9016: Data error - duplicate records. (Primary key violation).
--   Error 9018: Data error - unity error.
--   Error 9020: Data error - required field(s) is NULL.
--   Error 9022: Data error - Min/Max (data out of range).
--   Error 9024: Data error - foreign key violation.
--   Error 9026: Data definition (primary key) error.
--   Error 9028: Data definition (type) error.
--   Error 9030: Data definition (NULL/NOT NULL) error.
--   Error 9032: Data definition (size) error.
--   Error 9034: Data error - missing model year from table SCCVTypeDistribution (must have all years of 2000-2050).
-- Special DBChecker Warnings: (number range 9600 - 9999) 
--   Warning 9610: Table is empty.
--   Warning 9612: Foreign key existent in parent table but not used in child table(s).
 
\T C:\temp\NMIM.txt
SELECT CONCAT('Program started at ', CURTIME(), ' on ', CURDATE());
SET @StartTime := NULL;
SET @StartTime := CURTIME();
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: BaseYearVMT is empty', 'OK (table not empty)') AS CheckResult 
FROM BaseYearVMT;
 
SELECT BaseYear, VClass, RoadType, FIPSCountyId, FIPSStateId, VMT 
FROM BaseYearVMT LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT BaseYear, VClass, RoadType, FIPSCountyId, FIPSStateId, VMT 
FROM BaseYearVMT p 
WHERE ISNULL(p.BaseYear) OR ISNULL(p.VClass) OR ISNULL(p.RoadType) OR ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: County is empty', 'OK (table not empty)') AS CheckResult 
FROM County;
 
-- Validate table County
 
-- Check Altitude in County table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. Altitude in County,must be either H or L.' AS ErrorAltitude
FROM County t
WHERE t.Altitude NOT IN ('H','L')
GROUP BY Altitude
HAVING count(*) > 0 LIMIT 1 \p;
-- Validate table County
 
SELECT count(*) as cnt, 'Error 9996: Data out of range. OzoneSeasonStartMonth in County must be between 1 and 12.' AS ErrorOzoneSeasonStartMonth
FROM County t
WHERE t.OzoneSeasonStartMonth NOT BETWEEN 1 AND 12
GROUP BY OzoneSeasonStartMonth
HAVING count(*) > 0 LIMIT 1 \p;
-- Validate table County
 
SELECT count(*) as cnt, 'Error 9022: Data out of range. OzoneSeasonStartDay in County must be between 1 and 31.' AS ErrorOzoneSeasonStartDay
FROM County t
WHERE t.OzoneSeasonStartDay NOT BETWEEN 1 AND 31
GROUP BY OzoneSeasonStartDay
HAVING count(*) > 0 LIMIT 1 \p;
-- Validate table County
 
SELECT count(*) as cnt, 'Error 9022: Data out of range. OzoneSeasonEndMonth in County must be between 1 and 12.' AS OzoneSeasonEndMonth
FROM County t
WHERE t.OzoneSeasonEndMonth NOT BETWEEN 1 AND 12
GROUP BY OzoneSeasonEndMonth
HAVING count(*) > 0 LIMIT 1 \p;
-- Validate table County
 
SELECT count(*) as cnt, 'Error 9022: Data out of range. OzoneSeasonEndDay in County must be between 1 and 31' AS ErrorOzoneSeasonEndDay
FROM County t
WHERE t.OzoneSeasonEndDay NOT BETWEEN 1 AND 31
GROUP BY OzoneSeasonEndDay
HAVING count(*) > 0 LIMIT 1 \p;
SELECT FIPSCountyId, FIPSStateId, CountyName, Altitude, BarometricPressure, OzoneSeasonStartMonth, OzoneSeasonStartDay, OzoneSeasonEndMonth, OzoneSeasonEndDay, Stage2StartYear, PhaseInYears, LDVStage2Percent,  
HDVStage2Percent, NGVFractionFileName, CountyMonthHourDataSource, DataSourceId, CountyVMTMonthAllocDataSource 
FROM County LIMIT 1 ;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, CountyName, Altitude, BarometricPressure, OzoneSeasonStartMonth, OzoneSeasonStartDay, OzoneSeasonEndMonth, OzoneSeasonEndDay, Stage2StartYear, PhaseInYears, LDVStage2Percent,  
HDVStage2Percent, NGVFractionFileName, CountyMonthHourDataSource, DataSourceId, CountyVMTMonthAllocDataSource 
FROM County p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.CountyName) OR ISNULL(p.Altitude) OR ISNULL(p.BarometricPressure) OR ISNULL(p.OzoneSeasonStartMonth) OR ISNULL(p.OzoneSeasonStartDay) OR ISNULL(p.OzoneSeasonEndMonth) 
 OR ISNULL(p.OzoneSeasonEndDay) OR ISNULL(p.NGVFractionFileName) OR ISNULL(p.CountyVMTMonthAllocDataSource) \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyMap is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyMap;
 
SELECT FIPSCountyId, FIPSStateId, HwyRepFIPSCntyID, NRRepFIPSCntyID 
FROM CountyMap LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, HwyRepFIPSCntyID, NRRepFIPSCntyID 
FROM CountyMap p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.HwyRepFIPSCntyID) OR ISNULL(p.NRRepFIPSCntyID)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyMonth is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyMonth;
 
-- Validate table CountyMonth
 
-- Check Month in CountyMonth table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. Month in CountyMonth must be between 1 and 12.' AS ErrorMonth
FROM CountyMonth t
WHERE t.Month < 1 Or t.Month > 12
GROUP BY Month
HAVING count(*) > 0 LIMIT 1 \p;
SELECT FIPSCountyId, FIPSStateId, Month 
FROM CountyMonth LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, Month 
FROM CountyMonth p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Month)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyMonthHour is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyMonthHour;
 
-- Validate table CountyMonthHour
 
-- Check Month in CountyMonth table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. Month in CountyMonth must be between 1 and 12.' AS ErrorMonth
FROM CountyMonth t
WHERE t.Month < 1 Or t.Month > 12
GROUP BY Month
HAVING count(*) > 0 LIMIT 1 \p;
SELECT FIPSCountyId, FIPSStateId, Month, HourID, AverageTemp, AverageRelHumidity 
FROM CountyMonthHour LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, Month, HourID, AverageTemp, AverageRelHumidity 
FROM CountyMonthHour p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Month) OR ISNULL(p.HourID) OR ISNULL(p.AverageTemp) OR ISNULL(p.AverageRelHumidity)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyNRFile is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyNRFile;
 
SELECT FIPSCountyId, FIPSStateId, FileTypeID, CountyNRFileName, DataSourceId 
FROM CountyNRFile LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, FileTypeID, CountyNRFileName, DataSourceId 
FROM CountyNRFile p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.FileTypeID)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyVMTMonthAllocation is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyVMTMonthAllocation;
 
SELECT FIPSCountyId, FIPSStateId, VType, RoadType, Month, AllocFactor 
FROM CountyVMTMonthAllocation LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, VType, RoadType, Month, AllocFactor 
FROM CountyVMTMonthAllocation p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.VType) OR ISNULL(p.RoadType) OR ISNULL(p.Month) OR ISNULL(p.AllocFactor)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyYear is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyYear;
 
-- Validate table CountyYear
 
-- Check HasNRStateInputs in Countyyear table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. HasNRStateInputs in Countyyear must be either N or Y.' AS ErrorHasNRStateInputs
FROM Countyyear t
WHERE t.HasNRStateInputs NOT IN ('N','Y')
GROUP BY HasNRStateInputs
HAVING count(*) > 0 LIMIT 1 \p;
-- Validate table CountyYear
 
-- Check HasORStateInputs in Countyyear table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. HasORStateInputs in Countyyear must be either N or Y.' AS ErrorHasORStateInputs
FROM Countyyear t
WHERE t.HasORStateInputs NOT IN ('N','Y')
GROUP BY HasORStateInputs
HAVING count(*) > 0 LIMIT 1 \p;
SELECT FIPSCountyId, FIPSStateId, Year, ATPFileName, IMFileName, RegDistFileName, VMTByHourFileName, MileAccumFileName, TripsPerDayFileName, StartDistFileName, SoakDistFileName, HotSoakFileName, DiurnSoakActivityFileName,  
TripLengthFileName, DieselFractFileName, AvgSpeedDistBaseFileName, Stage2Pct, NRACTFileName, DataSourceId, HasNRStateInputs, HasORStateInputs,  
CountyYearMonthHourDataSource, BaseYearVMTDataSource 
FROM CountyYear LIMIT 1 ;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, Year, ATPFileName, IMFileName, RegDistFileName, VMTByHourFileName, MileAccumFileName, TripsPerDayFileName, StartDistFileName, SoakDistFileName, HotSoakFileName, DiurnSoakActivityFileName,  
TripLengthFileName, DieselFractFileName, AvgSpeedDistBaseFileName, Stage2Pct, NRACTFileName, DataSourceId, HasNRStateInputs, HasORStateInputs,  
CountyYearMonthHourDataSource, BaseYearVMTDataSource 
FROM CountyYear p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Year) OR ISNULL(p.ATPFileName) OR ISNULL(p.IMFileName) OR ISNULL(p.RegDistFileName) OR ISNULL(p.VMTByHourFileName) OR ISNULL(p.MileAccumFileName) 
 OR ISNULL(p.TripsPerDayFileName) OR ISNULL(p.StartDistFileName) OR ISNULL(p.SoakDistFileName) OR ISNULL(p.HotSoakFileName) OR ISNULL(p.DiurnSoakActivityFileName) OR ISNULL(p.TripLengthFileName) OR  
ISNULL(p.DieselFractFileName) OR ISNULL(p.AvgSpeedDistBaseFileName) OR ISNULL(p.NRACTFileName) OR ISNULL(p.HasNRStateInputs) OR ISNULL(p.HasORStateInputs) \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyYearMonth is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyYearMonth;
 
-- Validate table CountyYearMonth
 
-- Check Month in CountyMonth table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. Month in CountyMonth must be between 1 and 12.' AS ErrorMonth
FROM CountyMonth t
WHERE t.Month < 1 Or t.Month > 12
GROUP BY Month
HAVING count(*) > 0 LIMIT 1 \p;
SELECT FIPSCountyId, FIPSStateId, Year, Month, NGId, HwyDieselId, HwyGasolineId, NRGasolineId, NRDieselId, HwyFuelDataSource, NRFuelDataSource, RMDieselId 
FROM CountyYearMonth LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, Year, Month, NGId, HwyDieselId, HwyGasolineId, NRGasolineId, NRDieselId, HwyFuelDataSource, NRFuelDataSource, RMDieselId 
FROM CountyYearMonth p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Year) OR ISNULL(p.Month) OR ISNULL(p.HwyDieselId)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: CountyYearMonthHour is empty', 'OK (table not empty)') AS CheckResult 
FROM CountyYearMonthHour;
 
-- Validate table CountyYearMonthHour
 
-- Check Month in CountyMonth table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. Month in CountyMonth must be between 1 and 12.' AS ErrorMonth
FROM CountyMonth t
WHERE t.Month < 1 Or t.Month > 12
GROUP BY Month
HAVING count(*) > 0 LIMIT 1 \p;
SELECT FIPSCountyId, FIPSStateId, Year, Month, HourID, Temperature, RelativeHumidity 
FROM CountyYearMonthHour LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSCountyId, FIPSStateId, Year, Month, HourID, Temperature, RelativeHumidity 
FROM CountyYearMonthHour p 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Year) OR ISNULL(p.Month) OR ISNULL(p.HourID)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: DataSource is empty', 'OK (table not empty)') AS CheckResult 
FROM DataSource;
 
SELECT DataSourceId, Author, Date, Sponsor, DocumentId, QualityLevel, Comments 
FROM DataSource LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT DataSourceId, Author, Date, Sponsor, DocumentId, QualityLevel, Comments 
FROM DataSource p 
WHERE ISNULL(p.DataSourceId)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: Diesel is empty', 'OK (table not empty)') AS CheckResult 
FROM Diesel;
 
SELECT DieselId, DieselSulfur 
FROM Diesel LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT DieselId, DieselSulfur 
FROM Diesel p 
WHERE ISNULL(p.DieselId) OR ISNULL(p.DieselSulfur)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: EmissionType is empty', 'OK (table not empty)') AS CheckResult 
FROM EmissionType;
 
SELECT EmissionTypeID, EmissionType 
FROM EmissionType LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT EmissionTypeID, EmissionType 
FROM EmissionType p 
WHERE ISNULL(p.EmissionTypeID) OR ISNULL(p.EmissionType)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: FileType is empty', 'OK (table not empty)') AS CheckResult 
FROM FileType;
 
SELECT FileTypeID, FileTypeDescription 
FROM FileType LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FileTypeID, FileTypeDescription 
FROM FileType p 
WHERE ISNULL(p.FileTypeID) OR ISNULL(p.FileTypeDescription)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: Gasoline is empty', 'OK (table not empty)') AS CheckResult 
FROM Gasoline;
 
-- Validate table Gasoline
 
SELECT count(*) as cnt, 'Error 9022: Min/Max/Data out of range. RVPOxyWaiver in Gasoline, must be either 1 or 2' AS ErrorRVPOxyWaiver
FROM Gasoline t
WHERE t.RVPOxyWaiver NOT IN (1,2)
GROUP BY RVPOxyWaiver
HAVING count(*) > 0 LIMIT 1 \p;
-- Validate table Gasoline
 
SELECT max(ETOHMktShare) AS maxMarketShare, min(ETOHMktShare) AS minMarketShare,
'Error 9022: Min/Max of ETOHMktShare' AS ErrorMinMax
FROM Gasoline
GROUP BY ETOHMktShare
HAVING maxMarketShare  > 1.00 or minMarketShare  < 0.00 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(MTBEMktShare) AS maxMarketShare, min(MTBEMktShare) AS minMarketShare,
'Error 9022: Min/Max of MTBEMktShare' AS ErrorMinMax
FROM Gasoline
GROUP BY MTBEMktShare
HAVING maxMarketShare  > 1.00 or minMarketShare  < 0.00 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(ETBEMktShare) AS maxMarketShare, min(ETBEMktShare) AS minMarketShare,
'Error 9022: Min/Max of ETBEMktShare' AS ErrorMinMax
FROM Gasoline
GROUP BY ETBEMktShare
HAVING maxMarketShare  > 1.00 or minMarketShare  < 0.00 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(TAMEMktShare) AS maxMarketShare, min(TAMEMktShare) AS minMarketShare,
'Error 9022: Min/Max of TAMEMktShare' AS ErrorMinMax
FROM Gasoline
GROUP BY TAMEMktShare
HAVING maxMarketShare  > 1.00 or minMarketShare  < 0.00 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(AromaticContent) AS maxAromaticContent, min(AromaticContent) AS minAromaticContent,
'Error 9022: Min0/Max55 of AromaticContent' AS ErrorMinMax
FROM Gasoline
GROUP BY AromaticContent
HAVING maxAromaticContent  > 55 or minAromaticContent  < 0 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(OlefinContent) AS maxOlefinContent, min(OlefinContent) AS minOlefinContent,
'Error 9022: Min0/Max30 of OlefinContent' AS ErrorMinMax
FROM Gasoline
GROUP BY OlefinContent
HAVING maxOlefinContent  > 30 or minOlefinContent  < 0 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(BenzeneContent) AS maxBenzeneContent, min(BenzeneContent) AS minBenzeneContent,
'Error 9022: Min0/Max5 of BenzeneContent' AS ErrorMinMax
FROM Gasoline
GROUP BY BenzeneContent
HAVING maxBenzeneContent  > 5 or minBenzeneContent  < 0 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(E200) AS maxE200, min(E200) AS minE200,
'Error 9022: Min30/Max70 of E200' AS ErrorMinMax
FROM Gasoline
GROUP BY E200
HAVING maxE200  > 70 or minE200  < 30 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT max(E300) AS maxE300, min(E300) AS minE300,
'Error 9022: Min70/Max100 of E300' AS ErrorMinMax
FROM Gasoline
GROUP BY E300
HAVING maxE300  > 100 or minE300  < 70 LIMIT 50 \p;
-- Validate table Gasoline
 
SELECT count(*) as cnt, 'Error 9022: Min/Max/Data out of range. RFG in Gasoline, must be either Y or N' AS ErrorRFG
FROM Gasoline t
WHERE t.RFG NOT IN ('Y', 'N')
GROUP BY RFG
HAVING count(*) > 0 LIMIT 1 \p;
SELECT GasolineId, RVP, GasSulfur, GasMaxSulfur, RVPOxyWaiver, ETOHVolume, ETOHMktShare, MTBEVolume, MTBEMktShare, ETBEVolume, ETBEMktShare, TAMEVolume, TAMEMktShare, AromaticContent, OlefinContent, BenzeneContent,  
E200, E300, RFG 
FROM Gasoline LIMIT 1 ;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT GasolineId, RVP, GasSulfur, GasMaxSulfur, RVPOxyWaiver, ETOHVolume, ETOHMktShare, MTBEVolume, MTBEMktShare, ETBEVolume, ETBEMktShare, TAMEVolume, TAMEMktShare, AromaticContent, OlefinContent, BenzeneContent,  
E200, E300, RFG 
FROM Gasoline p 
WHERE ISNULL(p.GasolineId) OR ISNULL(p.RVP) OR ISNULL(p.GasSulfur) OR ISNULL(p.GasMaxSulfur) OR ISNULL(p.RVPOxyWaiver) OR ISNULL(p.ETOHVolume) OR ISNULL(p.ETOHMktShare) OR ISNULL(p.MTBEVolume) OR ISNULL(p.MTBEMktShare) 
 OR ISNULL(p.ETBEVolume) OR ISNULL(p.ETBEMktShare) OR ISNULL(p.TAMEVolume) OR ISNULL(p.TAMEMktShare) OR ISNULL(p.AromaticContent) OR ISNULL(p.OlefinContent) OR ISNULL(p.BenzeneContent) OR ISNULL(p.E200)  
OR ISNULL(p.E300) OR ISNULL(p.RFG) \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: Hour is empty', 'OK (table not empty)') AS CheckResult 
FROM Hour;
 
SELECT HourID, HourName 
FROM Hour LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT HourID, HourName 
FROM Hour p 
WHERE ISNULL(p.HourID) OR ISNULL(p.HourName)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: HPMSRoadType is empty', 'OK (table not empty)') AS CheckResult 
FROM HPMSRoadType;
 
-- Validate table HPMSRoadType
 
SELECT count(*) as cnt, 'Error 9022: Min/Max/Data out of range. RoadType in HPMSRoadType is out of range.' AS ErrorRoadType
FROM HPMSRoadType t
WHERE t.RoadType NOT IN (11,13,15,17,19,21,23,25,27,29,31,33)
GROUP BY RoadType
HAVING count(*) > 0 LIMIT 1 \p;
SELECT RoadType, RoadDesc 
FROM HPMSRoadType LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT RoadType, RoadDesc 
FROM HPMSRoadType p 
WHERE ISNULL(p.RoadType) OR ISNULL(p.RoadDesc)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: M6VClass is empty', 'OK (table not empty)') AS CheckResult 
FROM M6VClass;
 
-- Validate table M6VClass
 
SELECT max(VClass) AS maxVClass, min(VClass) AS minVClass,
'Error 9022: Min1/Max28 of VClass of M6VClass. Data out of range.' AS ErrorMinMax
FROM M6VClass
GROUP BY VClass
HAVING maxVClass  > 28 or minVClass  < 1 LIMIT 50 \p;
-- Validate table M6VClass
 
SELECT max(P5VClass) AS maxP5VClass, min(P5VClass) AS minP5VClass,
'Error: Min1/Max16 of P5VClass of M6VClass' AS ErrorMinMax
FROM M6VClass
GROUP BY P5VClass
HAVING maxP5VClass  > 16 or minP5VClass  < 1 LIMIT 50 \p;
SELECT VClass, VClassAbbr, VClassDesc, VType, P5VClass 
FROM M6VClass LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT VClass, VClassAbbr, VClassDesc, VType, P5VClass 
FROM M6VClass p 
WHERE ISNULL(p.VClass) OR ISNULL(p.VClassAbbr) OR ISNULL(p.VClassDesc) OR ISNULL(p.VType) OR ISNULL(p.P5VClass)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: M6VType is empty', 'OK (table not empty)') AS CheckResult 
FROM M6VType;
 
-- Validate table M6VType
 
SELECT max(VType) AS maxVType, min(VType) AS minVType,
'Error 9022: Min1/Max16 of VType of M6VType. Data out of range.' AS ErrorMinMax
FROM M6VType
GROUP BY VType
HAVING maxVType  > 16 or maxVType  < 1 LIMIT 50 \p;
SELECT VType, VTypeDesc 
FROM M6VType LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT VType, VTypeDesc 
FROM M6VType p 
WHERE ISNULL(p.VType) OR ISNULL(p.VTypeDesc)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: NaturalGas is empty', 'OK (table not empty)') AS CheckResult 
FROM NaturalGas;
 
SELECT NGId, NGSulfur 
FROM NaturalGas LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT NGId, NGSulfur 
FROM NaturalGas p 
WHERE ISNULL(p.NGId) OR ISNULL(p.NGSulfur)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: PollutantCode is empty', 'OK (table not empty)') AS CheckResult 
FROM PollutantCode;
 
SELECT PollutantCodeID, PollutantCode, PollutantName, RunSpecPollutantIndex 
FROM PollutantCode LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT PollutantCodeID, PollutantCode, PollutantName, RunSpecPollutantIndex 
FROM PollutantCode p 
WHERE ISNULL(p.PollutantCodeID) OR ISNULL(p.PollutantCode) OR ISNULL(p.PollutantName) OR ISNULL(p.RunSpecPollutantIndex)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: SCC is empty', 'OK (table not empty)') AS CheckResult 
FROM SCC;
 
-- Validate table SCC
 
-- Check FuelType in SCC table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. FuelType in SCC must be one of CNG, Gas, Dies, and LPG.' AS ErrorFuelType
FROM SCC t
WHERE t.FuelType NOT IN ('CNG','Gas','Dies','LPG')
GROUP BY FuelType
HAVING count(*) > 0 LIMIT 1 \p;
SELECT SCCID, SCC, Part5VClass, HPMSRoadType, Segment, FuelType, Strokes, Part5VClassDesc, RoadwayTypeDesc, SegmentDesc, SCCDesc, mgNH3perGallon, TOGfac, NMOGfac, NMHCfac, VOCfac, PM25fac, SOAfac 
FROM SCC LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT SCCID, SCC, Part5VClass, HPMSRoadType, Segment, FuelType, Strokes, Part5VClassDesc, RoadwayTypeDesc, SegmentDesc, SCCDesc, mgNH3perGallon, TOGfac, NMOGfac, NMHCfac, VOCfac, PM25fac, SOAfac 
FROM SCC p 
WHERE ISNULL(p.SCCID) OR ISNULL(p.SCC) OR ISNULL(p.FuelType) OR ISNULL(p.Part5VClassDesc) OR ISNULL(p.RoadwayTypeDesc) OR ISNULL(p.SegmentDesc) OR ISNULL(p.SCCDesc)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: SCCToxics is empty', 'OK (table not empty)') AS CheckResult 
FROM SCCToxics;
 
-- Validate table SCCToxics
 
-- Check ratioType in scctoxics table.
SELECT count(*) as cnt, 'Error 9022: Data error - Min/Max/data out of range. ratioType in scctoxics must be one of VOC, Gal, PM10, Mile, and PMVOC.' AS ErrorratioType
FROM scctoxics t
WHERE t.ratioType NOT IN ('VOC','Gal','PM10','Mile','PMVOC')
GROUP BY ratioType
HAVING count(*) > 0 LIMIT 1 \p;
SELECT SCCID, PollutantCodeID, exhBaseGas, exhEthGas, exhMTBEGas, exhRFGGas, exhDiesel, evapBaseGas, evapEthGas, evapMTBEGas, evapRFGGas, ratioType 
FROM SCCToxics LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT SCCID, PollutantCodeID, exhBaseGas, exhEthGas, exhMTBEGas, exhRFGGas, exhDiesel, evapBaseGas, evapEthGas, evapMTBEGas, evapRFGGas, ratioType 
FROM SCCToxics p 
WHERE ISNULL(p.SCCID) OR ISNULL(p.PollutantCodeID)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: State is empty', 'OK (table not empty)') AS CheckResult 
FROM State;
 
SELECT FIPSStateId, StateName, StateAbbr, NLEVFileName, T2ExhPhaseInFileName, T2EvapPhaseInFileName, T2CertFileName, DataSourceId 
FROM State LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT FIPSStateId, StateName, StateAbbr, NLEVFileName, T2ExhPhaseInFileName, T2EvapPhaseInFileName, T2CertFileName, DataSourceId 
FROM State p 
WHERE ISNULL(p.FIPSStateId) OR ISNULL(p.StateName) OR ISNULL(p.StateAbbr) OR ISNULL(p.NLEVFileName) OR ISNULL(p.T2ExhPhaseInFileName) OR ISNULL(p.T2EvapPhaseInFileName) OR ISNULL(p.T2CertFileName)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
FLUSH TABLES;
SELECT COUNT(*), IF(COUNT(*)<=0, 'Warning 9610: VMTMonthAllocation is empty', 'OK (table not empty)') AS CheckResult 
FROM VMTMonthAllocation;
 
SELECT VType, RoadType, Month, AllocFactor, DataSourceId 
FROM VMTMonthAllocation LIMIT 1 \p;
 
DROP TABLE IF EXISTS xTEMP1; 
CREATE TABLE xTEMP1 
SELECT VType, RoadType, Month, AllocFactor, DataSourceId 
FROM VMTMonthAllocation p 
WHERE ISNULL(p.VType) OR ISNULL(p.RoadType) OR ISNULL(p.Month) OR ISNULL(p.AllocFactor)  \p;
SELECT COUNT(*), IF(COUNT(*)> 0, 'Error 9020: Data error - required field(s) is NULL', 'Required fields checked OK') AS CheckResult 
FROM xTEMP1 ;
DROP TABLE IF EXISTS xTEMP1; 
 
 
-- Primary key of table BaseYearVMT
--   : BaseYear
--   : VClass
--   : RoadType
--   : FIPSCountyId
--   : FIPSStateId
-- Primary key of table County
--   : FIPSCountyId
--   : FIPSStateId
-- Primary key of table CountyMap
--   : FIPSCountyId
--   : FIPSStateId
-- Primary key of table CountyMonth
--   : FIPSCountyId
--   : FIPSStateId
--   : Month
-- Primary key of table CountyMonthHour
--   : FIPSCountyId
--   : FIPSStateId
--   : Month
--   : HourID
-- Primary key of table CountyNRFile
--   : FIPSCountyId
--   : FIPSStateId
--   : FileTypeID
-- Primary key of table CountyVMTMonthAllocation
--   : FIPSCountyId
--   : FIPSStateId
--   : VType
--   : RoadType
--   : Month
-- Primary key of table CountyYear
--   : FIPSCountyId
--   : FIPSStateId
--   : Year
-- Primary key of table CountyYearMonth
--   : FIPSCountyId
--   : FIPSStateId
--   : Year
--   : Month
-- Primary key of table CountyYearMonthHour
--   : FIPSCountyId
--   : FIPSStateId
--   : Year
--   : Month
--   : HourID
-- Primary key of table DataSource
--   : DataSourceId
-- Primary key of table Diesel
--   : DieselId
-- Primary key of table EmissionType
--   : EmissionTypeID
-- Primary key of table FileType
--   : FileTypeID
-- Primary key of table Gasoline
--   : GasolineId
-- Primary key of table Hour
--   : HourID
-- Primary key of table HPMSRoadType
--   : RoadType
-- Primary key of table M6VClass
--   : VClass
-- Primary key of table M6VType
--   : VType
-- Primary key of table NaturalGas
--   : NGId
-- Primary key of table PollutantCode
--   : PollutantCodeID
-- Primary key of table SCC
--   : SCCID
-- Primary key of table SCCToxics
--   : SCCID
--   : PollutantCodeID
-- Primary key of table State
--   : FIPSStateId
-- Primary key of table VMTMonthAllocation
--   : VType
--   : RoadType
--   : Month
 

SELECT 'Check data structure/definition of table BaseYearVMT ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempBaseYearVMT2005128174452.txt
SHOW CREATE TABLE BaseYearVMT;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempBaseYearVMT2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`BASEYEAR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. BaseYearVMT.BaseYear should be part of primary key.', 'BaseYearVMT.BaseYear type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BASEYEAR') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. BaseYearVMT.BaseYear should be = SMALLINT.', 'BaseYearVMT.BaseYear type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BASEYEAR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. BaseYearVMT.BaseYear should be NOT NULL.', 'BaseYearVMT.BaseYear required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VCLASS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. BaseYearVMT.VClass should be part of primary key.', 'BaseYearVMT.VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASS') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. BaseYearVMT.VClass should be = TINYINT.', 'BaseYearVMT.VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASS') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. BaseYearVMT.VClass size should be 2.', 'BaseYearVMT.VClass data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASS') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. BaseYearVMT.VClass should be NOT NULL.', 'BaseYearVMT.VClass required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ROADTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. BaseYearVMT.RoadType should be part of primary key.', 'BaseYearVMT.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. BaseYearVMT.RoadType should be = TINYINT.', 'BaseYearVMT.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. BaseYearVMT.RoadType size should be 2.', 'BaseYearVMT.RoadType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. BaseYearVMT.RoadType should be NOT NULL.', 'BaseYearVMT.RoadType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. BaseYearVMT.FIPSCountyId should be part of primary key.', 'BaseYearVMT.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. BaseYearVMT.FIPSCountyId should be = SMALLINT.', 'BaseYearVMT.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. BaseYearVMT.FIPSCountyId should be NOT NULL.', 'BaseYearVMT.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. BaseYearVMT.FIPSStateId should be part of primary key.', 'BaseYearVMT.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. BaseYearVMT.FIPSStateId should be = TINYINT.', 'BaseYearVMT.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. BaseYearVMT.FIPSStateId size should be 2.', 'BaseYearVMT.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. BaseYearVMT.FIPSStateId should be NOT NULL.', 'BaseYearVMT.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VMT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. BaseYearVMT.VMT should not be part of primary key.', 'BaseYearVMT.VMT type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VMT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. BaseYearVMT.VMT should be = FLOAT.', 'BaseYearVMT.VMT type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VMT') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. BaseYearVMT.VMT should be NULL.', 'BaseYearVMT.VMT required type: checked OK.');

SELECT 'Check data structure/definition of table County ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCounty2005128174452.txt
SHOW CREATE TABLE County;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCounty2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. County.FIPSCountyId should be part of primary key.', 'County.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.FIPSCountyId should be = SMALLINT.', 'County.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.FIPSCountyId should be NOT NULL.', 'County.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. County.FIPSStateId should be part of primary key.', 'County.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.FIPSStateId should be = TINYINT.', 'County.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.FIPSStateId size should be 2.', 'County.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.FIPSStateId should be NOT NULL.', 'County.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`COUNTYNAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.CountyName should not be part of primary key.', 'County.CountyName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYNAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.CountyName should be = CHAR.', 'County.CountyName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYNAME') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.CountyName size should be 50.', 'County.CountyName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYNAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.CountyName should be NOT NULL.', 'County.CountyName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ALTITUDE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.Altitude should not be part of primary key.', 'County.Altitude type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALTITUDE') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.Altitude should be = CHAR.', 'County.Altitude type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALTITUDE') AND instr(ucase(aline), '(1)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.Altitude size should be 1.', 'County.Altitude data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALTITUDE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.Altitude should be NOT NULL.', 'County.Altitude required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`BAROMETRICPRESSURE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.BarometricPressure should not be part of primary key.', 'County.BarometricPressure type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BAROMETRICPRESSURE') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.BarometricPressure should be = FLOAT.', 'County.BarometricPressure type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BAROMETRICPRESSURE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.BarometricPressure should be NOT NULL.', 'County.BarometricPressure required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`OZONESEASONSTARTMONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.OzoneSeasonStartMonth should not be part of primary key.', 'County.OzoneSeasonStartMonth type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONSTARTMONTH') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.OzoneSeasonStartMonth should be = TINYINT.', 'County.OzoneSeasonStartMonth type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONSTARTMONTH') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.OzoneSeasonStartMonth size should be 2.', 'County.OzoneSeasonStartMonth data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONSTARTMONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.OzoneSeasonStartMonth should be NOT NULL.', 'County.OzoneSeasonStartMonth required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`OZONESEASONSTARTDAY`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.OzoneSeasonStartDay should not be part of primary key.', 'County.OzoneSeasonStartDay type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONSTARTDAY') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.OzoneSeasonStartDay should be = TINYINT.', 'County.OzoneSeasonStartDay type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONSTARTDAY') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.OzoneSeasonStartDay size should be 2.', 'County.OzoneSeasonStartDay data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONSTARTDAY') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.OzoneSeasonStartDay should be NOT NULL.', 'County.OzoneSeasonStartDay required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`OZONESEASONENDMONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.OzoneSeasonEndMonth should not be part of primary key.', 'County.OzoneSeasonEndMonth type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONENDMONTH') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.OzoneSeasonEndMonth should be = TINYINT.', 'County.OzoneSeasonEndMonth type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONENDMONTH') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.OzoneSeasonEndMonth size should be 2.', 'County.OzoneSeasonEndMonth data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONENDMONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.OzoneSeasonEndMonth should be NOT NULL.', 'County.OzoneSeasonEndMonth required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`OZONESEASONENDDAY`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.OzoneSeasonEndDay should not be part of primary key.', 'County.OzoneSeasonEndDay type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONENDDAY') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.OzoneSeasonEndDay should be = TINYINT.', 'County.OzoneSeasonEndDay type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONENDDAY') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.OzoneSeasonEndDay size should be 2.', 'County.OzoneSeasonEndDay data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OZONESEASONENDDAY') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.OzoneSeasonEndDay should be NOT NULL.', 'County.OzoneSeasonEndDay required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`STAGE2STARTYEAR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.Stage2StartYear should not be part of primary key.', 'County.Stage2StartYear type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STAGE2STARTYEAR') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.Stage2StartYear should be = SMALLINT.', 'County.Stage2StartYear type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STAGE2STARTYEAR') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.Stage2StartYear should be NULL.', 'County.Stage2StartYear required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`PHASEINYEARS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.PhaseInYears should not be part of primary key.', 'County.PhaseInYears type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PHASEINYEARS') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.PhaseInYears should be = SMALLINT.', 'County.PhaseInYears type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PHASEINYEARS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.PhaseInYears should be NULL.', 'County.PhaseInYears required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`LDVSTAGE2PERCENT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.LDVStage2Percent should not be part of primary key.', 'County.LDVStage2Percent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'LDVSTAGE2PERCENT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.LDVStage2Percent should be = FLOAT.', 'County.LDVStage2Percent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'LDVSTAGE2PERCENT') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.LDVStage2Percent should be NULL.', 'County.LDVStage2Percent required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HDVSTAGE2PERCENT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.HDVStage2Percent should not be part of primary key.', 'County.HDVStage2Percent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HDVSTAGE2PERCENT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.HDVStage2Percent should be = FLOAT.', 'County.HDVStage2Percent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HDVSTAGE2PERCENT') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.HDVStage2Percent should be NULL.', 'County.HDVStage2Percent required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NGVFRACTIONFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.NGVFractionFileName should not be part of primary key.', 'County.NGVFractionFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGVFRACTIONFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.NGVFractionFileName should be = CHAR.', 'County.NGVFractionFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGVFRACTIONFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. County.NGVFractionFileName size should be 8.', 'County.NGVFractionFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGVFRACTIONFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.NGVFractionFileName should be NOT NULL.', 'County.NGVFractionFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`COUNTYMONTHHOURDATASOURCE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.CountyMonthHourDataSource should not be part of primary key.', 'County.CountyMonthHourDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYMONTHHOURDATASOURCE') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.CountyMonthHourDataSource should be = SMALLINT.', 'County.CountyMonthHourDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYMONTHHOURDATASOURCE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.CountyMonthHourDataSource should be NULL.', 'County.CountyMonthHourDataSource required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATASOURCEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.DataSourceId should not be part of primary key.', 'County.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.DataSourceId should be = SMALLINT.', 'County.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.DataSourceId should be NULL.', 'County.DataSourceId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`COUNTYVMTMONTHALLOCDATASOURCE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. County.CountyVMTMonthAllocDataSource should not be part of primary key.', 'County.CountyVMTMonthAllocDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYVMTMONTHALLOCDATASOURCE') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. County.CountyVMTMonthAllocDataSource should be = SMALLINT.', 'County.CountyVMTMonthAllocDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYVMTMONTHALLOCDATASOURCE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. County.CountyVMTMonthAllocDataSource should be NOT NULL.', 'County.CountyVMTMonthAllocDataSource required type definition: checked OK.');

SELECT 'Check data structure/definition of table CountyMap ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyMap2005128174452.txt
SHOW CREATE TABLE CountyMap;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyMap2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMap.FIPSCountyId should be part of primary key.', 'CountyMap.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMap.FIPSCountyId should be = SMALLINT.', 'CountyMap.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMap.FIPSCountyId should be NOT NULL.', 'CountyMap.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMap.FIPSStateId should be part of primary key.', 'CountyMap.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMap.FIPSStateId should be = TINYINT.', 'CountyMap.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyMap.FIPSStateId size should be 2.', 'CountyMap.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMap.FIPSStateId should be NOT NULL.', 'CountyMap.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HWYREPFIPSCNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyMap.HwyRepFIPSCntyID should not be part of primary key.', 'CountyMap.HwyRepFIPSCntyID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYREPFIPSCNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMap.HwyRepFIPSCntyID should be = SMALLINT.', 'CountyMap.HwyRepFIPSCntyID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYREPFIPSCNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMap.HwyRepFIPSCntyID should be NOT NULL.', 'CountyMap.HwyRepFIPSCntyID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NRREPFIPSCNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyMap.NRRepFIPSCntyID should not be part of primary key.', 'CountyMap.NRRepFIPSCntyID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRREPFIPSCNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMap.NRRepFIPSCntyID should be = SMALLINT.', 'CountyMap.NRRepFIPSCntyID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRREPFIPSCNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMap.NRRepFIPSCntyID should be NOT NULL.', 'CountyMap.NRRepFIPSCntyID required type definition: checked OK.');

SELECT 'Check data structure/definition of table CountyMonth ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyMonth2005128174452.txt
SHOW CREATE TABLE CountyMonth;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyMonth2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonth.FIPSCountyId should be part of primary key.', 'CountyMonth.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonth.FIPSCountyId should be = SMALLINT.', 'CountyMonth.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonth.FIPSCountyId should be NOT NULL.', 'CountyMonth.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonth.FIPSStateId should be part of primary key.', 'CountyMonth.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonth.FIPSStateId should be = TINYINT.', 'CountyMonth.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyMonth.FIPSStateId size should be 2.', 'CountyMonth.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonth.FIPSStateId should be NOT NULL.', 'CountyMonth.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonth.Month should be part of primary key.', 'CountyMonth.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonth.Month should be = TINYINT.', 'CountyMonth.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyMonth.Month size should be 2.', 'CountyMonth.Month data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonth.Month should be NOT NULL.', 'CountyMonth.Month required type definition: checked OK.');

SELECT 'Check data structure/definition of table CountyMonthHour ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyMonthHour2005128174452.txt
SHOW CREATE TABLE CountyMonthHour;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyMonthHour2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonthHour.FIPSCountyId should be part of primary key.', 'CountyMonthHour.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonthHour.FIPSCountyId should be = SMALLINT.', 'CountyMonthHour.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonthHour.FIPSCountyId should be NOT NULL.', 'CountyMonthHour.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonthHour.FIPSStateId should be part of primary key.', 'CountyMonthHour.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonthHour.FIPSStateId should be = TINYINT.', 'CountyMonthHour.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyMonthHour.FIPSStateId size should be 2.', 'CountyMonthHour.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonthHour.FIPSStateId should be NOT NULL.', 'CountyMonthHour.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonthHour.Month should be part of primary key.', 'CountyMonthHour.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonthHour.Month should be = TINYINT.', 'CountyMonthHour.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyMonthHour.Month size should be 2.', 'CountyMonthHour.Month data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonthHour.Month should be NOT NULL.', 'CountyMonthHour.Month required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HOURID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonthHour.HourID should be part of primary key.', 'CountyMonthHour.HourID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonthHour.HourID should be = TINYINT.', 'CountyMonthHour.HourID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyMonthHour.HourID size should be 2.', 'CountyMonthHour.HourID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonthHour.HourID should be NOT NULL.', 'CountyMonthHour.HourID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`AVERAGETEMP`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonthHour.AverageTemp should not be part of primary key.', 'CountyMonthHour.AverageTemp type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVERAGETEMP') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonthHour.AverageTemp should be = FLOAT.', 'CountyMonthHour.AverageTemp type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVERAGETEMP') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonthHour.AverageTemp should be NOT NULL.', 'CountyMonthHour.AverageTemp required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`AVERAGERELHUMIDITY`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyMonthHour.AverageRelHumidity should not be part of primary key.', 'CountyMonthHour.AverageRelHumidity type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVERAGERELHUMIDITY') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyMonthHour.AverageRelHumidity should be = FLOAT.', 'CountyMonthHour.AverageRelHumidity type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVERAGERELHUMIDITY') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyMonthHour.AverageRelHumidity should be NOT NULL.', 'CountyMonthHour.AverageRelHumidity required type definition: checked OK.');

SELECT 'Check data structure/definition of table CountyNRFile ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyNRFile2005128174452.txt
SHOW CREATE TABLE CountyNRFile;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyNRFile2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyNRFile.FIPSCountyId should be part of primary key.', 'CountyNRFile.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyNRFile.FIPSCountyId should be = SMALLINT.', 'CountyNRFile.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyNRFile.FIPSCountyId should be NOT NULL.', 'CountyNRFile.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyNRFile.FIPSStateId should be part of primary key.', 'CountyNRFile.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyNRFile.FIPSStateId should be = TINYINT.', 'CountyNRFile.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyNRFile.FIPSStateId size should be 2.', 'CountyNRFile.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyNRFile.FIPSStateId should be NOT NULL.', 'CountyNRFile.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FILETYPEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyNRFile.FileTypeID should be part of primary key.', 'CountyNRFile.FileTypeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEID') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyNRFile.FileTypeID should be = CHAR.', 'CountyNRFile.FileTypeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEID') AND instr(ucase(aline), '(3)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyNRFile.FileTypeID size should be 3.', 'CountyNRFile.FileTypeID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyNRFile.FileTypeID should be NOT NULL.', 'CountyNRFile.FileTypeID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`COUNTYNRFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyNRFile.CountyNRFileName should not be part of primary key.', 'CountyNRFile.CountyNRFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYNRFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyNRFile.CountyNRFileName should be = CHAR.', 'CountyNRFile.CountyNRFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYNRFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyNRFile.CountyNRFileName size should be 8.', 'CountyNRFile.CountyNRFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYNRFILENAME') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyNRFile.CountyNRFileName should be NULL.', 'CountyNRFile.CountyNRFileName required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATASOURCEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyNRFile.DataSourceId should not be part of primary key.', 'CountyNRFile.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyNRFile.DataSourceId should be = SMALLINT.', 'CountyNRFile.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyNRFile.DataSourceId should be NULL.', 'CountyNRFile.DataSourceId required type: checked OK.');

SELECT 'Check data structure/definition of table CountyVMTMonthAllocation ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyVMTMonthAllocation2005128174452.txt
SHOW CREATE TABLE CountyVMTMonthAllocation;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyVMTMonthAllocation2005128174452.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyVMTMonthAllocation.FIPSCountyId should be part of primary key.', 'CountyVMTMonthAllocation.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyVMTMonthAllocation.FIPSCountyId should be = SMALLINT.', 'CountyVMTMonthAllocation.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyVMTMonthAllocation.FIPSCountyId should be NOT NULL.', 'CountyVMTMonthAllocation.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyVMTMonthAllocation.FIPSStateId should be part of primary key.', 'CountyVMTMonthAllocation.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyVMTMonthAllocation.FIPSStateId should be = TINYINT.', 'CountyVMTMonthAllocation.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyVMTMonthAllocation.FIPSStateId size should be 2.', 'CountyVMTMonthAllocation.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyVMTMonthAllocation.FIPSStateId should be NOT NULL.', 'CountyVMTMonthAllocation.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyVMTMonthAllocation.VType should be part of primary key.', 'CountyVMTMonthAllocation.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyVMTMonthAllocation.VType should be = TINYINT.', 'CountyVMTMonthAllocation.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyVMTMonthAllocation.VType size should be 2.', 'CountyVMTMonthAllocation.VType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyVMTMonthAllocation.VType should be NOT NULL.', 'CountyVMTMonthAllocation.VType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ROADTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyVMTMonthAllocation.RoadType should be part of primary key.', 'CountyVMTMonthAllocation.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyVMTMonthAllocation.RoadType should be = TINYINT.', 'CountyVMTMonthAllocation.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyVMTMonthAllocation.RoadType size should be 2.', 'CountyVMTMonthAllocation.RoadType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyVMTMonthAllocation.RoadType should be NOT NULL.', 'CountyVMTMonthAllocation.RoadType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyVMTMonthAllocation.Month should be part of primary key.', 'CountyVMTMonthAllocation.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyVMTMonthAllocation.Month should be = SMALLINT.', 'CountyVMTMonthAllocation.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyVMTMonthAllocation.Month should be NOT NULL.', 'CountyVMTMonthAllocation.Month required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ALLOCFACTOR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyVMTMonthAllocation.AllocFactor should not be part of primary key.', 'CountyVMTMonthAllocation.AllocFactor type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALLOCFACTOR') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyVMTMonthAllocation.AllocFactor should be = FLOAT.', 'CountyVMTMonthAllocation.AllocFactor type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALLOCFACTOR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyVMTMonthAllocation.AllocFactor should be NOT NULL.', 'CountyVMTMonthAllocation.AllocFactor required type definition: checked OK.');

SELECT 'Check data structure/definition of table CountyYear ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyYear2005128174453.txt
SHOW CREATE TABLE CountyYear;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyYear2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.FIPSCountyId should be part of primary key.', 'CountyYear.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.FIPSCountyId should be = SMALLINT.', 'CountyYear.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.FIPSCountyId should be NOT NULL.', 'CountyYear.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.FIPSStateId should be part of primary key.', 'CountyYear.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.FIPSStateId should be = TINYINT.', 'CountyYear.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.FIPSStateId size should be 2.', 'CountyYear.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.FIPSStateId should be NOT NULL.', 'CountyYear.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`YEAR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.Year should be part of primary key.', 'CountyYear.Year type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'YEAR') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.Year should be = SMALLINT.', 'CountyYear.Year type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'YEAR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.Year should be NOT NULL.', 'CountyYear.Year required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ATPFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.ATPFileName should not be part of primary key.', 'CountyYear.ATPFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ATPFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.ATPFileName should be = CHAR.', 'CountyYear.ATPFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ATPFILENAME') AND instr(ucase(aline), '(12)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.ATPFileName size should be 12.', 'CountyYear.ATPFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ATPFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.ATPFileName should be NOT NULL.', 'CountyYear.ATPFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`IMFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.IMFileName should not be part of primary key.', 'CountyYear.IMFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'IMFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.IMFileName should be = CHAR.', 'CountyYear.IMFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'IMFILENAME') AND instr(ucase(aline), '(12)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.IMFileName size should be 12.', 'CountyYear.IMFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'IMFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.IMFileName should be NOT NULL.', 'CountyYear.IMFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`REGDISTFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.RegDistFileName should not be part of primary key.', 'CountyYear.RegDistFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'REGDISTFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.RegDistFileName should be = CHAR.', 'CountyYear.RegDistFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'REGDISTFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.RegDistFileName size should be 8.', 'CountyYear.RegDistFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'REGDISTFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.RegDistFileName should be NOT NULL.', 'CountyYear.RegDistFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VMTBYHOURFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.VMTByHourFileName should not be part of primary key.', 'CountyYear.VMTByHourFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VMTBYHOURFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.VMTByHourFileName should be = CHAR.', 'CountyYear.VMTByHourFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VMTBYHOURFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.VMTByHourFileName size should be 8.', 'CountyYear.VMTByHourFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VMTBYHOURFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.VMTByHourFileName should be NOT NULL.', 'CountyYear.VMTByHourFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MILEACCUMFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.MileAccumFileName should not be part of primary key.', 'CountyYear.MileAccumFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MILEACCUMFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.MileAccumFileName should be = CHAR.', 'CountyYear.MileAccumFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MILEACCUMFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.MileAccumFileName size should be 8.', 'CountyYear.MileAccumFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MILEACCUMFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.MileAccumFileName should be NOT NULL.', 'CountyYear.MileAccumFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`TRIPSPERDAYFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.TripsPerDayFileName should not be part of primary key.', 'CountyYear.TripsPerDayFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TRIPSPERDAYFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.TripsPerDayFileName should be = CHAR.', 'CountyYear.TripsPerDayFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TRIPSPERDAYFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.TripsPerDayFileName size should be 8.', 'CountyYear.TripsPerDayFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TRIPSPERDAYFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.TripsPerDayFileName should be NOT NULL.', 'CountyYear.TripsPerDayFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`STARTDISTFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.StartDistFileName should not be part of primary key.', 'CountyYear.StartDistFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STARTDISTFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.StartDistFileName should be = CHAR.', 'CountyYear.StartDistFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STARTDISTFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.StartDistFileName size should be 8.', 'CountyYear.StartDistFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STARTDISTFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.StartDistFileName should be NOT NULL.', 'CountyYear.StartDistFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SOAKDISTFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.SoakDistFileName should not be part of primary key.', 'CountyYear.SoakDistFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SOAKDISTFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.SoakDistFileName should be = CHAR.', 'CountyYear.SoakDistFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SOAKDISTFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.SoakDistFileName size should be 8.', 'CountyYear.SoakDistFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SOAKDISTFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.SoakDistFileName should be NOT NULL.', 'CountyYear.SoakDistFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HOTSOAKFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.HotSoakFileName should not be part of primary key.', 'CountyYear.HotSoakFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOTSOAKFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.HotSoakFileName should be = CHAR.', 'CountyYear.HotSoakFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOTSOAKFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.HotSoakFileName size should be 8.', 'CountyYear.HotSoakFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOTSOAKFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.HotSoakFileName should be NOT NULL.', 'CountyYear.HotSoakFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DIURNSOAKACTIVITYFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.DiurnSoakActivityFileName should not be part of primary key.', 'CountyYear.DiurnSoakActivityFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIURNSOAKACTIVITYFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.DiurnSoakActivityFileName should be = CHAR.', 'CountyYear.DiurnSoakActivityFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIURNSOAKACTIVITYFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.DiurnSoakActivityFileName size should be 8.', 'CountyYear.DiurnSoakActivityFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIURNSOAKACTIVITYFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.DiurnSoakActivityFileName should be NOT NULL.', 'CountyYear.DiurnSoakActivityFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`TRIPLENGTHFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.TripLengthFileName should not be part of primary key.', 'CountyYear.TripLengthFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TRIPLENGTHFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.TripLengthFileName should be = CHAR.', 'CountyYear.TripLengthFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TRIPLENGTHFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.TripLengthFileName size should be 8.', 'CountyYear.TripLengthFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TRIPLENGTHFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.TripLengthFileName should be NOT NULL.', 'CountyYear.TripLengthFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DIESELFRACTFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.DieselFractFileName should not be part of primary key.', 'CountyYear.DieselFractFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELFRACTFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.DieselFractFileName should be = CHAR.', 'CountyYear.DieselFractFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELFRACTFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.DieselFractFileName size should be 8.', 'CountyYear.DieselFractFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELFRACTFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.DieselFractFileName should be NOT NULL.', 'CountyYear.DieselFractFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`AVGSPEEDDISTBASEFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.AvgSpeedDistBaseFileName should not be part of primary key.', 'CountyYear.AvgSpeedDistBaseFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVGSPEEDDISTBASEFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.AvgSpeedDistBaseFileName should be = CHAR.', 'CountyYear.AvgSpeedDistBaseFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVGSPEEDDISTBASEFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.AvgSpeedDistBaseFileName size should be 8.', 'CountyYear.AvgSpeedDistBaseFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AVGSPEEDDISTBASEFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.AvgSpeedDistBaseFileName should be NOT NULL.', 'CountyYear.AvgSpeedDistBaseFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`STAGE2PCT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.Stage2Pct should not be part of primary key.', 'CountyYear.Stage2Pct type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STAGE2PCT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.Stage2Pct should be = FLOAT.', 'CountyYear.Stage2Pct type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STAGE2PCT') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.Stage2Pct should be NULL.', 'CountyYear.Stage2Pct required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NRACTFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.NRACTFileName should not be part of primary key.', 'CountyYear.NRACTFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRACTFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.NRACTFileName should be = CHAR.', 'CountyYear.NRACTFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRACTFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.NRACTFileName size should be 8.', 'CountyYear.NRACTFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRACTFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.NRACTFileName should be NOT NULL.', 'CountyYear.NRACTFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATASOURCEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.DataSourceId should not be part of primary key.', 'CountyYear.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.DataSourceId should be = SMALLINT.', 'CountyYear.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.DataSourceId should be NULL.', 'CountyYear.DataSourceId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HASNRSTATEINPUTS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.HasNRStateInputs should not be part of primary key.', 'CountyYear.HasNRStateInputs type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HASNRSTATEINPUTS') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.HasNRStateInputs should be = CHAR.', 'CountyYear.HasNRStateInputs type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HASNRSTATEINPUTS') AND instr(ucase(aline), '(1)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.HasNRStateInputs size should be 1.', 'CountyYear.HasNRStateInputs data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HASNRSTATEINPUTS') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.HasNRStateInputs should be NOT NULL.', 'CountyYear.HasNRStateInputs required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HASORSTATEINPUTS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.HasORStateInputs should not be part of primary key.', 'CountyYear.HasORStateInputs type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HASORSTATEINPUTS') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.HasORStateInputs should be = CHAR.', 'CountyYear.HasORStateInputs type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HASORSTATEINPUTS') AND instr(ucase(aline), '(1)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYear.HasORStateInputs size should be 1.', 'CountyYear.HasORStateInputs data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HASORSTATEINPUTS') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.HasORStateInputs should be NOT NULL.', 'CountyYear.HasORStateInputs required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`COUNTYYEARMONTHHOURDATASOURCE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.CountyYearMonthHourDataSource should not be part of primary key.', 'CountyYear.CountyYearMonthHourDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYYEARMONTHHOURDATASOURCE') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.CountyYearMonthHourDataSource should be = SMALLINT.', 'CountyYear.CountyYearMonthHourDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COUNTYYEARMONTHHOURDATASOURCE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.CountyYearMonthHourDataSource should be NULL.', 'CountyYear.CountyYearMonthHourDataSource required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`BASEYEARVMTDATASOURCE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYear.BaseYearVMTDataSource should not be part of primary key.', 'CountyYear.BaseYearVMTDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BASEYEARVMTDATASOURCE') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYear.BaseYearVMTDataSource should be = SMALLINT.', 'CountyYear.BaseYearVMTDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BASEYEARVMTDATASOURCE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYear.BaseYearVMTDataSource should be NULL.', 'CountyYear.BaseYearVMTDataSource required type: checked OK.');

SELECT 'Check data structure/definition of table CountyYearMonth ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyYearMonth2005128174453.txt
SHOW CREATE TABLE CountyYearMonth;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyYearMonth2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.FIPSCountyId should be part of primary key.', 'CountyYearMonth.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.FIPSCountyId should be = SMALLINT.', 'CountyYearMonth.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.FIPSCountyId should be NOT NULL.', 'CountyYearMonth.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.FIPSStateId should be part of primary key.', 'CountyYearMonth.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.FIPSStateId should be = TINYINT.', 'CountyYearMonth.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYearMonth.FIPSStateId size should be 2.', 'CountyYearMonth.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.FIPSStateId should be NOT NULL.', 'CountyYearMonth.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`YEAR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.Year should be part of primary key.', 'CountyYearMonth.Year type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'YEAR') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.Year should be = SMALLINT.', 'CountyYearMonth.Year type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'YEAR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.Year should be NOT NULL.', 'CountyYearMonth.Year required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.Month should be part of primary key.', 'CountyYearMonth.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.Month should be = TINYINT.', 'CountyYearMonth.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYearMonth.Month size should be 2.', 'CountyYearMonth.Month data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.Month should be NOT NULL.', 'CountyYearMonth.Month required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NGID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.NGId should not be part of primary key.', 'CountyYearMonth.NGId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.NGId should be = TINYINT.', 'CountyYearMonth.NGId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGID') AND instr(ucase(aline), '(4)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYearMonth.NGId size should be 4.', 'CountyYearMonth.NGId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.NGId should be NULL.', 'CountyYearMonth.NGId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HWYDIESELID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.HwyDieselId should not be part of primary key.', 'CountyYearMonth.HwyDieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYDIESELID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.HwyDieselId should be = SMALLINT.', 'CountyYearMonth.HwyDieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYDIESELID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.HwyDieselId should be NOT NULL.', 'CountyYearMonth.HwyDieselId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HWYGASOLINEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.HwyGasolineId should not be part of primary key.', 'CountyYearMonth.HwyGasolineId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYGASOLINEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.HwyGasolineId should be = SMALLINT.', 'CountyYearMonth.HwyGasolineId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYGASOLINEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.HwyGasolineId should be NULL.', 'CountyYearMonth.HwyGasolineId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NRGASOLINEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.NRGasolineId should not be part of primary key.', 'CountyYearMonth.NRGasolineId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRGASOLINEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.NRGasolineId should be = SMALLINT.', 'CountyYearMonth.NRGasolineId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRGASOLINEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.NRGasolineId should be NULL.', 'CountyYearMonth.NRGasolineId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NRDIESELID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.NRDieselId should not be part of primary key.', 'CountyYearMonth.NRDieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRDIESELID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.NRDieselId should be = SMALLINT.', 'CountyYearMonth.NRDieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRDIESELID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.NRDieselId should be NULL.', 'CountyYearMonth.NRDieselId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HWYFUELDATASOURCE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.HwyFuelDataSource should not be part of primary key.', 'CountyYearMonth.HwyFuelDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYFUELDATASOURCE') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.HwyFuelDataSource should be = SMALLINT.', 'CountyYearMonth.HwyFuelDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HWYFUELDATASOURCE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.HwyFuelDataSource should be NULL.', 'CountyYearMonth.HwyFuelDataSource required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NRFUELDATASOURCE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.NRFuelDataSource should not be part of primary key.', 'CountyYearMonth.NRFuelDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRFUELDATASOURCE') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.NRFuelDataSource should be = SMALLINT.', 'CountyYearMonth.NRFuelDataSource type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NRFUELDATASOURCE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.NRFuelDataSource should be NULL.', 'CountyYearMonth.NRFuelDataSource required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RMDIESELID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonth.RMDieselId should not be part of primary key.', 'CountyYearMonth.RMDieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RMDIESELID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonth.RMDieselId should be = SMALLINT.', 'CountyYearMonth.RMDieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RMDIESELID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonth.RMDieselId should be NULL.', 'CountyYearMonth.RMDieselId required type: checked OK.');

SELECT 'Check data structure/definition of table CountyYearMonthHour ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempCountyYearMonthHour2005128174453.txt
SHOW CREATE TABLE CountyYearMonthHour;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempCountyYearMonthHour2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSCOUNTYID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.FIPSCountyId should be part of primary key.', 'CountyYearMonthHour.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.FIPSCountyId should be = SMALLINT.', 'CountyYearMonthHour.FIPSCountyId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSCOUNTYID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.FIPSCountyId should be NOT NULL.', 'CountyYearMonthHour.FIPSCountyId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.FIPSStateId should be part of primary key.', 'CountyYearMonthHour.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.FIPSStateId should be = TINYINT.', 'CountyYearMonthHour.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYearMonthHour.FIPSStateId size should be 2.', 'CountyYearMonthHour.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.FIPSStateId should be NOT NULL.', 'CountyYearMonthHour.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`YEAR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.Year should be part of primary key.', 'CountyYearMonthHour.Year type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'YEAR') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.Year should be = SMALLINT.', 'CountyYearMonthHour.Year type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'YEAR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.Year should be NOT NULL.', 'CountyYearMonthHour.Year required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.Month should be part of primary key.', 'CountyYearMonthHour.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.Month should be = TINYINT.', 'CountyYearMonthHour.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYearMonthHour.Month size should be 2.', 'CountyYearMonthHour.Month data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.Month should be NOT NULL.', 'CountyYearMonthHour.Month required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HOURID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.HourID should be part of primary key.', 'CountyYearMonthHour.HourID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.HourID should be = TINYINT.', 'CountyYearMonthHour.HourID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. CountyYearMonthHour.HourID size should be 2.', 'CountyYearMonthHour.HourID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.HourID should be NOT NULL.', 'CountyYearMonthHour.HourID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`TEMPERATURE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.Temperature should not be part of primary key.', 'CountyYearMonthHour.Temperature type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TEMPERATURE') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.Temperature should be = FLOAT.', 'CountyYearMonthHour.Temperature type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TEMPERATURE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.Temperature should be NULL.', 'CountyYearMonthHour.Temperature required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RELATIVEHUMIDITY`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. CountyYearMonthHour.RelativeHumidity should not be part of primary key.', 'CountyYearMonthHour.RelativeHumidity type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RELATIVEHUMIDITY') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. CountyYearMonthHour.RelativeHumidity should be = FLOAT.', 'CountyYearMonthHour.RelativeHumidity type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RELATIVEHUMIDITY') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. CountyYearMonthHour.RelativeHumidity should be NULL.', 'CountyYearMonthHour.RelativeHumidity required type: checked OK.');

SELECT 'Check data structure/definition of table DataSource ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempDataSource2005128174453.txt
SHOW CREATE TABLE DataSource;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempDataSource2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATASOURCEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.DataSourceId should be part of primary key.', 'DataSource.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.DataSourceId should be = SMALLINT.', 'DataSource.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.DataSourceId should be NOT NULL.', 'DataSource.DataSourceId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`AUTHOR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.Author should not be part of primary key.', 'DataSource.Author type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AUTHOR') AND instr(ucase(aline), 'VARCHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.Author should be = VARCHAR.', 'DataSource.Author type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AUTHOR') AND instr(ucase(aline), '(25)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. DataSource.Author size should be 25.', 'DataSource.Author data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AUTHOR') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.Author should be NULL.', 'DataSource.Author required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.Date should not be part of primary key.', 'DataSource.Date type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATE') AND instr(ucase(aline), 'DATE');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.Date should be = DATE.', 'DataSource.Date type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.Date should be NULL.', 'DataSource.Date required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SPONSOR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.Sponsor should not be part of primary key.', 'DataSource.Sponsor type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SPONSOR') AND instr(ucase(aline), 'VARCHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.Sponsor should be = VARCHAR.', 'DataSource.Sponsor type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SPONSOR') AND instr(ucase(aline), '(30)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. DataSource.Sponsor size should be 30.', 'DataSource.Sponsor data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SPONSOR') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.Sponsor should be NULL.', 'DataSource.Sponsor required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DOCUMENTID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.DocumentId should not be part of primary key.', 'DataSource.DocumentId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DOCUMENTID') AND instr(ucase(aline), 'VARCHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.DocumentId should be = VARCHAR.', 'DataSource.DocumentId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DOCUMENTID') AND instr(ucase(aline), '(30)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. DataSource.DocumentId size should be 30.', 'DataSource.DocumentId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DOCUMENTID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.DocumentId should be NULL.', 'DataSource.DocumentId required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`QUALITYLEVEL`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.QualityLevel should not be part of primary key.', 'DataSource.QualityLevel type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'QUALITYLEVEL') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.QualityLevel should be = CHAR.', 'DataSource.QualityLevel type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'QUALITYLEVEL') AND instr(ucase(aline), '(1)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. DataSource.QualityLevel size should be 1.', 'DataSource.QualityLevel data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'QUALITYLEVEL') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.QualityLevel should be NULL.', 'DataSource.QualityLevel required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`COMMENTS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. DataSource.Comments should not be part of primary key.', 'DataSource.Comments type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COMMENTS') AND instr(ucase(aline), 'VARCHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. DataSource.Comments should be = VARCHAR.', 'DataSource.Comments type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COMMENTS') AND instr(ucase(aline), '(255)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. DataSource.Comments size should be 255.', 'DataSource.Comments data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'COMMENTS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. DataSource.Comments should be NULL.', 'DataSource.Comments required type: checked OK.');

SELECT 'Check data structure/definition of table Diesel ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempDiesel2005128174453.txt
SHOW CREATE TABLE Diesel;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempDiesel2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DIESELID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. Diesel.DieselId should be part of primary key.', 'Diesel.DieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Diesel.DieselId should be = SMALLINT.', 'Diesel.DieselId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Diesel.DieselId should be NOT NULL.', 'Diesel.DieselId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DIESELSULFUR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Diesel.DieselSulfur should not be part of primary key.', 'Diesel.DieselSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELSULFUR') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Diesel.DieselSulfur should be = FLOAT.', 'Diesel.DieselSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DIESELSULFUR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Diesel.DieselSulfur should be NOT NULL.', 'Diesel.DieselSulfur required type definition: checked OK.');

SELECT 'Check data structure/definition of table EmissionType ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempEmissionType2005128174453.txt
SHOW CREATE TABLE EmissionType;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempEmissionType2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EMISSIONTYPEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. EmissionType.EmissionTypeID should be part of primary key.', 'EmissionType.EmissionTypeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EMISSIONTYPEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. EmissionType.EmissionTypeID should be = TINYINT.', 'EmissionType.EmissionTypeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EMISSIONTYPEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. EmissionType.EmissionTypeID size should be 2.', 'EmissionType.EmissionTypeID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EMISSIONTYPEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. EmissionType.EmissionTypeID should be NOT NULL.', 'EmissionType.EmissionTypeID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EMISSIONTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. EmissionType.EmissionType should not be part of primary key.', 'EmissionType.EmissionType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EMISSIONTYPE') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. EmissionType.EmissionType should be = CHAR.', 'EmissionType.EmissionType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EMISSIONTYPE') AND instr(ucase(aline), '(4)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. EmissionType.EmissionType size should be 4.', 'EmissionType.EmissionType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EMISSIONTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. EmissionType.EmissionType should be NOT NULL.', 'EmissionType.EmissionType required type definition: checked OK.');

SELECT 'Check data structure/definition of table FileType ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempFileType2005128174453.txt
SHOW CREATE TABLE FileType;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempFileType2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FILETYPEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. FileType.FileTypeID should be part of primary key.', 'FileType.FileTypeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEID') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. FileType.FileTypeID should be = CHAR.', 'FileType.FileTypeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEID') AND instr(ucase(aline), '(3)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. FileType.FileTypeID size should be 3.', 'FileType.FileTypeID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. FileType.FileTypeID should be NOT NULL.', 'FileType.FileTypeID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FILETYPEDESCRIPTION`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. FileType.FileTypeDescription should not be part of primary key.', 'FileType.FileTypeDescription type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEDESCRIPTION') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. FileType.FileTypeDescription should be = CHAR.', 'FileType.FileTypeDescription type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEDESCRIPTION') AND instr(ucase(aline), '(100)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. FileType.FileTypeDescription size should be 100.', 'FileType.FileTypeDescription data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FILETYPEDESCRIPTION') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. FileType.FileTypeDescription should be NOT NULL.', 'FileType.FileTypeDescription required type definition: checked OK.');

SELECT 'Check data structure/definition of table Gasoline ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempGasoline2005128174453.txt
SHOW CREATE TABLE Gasoline;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempGasoline2005128174453.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`GASOLINEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.GasolineId should be part of primary key.', 'Gasoline.GasolineId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'GASOLINEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.GasolineId should be = SMALLINT.', 'Gasoline.GasolineId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'GASOLINEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.GasolineId should be NOT NULL.', 'Gasoline.GasolineId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RVP`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.RVP should not be part of primary key.', 'Gasoline.RVP type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RVP') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.RVP should be = FLOAT.', 'Gasoline.RVP type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RVP') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.RVP should be NOT NULL.', 'Gasoline.RVP required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`GASSULFUR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.GasSulfur should not be part of primary key.', 'Gasoline.GasSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'GASSULFUR') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.GasSulfur should be = FLOAT.', 'Gasoline.GasSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'GASSULFUR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.GasSulfur should be NOT NULL.', 'Gasoline.GasSulfur required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`GASMAXSULFUR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.GasMaxSulfur should not be part of primary key.', 'Gasoline.GasMaxSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'GASMAXSULFUR') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.GasMaxSulfur should be = FLOAT.', 'Gasoline.GasMaxSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'GASMAXSULFUR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.GasMaxSulfur should be NOT NULL.', 'Gasoline.GasMaxSulfur required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RVPOXYWAIVER`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.RVPOxyWaiver should not be part of primary key.', 'Gasoline.RVPOxyWaiver type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RVPOXYWAIVER') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.RVPOxyWaiver should be = TINYINT.', 'Gasoline.RVPOxyWaiver type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RVPOXYWAIVER') AND instr(ucase(aline), '(1)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. Gasoline.RVPOxyWaiver size should be 1.', 'Gasoline.RVPOxyWaiver data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RVPOXYWAIVER') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.RVPOxyWaiver should be NOT NULL.', 'Gasoline.RVPOxyWaiver required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ETOHVOLUME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.ETOHVolume should not be part of primary key.', 'Gasoline.ETOHVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETOHVOLUME') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.ETOHVolume should be = FLOAT.', 'Gasoline.ETOHVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETOHVOLUME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.ETOHVolume should be NOT NULL.', 'Gasoline.ETOHVolume required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ETOHMKTSHARE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.ETOHMktShare should not be part of primary key.', 'Gasoline.ETOHMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETOHMKTSHARE') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.ETOHMktShare should be = FLOAT.', 'Gasoline.ETOHMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETOHMKTSHARE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.ETOHMktShare should be NOT NULL.', 'Gasoline.ETOHMktShare required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MTBEVOLUME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.MTBEVolume should not be part of primary key.', 'Gasoline.MTBEVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MTBEVOLUME') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.MTBEVolume should be = FLOAT.', 'Gasoline.MTBEVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MTBEVOLUME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.MTBEVolume should be NOT NULL.', 'Gasoline.MTBEVolume required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MTBEMKTSHARE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.MTBEMktShare should not be part of primary key.', 'Gasoline.MTBEMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MTBEMKTSHARE') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.MTBEMktShare should be = FLOAT.', 'Gasoline.MTBEMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MTBEMKTSHARE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.MTBEMktShare should be NOT NULL.', 'Gasoline.MTBEMktShare required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ETBEVOLUME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.ETBEVolume should not be part of primary key.', 'Gasoline.ETBEVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETBEVOLUME') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.ETBEVolume should be = FLOAT.', 'Gasoline.ETBEVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETBEVOLUME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.ETBEVolume should be NOT NULL.', 'Gasoline.ETBEVolume required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ETBEMKTSHARE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.ETBEMktShare should not be part of primary key.', 'Gasoline.ETBEMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETBEMKTSHARE') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.ETBEMktShare should be = FLOAT.', 'Gasoline.ETBEMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ETBEMKTSHARE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.ETBEMktShare should be NOT NULL.', 'Gasoline.ETBEMktShare required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`TAMEVOLUME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.TAMEVolume should not be part of primary key.', 'Gasoline.TAMEVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TAMEVOLUME') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.TAMEVolume should be = FLOAT.', 'Gasoline.TAMEVolume type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TAMEVOLUME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.TAMEVolume should be NOT NULL.', 'Gasoline.TAMEVolume required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`TAMEMKTSHARE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.TAMEMktShare should not be part of primary key.', 'Gasoline.TAMEMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TAMEMKTSHARE') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.TAMEMktShare should be = FLOAT.', 'Gasoline.TAMEMktShare type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TAMEMKTSHARE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.TAMEMktShare should be NOT NULL.', 'Gasoline.TAMEMktShare required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`AROMATICCONTENT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.AromaticContent should not be part of primary key.', 'Gasoline.AromaticContent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AROMATICCONTENT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.AromaticContent should be = FLOAT.', 'Gasoline.AromaticContent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'AROMATICCONTENT') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.AromaticContent should be NOT NULL.', 'Gasoline.AromaticContent required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`OLEFINCONTENT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.OlefinContent should not be part of primary key.', 'Gasoline.OlefinContent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OLEFINCONTENT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.OlefinContent should be = FLOAT.', 'Gasoline.OlefinContent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'OLEFINCONTENT') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.OlefinContent should be NOT NULL.', 'Gasoline.OlefinContent required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`BENZENECONTENT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.BenzeneContent should not be part of primary key.', 'Gasoline.BenzeneContent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BENZENECONTENT') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.BenzeneContent should be = FLOAT.', 'Gasoline.BenzeneContent type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'BENZENECONTENT') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.BenzeneContent should be NOT NULL.', 'Gasoline.BenzeneContent required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`E200`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.E200 should not be part of primary key.', 'Gasoline.E200 type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'E200') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.E200 should be = FLOAT.', 'Gasoline.E200 type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'E200') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.E200 should be NOT NULL.', 'Gasoline.E200 required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`E300`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.E300 should not be part of primary key.', 'Gasoline.E300 type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'E300') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.E300 should be = FLOAT.', 'Gasoline.E300 type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'E300') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.E300 should be NOT NULL.', 'Gasoline.E300 required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RFG`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Gasoline.RFG should not be part of primary key.', 'Gasoline.RFG type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RFG') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Gasoline.RFG should be = CHAR.', 'Gasoline.RFG type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RFG') AND instr(ucase(aline), '(1)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. Gasoline.RFG size should be 1.', 'Gasoline.RFG data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RFG') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Gasoline.RFG should be NOT NULL.', 'Gasoline.RFG required type definition: checked OK.');

SELECT 'Check data structure/definition of table Hour ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempHour2005128174454.txt
SHOW CREATE TABLE Hour;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempHour2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HOURID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. Hour.HourID should be part of primary key.', 'Hour.HourID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Hour.HourID should be = TINYINT.', 'Hour.HourID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. Hour.HourID size should be 2.', 'Hour.HourID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Hour.HourID should be NOT NULL.', 'Hour.HourID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HOURNAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. Hour.HourName should not be part of primary key.', 'Hour.HourName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURNAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. Hour.HourName should be = CHAR.', 'Hour.HourName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURNAME') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. Hour.HourName size should be 50.', 'Hour.HourName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HOURNAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. Hour.HourName should be NOT NULL.', 'Hour.HourName required type definition: checked OK.');

SELECT 'Check data structure/definition of table HPMSRoadType ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempHPMSRoadType2005128174454.txt
SHOW CREATE TABLE HPMSRoadType;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempHPMSRoadType2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ROADTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. HPMSRoadType.RoadType should be part of primary key.', 'HPMSRoadType.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. HPMSRoadType.RoadType should be = TINYINT.', 'HPMSRoadType.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. HPMSRoadType.RoadType size should be 2.', 'HPMSRoadType.RoadType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. HPMSRoadType.RoadType should be NOT NULL.', 'HPMSRoadType.RoadType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ROADDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. HPMSRoadType.RoadDesc should not be part of primary key.', 'HPMSRoadType.RoadDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. HPMSRoadType.RoadDesc should be = CHAR.', 'HPMSRoadType.RoadDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADDESC') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. HPMSRoadType.RoadDesc size should be 50.', 'HPMSRoadType.RoadDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. HPMSRoadType.RoadDesc should be NOT NULL.', 'HPMSRoadType.RoadDesc required type definition: checked OK.');

SELECT 'Check data structure/definition of table M6VClass ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempM6VClass2005128174454.txt
SHOW CREATE TABLE M6VClass;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempM6VClass2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VCLASS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. M6VClass.VClass should be part of primary key.', 'M6VClass.VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASS') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VClass.VClass should be = TINYINT.', 'M6VClass.VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASS') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VClass.VClass size should be 2.', 'M6VClass.VClass data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASS') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VClass.VClass should be NOT NULL.', 'M6VClass.VClass required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VCLASSABBR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. M6VClass.VClassAbbr should not be part of primary key.', 'M6VClass.VClassAbbr type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASSABBR') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VClass.VClassAbbr should be = CHAR.', 'M6VClass.VClassAbbr type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASSABBR') AND instr(ucase(aline), '(6)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VClass.VClassAbbr size should be 6.', 'M6VClass.VClassAbbr data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASSABBR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VClass.VClassAbbr should be NOT NULL.', 'M6VClass.VClassAbbr required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VCLASSDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. M6VClass.VClassDesc should not be part of primary key.', 'M6VClass.VClassDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASSDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VClass.VClassDesc should be = CHAR.', 'M6VClass.VClassDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASSDESC') AND instr(ucase(aline), '(80)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VClass.VClassDesc size should be 80.', 'M6VClass.VClassDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VCLASSDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VClass.VClassDesc should be NOT NULL.', 'M6VClass.VClassDesc required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. M6VClass.VType should not be part of primary key.', 'M6VClass.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VClass.VType should be = TINYINT.', 'M6VClass.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VClass.VType size should be 2.', 'M6VClass.VType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VClass.VType should be NOT NULL.', 'M6VClass.VType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`P5VCLASS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. M6VClass.P5VClass should not be part of primary key.', 'M6VClass.P5VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'P5VCLASS') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VClass.P5VClass should be = TINYINT.', 'M6VClass.P5VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'P5VCLASS') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VClass.P5VClass size should be 2.', 'M6VClass.P5VClass data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'P5VCLASS') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VClass.P5VClass should be NOT NULL.', 'M6VClass.P5VClass required type definition: checked OK.');

SELECT 'Check data structure/definition of table M6VType ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempM6VType2005128174454.txt
SHOW CREATE TABLE M6VType;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempM6VType2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. M6VType.VType should be part of primary key.', 'M6VType.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VType.VType should be = TINYINT.', 'M6VType.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VType.VType size should be 2.', 'M6VType.VType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VType.VType should be NOT NULL.', 'M6VType.VType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VTYPEDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. M6VType.VTypeDesc should not be part of primary key.', 'M6VType.VTypeDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPEDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. M6VType.VTypeDesc should be = CHAR.', 'M6VType.VTypeDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPEDESC') AND instr(ucase(aline), '(80)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. M6VType.VTypeDesc size should be 80.', 'M6VType.VTypeDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPEDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. M6VType.VTypeDesc should be NOT NULL.', 'M6VType.VTypeDesc required type definition: checked OK.');

SELECT 'Check data structure/definition of table NaturalGas ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempNaturalGas2005128174454.txt
SHOW CREATE TABLE NaturalGas;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempNaturalGas2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NGID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. NaturalGas.NGId should be part of primary key.', 'NaturalGas.NGId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. NaturalGas.NGId should be = TINYINT.', 'NaturalGas.NGId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGID') AND instr(ucase(aline), '(4)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. NaturalGas.NGId size should be 4.', 'NaturalGas.NGId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. NaturalGas.NGId should be NOT NULL.', 'NaturalGas.NGId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NGSULFUR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. NaturalGas.NGSulfur should not be part of primary key.', 'NaturalGas.NGSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGSULFUR') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. NaturalGas.NGSulfur should be = FLOAT.', 'NaturalGas.NGSulfur type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NGSULFUR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. NaturalGas.NGSulfur should be NOT NULL.', 'NaturalGas.NGSulfur required type definition: checked OK.');

SELECT 'Check data structure/definition of table PollutantCode ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempPollutantCode2005128174454.txt
SHOW CREATE TABLE PollutantCode;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempPollutantCode2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`POLLUTANTCODEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. PollutantCode.PollutantCodeID should be part of primary key.', 'PollutantCode.PollutantCodeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. PollutantCode.PollutantCodeID should be = TINYINT.', 'PollutantCode.PollutantCodeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. PollutantCode.PollutantCodeID size should be 2.', 'PollutantCode.PollutantCodeID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. PollutantCode.PollutantCodeID should be NOT NULL.', 'PollutantCode.PollutantCodeID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`POLLUTANTCODE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. PollutantCode.PollutantCode should not be part of primary key.', 'PollutantCode.PollutantCode type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODE') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. PollutantCode.PollutantCode should be = CHAR.', 'PollutantCode.PollutantCode type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODE') AND instr(ucase(aline), '(10)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. PollutantCode.PollutantCode size should be 10.', 'PollutantCode.PollutantCode data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. PollutantCode.PollutantCode should be NOT NULL.', 'PollutantCode.PollutantCode required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`POLLUTANTNAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. PollutantCode.PollutantName should not be part of primary key.', 'PollutantCode.PollutantName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTNAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. PollutantCode.PollutantName should be = CHAR.', 'PollutantCode.PollutantName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTNAME') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. PollutantCode.PollutantName size should be 50.', 'PollutantCode.PollutantName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTNAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. PollutantCode.PollutantName should be NOT NULL.', 'PollutantCode.PollutantName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RUNSPECPOLLUTANTINDEX`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. PollutantCode.RunSpecPollutantIndex should not be part of primary key.', 'PollutantCode.RunSpecPollutantIndex type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RUNSPECPOLLUTANTINDEX') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. PollutantCode.RunSpecPollutantIndex should be = TINYINT.', 'PollutantCode.RunSpecPollutantIndex type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RUNSPECPOLLUTANTINDEX') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. PollutantCode.RunSpecPollutantIndex size should be 2.', 'PollutantCode.RunSpecPollutantIndex data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RUNSPECPOLLUTANTINDEX') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. PollutantCode.RunSpecPollutantIndex should be NOT NULL.', 'PollutantCode.RunSpecPollutantIndex required type definition: checked OK.');

SELECT 'Check data structure/definition of table SCC ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempSCC2005128174454.txt
SHOW CREATE TABLE SCC;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempSCC2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SCCID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. SCC.SCCID should be part of primary key.', 'SCC.SCCID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.SCCID should be = SMALLINT.', 'SCC.SCCID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.SCCID should be NOT NULL.', 'SCC.SCCID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SCC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.SCC should not be part of primary key.', 'SCC.SCC type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.SCC should be = CHAR.', 'SCC.SCC type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCC') AND instr(ucase(aline), '(10)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.SCC size should be 10.', 'SCC.SCC data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.SCC should be NOT NULL.', 'SCC.SCC required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`PART5VCLASS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.Part5VClass should not be part of primary key.', 'SCC.Part5VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PART5VCLASS') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.Part5VClass should be = TINYINT.', 'SCC.Part5VClass type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PART5VCLASS') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.Part5VClass size should be 2.', 'SCC.Part5VClass data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PART5VCLASS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.Part5VClass should be NULL.', 'SCC.Part5VClass required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`HPMSROADTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.HPMSRoadType should not be part of primary key.', 'SCC.HPMSRoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HPMSROADTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.HPMSRoadType should be = TINYINT.', 'SCC.HPMSRoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HPMSROADTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.HPMSRoadType size should be 2.', 'SCC.HPMSRoadType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'HPMSROADTYPE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.HPMSRoadType should be NULL.', 'SCC.HPMSRoadType required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SEGMENT`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.Segment should not be part of primary key.', 'SCC.Segment type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SEGMENT') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.Segment should be = TINYINT.', 'SCC.Segment type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SEGMENT') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.Segment size should be 2.', 'SCC.Segment data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SEGMENT') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.Segment should be NULL.', 'SCC.Segment required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FUELTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.FuelType should not be part of primary key.', 'SCC.FuelType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FUELTYPE') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.FuelType should be = CHAR.', 'SCC.FuelType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FUELTYPE') AND instr(ucase(aline), '(4)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.FuelType size should be 4.', 'SCC.FuelType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FUELTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.FuelType should be NOT NULL.', 'SCC.FuelType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`STROKES`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.Strokes should not be part of primary key.', 'SCC.Strokes type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STROKES') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.Strokes should be = TINYINT.', 'SCC.Strokes type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STROKES') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.Strokes size should be 2.', 'SCC.Strokes data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STROKES') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.Strokes should be NULL.', 'SCC.Strokes required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`PART5VCLASSDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.Part5VClassDesc should not be part of primary key.', 'SCC.Part5VClassDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PART5VCLASSDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.Part5VClassDesc should be = CHAR.', 'SCC.Part5VClassDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PART5VCLASSDESC') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.Part5VClassDesc size should be 50.', 'SCC.Part5VClassDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PART5VCLASSDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.Part5VClassDesc should be NOT NULL.', 'SCC.Part5VClassDesc required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ROADWAYTYPEDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.RoadwayTypeDesc should not be part of primary key.', 'SCC.RoadwayTypeDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADWAYTYPEDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.RoadwayTypeDesc should be = CHAR.', 'SCC.RoadwayTypeDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADWAYTYPEDESC') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.RoadwayTypeDesc size should be 50.', 'SCC.RoadwayTypeDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADWAYTYPEDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.RoadwayTypeDesc should be NOT NULL.', 'SCC.RoadwayTypeDesc required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SEGMENTDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.SegmentDesc should not be part of primary key.', 'SCC.SegmentDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SEGMENTDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.SegmentDesc should be = CHAR.', 'SCC.SegmentDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SEGMENTDESC') AND instr(ucase(aline), '(50)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.SegmentDesc size should be 50.', 'SCC.SegmentDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SEGMENTDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.SegmentDesc should be NOT NULL.', 'SCC.SegmentDesc required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SCCDESC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.SCCDesc should not be part of primary key.', 'SCC.SCCDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCDESC') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.SCCDesc should be = CHAR.', 'SCC.SCCDesc type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCDESC') AND instr(ucase(aline), '(150)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCC.SCCDesc size should be 150.', 'SCC.SCCDesc data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCDESC') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.SCCDesc should be NOT NULL.', 'SCC.SCCDesc required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MGNH3PERGALLON`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.mgNH3perGallon should not be part of primary key.', 'SCC.mgNH3perGallon type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MGNH3PERGALLON') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.mgNH3perGallon should be = FLOAT.', 'SCC.mgNH3perGallon type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MGNH3PERGALLON') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.mgNH3perGallon should be NULL.', 'SCC.mgNH3perGallon required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`TOGFAC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.TOGfac should not be part of primary key.', 'SCC.TOGfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TOGFAC') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.TOGfac should be = FLOAT.', 'SCC.TOGfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'TOGFAC') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.TOGfac should be NULL.', 'SCC.TOGfac required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NMOGFAC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.NMOGfac should not be part of primary key.', 'SCC.NMOGfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NMOGFAC') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.NMOGfac should be = FLOAT.', 'SCC.NMOGfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NMOGFAC') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.NMOGfac should be NULL.', 'SCC.NMOGfac required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NMHCFAC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.NMHCfac should not be part of primary key.', 'SCC.NMHCfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NMHCFAC') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.NMHCfac should be = FLOAT.', 'SCC.NMHCfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NMHCFAC') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.NMHCfac should be NULL.', 'SCC.NMHCfac required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VOCFAC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.VOCfac should not be part of primary key.', 'SCC.VOCfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VOCFAC') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.VOCfac should be = FLOAT.', 'SCC.VOCfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VOCFAC') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.VOCfac should be NULL.', 'SCC.VOCfac required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`PM25FAC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.PM25fac should not be part of primary key.', 'SCC.PM25fac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PM25FAC') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.PM25fac should be = FLOAT.', 'SCC.PM25fac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'PM25FAC') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.PM25fac should be NULL.', 'SCC.PM25fac required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SOAFAC`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCC.SOAfac should not be part of primary key.', 'SCC.SOAfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SOAFAC') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCC.SOAfac should be = FLOAT.', 'SCC.SOAfac type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SOAFAC') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCC.SOAfac should be NULL.', 'SCC.SOAfac required type: checked OK.');

SELECT 'Check data structure/definition of table SCCToxics ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempSCCToxics2005128174454.txt
SHOW CREATE TABLE SCCToxics;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempSCCToxics2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`SCCID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.SCCID should be part of primary key.', 'SCCToxics.SCCID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.SCCID should be = SMALLINT.', 'SCCToxics.SCCID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'SCCID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.SCCID should be NOT NULL.', 'SCCToxics.SCCID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`POLLUTANTCODEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.PollutantCodeID should be part of primary key.', 'SCCToxics.PollutantCodeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.PollutantCodeID should be = TINYINT.', 'SCCToxics.PollutantCodeID type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCCToxics.PollutantCodeID size should be 2.', 'SCCToxics.PollutantCodeID data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'POLLUTANTCODEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.PollutantCodeID should be NOT NULL.', 'SCCToxics.PollutantCodeID required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EXHBASEGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.exhBaseGas should not be part of primary key.', 'SCCToxics.exhBaseGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHBASEGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.exhBaseGas should be = FLOAT.', 'SCCToxics.exhBaseGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHBASEGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.exhBaseGas should be NULL.', 'SCCToxics.exhBaseGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EXHETHGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.exhEthGas should not be part of primary key.', 'SCCToxics.exhEthGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHETHGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.exhEthGas should be = FLOAT.', 'SCCToxics.exhEthGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHETHGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.exhEthGas should be NULL.', 'SCCToxics.exhEthGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EXHMTBEGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.exhMTBEGas should not be part of primary key.', 'SCCToxics.exhMTBEGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHMTBEGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.exhMTBEGas should be = FLOAT.', 'SCCToxics.exhMTBEGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHMTBEGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.exhMTBEGas should be NULL.', 'SCCToxics.exhMTBEGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EXHRFGGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.exhRFGGas should not be part of primary key.', 'SCCToxics.exhRFGGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHRFGGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.exhRFGGas should be = FLOAT.', 'SCCToxics.exhRFGGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHRFGGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.exhRFGGas should be NULL.', 'SCCToxics.exhRFGGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EXHDIESEL`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.exhDiesel should not be part of primary key.', 'SCCToxics.exhDiesel type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHDIESEL') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.exhDiesel should be = FLOAT.', 'SCCToxics.exhDiesel type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EXHDIESEL') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.exhDiesel should be NULL.', 'SCCToxics.exhDiesel required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EVAPBASEGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.evapBaseGas should not be part of primary key.', 'SCCToxics.evapBaseGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPBASEGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.evapBaseGas should be = FLOAT.', 'SCCToxics.evapBaseGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPBASEGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.evapBaseGas should be NULL.', 'SCCToxics.evapBaseGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EVAPETHGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.evapEthGas should not be part of primary key.', 'SCCToxics.evapEthGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPETHGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.evapEthGas should be = FLOAT.', 'SCCToxics.evapEthGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPETHGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.evapEthGas should be NULL.', 'SCCToxics.evapEthGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EVAPMTBEGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.evapMTBEGas should not be part of primary key.', 'SCCToxics.evapMTBEGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPMTBEGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.evapMTBEGas should be = FLOAT.', 'SCCToxics.evapMTBEGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPMTBEGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.evapMTBEGas should be NULL.', 'SCCToxics.evapMTBEGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`EVAPRFGGAS`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.evapRFGGas should not be part of primary key.', 'SCCToxics.evapRFGGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPRFGGAS') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.evapRFGGas should be = FLOAT.', 'SCCToxics.evapRFGGas type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'EVAPRFGGAS') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.evapRFGGas should be NULL.', 'SCCToxics.evapRFGGas required type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`RATIOTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. SCCToxics.ratioType should not be part of primary key.', 'SCCToxics.ratioType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RATIOTYPE') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. SCCToxics.ratioType should be = CHAR.', 'SCCToxics.ratioType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RATIOTYPE') AND instr(ucase(aline), '(5)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. SCCToxics.ratioType size should be 5.', 'SCCToxics.ratioType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'RATIOTYPE') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. SCCToxics.ratioType should be NULL.', 'SCCToxics.ratioType required type: checked OK.');

SELECT 'Check data structure/definition of table State ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempState2005128174454.txt
SHOW CREATE TABLE State;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempState2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`FIPSSTATEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. State.FIPSStateId should be part of primary key.', 'State.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.FIPSStateId should be = TINYINT.', 'State.FIPSStateId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.FIPSStateId size should be 2.', 'State.FIPSStateId data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'FIPSSTATEID') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.FIPSStateId should be NOT NULL.', 'State.FIPSStateId required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`STATENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.StateName should not be part of primary key.', 'State.StateName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STATENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.StateName should be = CHAR.', 'State.StateName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STATENAME') AND instr(ucase(aline), '(25)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.StateName size should be 25.', 'State.StateName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STATENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.StateName should be NOT NULL.', 'State.StateName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`STATEABBR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.StateAbbr should not be part of primary key.', 'State.StateAbbr type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STATEABBR') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.StateAbbr should be = CHAR.', 'State.StateAbbr type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STATEABBR') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.StateAbbr size should be 2.', 'State.StateAbbr data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'STATEABBR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.StateAbbr should be NOT NULL.', 'State.StateAbbr required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`NLEVFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.NLEVFileName should not be part of primary key.', 'State.NLEVFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NLEVFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.NLEVFileName should be = CHAR.', 'State.NLEVFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NLEVFILENAME') AND instr(ucase(aline), '(12)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.NLEVFileName size should be 12.', 'State.NLEVFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'NLEVFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.NLEVFileName should be NOT NULL.', 'State.NLEVFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`T2EXHPHASEINFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.T2ExhPhaseInFileName should not be part of primary key.', 'State.T2ExhPhaseInFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2EXHPHASEINFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.T2ExhPhaseInFileName should be = CHAR.', 'State.T2ExhPhaseInFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2EXHPHASEINFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.T2ExhPhaseInFileName size should be 8.', 'State.T2ExhPhaseInFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2EXHPHASEINFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.T2ExhPhaseInFileName should be NOT NULL.', 'State.T2ExhPhaseInFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`T2EVAPPHASEINFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.T2EvapPhaseInFileName should not be part of primary key.', 'State.T2EvapPhaseInFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2EVAPPHASEINFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.T2EvapPhaseInFileName should be = CHAR.', 'State.T2EvapPhaseInFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2EVAPPHASEINFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.T2EvapPhaseInFileName size should be 8.', 'State.T2EvapPhaseInFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2EVAPPHASEINFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.T2EvapPhaseInFileName should be NOT NULL.', 'State.T2EvapPhaseInFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`T2CERTFILENAME`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.T2CertFileName should not be part of primary key.', 'State.T2CertFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2CERTFILENAME') AND instr(ucase(aline), 'CHAR');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.T2CertFileName should be = CHAR.', 'State.T2CertFileName type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2CERTFILENAME') AND instr(ucase(aline), '(8)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. State.T2CertFileName size should be 8.', 'State.T2CertFileName data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'T2CERTFILENAME') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.T2CertFileName should be NOT NULL.', 'State.T2CertFileName required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATASOURCEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. State.DataSourceId should not be part of primary key.', 'State.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. State.DataSourceId should be = SMALLINT.', 'State.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. State.DataSourceId should be NULL.', 'State.DataSourceId required type: checked OK.');

SELECT 'Check data structure/definition of table VMTMonthAllocation ...';
DROP TABLE IF EXISTS xTemp;
CREATE TABLE xTemp (rowID smallint not null auto_increment, primary key (rowID), aline varchar(200) NULL);
\t
\T C:\\temp\\xTempVMTMonthAllocation2005128174454.txt
SHOW CREATE TABLE VMTMonthAllocation;
\t
\T C:\\temp\\NMIM.txt
LOAD DATA INFILE 'C:\\temp\\xTempVMTMonthAllocation2005128174454.txt' 
       REPLACE 
       INTO TABLE xTemp
       FIELDS TERMINATED BY '^' 
       ENCLOSED by '"' 
       ESCAPED by '\\' 
       LINES TERMINATED BY '\r\n' 
       (aline);
FLUSH TABLES;
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`VTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. VMTMonthAllocation.VType should be part of primary key.', 'VMTMonthAllocation.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. VMTMonthAllocation.VType should be = TINYINT.', 'VMTMonthAllocation.VType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. VMTMonthAllocation.VType size should be 2.', 'VMTMonthAllocation.VType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'VTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. VMTMonthAllocation.VType should be NOT NULL.', 'VMTMonthAllocation.VType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ROADTYPE`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. VMTMonthAllocation.RoadType should be part of primary key.', 'VMTMonthAllocation.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'TINYINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. VMTMonthAllocation.RoadType should be = TINYINT.', 'VMTMonthAllocation.RoadType type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), '(2)');
SELECT IF(ISNULL(@TestLine),'Error 9032: Data definition (size) error. VMTMonthAllocation.RoadType size should be 2.', 'VMTMonthAllocation.RoadType data size type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ROADTYPE') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. VMTMonthAllocation.RoadType should be NOT NULL.', 'VMTMonthAllocation.RoadType required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`MONTH`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(ISNULL(@TestLine),'Error 9008: Primary key error. VMTMonthAllocation.Month should be part of primary key.', 'VMTMonthAllocation.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. VMTMonthAllocation.Month should be = SMALLINT.', 'VMTMonthAllocation.Month type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'MONTH') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. VMTMonthAllocation.Month should be NOT NULL.', 'VMTMonthAllocation.Month required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`ALLOCFACTOR`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. VMTMonthAllocation.AllocFactor should not be part of primary key.', 'VMTMonthAllocation.AllocFactor type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALLOCFACTOR') AND instr(ucase(aline), 'FLOAT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. VMTMonthAllocation.AllocFactor should be = FLOAT.', 'VMTMonthAllocation.AllocFactor type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'ALLOCFACTOR') AND instr(ucase(aline), 'NOT NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. VMTMonthAllocation.AllocFactor should be NOT NULL.', 'VMTMonthAllocation.AllocFactor required type definition: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), '`DATASOURCEID`') AND instr(Ucase(aline), 'PRIMARY KEY');
SELECT IF(NOT ISNULL(@TestLine),'Error 9008: Primary key error. VMTMonthAllocation.DataSourceId should not be part of primary key.', 'VMTMonthAllocation.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'SMALLINT');
SELECT IF(ISNULL(@TestLine),'Error 9028: Data definition (type) error. VMTMonthAllocation.DataSourceId should be = SMALLINT.', 'VMTMonthAllocation.DataSourceId type: checked OK.');
SET @TestLine := NULL;
SELECT @TestLine := aline from xTemp where instr(ucase(aline), 'DATASOURCEID') AND instr(ucase(aline), 'NULL');
SELECT IF(ISNULL(@TestLine),'Error 9030: Data definition (NULL/NOT NULL) error. VMTMonthAllocation.DataSourceId should be NULL.', 'VMTMonthAllocation.DataSourceId required type: checked OK.');
 
-- *** Foreign key/data integrity between parent and child tables *** 
 
SELECT 'Checking relationship between HPMSRoadType and  BaseYearVMT' \p;
SELECT COUNT(*) FROM BaseYearVMT \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT RoadType FROM BaseYearVMT;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (RoadType); 
CREATE TABLE x_tempP SELECT DISTINCT RoadType FROM HPMSRoadType;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (RoadType); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in HPMSRoadType not used by BaseYearVMT') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.RoadType=c.RoadType  
WHERE ISNULL(c.RoadType) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in BaseYearVMT not defined in HPMSRoadType') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.RoadType=c.RoadType  
WHERE ISNULL(p.RoadType) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between M6VClass and  BaseYearVMT' \p;
SELECT COUNT(*) FROM BaseYearVMT \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT VClass FROM BaseYearVMT;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (VClass); 
CREATE TABLE x_tempP SELECT DISTINCT VClass FROM M6VClass;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (VClass); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in M6VClass not used by BaseYearVMT') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.VClass=c.VClass  
WHERE ISNULL(c.VClass) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in BaseYearVMT not defined in M6VClass') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.VClass=c.VClass  
WHERE ISNULL(p.VClass) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between CountyMap and  CountyMap' \p;
SELECT COUNT(*) FROM CountyMap \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT NRRepFIPSCntyID, FIPSStateId FROM CountyMap;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (NRRepFIPSCntyID, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyMap;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyMap not defined in CountyMap') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.NRRepFIPSCntyID  AND p.FIPSStateId=c.FIPSStateId 
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId);
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between CountyMap and  CountyMap' \p;
SELECT COUNT(*) FROM CountyMap \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT HwyRepFIPSCntyID, FIPSStateId FROM CountyMap;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (HwyRepFIPSCntyID, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyMap;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyMap not defined in CountyMap') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.HwyRepFIPSCntyID  AND p.FIPSStateId=c.FIPSStateId
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId);
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between County and  CountyMap' \p;
SELECT COUNT(*) FROM CountyMap \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyMap;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM County;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in County not used by CountyMap') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(c.FIPSCountyId) OR ISNULL(c.FIPSStateId) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyMap not defined in County') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between County and  CountyMonth' \p;
SELECT COUNT(*) FROM CountyMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM County;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in County not used by CountyMonth') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(c.FIPSCountyId) OR ISNULL(c.FIPSStateId) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyMonth not defined in County') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between Hour and  CountyMonthHour' \p;
SELECT COUNT(*) FROM CountyMonthHour \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT HourID FROM CountyMonthHour;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (HourID); 
CREATE TABLE x_tempP SELECT DISTINCT HourID FROM Hour;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (HourID); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in Hour not used by CountyMonthHour') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.HourID=c.HourID  
WHERE ISNULL(c.HourID) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyMonthHour not defined in Hour') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.HourID=c.HourID  
WHERE ISNULL(p.HourID) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between CountyMonth and  CountyMonthHour' \p;
SELECT COUNT(*) FROM CountyMonthHour \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId, Month FROM CountyMonthHour;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId, Month); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId, Month FROM CountyMonth;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId, Month); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in CountyMonth not used by CountyMonthHour') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Month=c.Month  
WHERE ISNULL(c.FIPSCountyId) OR ISNULL(c.FIPSStateId) OR ISNULL(c.Month) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyMonthHour not defined in CountyMonth') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Month=c.Month  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Month) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between FileType and  CountyNRFile' \p;
SELECT COUNT(*) FROM CountyNRFile \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FileTypeID FROM CountyNRFile;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FileTypeID); 
CREATE TABLE x_tempP SELECT DISTINCT FileTypeID FROM FileType;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FileTypeID); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyNRFile not defined in FileType') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FileTypeID=c.FileTypeID  
WHERE ISNULL(p.FileTypeID) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between County and  CountyNRFile' \p;
SELECT COUNT(*) FROM CountyNRFile \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyNRFile;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM County;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyNRFile not defined in County') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between VMTMonthAllocation and  CountyVMTMonthAllocation' \p;
SELECT COUNT(*) FROM CountyVMTMonthAllocation \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT VType, RoadType, Month FROM CountyVMTMonthAllocation;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (VType, RoadType, Month); 
CREATE TABLE x_tempP SELECT DISTINCT VType, RoadType, Month FROM VMTMonthAllocation;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (VType, RoadType, Month); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyVMTMonthAllocation not defined in VMTMonthAllocation') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.VType=c.VType  AND p.RoadType=c.RoadType  AND p.Month=c.Month  
WHERE ISNULL(p.VType) OR ISNULL(p.RoadType) OR ISNULL(p.Month) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between County and  CountyVMTMonthAllocation' \p;
SELECT COUNT(*) FROM CountyVMTMonthAllocation \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyVMTMonthAllocation;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM County;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyVMTMonthAllocation not defined in County') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between County and  CountyYear' \p;
SELECT COUNT(*) FROM CountyYear \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId FROM CountyYear;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId FROM County;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in County not used by CountyYear') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(c.FIPSCountyId) OR ISNULL(c.FIPSStateId) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYear not defined in County') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between Diesel and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT NRDieselId FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (NRDieselId); 
CREATE TABLE x_tempP SELECT DISTINCT DieselId FROM Diesel;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (DieselId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in Diesel') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.DieselId=c.NRDieselId  
WHERE ISNULL(p.DieselId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between Gasoline and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT NRGasolineId FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (NRGasolineId); 
CREATE TABLE x_tempP SELECT DISTINCT GasolineId FROM Gasoline;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (GasolineId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in Gasoline') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.GasolineId=c.NRGasolineId  
WHERE ISNULL(p.GasolineId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between Gasoline and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT HwyGasolineId FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (HwyGasolineId); 
CREATE TABLE x_tempP SELECT DISTINCT GasolineId FROM Gasoline;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (GasolineId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in Gasoline') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.GasolineId=c.HwyGasolineId  
WHERE ISNULL(p.GasolineId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between NaturalGas and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT NGId FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (NGId); 
CREATE TABLE x_tempP SELECT DISTINCT NGId FROM NaturalGas;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (NGId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in NaturalGas') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.NGId=c.NGId  
WHERE ISNULL(p.NGId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between Diesel and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT HwyDieselId FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (HwyDieselId); 
CREATE TABLE x_tempP SELECT DISTINCT DieselId FROM Diesel;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (DieselId); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in Diesel') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.DieselId=c.HwyDieselId  
WHERE ISNULL(p.DieselId) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between CountyYear and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId, Year FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId, Year); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId, Year FROM CountyYear;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId, Year); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in CountyYear not used by CountyYearMonth') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Year=c.Year  
WHERE ISNULL(c.FIPSCountyId) OR ISNULL(c.FIPSStateId) OR ISNULL(c.Year) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in CountyYear') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Year=c.Year  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Year) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between CountyMonth and  CountyYearMonth' \p;
SELECT COUNT(*) FROM CountyYearMonth \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId, Month FROM CountyYearMonth;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId, Month); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId, Month FROM CountyMonth;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId, Month); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in CountyMonth not used by CountyYearMonth') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Month=c.Month  
WHERE ISNULL(c.FIPSCountyId) OR ISNULL(c.FIPSStateId) OR ISNULL(c.Month) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonth not defined in CountyMonth') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Month=c.Month  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Month) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between Hour and  CountyYearMonthHour' \p;
SELECT COUNT(*) FROM CountyYearMonthHour \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT HourID FROM CountyYearMonthHour;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (HourID); 
CREATE TABLE x_tempP SELECT DISTINCT HourID FROM Hour;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (HourID); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonthHour not defined in Hour') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.HourID=c.HourID  
WHERE ISNULL(p.HourID) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between CountyYearMonth and  CountyYearMonthHour' \p;
SELECT COUNT(*) FROM CountyYearMonthHour \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT FIPSCountyId, FIPSStateId, Year, Month FROM CountyYearMonthHour;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (FIPSCountyId, FIPSStateId, Year, Month); 
CREATE TABLE x_tempP SELECT DISTINCT FIPSCountyId, FIPSStateId, Year, Month FROM CountyYearMonth;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (FIPSCountyId, FIPSStateId, Year, Month); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in CountyYearMonthHour not defined in CountyYearMonth') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.FIPSCountyId=c.FIPSCountyId  AND p.FIPSStateId=c.FIPSStateId  AND p.Year=c.Year  AND p.Month=c.Month  
WHERE ISNULL(p.FIPSCountyId) OR ISNULL(p.FIPSStateId) OR ISNULL(p.Year) OR ISNULL(p.Month) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between M6VType and  M6VClass' \p;
SELECT COUNT(*) FROM M6VClass \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT VType FROM M6VClass;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (VType); 
CREATE TABLE x_tempP SELECT DISTINCT VType FROM M6VType;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (VType); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in M6VClass not defined in M6VType') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.VType=c.VType  
WHERE ISNULL(p.VType) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between SCC and  SCCToxics' \p;
SELECT COUNT(*) FROM SCCToxics \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT SCCID FROM SCCToxics;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (SCCID); 
CREATE TABLE x_tempP SELECT DISTINCT SCCID FROM SCC;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (SCCID); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in SCCToxics not defined in SCC') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.SCCID=c.SCCID  
WHERE ISNULL(p.SCCID) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between PollutantCode and  SCCToxics' \p;
SELECT COUNT(*) FROM SCCToxics \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT PollutantCodeID FROM SCCToxics;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (PollutantCodeID); 
CREATE TABLE x_tempP SELECT DISTINCT PollutantCodeID FROM PollutantCode;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (PollutantCodeID); 
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in SCCToxics not defined in PollutantCode') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.PollutantCodeID=c.PollutantCodeID  
WHERE ISNULL(p.PollutantCodeID) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between HPMSRoadType and  VMTMonthAllocation' \p;
SELECT COUNT(*) FROM VMTMonthAllocation \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT RoadType FROM VMTMonthAllocation;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (RoadType); 
CREATE TABLE x_tempP SELECT DISTINCT RoadType FROM HPMSRoadType;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (RoadType); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in HPMSRoadType not used by VMTMonthAllocation') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.RoadType=c.RoadType  
WHERE ISNULL(c.RoadType) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in VMTMonthAllocation not defined in HPMSRoadType') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.RoadType=c.RoadType  
WHERE ISNULL(p.RoadType) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
SELECT 'Checking relationship between M6VType and  VMTMonthAllocation' \p;
SELECT COUNT(*) FROM VMTMonthAllocation \p;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
CREATE TABLE x_tempC SELECT DISTINCT VType FROM VMTMonthAllocation;
ALTER TABLE x_tempC ADD UNIQUE xTempCindx (VType); 
CREATE TABLE x_tempP SELECT DISTINCT VType FROM M6VType;
ALTER TABLE x_tempP ADD UNIQUE xTempPindx (VType); 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Warning 9612: Foreign key in M6VType not used by VMTMonthAllocation') AS CheckResult 
FROM x_tempP p 
LEFT JOIN x_tempC c 
ON p.VType=c.VType  
WHERE ISNULL(c.VType) ;
 
SELECT COUNT(*), IF(COUNT(*)=0, 'FK checked OK', 'Error 9024: Foreign key in VMTMonthAllocation not defined in M6VType') AS CheckResult 
FROM x_tempC c 
LEFT JOIN x_tempP p 
ON p.VType=c.VType  
WHERE ISNULL(p.VType) ;
 
DROP TABLE IF EXISTS x_tempP; 
DROP TABLE IF EXISTS x_tempC; 
 
-- Optimizing database tables. 
 
OPTIMIZE TABLE BaseYearVMT; 
OPTIMIZE TABLE County; 
OPTIMIZE TABLE CountyMap; 
OPTIMIZE TABLE CountyMonth; 
OPTIMIZE TABLE CountyMonthHour; 
OPTIMIZE TABLE CountyNRFile; 
OPTIMIZE TABLE CountyVMTMonthAllocation; 
OPTIMIZE TABLE CountyYear; 
OPTIMIZE TABLE CountyYearMonth; 
OPTIMIZE TABLE CountyYearMonthHour; 
OPTIMIZE TABLE DataSource; 
OPTIMIZE TABLE Diesel; 
OPTIMIZE TABLE EmissionType; 
OPTIMIZE TABLE FileType; 
OPTIMIZE TABLE Gasoline; 
OPTIMIZE TABLE Hour; 
OPTIMIZE TABLE HPMSRoadType; 
OPTIMIZE TABLE M6VClass; 
OPTIMIZE TABLE M6VType; 
OPTIMIZE TABLE NaturalGas; 
OPTIMIZE TABLE PollutantCode; 
OPTIMIZE TABLE SCC; 
OPTIMIZE TABLE SCCToxics; 
OPTIMIZE TABLE State; 
OPTIMIZE TABLE VMTMonthAllocation; 
 
-- Create reports 
DROP TABLE IF EXISTS x_MOVESout; 
CREATE TABLE IF NOT EXISTS x_MOVESout( 
     RowID       SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
     Message     CHAR(150) NULL);
LOAD DATA INFILE 'C:\\temp\\NMIM.txt' 
     INTO TABLE x_MOVESout 
     FIELDS TERMINATED BY '^' 
     ENCLOSED by '"' 
     ESCAPED by '\\' 
     LINES TERMINATED BY '\r\n' 
     (Message);
 
DROP TABLE IF EXISTS x_ErrWarning;
DELETE FROM x_MOVESout WHERE INSTR(UCASE(Message),'SUM(') AND INSTR(UCASE(Message),'AS');
DELETE FROM x_MOVESout WHERE INSTR(UCASE(Message),'IF(') AND INSTR(UCASE(Message),'ERROR');
DELETE FROM x_MOVESout WHERE INSTR(UCASE(Message),'AS ERRORMINMAX');
DELETE FROM x_MOVESout WHERE INSTR(UCASE(Message),'WARNINGS: 0');
DELETE FROM x_MOVESout WHERE INSTR(UCASE(Message),'IF(') AND INSTR(UCASE(Message),'IF(');
DELETE FROM x_MOVESout WHERE INSTR(UCASE(Message),'SELECT') AND INSTR(UCASE(Message),':') AND INSTR(UCASE(Message),'AS') AND INSTR(UCASE(Message),'ERROR');
CREATE TABLE x_ErrWarning
     SELECT RowID, Message 
     FROM x_MOVESout 
     WHERE 
     (INSTR(UCASE(Message),'ERROR') AND MID(Message,INSTR(UCASE(Message),'ERROR')+10,1)=':') OR 
     (UCASE(MID(Message,1,5))='ERROR' AND MID(Message,11,1)=':') OR 
     (INSTR(UCASE(Message),'WARNING') AND MID(Message,INSTR(UCASE(Message),'WARNING')+12,1)=':' AND NOT INSTR(UCASE(Message),'IF(')) OR 
     (UCASE(MID(Message,1,7))='WARNING' AND MID(Message,13,1)=':');
 
INSERT INTO x_ErrWarning (RowID, Message) VALUES (-4, '*********************************************************** ');
INSERT INTO x_ErrWarning (RowID, Message) VALUES (-3, '  ');
INSERT INTO x_ErrWarning (RowID, Message) VALUES (-2, '    -- Errors/Warnings Report From Database Check Result -- ');
 
SET @EndTime := NULL;
SET @EndTime := CURTIME();
INSERT INTO x_ErrWarning (RowID, Message) VALUES (-6, 'Program started at / ended at ');
INSERT INTO x_ErrWarning (RowID, Message) VALUES (-5, CONCAT(@StartTime, ' / ', @EndTime));
SET @TestLine := NULL;
SELECT @TestLine := CURDATE();
INSERT INTO x_ErrWarning (RowID, Message) VALUES (-1, @TestLine);
CREATE UNIQUE INDEX RIDIDX ON x_ErrWarning (RowID);
SELECT Message 
     INTO OUTFILE 'C:\\temp\\NMIMErr.txt' 
     FIELDS TERMINATED BY ',' 
     ENCLOSED by '"' 
     ESCAPED by '\\' 
     LINES TERMINATED BY '\r\n' 
FROM x_ErrWarning;
FLUSH TABLES;
DROP TABLE IF EXISTS x_MOVESout; 
 
SELECT 'Errors/Warnings Report:';
SELECT * FROM x_ErrWarning;
 
SELECT 'See report file: C:\\temp\\NMIM.txt for details';
DROP TABLE IF EXISTS x_ErrWarning;
DROP TABLE IF EXISTS xTemp;
FLUSH TABLES;
SELECT '*** End of the DBChecker program.';
\t 
