Re: SQL emergency question
This might help. With a little modification, you might be able to use
this.
Good luck.
#include <stdio.h>
#include <sys/types.h>
$include sqlca;
FILE *fopen(), *out;
int pageno = 0, line_cnt = 0;
char tdatestr[11];
main()
{
int rec_cnt = 0, inv_cnt = 0;
long tdate;
$char ck_tabname[19];
$char ck_colname[19];
$string sel_str1[200];
$int cnt_inv_dates;
if ((out = fopen("ck_dates.prt", "w")) == 0)
{
fprintf(stderr,
"\\n\\n COULD NOT OPEN ck_dates.prt FOR WRITING\\n");
exit(1);
}
$ DATABASE edbase;
checkerr("CANNOT OPEN DATABASE edbase ");
rtoday(&tdate);
rfmtdate(tdate, "mm/dd/yyyy", tdatestr);
/* Get all date columns from Database */
$ DECLARE ckdt_cur CURSOR FOR
SELECT colname, tabname
INTO $ck_colname, $ck_tabname
FROM systables, syscolumns
WHERE systables.tabid = syscolumns.tabid
AND syscolumns.coltype = 7
AND systables.tabname[1,3] != "sys"
ORDER BY tabname, colname;
checkerr("CANNOT DECLARE ckdt_cur CURSOR");
$OPEN ckdt_cur;
checkerr("CANNOT OPEN ckdt_cur CURSOR");
prt_hdg();
for (; ;)
{
$FETCH ckdt_cur;
if (sqlca.sqlcode == SQLNOTFOUND)
break;
checkerr("CANNOT FETCH ckdt_cur CURSOR");
sprintf(sel_str1,"%s %s %s %s %s %s %s %s",
"SELECT count(*) FROM ", ck_tabname,
"WHERE", ck_colname, "< \\"01/01/1899\\" ",
"OR", ck_colname, " > \\"12/31/1999\\" ");
$PREPARE p_sel_str1 FROM $sel_str1;
checkerr("CANNOT PREPARE p_sel_str1");
$DECLARE ck_cur CURSOR for p_sel_str1;
checkerr("CANNOT DECLARE ck_cur");
$OPEN ck_cur;
checkerr("CANNOT OPEN ck_cur");
$FETCH ck_cur INTO $cnt_inv_dates;
checkerr("CANNOT FETCH ck_cur");
if (cnt_inv_dates > 0)
{
inv_cnt++;
fprintf(out, "\\n %s %s %5d\\n",
ck_tabname, ck_colname, cnt_inv_dates);
line_cnt = line_cnt + 2;
fflush(out);
if (line_cnt > 58)
prt_hdg();
}
rec_cnt++;
fprintf(stdout, "\\n tabname: %s colname: %s rec_cnt: %5d",
ck_tabname, ck_colname, rec_cnt);
fflush(stdout);
} /* end of for */
$CLOSE ckdt_cur;
checkerr("CANNOT CLOSE ckdt_cur CURSOR");
fprintf(out, "\\n\\n # INVALID DATE COLUMNS FOUND: %d, # REVIEWED: %
d\\n",
inv_cnt, rec_cnt);
fclose(out);
exit(0);
} /* end of main function */
prt_hdg()
{
pageno++;
fprintf(out, "\\f");
fprintf(out,
"\\n\\n\\n DATE: %s
PAGE: %3d",
tdatestr, pageno);
fprintf(out, "\\n\\n INVALID DATE LISTING");
fprintf(out, "\\n ____________________");
fprintf(out, "\\n\\n");
fprintf(out," TABLE COLUMN # INVALID
ROWS \\n");
fprintf(out," _____ ______
______________ \\n");
line_cnt = 10;
fflush(out);
}
checkerr(msg)
char *msg;
{
if (sqlca.sqlcode)
{
fprintf(stderr, "\\n\\n %s -- %d\\n", msg, sqlca.sqlcode);
sleep(2);
exit(sqlca.sqlcode);
}
}
In article
<6DD3B31B22B4E515.8D5CD89D94643219.1DF47C906F115278@lp.airnews.net>,
-=Eclypse=- <eclypse@cdc.net> wrote:
> This past weekend we upgraded one of our RS/6000s and in the process
the NVRAM on
> the box got corrupted. The date got changed to 7/14/2019 and went
unnoticed until
> Monday morning. We run BaaN and Informix on the backend. Several
transactions
> were processed before we caught the date error. We have approximately
80000 tables
> in our database and several of those have columns of type "DATE".
>
> What I need help with is the wording on an SQL statement to find any
table with a
> column type of "DATE" with the value in that column equal to
"07/14/2019" and
> change it to "06/21/1999" anywhere in the database. Any help is
appreciated!
>
> --
>
> Jon Freeland<*>
> Informix DBA
> Miller Industries, Inc.
> jon@millerind.com
> eclypse@babylon5.cdc.net
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.