dbtable.cpp

来自「A*算法 A*算法 A*算法 A*算法A*算法A*算法」· C++ 代码 · 共 1,992 行 · 第 1/5 页

    if (hstmtDefault)
        DeleteCursor(hstmtDefault);

    if (hstmtCount)
        DeleteCursor(hstmtCount);

    if (m_hstmtGridQuery)
        DeleteCursor(m_hstmtGridQuery);

}  // wxDbTable::cleanup()


/***************************** PRIVATE FUNCTIONS *****************************/


void wxDbTable::setCbValueForColumn(int columnIndex)
{
    switch(colDefs[columnIndex].DbDataType)
    {
        case DB_DATA_TYPE_VARCHAR:
            if (colDefs[columnIndex].Null)
                colDefs[columnIndex].CbValue = SQL_NULL_DATA;
            else
                colDefs[columnIndex].CbValue = SQL_NTS;
            break;
        case DB_DATA_TYPE_INTEGER:
            if (colDefs[columnIndex].Null)
                colDefs[columnIndex].CbValue = SQL_NULL_DATA;
            else
                colDefs[columnIndex].CbValue = 0;
            break;
        case DB_DATA_TYPE_FLOAT:
            if (colDefs[columnIndex].Null)
                colDefs[columnIndex].CbValue = SQL_NULL_DATA;
            else
                colDefs[columnIndex].CbValue = 0;
            break;
        case DB_DATA_TYPE_DATE:
            if (colDefs[columnIndex].Null)
                colDefs[columnIndex].CbValue = SQL_NULL_DATA;
            else
                colDefs[columnIndex].CbValue = 0;
            break;
        case DB_DATA_TYPE_BLOB:
            if (colDefs[columnIndex].Null)
                colDefs[columnIndex].CbValue = SQL_NULL_DATA;
            else
                if (colDefs[columnIndex].SqlCtype == SQL_C_WXCHAR)
                    colDefs[columnIndex].CbValue = SQL_NTS;
                else
                    colDefs[columnIndex].CbValue = SQL_LEN_DATA_AT_EXEC(colDefs[columnIndex].SzDataObj);
            break;
    }
}

/********** wxDbTable::bindParams() **********/
bool wxDbTable::bindParams(bool forUpdate)
{
    wxASSERT(!queryOnly);
    if (queryOnly)
        return false;

    SWORD   fSqlType    = 0;
    SDWORD  precision   = 0;
    SWORD   scale       = 0;

    // Bind each column of the table that should be bound
    // to a parameter marker
    int i;
    UWORD colNumber;

    for (i=0, colNumber=1; i < m_numCols; i++)
    {
        if (forUpdate)
        {
            if (!colDefs[i].Updateable)
                continue;
        }
        else
        {
            if (!colDefs[i].InsertAllowed)
                continue;
        }

        switch(colDefs[i].DbDataType)
        {
            case DB_DATA_TYPE_VARCHAR:
                fSqlType = pDb->GetTypeInfVarchar().FsqlType;
                precision = colDefs[i].SzDataObj;
                scale = 0;
                break;
            case DB_DATA_TYPE_INTEGER:
                fSqlType = pDb->GetTypeInfInteger().FsqlType;
                precision = pDb->GetTypeInfInteger().Precision;
                scale = 0;
                break;
            case DB_DATA_TYPE_FLOAT:
                fSqlType = pDb->GetTypeInfFloat().FsqlType;
                precision = pDb->GetTypeInfFloat().Precision;
                scale = pDb->GetTypeInfFloat().MaximumScale;
                // SQL Sybase Anywhere v5.5 returned a negative number for the
                // MaxScale.  This caused ODBC to kick out an error on ibscale.
                // I check for this here and set the scale = precision.
                //if (scale < 0)
                // scale = (short) precision;
                break;
            case DB_DATA_TYPE_DATE:
                fSqlType = pDb->GetTypeInfDate().FsqlType;
                precision = pDb->GetTypeInfDate().Precision;
                scale = 0;
                break;
            case DB_DATA_TYPE_BLOB:
                fSqlType = pDb->GetTypeInfBlob().FsqlType;
                precision = colDefs[i].SzDataObj;
                scale = 0;
                break;
        }

        setCbValueForColumn(i);

        if (forUpdate)
        {
            if (SQLBindParameter(hstmtUpdate, colNumber++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
                                 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
                                 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
            {
                return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));
            }
        }
        else
        {
            if (SQLBindParameter(hstmtInsert, colNumber++, SQL_PARAM_INPUT, colDefs[i].SqlCtype,
                                 fSqlType, precision, scale, (UCHAR*) colDefs[i].PtrDataObj,
                                 precision+1, &colDefs[i].CbValue) != SQL_SUCCESS)
            {
                return(pDb->DispAllErrors(henv, hdbc, hstmtInsert));
            }
        }
    }

    // Completed successfully
    return true;

}  // wxDbTable::bindParams()


