#include #include #include #include #define CONNECT_STRING "netdc/dB2Usr@ncedc" #define NOMOREROWS 1403 EXEC SQL INCLUDE sqlca.h; EXEC SQL BEGIN DECLARE SECTION; char user_pwd[80]; char SQL_Command[1024]; char network[71]; char sta[7]; char staname[51]; float lat; float lon; float elev; char ondate[21]; char offdate[21]; char seedchan[4]; float samprate; char instrument[71]; char remark[31]; short ind; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE sql_stmt STATEMENT; long SQLCODE; void sql_error(); /* Handles unrecoverable errors */ /**************************************/ /* Trim trailing blanks from a string */ /**************************************/ char* Remove_Blank (char *s) { char *p = s + strlen (s); while (--p >= s) if (*p == ' ') *p = '\0'; else break; return (s); } /*****************/ /* Main function */ /*****************/ main (argc, argv) int argc; char* argv[]; { FILE *f1, *f2; char update[20]; struct tm *ltimer; time_t tloc, timeofday; /* Get the current system time */ timeofday = time( &tloc ); ltimer = localtime( &timeofday ); strftime( update, 20, "%B %d, %Y", ltimer); /* Opening output files */ if ((f1 = fopen ("cit.stations.info.html", "w+t")) == NULL) { printf ("\nError: couldn't open file cit.stations.info.html\n\n"); exit (-1); } if ((f2 = fopen ("cit.stations.html", "w+t")) == NULL) { printf ("\nError: couldn't open file cit.stations.html\n\n"); exit (-1); } /* Connect to ORACLE. */ EXEC SQL WHENEVER SQLERROR DO sql_error(); strcpy (user_pwd, CONNECT_STRING); EXEC SQL CONNECT :user_pwd; /**************************/ /* cit.stations.info.html */ /**************************/ /* Outputing header information */ fprintf (f1, "\n\n\nCIT Station Information (Generated on %s)\n\n", update); fprintf (f1, "\n\n"); fprintf (f1, "

CIT Station Information

\n\n"); fprintf (f1, "

TriNet Seismic Network - CalTech Pasadena

\n\n"); /* Building SQL statement */ strcpy (SQL_Command, "SELECT DISTINCT sta, MIN (ondate), MAX (offdate) FROM Station_Data WHERE net = 'CI' GROUP BY sta ORDER BY sta"); /* Declaring cursor */ EXEC SQL PREPARE sql_stmt FROM :SQL_Command; EXEC SQL DECLARE db_cursor CURSOR FOR sql_stmt; /* Opening cursor */ EXEC SQL OPEN db_cursor; /* fetch instances from the NCEDC database */ EXEC SQL FETCH db_cursor INTO :sta, :ondate, :offdate; while (sqlca.sqlcode != NOMOREROWS) { Remove_Blank (sta); Remove_Blank (ondate); Remove_Blank (offdate); EXEC SQL SELECT d.description, s.staname, s.lat, s.lon, s.elev INTO :network, :staname, :lat, :lon, :elev FROM Station_Data s, D_Abbreviation d WHERE s.net_id = d.id AND s.net = 'CI' AND s.sta = :sta AND s.offdate = :offdate; Remove_Blank (network); Remove_Blank (staname); if (!strcmp (offdate, "3000/01/01 00:00:00")) strcpy (offdate, "Current"); fprintf (f1, "

%s - %s

\n", sta, sta, staname); fprintf (f1, "\n"); fprintf (f1, "\n

Station	%s", sta); fprintf (f1, "\n
Name	%s", staname); fprintf (f1, "\n
Network	%s", "CI"); fprintf (f1, "\n
Owner	%s", network); fprintf (f1, "\n
Latitude	%f", lat); fprintf (f1, "\n
Longitude	%f", lon); fprintf (f1, "\n
Elevation	%5.1f", elev); fprintf (f1, "\n
Starting date	%s", ondate); fprintf (f1, "\n
Ending date	%s", offdate); fprintf (f1, "\n

\n"); EXEC SQL FETCH db_cursor INTO :sta, :ondate, :offdate; } /* Close the cursor */ EXEC SQL CLOSE db_cursor; /* Outputing footer information */ fprintf (f1, "\n\n\n"); /*********************/ /* cit.stations.html */ /*********************/ /* Outputing header information */ fprintf (f2, "\n\n\nCIT Station Information (Generated on %s)\n\n", update); fprintf (f2, "\n\n"); fprintf (f2, "

CIT Station Information

\n\n"); fprintf (f2, "

TriNet Seismic Network (CIT) - CalTech Pasadena

(Generated on %s)

\n\n", update); /**************************/ /* CIT Broadband Stations */ /**************************/ fprintf (f2, "\n

CIT Broadband Stations

\n"); fprintf (f2, "\n

