src/QOpenOCCIResult.cpp

Go to the documentation of this file.

/****************************************************************************
**
** Copyright (C) 2008 Andrew White. All rights reserved.
**
** This file is part of the QOpenOCCI library.
**
** This file may be used under the terms of the GNU General Public
** License version 2.0 as published by the Free Software Foundation
** and appearing in the file LICENSE.GPL included in the 
** packaging of this file.
**
** This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
** WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
**
****************************************************************************/

#include "QOpenOCCI.h"
#include <QString>

const char* QOpenOCCIResult::EXEC_FAILED             = "QOpenOCCI: Execute Failed";
const char* QOpenOCCIResult::BIND_FAILED             = "QOpenOCCI: Bind Failed";

template<class C>
QOpenOCCIBindElementI<C>::QOpenOCCIBindElementI(C* data) : mData(data) {};
    
template<class C>
QOpenOCCIBindElementI<C>::~QOpenOCCIBindElementI() {};

template<class C>
void QOpenOCCIBindElementI<C>::free() { delete mData; };

template<>
void QOpenOCCIBindElementI<OCI_Date>::free() { OCI_DateFree(mData); };

template<>
void QOpenOCCIBindElementI<OCI_Lob>::free() { OCI_LobFree(mData); };

template<class C>
void* QOpenOCCIBindElementI<C>::ptrValue() { return (void*) mData; };
    
template<class C>
QVariant QOpenOCCIBindElementI<C>::toQVariant() const { return QVariant(*mData); };

template<>
QVariant QOpenOCCIBindElementI<OCI_Date>::toQVariant() const 
{ 
    int year, month, day, hour, min, sec;
    OCI_DateGetDate(mData, &year, &month, &day);
    OCI_DateGetTime(mData, &hour, &min, &sec);

    return QVariant(QDateTime(QDate(year, month, day), QTime(hour, min, sec))); 
} 

template<>
QVariant QOpenOCCIBindElementI<OCI_Lob>::toQVariant() const 
{ 
    QByteArray ret;
    ret.resize(OCI_LobGetLength(mData));
    OCI_LobRead(mData, ret.data(), ret.length());
    return QVariant(ret); 
} 

template<>
QVariant QOpenOCCIBindElementI<char>::toQVariant() const 
{ 
    return QVariant(QString(mData)); 
}

    

QOpenOCCIMetadata::QOpenOCCIMetadata(OCI_Resultset* resultSet)
{
    for (int i = 1; i <= OCI_GetColumnCount(resultSet); i++) {
        OCI_Column* col = OCI_GetColumn(resultSet, i);
        
        ColumnMetadata cmd;
        cmd.name = OCI_GetColumnName(col);
        cmd.type = OCI_GetColumnSQLType(col);
        cmd.typeCode = OCI_GetColumnType(col);
        cmd.size = OCI_GetColumnSize(col);
        cmd.precision = OCI_GetColumnPrecision(col);
        cmd.scale = OCI_GetColumnScale(col);
        cmd.nullable = OCI_GetColumnNullable(col);
        
        mColMetadata.append(cmd);
    }
}

QOpenOCCIResult::QOpenOCCIResult(
    const QSqlDriver* d, OCI_Connection* c,  
    unsigned int prefetch_memory, unsigned int prefetch_rows) : 
    
    QSqlResult( d ),
    mConnection( c ),
    mStatement( OCI_CreateStatement(c) ),
    mResultSet( NULL )
{
    OCI_SetPrefetchSize(mStatement, prefetch_rows);
    OCI_SetPrefetchMemory(mStatement, prefetch_memory);
}

QOpenOCCIResult::~QOpenOCCIResult()
{
    try {
        cleanup();
        if (mConnection && mStatement) 
            OCI_FreeStatement(mStatement);
    }
    catch(...) {
    }
    
}

void QOpenOCCIResult::rebind(int bound_index, const QVariant& val, QSql::ParamType btype)
{
    if (mBindNameIndex.size()) 
        QSqlResult::bindValue(boundValueName(bound_index),val, btype);
    else 
        QSqlResult::bindValue(bound_index, val, btype);
}

void QOpenOCCIResult::updateBoundResults()
{
    for (int i = 0; i < boundValueCount(); i++) {
        if (bindValueType(i) == QSql::In || bindValueType(i) == QSql::InOut) {
            QString key;
            if (bindingSyntax() == QSqlResult::PositionalBinding) {      
                key = QString(':').append(QString::number(i+1));
                bindValue(i, mBindData[key]->toQVariant(), bindValueType(i));
            }
            else { 
                key = boundValueName(i);
                bindValue(key, mBindData[key]->toQVariant(), bindValueType(i));
            }
        }
    }
}