/********** wxDbTable::bindInsertParams() **********/
bool wxDbTable::bindInsertParams(void)
{
    return bindParams(false);
}  // wxDbTable::bindInsertParams()


/********** wxDbTable::bindUpdateParams() **********/
bool wxDbTable::bindUpdateParams(void)
{
    return bindParams(true);
}  // wxDbTable::bindUpdateParams()


/********** wxDbTable::bindCols() **********/
bool wxDbTable::bindCols(HSTMT cursor)
{
    static SQLLEN cb;

    // Bind each column of the table to a memory address for fetching data
    UWORD i;
    for (i = 0; i < m_numCols; i++)
    {
        cb = colDefs[i].CbValue;
        if (SQLBindCol(cursor, (UWORD)(i+1), colDefs[i].SqlCtype, (UCHAR*) colDefs[i].PtrDataObj,
                       colDefs[i].SzDataObj, &cb ) != SQL_SUCCESS)
          return (pDb->DispAllErrors(henv, hdbc, cursor));
    }

    // Completed successfully
    return true;

}  // wxDbTable::bindCols()


/********** wxDbTable::getRec() **********/
bool wxDbTable::getRec(UWORD fetchType)
{
    RETCODE retcode;

    if (!pDb->FwdOnlyCursors())
    {
        // Fetch the NEXT, PREV, FIRST or LAST record, depending on fetchType
        SQLULEN cRowsFetched;
        UWORD   rowStatus;

        retcode = SQLExtendedFetch(hstmt, fetchType, 0, &cRowsFetched, &rowStatus);
        if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
        {
            if (retcode == SQL_NO_DATA_FOUND)
                return false;
            else
                return(pDb->DispAllErrors(henv, hdbc, hstmt));
        }
        else
        {
            // Set the Null member variable to indicate the Null state
            // of each column just read in.
            int i;
            for (i = 0; i < m_numCols; i++)
                colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
        }
    }
    else
    {
        // Fetch the next record from the record set
        retcode = SQLFetch(hstmt);
        if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
        {
            if (retcode == SQL_NO_DATA_FOUND)
                return false;
            else
                return(pDb->DispAllErrors(henv, hdbc, hstmt));
        }
        else
        {
            // Set the Null member variable to indicate the Null state
            // of each column just read in.
            int i;
            for (i = 0; i < m_numCols; i++)
                colDefs[i].Null = (colDefs[i].CbValue == SQL_NULL_DATA);
        }
    }

    // Completed successfully
    return true;

}  // wxDbTable::getRec()


/********** wxDbTable::execDelete() **********/
bool wxDbTable::execDelete(const wxString &pSqlStmt)
{
    RETCODE retcode;

    // Execute the DELETE statement
    retcode = SQLExecDirect(hstmtDelete, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);

    if (retcode == SQL_SUCCESS ||
        retcode == SQL_NO_DATA_FOUND ||
        retcode == SQL_SUCCESS_WITH_INFO)
    {
        // Record deleted successfully
        return true;
    }

    // Problem deleting record
    return(pDb->DispAllErrors(henv, hdbc, hstmtDelete));

}  // wxDbTable::execDelete()


