示例#1
0
void SQLTests::non_query ()
{
    if (CreateSchemaOnly())  return;

    try
    {
        FdoPtr<FdoISQLCommand> sqlCmd = (FdoISQLCommand*)mConnection->CreateCommand (FdoCommandType_SQLCommand);
        sqlCmd->SetSQLStatement (L"create table foo (name varchar(32))");
        sqlCmd->ExecuteNonQuery ();
        sqlCmd->SetSQLStatement (L"drop table foo");
        sqlCmd->ExecuteNonQuery ();
    }
    catch (FdoException *e)
    {
        fail(e);
    }
}
示例#2
0
void SQLTests::query ()
{
    if (CreateSchemaOnly())  return;

    try
    {
        FdoPtr<FdoISQLCommand> sqlCmd = (FdoISQLCommand*)mConnection->CreateCommand (FdoCommandType_SQLCommand);

        // Clean up previous tests (ignore exceptions, we'll get one if the table doesn't actually exist):
        try
        {
            sqlCmd->SetSQLStatement (L"drop table bar");
            sqlCmd->ExecuteNonQuery ();
        }
        catch (FdoException *e)
        {
            e->Release();
        }

        // Execute various tests:
        sqlCmd->SetSQLStatement (ArcSDETestConfig::SqlStmtCreateTable());
        sqlCmd->ExecuteNonQuery ();
        sqlCmd->SetSQLStatement (ArcSDETestConfig::SqlStmtInsert1());
        sqlCmd->ExecuteNonQuery ();
        sqlCmd->SetSQLStatement (L"select * from bar");
        {
            FdoPtr<FdoISQLDataReader> reader = sqlCmd->ExecuteReader ();

            // Test accessing SQLDataReader's metadata BEFORE calling ReadNext():
            IterateSQLDataReaderProperties(reader);

            reader->ReadNext ();
            CPPUNIT_ASSERT_MESSAGE ("int16 wrong", 42 == reader->GetInt16 (L"ID"));
            CPPUNIT_ASSERT_MESSAGE ("int32 wrong", 8272772 == reader->GetInt32 (L"COUNT"));
            float x = (float)reader->GetSingle (L"SCALE");
            float diff = x - 1e-2f;
            if (0 > diff)
                diff = -diff;
            CPPUNIT_ASSERT_MESSAGE ("float wrong", 1e-9 > diff);
            double y = reader->GetDouble (L"LENGTH");
            double difference = y - 10280288.29929;
            if (0 > difference)
                difference = -difference;
            CPPUNIT_ASSERT_MESSAGE ("double wrong", 1e-4 > difference);
            CPPUNIT_ASSERT_MESSAGE ("string wrong", 0 == wcscmp (L"the quick brown fox jumps over a lazy dog", reader->GetString(L"DESCRIPTION")));

            FdoPtr<FdoBLOBValue> blobValue = static_cast<FdoBLOBValue*>(reader->GetLOB(L"DATA"));
            FdoByteArray* data = blobValue->GetData();
            FdoByte test[] = { 0x25, 0x2f, 0x82, 0xe3 };
            for (int i = 0; i < data->GetCount (); i++)
                CPPUNIT_ASSERT_MESSAGE ("blob wrong", test[i] == (*data)[i]);
            data->Release ();

            FdoDateTime now = reader->GetDateTime (L"MODIFIED");
            struct tm systime;
#ifdef _WIN32
#pragma warning(disable : 4996)
            _getsystime (&systime);
#pragma warning(default : 4996)
#else
            time_t current;
            time (&current);
            localtime_r (&current, &systime);
#endif
            CPPUNIT_ASSERT_MESSAGE ("year wrong (NOTE: THIS IS MAY BE CAUSED BY TIME ZONE DIFFERENCE BETWEEN SERVER AND CLIENT MACHINES)", now.year == systime.tm_year + 1900);
            CPPUNIT_ASSERT_MESSAGE ("month wrong (NOTE: THIS IS MAY BE CAUSED BY TIME ZONE DIFFERENCE BETWEEN SERVER AND CLIENT MACHINES)", now.month == systime.tm_mon + 1);
            CPPUNIT_ASSERT_MESSAGE ("day wrong (NOTE: THIS IS OFTEN CAUSED BY TIME ZONE DIFFERENCE BETWEEN SERVER AND CLIENT MACHINES)", now.day == systime.tm_mday);
            //THE FOLLOWING TEST FAILS FREQUENTLY, SINCE THE CLIENT'S TIME AND SERVER'S TIME ARE USUALLY NOT IN SYNC:
            //CPPUNIT_ASSERT_MESSAGE ("hour wrong", now.hour == systime.tm_hour);
        }

        // Test aggregate functions in select clause:
        sqlCmd->SetSQLStatement (ArcSDETestConfig::SqlStmtInsert2());
        sqlCmd->ExecuteNonQuery ();
        sqlCmd->SetSQLStatement (L"select count(*) from bar");
        FdoPtr<FdoISQLDataReader> reader = sqlCmd->ExecuteReader();
        CPPUNIT_ASSERT_MESSAGE("Expected 1 row, got 0 rows instead.", reader->ReadNext ());
        FdoInt32 colCount = reader->GetColumnCount();
        CPPUNIT_ASSERT_MESSAGE ("column count wrong", colCount == 1);
        FdoString *colName = reader->GetColumnName(0);
        CPPUNIT_ASSERT_MESSAGE ("column name wrong", 0==FdoCommonOSUtil::wcsicmp(colName, ArcSDETestConfig::SqlCountStarColumnName()));
        FdoDataType colType = reader->GetColumnType(colName);
        if (colType==FdoDataType_Int32)
        {
            FdoInt32 iRowCount = reader->GetInt32(colName);
            CPPUNIT_ASSERT_MESSAGE("count(*) value wrong", iRowCount == 2);
        }
        else if (colType==FdoDataType_Double)
        {
            double dRowCount = reader->GetDouble(colName);
            CPPUNIT_ASSERT_MESSAGE("count(*) value wrong", dRowCount == 2);
        }
        CPPUNIT_ASSERT_MESSAGE("Expected 1 row, got 2 or more rows instead.", !reader->ReadNext ());

        // Test functions in where clause:
        sqlCmd->SetSQLStatement (ArcSDETestConfig::SqlStmtAggrQuery1());
        reader = sqlCmd->ExecuteReader();
        CPPUNIT_ASSERT_MESSAGE("Expected 1 row, got 0 rows instead.", reader->ReadNext ());
        colCount = reader->GetColumnCount();
        CPPUNIT_ASSERT_MESSAGE ("column count wrong", colCount == 1);
        colName = reader->GetColumnName(0);
        CPPUNIT_ASSERT_MESSAGE ("column name wrong", 0==FdoCommonOSUtil::wcsicmp(colName, ArcSDETestConfig::SqlAggrColumnName()));
        colType = reader->GetColumnType(colName);
        CPPUNIT_ASSERT_MESSAGE ("column type wrong", colType==FdoDataType_Double);
        double dResult = reader->GetDouble(colName);
        CPPUNIT_ASSERT_MESSAGE("ABS(SUM(length)) value wrong", ArcSDETests::fuzzyEqual(dResult, ArcSDETestConfig::SqlStmtAggrQuery1Result()));
        CPPUNIT_ASSERT_MESSAGE("Expected 1 row, got 2 or more rows instead.", !reader->ReadNext ());


        // Clean up:
        sqlCmd->SetSQLStatement (L"drop table bar");
        sqlCmd->ExecuteNonQuery ();
    }
    catch (FdoException *e)
    {
        fail(e);
    }
}
/// <summary>Executes the release lock command, returning an FdoILockConflictReader.</summary>
/// <returns>Returns an FdoILockConflictReader</returns> 
FdoILockConflictReader* ArcSDEReleaseLockCommand::Execute ()
{
    const CHAR* columns[1];
    FdoPtr<ArcSDEConnection> connection;
    CHAR table[SE_QUALIFIED_TABLE_NAME];
    CHAR column[SE_MAX_COLUMN_LEN];
    wchar_t* wcolumn;
    CHAR *where;
    SHORT count;
    SE_FILTER* filters;
    FdoString* property;
    LONG result;
    SE_STREAM stream;
    CHAR user_name[SE_MAX_OWNER_LEN];
    wchar_t* me;
    CHAR logfile[SE_MAX_PATH_LEN];
    SE_SQL_CONSTRUCT  sql_construct;
    CHAR* tables[1];
    FdoPtr<FdoISQLDataReader> reader;
    SE_LOG log;
    LONG number = 0;
    Lock* locks = NULL;
    wchar_t* locktable = NULL;
    FdoPtr<ArcSDELockConflictReader> ret;

    // verify the connection
    connection = static_cast<ArcSDEConnection*>(GetConnection ());
    if (connection == NULL)
        throw FdoException::Create (NlsMsgGet (ARCSDE_CONNECTION_NOT_ESTABLISHED, "Connection not established."));

    // verify the feature class name is specified
    if (mClassName == NULL)
        throw FdoException::Create (NlsMsgGet (ARCSDE_FEATURE_CLASS_UNSPECIFIED, "Feature class name not specified."));

    // get the class definition which reflects the requested feature class name
    FdoPtr<FdoClassDefinition> definition = connection->GetRequestedClassDefinition (mClassName);

    // get the filter if any
    FdoPtr<FdoFilter> filter = GetFilter ();

    // get SQL query's "from" table list
    mConnection->ClassToTable (table, definition);

    // ensure lockable table
    if (!ArcSDELockUtility::IsLockable (connection->GetConnection (), table, column))
    {
        wchar_t* wtable;
        sde_multibyte_to_wide (wtable, table);
        throw FdoException::Create (NlsMsgGet1 (ARCSDE_LOCKING_NOT_ENABLED, "Table '%1$ls' is not lock enabled.", wtable));
    }

    // get the property name that is the row_id
    sde_multibyte_to_wide (wcolumn, column);
    property = connection->ColumnToProperty (definition, wcolumn);

    // get SQL query's "where" clause & spatial filters
    where = NULL;
    count = 0;
    filters = NULL;
    GetFilterInfo (connection, filter, definition, where, count, filters);

    // establish an empty conflict reader
    ret = new ArcSDELockConflictReader (connection, definition->GetQualifiedName (), table, property);

    // initialize the stream query
    result = SE_stream_create (connection->GetConnection (), &stream);
    handle_sde_err<FdoCommandException> (connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_STREAM_ALLOC, "Cannot initialize SE_STREAM structure.");

    // if necessary, version enable the stream
    ArcSDELongTransactionUtility::VersionStream (connection, stream, table, false);

    // release lock, don't return rows
    result = SE_connection_get_user_name (connection->GetConnection (), user_name);
    handle_sde_err<FdoCommandException> (connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_USER_UNKNOWN, "Cannot determine current user.");
    sde_multibyte_to_wide (me, user_name);
    if (0 == wcscmp (GetLockOwner (), L""))
    {
        result = SE_stream_set_rowlocking (stream, SE_ROWLOCKING_UNLOCK_ON_QUERY
        | SE_ROWLOCKING_LOCK_ONLY);
        handle_sde_err<FdoCommandException> (connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_STREAM_LOCK, "Cannot set row locking on the stream.");
    }
    else if (0 == wcscmp (GetLockOwner (), me))
    {
        result = SE_stream_set_rowlocking (stream, SE_ROWLOCKING_UNLOCK_ON_QUERY
        | SE_ROWLOCKING_FILTER_MY_LOCKS | SE_ROWLOCKING_LOCK_ONLY);
        handle_sde_err<FdoCommandException> (connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_STREAM_LOCK, "Cannot set row locking on the stream.");
    }
    else
    {
        LONG *ids = NULL;
        CHAR **users = NULL;
        CHAR lt[SE_QUALIFIED_TABLE_NAME];

        if (0)
            throw FdoCommandException::Create (NlsMsgGet (ARCSDE_RELEASE_UNOWNED_LOCKS, "Releasing other owners locks is not supported."));

        result = SE_table_get_rowlocks (connection->GetConnection(), table, &number, &ids, &users);
        handle_sde_err<FdoCommandException>(connection->GetConnection(), result, __FILE__, __LINE__, ARCSDE_GET_ROW_LOCK_LIST_FAILED, "Failed to get the row lock list.");
        if (0 != number)
        {
            // put the id's and users in an array of lock structures
            locks = (Lock*)calloc (number, sizeof (Lock));
            for (int i = 0; i < number; i++)
            {
                locks[i].id = ids[i];
                sde_strcpy (sde_pus2wc(locks[i].user), sde_pcus2wc(users[i]));
            }
            qsort (locks, number, sizeof (LONG), compare);
            ArcSDELockUtility::LockTableName (lt, connection, table);
            sde_multibyte_to_wide (locktable, lt);
            SE_table_free_rowlocks_list (number, ids, users);
        }
    }

    // apply attribute and spatial query to stream
    columns[0] = column;
    ApplyFilterInfoToStream (connection, stream, table, where, 1, columns, count, filters);

    // set up a temporary log file
    mConnection->MakeLog (&log, table);

    // accumulate the query in the log file
    result = SE_stream_set_logfile (stream, log, FALSE);
    handle_sde_err<FdoCommandException> (stream, result, __FILE__, __LINE__, ARCSDE_LOG_SET_LOGFILE, "Could not set log file.");

    // lock the table's lock table to prevent alteration
    reader = ArcSDELockUtility::LockLockTable (mConnection, table);

    // actually execute the query
    result = SE_stream_execute (stream);
    handle_sde_err<FdoCommandException>(stream, result, __FILE__, __LINE__, ARCSDE_STREAM_EXECUTE, "Stream execute failed.");

    result = SE_stream_fetch (stream);
    // three possibilities: locks for the specified user exist (SE_SUCCESS) and the log file isn't filled,
    // everything was unlocked (SE_FINISHED)
    // or there was a conflict (SE_LOCK_CONFLICT)
    switch (result)
    {
        case SE_SUCCESS:
            if (0 != number)
            {
                CHAR* user;
                LONG id;
                wchar_t drop[1024];
                FdoPtr<FdoISQLCommand> sql;
                Lock key;
                Lock* item;

		// Get lock owner name:
		const wchar_t *wLockOwner = GetLockOwner();
		wchar_t *wLockOwnerUpr = (wchar_t*)alloca( (1+wcslen(wLockOwner)) * sizeof(wchar_t));
		wcscpy(wLockOwnerUpr, wLockOwner);
		FdoCommonOSUtil::wcsupr(wLockOwnerUpr);  // ToDo: Oracle-specific
                sde_wide_to_multibyte (user, wLockOwnerUpr);

                // process each row returned (ignoring the log file)
                sql = (FdoISQLCommand*)connection->CreateCommand (FdoCommandType_SQLCommand);
                do
                {
                    if (SE_SUCCESS != (result = SE_stream_get_integer (stream, 1, &id)))
                    {
                        sde_multibyte_to_wide (wcolumn, column);
                        handle_sde_err<FdoCommandException> (stream, result, __FILE__, __LINE__, ARCSDE_STREAM_GET, "Stream get ('%1$ls') failed for column '%2$ls'.", L"SE_stream_get_integer", wcolumn);
                    }
                    else
                    {
                        key.id = id;
                        // look it up to see if it's a conflict (i.e. not found)
                        if (NULL != (item = (Lock*)bsearch (&key, locks, number, sizeof (LONG), compare)))
                        {
                            if (0 == sde_strcmp (sde_pcus2wc(user), sde_pcus2wc(item->user)))
                            {
                                // ToDo: optimize this singleton delete somewhat
                                //ROW_ID                                    NOT NULL NUMBER(38)
                                //USER_NAME                                 NOT NULL VARCHAR2(32)
                                FdoCommonOSUtil::swprintf (drop, ELEMENTS (drop), L"delete from %ls where user_name=upper('%ls') and row_id = %ld", locktable, GetLockOwner (), id);
                                sql->SetSQLStatement (drop);
                                sql->ExecuteNonQuery ();
                            }
                            else
                                ret->AddIdentity (id);
                        }
                        else
                        {
                            // no lock, hence no conflict
                        }
                    }
                }
                while (SE_SUCCESS == (result = SE_stream_fetch (stream)));
                if (SE_FINISHED != result)
                    handle_sde_err<FdoCommandException> (stream, result, __FILE__, __LINE__, ARCSDE_STREAM_FETCH, "Stream fetch failed.");
            }
            break;
        case SE_FINISHED:
            break;
        case SE_LOCK_CONFLICT:
            // reuse the same stream
            result = SE_stream_close (stream, TRUE);

            // if necessary, version enable the stream
            ArcSDELongTransactionUtility::VersionStream (connection, stream, table, false);

            // select locks still remaining
            result = SE_stream_set_rowlocking (stream, SE_ROWLOCKING_FILTER_OTHER_LOCKS);
            handle_sde_err<FdoCommandException> (connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_STREAM_LOCK, "Cannot set row locking on the stream.");

            // get the list of row ids from the log file
            tables[0] = table;
            sql_construct.tables = tables;
            sql_construct.num_tables = ELEMENTS (tables);
            sql_construct.where = NULL;
            ArcSDELockUtility::GetLogFile (logfile, connection->GetConnection (), log);
            result = SE_stream_query_logfile (stream, logfile, 1, columns, &sql_construct);
            handle_sde_err<FdoCommandException>(stream, result, __FILE__, __LINE__, ARCSDE_LOG_FILE_QUERY, "Unable to query log file.");

            // execute the query that fetches conflicts
            result = SE_stream_execute (stream);
            handle_sde_err<FdoCommandException>(stream, result, __FILE__, __LINE__, ARCSDE_STREAM_EXECUTE, "Stream execute failed.");

            // gather the conflicts
            ArcSDELockUtility::GatherConflicts (stream, column, 1, ret);

            // if there were conflicts (and there will be), do a partial unlock
            if (0 != ret->mIds->GetCount ())
            {
                // reuse the same stream
                result = SE_stream_close (stream, TRUE);

                // if necessary, version enable the stream
                ArcSDELongTransactionUtility::VersionStream (connection, stream, table, false);

                // select locks still remaining
                result = SE_stream_set_rowlocking (stream, SE_ROWLOCKING_FILTER_MY_LOCKS | SE_ROWLOCKING_UNLOCK_ON_QUERY | SE_ROWLOCKING_LOCK_ONLY);
                handle_sde_err<FdoCommandException> (connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_STREAM_LOCK, "Cannot set row locking on the stream.");

                // get the list of row ids from the log file
                result = SE_stream_query_logfile (stream, logfile, 1, columns, &sql_construct);
                handle_sde_err<FdoCommandException>(stream, result, __FILE__, __LINE__, ARCSDE_LOG_FILE_QUERY, "Unable to query log file.");

                // execute the query that unlocks rows
                result = SE_stream_execute (stream);
                handle_sde_err<FdoCommandException>(stream, result, __FILE__, __LINE__, ARCSDE_STREAM_EXECUTE, "Stream execute failed.");
            }
            else
                throw FdoException::Create (NlsMsgGet(ARCSDE_UNEXPECTED_ERROR, "Unexpected error encountered in ArcSDE Provider."));
            break;
        default:
            handle_sde_err<FdoCommandException> (stream, result, __FILE__, __LINE__, ARCSDE_STREAM_FETCH, "Stream fetch failed.");
    }

    // release the transaction lock
    if (reader != NULL)
        reader->Close ();

    // clean up
    if (NULL != locks)
        free(locks);
    result = SE_stream_free (stream);
    handle_sde_err<FdoCommandException>(connection->GetConnection (), result, __FILE__, __LINE__, ARCSDE_STREAM_FREE, "Stream free failed.");
    delete[] where;
    if (NULL != filters)
    {
        for (int i = 0; i < count; i++)
            if (NULL != filters[i].filter.shape)
                SE_shape_free (filters[i].filter.shape);
        delete[] filters;
    }

    return (FDO_SAFE_ADDREF (ret.p));
}