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);
}
}
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 (¤t);
localtime_r (¤t, &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));
}