/********** wxDbTable::execUpdate() **********/
bool wxDbTable::execUpdate(const wxString &pSqlStmt)
{
    RETCODE retcode;

    // Execute the UPDATE statement
    retcode = SQLExecDirect(hstmtUpdate, (SQLTCHAR FAR *) pSqlStmt.c_str(), SQL_NTS);

    if (retcode == SQL_SUCCESS ||
        retcode == SQL_NO_DATA_FOUND ||
        retcode == SQL_SUCCESS_WITH_INFO)
    {
        // Record updated successfully
        return true;
    }
    else if (retcode == SQL_NEED_DATA)
    {
        PTR pParmID;
        retcode = SQLParamData(hstmtUpdate, &pParmID);
        while (retcode == SQL_NEED_DATA)
        {
            // Find the parameter
            int i;
            for (i=0; i < m_numCols; i++)
            {
                if (colDefs[i].PtrDataObj == pParmID)
                {
                    // We found it.  Store the parameter.
                    retcode = SQLPutData(hstmtUpdate, pParmID, colDefs[i].SzDataObj);
                    if (retcode != SQL_SUCCESS)
                    {
                        pDb->DispNextError();
                        return pDb->DispAllErrors(henv, hdbc, hstmtUpdate);
                    }
                    break;
                }
            }
            retcode = SQLParamData(hstmtUpdate, &pParmID);
        }
        if (retcode == SQL_SUCCESS ||
            retcode == SQL_NO_DATA_FOUND ||
            retcode == SQL_SUCCESS_WITH_INFO)
        {
            // Record updated successfully
            return true;
        }
    }

    // Problem updating record
    return(pDb->DispAllErrors(henv, hdbc, hstmtUpdate));

}  // wxDbTable::execUpdate()


/********** wxDbTable::query() **********/
bool wxDbTable::query(int queryType, bool forUpdate, bool distinct, const wxString &pSqlStmt)
{
    wxString sqlStmt;

    if (forUpdate)
        // The user may wish to select for update, but the DBMS may not be capable
        selectForUpdate = CanSelectForUpdate();
    else
        selectForUpdate = false;

    // Set the SQL SELECT string
    if (queryType != DB_SELECT_STATEMENT)               // A select statement was not passed in,
    {                                                   // so generate a select statement.
        BuildSelectStmt(sqlStmt, queryType, distinct);
        pDb->WriteSqlLog(sqlStmt);
    }

    // Make sure the cursor is closed first
    if (!CloseCursor(hstmt))
        return false;

    // Execute the SQL SELECT statement
    int retcode;
    retcode = SQLExecDirect(hstmt, (SQLTCHAR FAR *) (queryType == DB_SELECT_STATEMENT ? pSqlStmt.c_str() : sqlStmt.c_str()), SQL_NTS);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
        return(pDb->DispAllErrors(henv, hdbc, hstmt));

    // Completed successfully
    return true;

}  // wxDbTable::query()


/***************************** PUBLIC FUNCTIONS *****************************/


/********** wxDbTable::Open() **********/
bool wxDbTable::Open(bool checkPrivileges, bool checkTableExists)
{
    if (!pDb)
        return false;

    int i;
    wxString sqlStmt;
    wxString s;

    // Calculate the maximum size of the concatenated
    // keys for use with wxDbGrid
    m_keysize = 0;
    for (i=0; i < m_numCols; i++)
    {
        if (colDefs[i].KeyField)
        {
            m_keysize += colDefs[i].SzDataObj;
        }
    }

    s.Empty();

    bool exists = true;
    if (checkTableExists)
    {
        if (pDb->Dbms() == dbmsPOSTGRES)
            exists = pDb->TableExists(tableName, NULL, tablePath);
        else
            exists = pDb->TableExists(tableName, pDb->GetUsername(), tablePath);
    }

    // Verify that the table exists in the database
    if (!exists)
    {
        s = wxT("Table/view does not exist in the database");
        if ( *(pDb->dbInf.accessibleTables) == wxT('Y'))
            s += wxT(", or you have no permissions.\n");
        else
            s += wxT(".\n");
    }
    else if (checkPrivileges)
    {
        // Verify the user has rights to access the table.
        bool hasPrivs wxDUMMY_INITIALIZE(true);

        if (pDb->Dbms() == dbmsPOSTGRES)
            hasPrivs = pDb->TablePrivileges(tableName, wxT("SELECT"), pDb->GetUsername(), NULL, tablePath);
        else
            hasPrivs = pDb->TablePrivileges(tableName, wxT("SELECT"), pDb->GetUsername(), pDb->GetUsername(), tablePath);