fix_date.c

解压后请将扩展名改为.C,实现了在CVI里的数据库操作

/* This sample program shows how to update the values in a record. */

#define HELP_MSG \
"The purpose of this program is to fix the date columns stored in the \n\
Test Suite (Test Executive) generated SQL files so that tests and \n\
sequence results from different years can be sorted correctly by the \n\
SPCVIEWER program.  Currently the dates stored in the Test Suite SQL \n\
data sources have the following format, MM/DD/YYYY.  This program \n\
will change all the data fields to the sortable format of YYYY/MM/DD.\n\
\n\
A simple change can be made to the current TestSuite, version 1.0,\n\
software to correctly store the data in the YYY/MM/DD format..."

#include "cvi_db.h"
#include <formatio.h>
#include <ansi_c.h>
#include <userint.h>
#include "pick_src.h"

#ifndef TRUE
#define TRUE    1
#endif

#ifndef FALSE
#define FALSE   0
#endif
#define DSN_PRELUDE "DSN="
#define PROMPT_USR_PWD ";DLG=2"

static void ShowError(void);
static char buffer[256];

void main()
{
    int hdbc = 0;       /* Handle to database connection    */
    int hmap = 0;       /* Handle to map                    */
    int hstmt = 0;      /* Handle to SQL statement          */
    char uutNum[50];    /* Buffer for uut serial number     */
    char runDate[50];   /* Buffer for date                  */
    int uutStat;        /* Status variable for uut number   */
    int runDateStat;    /* Status variable for run date     */
    int resCode;        /* Result code                      */
    int response;       /* User response code               */
    char *sourceName = NULL; /* data source name            */
    char *dsn = NULL;   /* data source name selection string*/
    char *dbName = NULL; /* separate database if supported  */
    char *tableName = NULL; /* individual table name        */
    int year, month, day;
    int width;
    
    /* Confirm that the user wants to continue with program */
    response = GenericMessagePopup ("SQL Textexec Fix Date Program",
                                HELP_MSG, "Continue", "Cancel", NULL,
                                NULL, 0, 0, VAL_GENERIC_POPUP_BTN1,
                                VAL_GENERIC_POPUP_BTN1,
                                VAL_GENERIC_POPUP_BTN2);
    if (response!=1) 
        goto Error;
        
    while (TRUE) {
        sourceName = PickSourceName();
        if (sourceName == NULL)
            goto Cancel; /* user cancelled */
		dsn = malloc(strlen(DSN_PRELUDE) + strlen(sourceName) + strlen (PROMPT_USR_PWD)+ 1);
        strcpy(dsn,DSN_PRELUDE);
        strcat(dsn,sourceName);
		strcat(dsn, PROMPT_USR_PWD); 
        free(sourceName);
        sourceName = NULL;
        hdbc = DBConnect (dsn);
        if (hdbc == 0) {ShowError(); goto Error;}
        dbName = PickDatabaseName(hdbc);
        if (dbName == NULL)
            goto Cancel; /* user cancelled */
        if (dbName[0] != '\0') {
            resCode = DBSetDatabase(hdbc, dbName);
            if (resCode < 0) {ShowError(); goto Error;}
        }

        resCode = DBAllowFetchAnyDirection (hdbc, 1);
        if (resCode < 0) {ShowError(); goto Error;}

        tableName = PickOneTableName(hdbc);
        if (tableName == NULL)
            goto Cancel;
    
        hmap = DBBeginMap (hdbc);
        if (hmap <= 0) {ShowError(); goto Error;}

        resCode = DBMapColumnToChar (hmap, "RUN_DATE", 11, runDate, 
                &runDateStat,"");
        if (resCode != DB_SUCCESS) {ShowError(); goto Error;}

        hstmt = DBActivateMap (hmap, tableName);
        if (hstmt == 0) {
            sprintf(buffer, "The following error occurred connecting to the selected table:\n    %s\n\n  Please verify that this is a valid Test Executive generated table.", DBErrorMessage());
            MessagePopup ("Database Error", buffer);
            goto Cancel;
        }

        width = DBColumnWidth(hstmt, 1);
        while ((resCode = DBFetchNext (hstmt)) == DB_SUCCESS) {
            Scan(runDate, "%s>%d[x]%d[x]%d[x]", &month, &day, &year);
            if (year < 1000) {
                sprintf(buffer, "The date %s already appears to be corrected.", runDate);
                response = GenericMessagePopup ("Test Suite(Test Executive) Date Fixup",
                    buffer, "Quit", "Skip this record", "Fix it anyway", 0, 0, 0,
                    VAL_GENERIC_POPUP_BTN1, VAL_GENERIC_POPUP_BTN1, VAL_GENERIC_POPUP_BTN2);
                switch (response) {
                    case VAL_GENERIC_POPUP_BTN1:
                        resCode = DBDeactivateMap (hmap);
                        if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
                        resCode = DBDisconnect (hdbc);
                        if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
                        goto Cancel;
                        break;
                    case VAL_GENERIC_POPUP_BTN2:
                        continue;
                    case VAL_GENERIC_POPUP_BTN3:
                        break;
                }
            }
            /* Parse data and rebuild to ensure formatDate format */
            Fmt(runDate,"%s<%d[w4p0]/%d[w2p0]/%d[w2p0]",year,month,day);
            resCode = DBPutRecord (hstmt);
            if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
        }
        if (resCode != DB_SUCCESS && resCode != DB_EOF) 
            {ShowError(); goto Error;}

        resCode = DBDeactivateMap (hmap);
        if (resCode != DB_SUCCESS) {ShowError(); goto Error;}

        resCode = DBDisconnect (hdbc);
        if (resCode != DB_SUCCESS) {ShowError(); goto Error;}
Cancel:
        free(dsn);
        dsn = NULL;
        free(dbName);
        dbName = NULL;
        free(tableName);
        tableName = NULL;
        if (hstmt != 0) {
            resCode = DBDeactivateSQL(hstmt);
            if (resCode < 0) {ShowError(); goto Error;}
            hstmt = 0;
        }
        if (hdbc != 0) {
            resCode = DBDisconnect (hdbc);
            if (resCode < 0) {ShowError(); hdbc = 0; goto Error;}
            hdbc = 0;
        }
        response = ConfirmPopup ("Test Executive Date Fixup",
                                 "Fix dates in another table?");
        if (response == 0)
            break;
    }
    return;
Error:
    return;
}

static void ShowError()
{
    MessagePopup("Database Error",DBErrorMessage());
}