Station	Network	Latitude	Longitude	Elevation	Starting date	Ending date	Geographic location\n"); /* Building SQL statement / strcpy (SQL_Command, "SELECT DISTINCT sta, MIN (ondate), MAX (offdate) FROM Channel_Data WHERE net = 'CI' AND seedchan LIKE '_H_' GROUP BY sta ORDER BY sta"); / Declaring cursor / EXEC SQL PREPARE sql_stmt FROM :SQL_Command; EXEC SQL DECLARE db_cursor8 CURSOR FOR sql_stmt; / Opening cursor / EXEC SQL OPEN db_cursor8; / fetch instances from the NCEDC database */ EXEC SQL FETCH db_cursor8 INTO :sta, :ondate, :offdate; while (sqlca.sqlcode != NOMOREROWS) { Remove_Blank (sta); Remove_Blank (ondate); Remove_Blank (offdate); EXEC SQL SELECT s.staname, s.lat, s.lon, s.elev INTO :staname, :lat, :lon, :elev FROM Station_Data s WHERE s.net = 'CI' AND s.sta = :sta AND s.offdate = :offdate; Remove_Blank (network); Remove_Blank (staname); if (!strcmp (offdate, "3000/01/01 00:00:00")) strcpy (offdate, "Current"); fprintf (f2, "
%s", sta); fprintf (f2, "	%s", "CI"); fprintf (f2, "	%f", lat); fprintf (f2, "	%f", lon); fprintf (f2, "	%5.1f", elev); fprintf (f2, "	%.10s", ondate); fprintf (f2, "	%.10s", offdate); fprintf (f2, "	%s", staname); EXEC SQL FETCH db_cursor8 INTO :sta, :ondate, :offdate; } /* Close the cursor */ EXEC SQL CLOSE db_cursor8; fprintf (f2, "\n

"); /***************************/ /* CIT Continuous Channels */ /***************************/ fprintf (f2, "\n

CIT Continuous Channels

\n"); fprintf (f2, "\n

SEED Channels	Sample Rate	Instrument Type\n"); /* Building SQL statement / strcpy (SQL_Command, "SELECT DISTINCT RTRIM (c.seedchan, 'ENZ'), c.samprate, d.description FROM Channel_Data c, D_Abbreviation d WHERE c.inid = d.id AND net = 'CI' AND seedchan LIKE '_H_' AND flags LIKE 'C%' ORDER BY samprate DESC"); / Declaring cursor / EXEC SQL PREPARE sql_stmt FROM :SQL_Command; EXEC SQL DECLARE db_cursor9 CURSOR FOR sql_stmt; / Opening cursor / EXEC SQL OPEN db_cursor9; / fetch instances from the NCEDC database */ EXEC SQL FETCH db_cursor9 INTO :seedchan, :samprate, :instrument; while (sqlca.sqlcode != NOMOREROWS) { Remove_Blank (seedchan); Remove_Blank (instrument); fprintf (f2, "\n
%s?	%f	%s", seedchan, samprate, instrument); EXEC SQL FETCH db_cursor9 INTO :seedchan, :samprate, :instrument; } /* Close the cursor */ EXEC SQL CLOSE db_cursor9; fprintf (f2, "\n

"); /**************************/ /* CIT Triggered Channels */ /**************************/ fprintf (f2, "\n

CIT Triggered Channels

\n"); fprintf (f2, "\n

SEED Channels	Sample Rate	Instrument Type\n"); /* Building SQL statement / strcpy (SQL_Command, "SELECT DISTINCT RTRIM (c.seedchan, 'ENZ'), c.samprate, d.description FROM Channel_Data c, D_Abbreviation d WHERE c.inid = d.id AND net = 'CI' AND seedchan LIKE '_H_' AND flags LIKE 'T%' ORDER BY samprate DESC"); / Declaring cursor / EXEC SQL PREPARE sql_stmt FROM :SQL_Command; EXEC SQL DECLARE db_cursor10 CURSOR FOR sql_stmt; / Opening cursor / EXEC SQL OPEN db_cursor10; / fetch instances from the NCEDC database */ EXEC SQL FETCH db_cursor10 INTO :seedchan, :samprate, :instrument; while (sqlca.sqlcode != NOMOREROWS) { Remove_Blank (seedchan); Remove_Blank (instrument); fprintf (f2, "\n
%s?	%f	%s", seedchan, samprate, instrument); EXEC SQL FETCH db_cursor10 INTO :seedchan, :samprate, :instrument; } /* Close the cursor */ EXEC SQL CLOSE db_cursor10; fprintf (f2, "\n

"); /* Outputing footer information */ fprintf (f2, "\n\n\n"); /* Disconnect from the database. */ EXEC SQL COMMIT WORK RELEASE; /* Closing output files */ fclose (f1); fclose (f2); exit(0); } /* Handle errors. Exit on any error. */ void sql_error() { char msg[512]; size_t buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; buf_len = sizeof(msg); sqlglm(msg, &buf_len, &msg_len); printf("\nORACLE error detected:"); printf("\n%.*s \n", msg_len, msg); EXEC SQL ROLLBACK WORK RELEASE; exit(1); }