Beispiel #1
0
bool BlockTableExists(OWConnection* connection, std::string tableName)
{
    std::ostringstream oss;

    char szTable[OWNAME]= "";
    oss << "select table_name from user_tables where table_name like upper('%%"<< tableName <<"%%') ";

    OWStatement* statement = 0;
    
    statement = connection->CreateStatement(oss.str().c_str());
    statement->Define(szTable);
    
    try {
        statement->Execute();
    } catch (std::runtime_error const& ) {
        // Assume for now that an error returned here is OCI_NODATA, which means 
        // the table doesn't exist.  If this really isn't the case, we're going 
        // to get more legit message further down the line.

        return false;
        // delete statement;
        // std::ostringstream oss;
        // oss << "Failed select if block table "<< tableName << " exists.  Do you have rights to select?"  
        //     << std::endl << e.what() << std::endl;
        // throw std::runtime_error(oss.str());
    }  
    
    return true;
        
}
Beispiel #2
0
OWStatement* RunSQL(OWConnection* connection, std::ostringstream& command) 
{
    OWStatement* statement = 0;
    statement = connection->CreateStatement(command.str().c_str());
    
    try {
        statement->Execute();
    } catch (std::runtime_error const& e) {
        delete statement;
        std::ostringstream oss;
        oss << "Failed to run SQL:" << command.str() << std::endl << e.what() << std::endl;
        throw std::runtime_error(oss.str());
    }      
    return statement;    
}
Beispiel #3
0
bool IsGeographic(OWConnection* connection, long srid) 
{

    std::ostringstream oss;
    char* kind = (char* ) malloc (OWNAME * sizeof(char));
    oss << "SELECT COORD_REF_SYS_KIND from MDSYS.SDO_COORD_REF_SYSTEM WHERE SRID = :1";
    
    OWStatement* statement = 0;

    statement = connection->CreateStatement(oss.str().c_str());
    long* p_srid = (long*) malloc( 1 * sizeof(long));
    p_srid[0] = srid;
    
    statement->Bind(p_srid);
    statement->Define(kind);    
    
    try {
        statement->Execute();
    } catch (std::runtime_error const& e) {
        delete statement;
        std::ostringstream oss;
        oss << "Failed to fetch geographicness of srid " << srid << std::endl << e.what() << std::endl;
        throw std::runtime_error(oss.str());
    }  
    
    if (compare_no_case(kind, "GEOGRAPHIC2D",12) == 0) {
        delete statement;
        free(kind);
        free(p_srid);
        return true;
    }
    if (compare_no_case(kind, "GEOGRAPHIC3D",12) == 0) {
        delete statement;
        free(kind);
        free(p_srid);
        return true;
    }

    free(kind);
    free(p_srid);

    return false;
}
Beispiel #4
0
bool EnableTracing(OWConnection* connection)
{
    std::ostringstream oss;
// http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php
    oss << "ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'";

    OWStatement* statement = 0;
    
    statement = connection->CreateStatement(oss.str().c_str());
    
    try {
        statement->Execute();
    } catch (std::runtime_error const& e) {
        delete statement;
        std::ostringstream oss;
        oss << "Failed to Enable tracing "  << std::endl << e.what() << std::endl;
        throw std::runtime_error(oss.str());
    }    
    
    return true;
}
const GDALRasterAttributeTable *GeoRasterRasterBand::GetDefaultRAT()
{
    if( poDefaultRAT )
    {
        return poDefaultRAT->Clone();
    }
    else
    {
        poDefaultRAT = new GDALRasterAttributeTable();
    }

    GeoRasterDataset* poGDS = (GeoRasterDataset*) poDS;

    // ----------------------------------------------------------
    // Get the name of the VAT Table
    // ----------------------------------------------------------

    char* pszVATName = poGDS->poGeoRaster->GetVAT( nBand );

    if( pszVATName == NULL )
    {
        return NULL;
    }

    OCIParam* phDesc = NULL;

    phDesc = poGDS->poGeoRaster->poConnection->GetDescription( pszVATName );

    if( phDesc == NULL )
    {
        return NULL;
    }

    // ----------------------------------------------------------
    // Create the RAT and the SELECT statemet based on fields description
    // ----------------------------------------------------------

    int   iCol = 0;
    char  szField[OWNAME];
    int   hType = 0;
    int   nSize = 0;
    int   nPrecision = 0;
    signed short nScale = 0;

    char szColumnList[OWTEXT];
    szColumnList[0] = '\0';

    while( poGDS->poGeoRaster->poConnection->GetNextField(
                phDesc, iCol, szField, &hType, &nSize, &nPrecision, &nScale ) )
    {
        switch( hType )
        {
            case SQLT_FLT:
                poDefaultRAT->CreateColumn( szField, GFT_Real, GFU_Generic );
                break;
            case SQLT_NUM:
                if( nPrecision == 0 )
                {
                    poDefaultRAT->CreateColumn( szField, GFT_Integer,
                        GFU_Generic );
                }
                else
                {
                    poDefaultRAT->CreateColumn( szField, GFT_Real,
                        GFU_Generic );
                }
                break;
            case SQLT_CHR:
            case SQLT_AFC:
            case SQLT_DAT:
            case SQLT_DATE:
            case SQLT_TIMESTAMP:
            case SQLT_TIMESTAMP_TZ:
            case SQLT_TIMESTAMP_LTZ:
            case SQLT_TIME:
            case SQLT_TIME_TZ:
                    poDefaultRAT->CreateColumn( szField, GFT_String, 
                        GFU_Generic );
                break;
            default:
                CPLDebug("GEORASTER", "VAT (%s) Column (%s) type (%d) not supported"
                    "as GDAL RAT", pszVATName, szField, hType );
                continue;
        }
        strcpy( szColumnList, CPLSPrintf( "%s substr(%s,1,%d),",
            szColumnList, szField, MIN(nSize,OWNAME) ) );

        iCol++;
    }

    szColumnList[strlen(szColumnList) - 1] = '\0'; // remove the last comma

    // ----------------------------------------------------------
    // Read VAT and load RAT
    // ----------------------------------------------------------

    OWStatement* poStmt = NULL;

    poStmt = poGeoRaster->poConnection->CreateStatement( CPLSPrintf (
        "SELECT %s FROM %s", szColumnList, pszVATName ) );

    char** papszValue = (char**) CPLMalloc( sizeof(char**) * iCol );

    int i = 0;

    for( i = 0; i < iCol; i++ )
    {
        papszValue[i] = (char*) CPLMalloc( sizeof(char*) * OWNAME );
        poStmt->Define( papszValue[i] );
    }

    if( ! poStmt->Execute() )
    {
        CPLError( CE_Failure, CPLE_AppDefined, "Error reading VAT %s",
            pszVATName );
        return NULL;
    }

    int iRow = 0;

    while( poStmt->Fetch() )
    {
        for( i = 0; i < iCol; i++ )
        {
           poDefaultRAT->SetValue( iRow, i, papszValue[i] );
        }
        iRow++;
    }

    for( i = 0; i < iCol; i++ )
    {
        CPLFree( papszValue[i] );
    }
    CPLFree( papszValue );

    delete poStmt;

    CPLFree( pszVATName );

    return poDefaultRAT;
}
CPLErr GeoRasterRasterBand::SetDefaultRAT( const GDALRasterAttributeTable *poRAT )
{
    GeoRasterDataset* poGDS = (GeoRasterDataset*) poDS;

    if( ! poRAT )
    {
        return CE_Failure;
    }

    if( poDefaultRAT )
    {
        delete poDefaultRAT;
    }

    poDefaultRAT = poRAT->Clone();

    // ----------------------------------------------------------
    // Check if RAT is just colortable and/or histogram
    // ----------------------------------------------------------

    CPLString sColName = "";
    int  iCol = 0;
    int  nColCount = poRAT->GetColumnCount();

    for( iCol = 0; iCol < poRAT->GetColumnCount(); iCol++ )
    {
        sColName = poRAT->GetNameOfCol( iCol );

        if( EQUAL( sColName, "histogram" ) ||
            EQUAL( sColName, "red" ) ||
            EQUAL( sColName, "green" ) ||
            EQUAL( sColName, "blue" ) ||
            EQUAL( sColName, "opacity" ) )
        {
            nColCount--;
        }
    }

    if( nColCount < 2 )
    {
        return CE_None;
    }

    // ----------------------------------------------------------
    // Format Table description
    // ----------------------------------------------------------

    char szName[OWTEXT];
    char szDescription[OWTEXT];

    strcpy( szDescription, "( ID NUMBER" );

    for( iCol = 0; iCol < poRAT->GetColumnCount(); iCol++ )
    {
        strcpy( szName, poRAT->GetNameOfCol( iCol ) );

        strcpy( szDescription, CPLSPrintf( "%s, %s",
            szDescription, szName ) );

        if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
        {
            strcpy( szDescription, CPLSPrintf( "%s NUMBER",
                szDescription ) );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
        {
            strcpy( szDescription, CPLSPrintf( "%s FLOAT",
                szDescription ) );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
        {
            strcpy( szDescription, CPLSPrintf( "%s VARCHAR2(%d)",
                szDescription, MAXLEN_VATSTR) );
        }
    }
    strcpy( szDescription, CPLSPrintf( "%s )", szDescription ) );

    // ----------------------------------------------------------
    // Create VAT named based on RDT and RID and Layer (nBand)
    // ----------------------------------------------------------

    if( ! pszVATName )
    {
        pszVATName = CPLStrdup( CPLSPrintf(
            "RAT_%s_%d_%d", 
            poGeoRaster->sDataTable.c_str(),
            poGeoRaster->nRasterId,
            nBand ) );
    }

    // ----------------------------------------------------------
    // Create VAT table
    // ----------------------------------------------------------

    OWStatement* poStmt = poGeoRaster->poConnection->CreateStatement( CPLSPrintf(
        "DECLARE\n"
        "  TAB VARCHAR2(68)  := UPPER(:1);\n"
        "  CNT NUMBER        := 0;\n"
        "BEGIN\n"
        "  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES\n"
        "    WHERE TABLE_NAME = :1' INTO CNT USING TAB;\n"
        "\n"
        "  IF NOT CNT = 0 THEN\n"
        "    EXECUTE IMMEDIATE 'DROP TABLE '||TAB||' PURGE';\n"
        "  END IF;\n"
        "\n"
        "  EXECUTE IMMEDIATE 'CREATE TABLE '||TAB||' %s';\n"
        "END;", szDescription ) );

    poStmt->Bind( pszVATName );

    if( ! poStmt->Execute() )
    {
        delete poStmt;
        CPLError( CE_Failure, CPLE_AppDefined, "Create VAT Table Error!" );
        return CE_Failure;
    }

    delete poStmt;

    // ----------------------------------------------------------
    // Insert Data to VAT
    // ----------------------------------------------------------

    int iEntry       = 0;
    int nEntryCount  = poRAT->GetRowCount();
    int nColunsCount = poRAT->GetColumnCount();
    int nVATStrSize  = MAXLEN_VATSTR * poGeoRaster->poConnection->GetCharSize();

    // ---------------------------
    // Allocate array of buffers
    // ---------------------------

    void** papWriteFields = (void**) VSIMalloc2(sizeof(void*), nColunsCount + 1);

    papWriteFields[0] = 
        (void*) VSIMalloc3(sizeof(int), sizeof(int), nEntryCount ); // ID field

    for(iCol = 0; iCol < nColunsCount; iCol++)
    {
        if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
        {
            papWriteFields[iCol + 1] =
                (void*) VSIMalloc3(sizeof(char), nVATStrSize, nEntryCount );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
        {
            papWriteFields[iCol + 1] =
                (void*) VSIMalloc3(sizeof(int), sizeof(int), nEntryCount );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
        {
            papWriteFields[iCol + 1] =
                 (void*) VSIMalloc3(sizeof(double), sizeof(double), nEntryCount );
        }
    }
    
    // ---------------------------
    // Load data to buffers
    // ---------------------------

    for( iEntry = 0; iEntry < nEntryCount; iEntry++ )
    {
        ((int *)(papWriteFields[0]))[iEntry] = iEntry; // ID field

        for(iCol = 0; iCol < nColunsCount; iCol++)
        {
            if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
            {

                int nOffset = iEntry * nVATStrSize;
                char* pszTarget = ((char*)papWriteFields[iCol + 1]) + nOffset;
                const char *pszStrValue = poRAT->GetValueAsString(iEntry, iCol);
                int nLen = strlen( pszStrValue );
                nLen = nLen > ( nVATStrSize - 1 ) ? nVATStrSize : ( nVATStrSize - 1 );
                strncpy( pszTarget, pszStrValue, nLen );
                pszTarget[nLen] = '\0';
            }
            if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
            {
                ((int *)(papWriteFields[iCol + 1]))[iEntry] =
                    poRAT->GetValueAsInt(iEntry, iCol);
            }
            if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
            {
                ((double *)(papWriteFields[iCol]))[iEntry + 1] =
                    poRAT->GetValueAsDouble(iEntry, iCol);
            }
        }
    }

    // ---------------------------
    // Prepare insert statement
    // ---------------------------

    CPLString osInsert = CPLSPrintf( "INSERT INTO %s VALUES (", pszVATName );
    
    for( iCol = 0; iCol < ( nColunsCount + 1); iCol++ )
    {
        if( iCol > 0 )
        {
            osInsert.append(", ");
        }
        osInsert.append( CPLSPrintf(":%d", iCol + 1) );
    }
    osInsert.append(")");

    poStmt = poGeoRaster->poConnection->CreateStatement( osInsert.c_str() );

    // ---------------------------
    // Bind buffers to columns
    // ---------------------------

    poStmt->Bind((int*) papWriteFields[0]); // ID field
    
    for(iCol = 0; iCol < nColunsCount; iCol++)
    {
        if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
        {
            poStmt->Bind( (char*) papWriteFields[iCol + 1], nVATStrSize );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
        {
            poStmt->Bind( (int*) papWriteFields[iCol + 1]);
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
        {
            poStmt->Bind( (double*) papWriteFields[iCol + 1]);
        }
    }

    if( poStmt->Execute( iEntry ) )
    {
        poGDS->poGeoRaster->SetVAT( nBand, pszVATName );
    }
    else
    {
        CPLError( CE_Failure, CPLE_AppDefined, "Insert VAT Error!" );
    }

    // ---------------------------
    // Clean up
    // ---------------------------

    for(iCol = 0; iCol < ( nColunsCount + 1); iCol++)
    {
        CPLFree( papWriteFields[iCol] );
    }
    
    CPLFree( papWriteFields );

    delete poStmt;

    return CE_None;
}
CPLErr GeoRasterRasterBand::SetDefaultRAT( const GDALRasterAttributeTable *poRAT )
{
    GeoRasterDataset* poGDS = (GeoRasterDataset*) poDS;

    if( ! poRAT )
    {
        return CE_Failure;
    }

    if( poDefaultRAT )
    {
        delete poDefaultRAT;
    }

    poDefaultRAT = poRAT->Clone();

    // ----------------------------------------------------------
    // Format Table description
    // ----------------------------------------------------------

    char szName[OWTEXT];
    char szDescription[OWTEXT];
    int  iCol = 0;

    strcpy( szDescription, "( ID NUMBER" );

    for( iCol = 0; iCol < poRAT->GetColumnCount(); iCol++ )
    {
        strcpy( szName, poRAT->GetNameOfCol( iCol ) );

    if( EQUAL( szName, "histogram" ) )
    {
        return CE_None;
    }

        strcpy( szDescription, CPLSPrintf( "%s, %s",
            szDescription, szName ) );

        if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer )
        {
            strcpy( szDescription, CPLSPrintf( "%s NUMBER",
                szDescription ) );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_Real )
        {
            strcpy( szDescription, CPLSPrintf( "%s FLOAT",
                szDescription ) );
        }
        if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
        {
            strcpy( szDescription, CPLSPrintf( "%s VARCHAR2(128)",
                szDescription ) );
        }
    }
    strcpy( szDescription, CPLSPrintf( "%s )", szDescription ) );

    // ----------------------------------------------------------
    // Create VAT named based on RDT and RID and Layer (nBand)
    // ----------------------------------------------------------

    if( ! pszVATName )
    {
        pszVATName = CPLStrdup( CPLSPrintf(
            "RAT_%s_%d_%d", 
            poGeoRaster->pszDataTable, 
            poGeoRaster->nRasterId,
            nBand ) );
    }

    // ----------------------------------------------------------
    // Create VAT table
    // ----------------------------------------------------------

    OWStatement* poStmt = poGeoRaster->poConnection->CreateStatement( CPLSPrintf(
        "DECLARE\n"
        "  TAB VARCHAR2(68)  := UPPER(:1);\n"
        "  CNT NUMBER        := 0;\n"
        "BEGIN\n"
        "  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES\n"
        "    WHERE TABLE_NAME = :1' INTO CNT USING TAB;\n"
        "\n"
        "  IF NOT CNT = 0 THEN\n"
        "    EXECUTE IMMEDIATE 'DROP TABLE '||TAB||' PURGE';\n"
        "  END IF;\n"
        "\n"
        "  EXECUTE IMMEDIATE 'CREATE TABLE '||TAB||' %s';\n"
        "END;", szDescription ) );

    poStmt->Bind( pszVATName );

    if( ! poStmt->Execute() )
    {
        delete poStmt;
        CPLError( CE_Failure, CPLE_AppDefined, "Create VAT Table Error!" );
        return CE_Failure;
    }

    delete poStmt;

    // ----------------------------------------------------------
    // Insert Data to VAT
    // ----------------------------------------------------------

    int iEntry       = 0;
    int nEntryCount  = poRAT->GetRowCount();
    int nColunsCount = poRAT->GetColumnCount();

    char szInsert[OWTEXT];

    CPLString osInserts = 
        "DECLARE\n"
        "  GR1  SDO_GEORASTER   := NULL;\n"
        "BEGIN\n";

    for( iEntry = 0; iEntry < nEntryCount; iEntry++ )
    {
        szInsert[0] = '\0';

        strcat( szInsert, CPLSPrintf ( "  INSERT INTO %s VALUES (%d", 
            pszVATName, iEntry ) );

        for( iCol = 0; iCol < nColunsCount; iCol++ )
        {
            if( poRAT->GetTypeOfCol( iCol ) == GFT_String )
            {
                strcat( szInsert, CPLSPrintf ( ", '%s'", 
                    poRAT->GetValueAsString( iEntry, iCol ) ) );
            }
            if( poRAT->GetTypeOfCol( iCol ) == GFT_Integer ||
                poRAT->GetTypeOfCol( iCol ) == GFT_Real )
            {
                strcat( szInsert, CPLSPrintf ( ", %s", 
                    poRAT->GetValueAsString( iEntry, iCol ) ) );
            }
        }

        strcat( szInsert, ");\n" );
        osInserts += szInsert;
    }

    osInserts += CPLSPrintf(
        "  SELECT %s INTO GR1 FROM %s T WHERE\n"
        "    T.%s.RasterDataTable = '%s' AND\n"
        "    T.%s.RasterId = %d FOR UPDATE;\n"
        "  SDO_GEOR.setVAT(GR1, %d, '%s');\n"
        "  UPDATE %s T SET %s = GR1 WHERE\n"
        "    T.%s.RasterDataTable = '%s' AND\n"
        "    T.%s.RasterId = %d;\n"
        "END;",
        poGeoRaster->pszColumn, poGeoRaster->pszTable,
        poGeoRaster->pszColumn, poGeoRaster->pszDataTable,
        poGeoRaster->pszColumn, poGeoRaster->nRasterId,
        nBand, pszVATName,
        poGeoRaster->pszTable,  poGeoRaster->pszColumn,
        poGeoRaster->pszColumn, poGeoRaster->pszDataTable,
        poGeoRaster->pszColumn, poGeoRaster->nRasterId  );

    poStmt = poGeoRaster->poConnection->CreateStatement( osInserts.c_str() );

    if( ! poStmt->Execute() )
    {
        CPLError( CE_Failure, CPLE_AppDefined, "Insert/registering VAT Error!" );
        return CE_Failure;
    }

    delete poStmt;

    poGDS->poGeoRaster->SetVAT( nBand, pszVATName );

    return CE_None;
}