bool QOpenOCCIResult::exec()
{
    try {
        setSelect(FALSE);
        setActive(FALSE);
        
        if (boundValueCount()) {
            if (bindingSyntax() == QSqlResult::PositionalBinding)
                OCI_SetBindMode(mStatement, OCI_BIND_BY_POS);
            else 
                OCI_SetBindMode(mStatement, OCI_BIND_BY_NAME);
        }
        
        for (int i = 0; i < boundValueCount(); i++) {
            if (boundValueCount() && bindingSyntax() == QSqlResult::PositionalBinding)
                bind(QString(':').append(QString::number(i+1)), boundValue(i), bindValueType(i));
            else {
                QString key = boundValueName(i);
                bind(key, boundValue(key), bindValueType(key));
            }
        }
        
        OCI_Execute(mStatement);
 
        updateBoundResults();
        
        switch( OCI_GetStatementType(mStatement) ) 
        {
            case OCI_CST_SELECT: 
            case OCI_CST_BEGIN:
            case OCI_CST_DECLARE:
                mResultSet = OCI_GetResultset(mStatement);
                mResultMetadata = QOpenOCCIMetadata(mResultSet);
                setSelect(TRUE);
                setActive(TRUE);
                return TRUE;
            case OCI_CST_UPDATE: 
            case OCI_CST_DELETE: 
            case OCI_CST_INSERT:
                setSelect(FALSE);
                setActive(FALSE);            
                return TRUE;
            case OCI_CST_DROP:    
            case OCI_CST_CREATE:  
            case OCI_CST_ALTER:
                setSelect(FALSE);
                setActive(FALSE);            
                return TRUE;
            default:
                throw QOPEN_OCCI_EXCEPTION("Statement failed");
        }
    }
    catch (exception& e) {
        setLastError(QSqlError(EXEC_FAILED, e.what(), QSqlError::StatementError));
        return FALSE;
    }
    return FALSE;
}

bool QOpenOCCIResult::reset ( const QString& query )
{
    OCI_Prepare(mStatement, (char*) query.toStdString().c_str());
    
    cleanup();
   
    return exec();
}

int QOpenOCCIResult::numRowsAffected()
{
    if (isActive() == TRUE || isSelect() == FALSE) return -1;
    return OCI_GetAffectedRows(mStatement);
}

void QOpenOCCIResult::setQuery ( const QString & query )
{
    OCI_Prepare(mStatement, (char*) query.toStdString().c_str());
    cleanup();
}

void QOpenOCCIResult::cleanup() 
{
     mResultSet = NULL; //freed by the setSQL statment
     mCache.clear();
     mBindNameIndex.clear();
     boundValues().clear();
     QSqlResult::clear();
     mBindData.cleanup();
}

bool QOpenOCCIResult::prepare ( const QString& query )
{
    cleanup();
    return OCI_Prepare(mStatement, (char*) query.toStdString().c_str());
}

bool QOpenOCCIResult::savePrepare ( const QString & query )
{
    return prepare(query);
}


void QOpenOCCIResult::bind(QString name, const QVariant& val, QSql::ParamType paramType )
{
    QOpenOCCIBindElement* bindElement;
        
    try {
        switch(val.type()) {
            case QVariant::Char:
            case QVariant::LongLong:
            case QVariant::String:
            case QVariant::ULongLong:
            case QVariant::UInt:
                if (paramType == QSql::Out)
                    OCI_RegisterString(mStatement, name.toAscii().data(), 4000);
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<char>( 
                                      QOpenOCCIBindElement::QStringToChar(val.toString()) ); 
                    mBindData.add(name, bindElement);
                    OCI_BindString( mStatement, name.toAscii().data(), 
                                    static_cast<char*>(bindElement->ptrValue()), 
                                    (val.isNull()) ? 4000 : val.toString().size() );
                }
                break;
            case QVariant::Date:
                if (paramType == QSql::Out)
                    OCI_RegisterDate(mStatement, name.toAscii().data());
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<OCI_Date>( 
                                      QOpenOCCIBindElement::QDateToOCIDate(mConnection, 
                                                                           val.toDate()) ); 
                    mBindData.add(name, bindElement);
                    OCI_BindDate( mStatement, name.toAscii().data(), 
                                    static_cast<OCI_Date*>(bindElement->ptrValue()) );
                }
                break;
            case QVariant::DateTime:
                if (paramType == QSql::Out)
                    OCI_RegisterDate(mStatement, name.toAscii().data());
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<OCI_Date>( 
                                      QOpenOCCIBindElement::QDateToOCIDateTime(mConnection, 
                                                                               val.toDateTime()) ); 
                    mBindData.add(name, bindElement);
                    OCI_BindDate( mStatement, name.toAscii().data(), 
                                    static_cast<OCI_Date*>(bindElement->ptrValue()) );
                }
                break;
            case QVariant::Time:
                if (paramType == QSql::Out)
                    OCI_RegisterDate(mStatement, name.toAscii().data());
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<OCI_Date>( 
                                      QOpenOCCIBindElement::QDateToOCITime(mConnection, 
                                                                           val.toTime()) ); 
                    mBindData.add(name, bindElement);
                    OCI_BindDate( mStatement, name.toAscii().data(), 
                                    static_cast<OCI_Date*>(bindElement->ptrValue()) );
                }
                break;        
            case QVariant::Double:
                if (paramType == QSql::Out)
                    OCI_RegisterInt(mStatement, name.toAscii().data());
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<double>( new double(val.toDouble()) ); 
                    
                    mBindData.add(name, bindElement);
                    OCI_BindDouble(mStatement, name.toAscii().data(), 
                                   static_cast<double*>(bindElement->ptrValue()));
                }
                break;
            case QVariant::Int:
                if (paramType == QSql::Out)
                    OCI_RegisterInt(mStatement, name.toAscii().data());
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<int>( new int(val.toInt()) ); 
                    
                    mBindData.add(name, bindElement);
                    OCI_BindInt(mStatement, name.toAscii().data(), 
                                static_cast<int*>(bindElement->ptrValue()));
                }
                break;
            case QVariant::ByteArray:
                if (paramType == QSql::Out)
                    OCI_RegisterLob(mStatement, name.toAscii().data(), OCI_BLOB);
                if (paramType == QSql::In || paramType == QSql::InOut) {
                    bindElement = new QOpenOCCIBindElementI<OCI_Lob>( 
                                      QOpenOCCIBindElement::QByteArrayToBLOB( mConnection,
                                                                              val.toByteArray() ));
                    mBindData.add(name, bindElement);
                    OCI_BindLob(mStatement, name.toAscii().data(), 
                                static_cast<OCI_Lob*>(bindElement->ptrValue()));
                }
                break;
            default:
                throw QOPEN_OCCI_EXCEPTION("Can not handle this variable type yet!");
        }
    }
    catch (exception& e) {
        setLastError(QSqlError(BIND_FAILED, e.what(), QSqlError::StatementError));
    }
}

bool QOpenOCCIResult::fetch( int row_id )
{
    if (at() == row_id) return true;
    
    //Optimize this (tie in cache if possible)!!
    if (isForwardOnly()) {
        if (at() < row_id) {
            // fake a forward seek
            for (int x = row_id - at() - 1; x && fetchNext(); x--);
            return fetchNext();
        } 
        else return false;
    }
    
    if (at() < row_id) {
        // fake a forward seek
        for (int x = row_id - at() - 1; x && fetchNext(); x--);
        return fetchNext();    
    }
    if (at() > row_id) {
        // fake a forward seek
        for (int x = at() - row_id - 1; x && fetchPrevious(); x--);
        return fetchPrevious();     
    }
    
    return FALSE;
}

QSqlRecord QOpenOCCIResult::record() const
{ 
    QSqlRecord ret;
    const QVector<QOpenOCCIMetadata::ColumnMetadata>& colMD = mResultMetadata.mColMetadata;
    
    for (int c = 0; c < colMD.size(); c++) {
        QVariant::Type datatype = ocilib_to_qvariant_type(colMD[c].typeCode);
        QString        name       (colMD[c].name);
        QSqlField      field      (name, datatype);
        
        field.setLength(colMD[c].size);
        field.setPrecision(colMD[c].precision);
        
        ret.append(field);
    }
    return ret;
}

bool QOpenOCCIResult::fetchFirst() 
{
    if (at() == 0) return true;

    if (isForwardOnly())
        return (at() == QSql::BeforeFirstRow) ? fetchNext() : false;
    return fetch(0);
}

bool QOpenOCCIResult::fetchNext()
{
    if (mCache.isRowCached(at()+1)) {
        setAt(at()+1);
        return TRUE;
    }
    
    if (!OCI_FetchNext(mResultSet)) return FALSE;
    
    addRowToCache();
    setAt(at()+1);
    return TRUE;
}

bool QOpenOCCIResult::fetchPrevious() {
    if (at() == 0 || at() == QSql::BeforeFirstRow) return false;
    
    if (at() == QSql::AfterLastRow) {
        setAt(mCache.size() - 1);
        return TRUE;
    }
    
    setAt(at()-1);
    return TRUE;    
}

bool QOpenOCCIResult::fetchLast() {
    if (mCache.size() == 0) return FALSE;
    
    setAt(mCache.size() - 1);
    return TRUE; 
}

bool QOpenOCCIResult::isNull(int column_id) 
{
    return (isForwardOnly()) ? mCache.getLastRow()[column_id].isNull()
                             : mCache.getRow(at())[column_id].isNull();
}

void QOpenOCCIResult::addRowToCache()
{
    if (isForwardOnly()) mCache.clear();
    
    QVector<QVariant>& row = mCache.addRow();

    QVector<QOpenOCCIMetadata::ColumnMetadata>& colMD = mResultMetadata.mColMetadata;

    //String vars
    string strData;
    
    //Date/timestamp vars
    int year = 0, mon = 0, day = 0, hour = 0, min = 0, sec = 0, fs = 0;
  
    //Lob vars
    OCI_Lob*       lobPtr  = NULL;
    OCI_Date*      datePtr = NULL;
    OCI_Timestamp* tsPtr   = NULL;
    
    QByteArray lobData;
        
    for (int c = 1; c <= colMD.size(); c++) {
        
        int typeCode = colMD[c-1].typeCode;
        
        if (OCI_IsNull(mResultSet, c)) {
            row.append(ocilib_to_qvariant_type(typeCode));
            continue;
        }
        
        switch (typeCode)
        {
            case OCI_CDT_TEXT:
                row.append(QString(OCI_GetString(mResultSet, c)));
                break;
            case OCI_CDT_INTEGER:
                row.append(OCI_GetInt(mResultSet, c)); 
                break;
            case OCI_CDT_DOUBLE:
                row.append(OCI_GetDouble(mResultSet, c)); 
                break;                
            case OCI_CDT_LOB:
                lobPtr = OCI_GetLob(mResultSet, c);
                lobData.resize(OCI_LobGetLength(lobPtr));
                OCI_LobRead(lobPtr, lobData.data(), lobData.size());
                row.append(lobData); 
                break;
            case OCI_CDT_DATETIME:
                datePtr = OCI_GetDate(mResultSet, c);
                OCI_DateGetDate(datePtr, &year, &mon, &day);
                OCI_DateGetTime(datePtr, &hour, &min, &sec);
                row.append(QDateTime(QDate(year, mon, day), QTime(hour, min, sec)));
                break;
            case OCI_CDT_TIMESTAMP:
                tsPtr = OCI_GetTimestamp(mResultSet, c);
                OCI_TimestampGetDate(tsPtr, &year, &mon, &day);
                OCI_TimestampGetTime(tsPtr, &hour, &min, &sec, &fs);
                row.append(QDateTime(QDate(year, mon, day), QTime(hour, min, sec, fs)));
                break;
            default:
                row.append( QString(OCI_GetString(mResultSet, c)) );
        }
    }
}

QVariant QOpenOCCIResult::data(int column_id)
{
    if (column_id  >= mResultMetadata.mColMetadata.size() ||
        isActive() == FALSE) {

        return QVariant();
    }
    
    return (isForwardOnly()) ? mCache.getLastRow()[column_id]
                             : mCache.getRow(at())[column_id];
}

QVariant::Type QOpenOCCIResult::ocilib_to_qvariant_type(int ocilib_type)
{
    
    switch (ocilib_type)
    {
        case OCI_CDT_TEXT:
            return QVariant::String;
        case OCI_CDT_INTEGER:
            return QVariant::Int;
        case OCI_CDT_DOUBLE:
            return QVariant::Double;
        case OCI_CDT_DATETIME:
        case OCI_CDT_TIMESTAMP:
            return QVariant::DateTime;
        case OCI_CDT_LOB:
            return QVariant::ByteArray;
        default: 
            return QVariant::Invalid;
    }    
}

---