DWORD CCarta::SaveToDB()
{
char SqlStr[300];
char* sqlinsert = "insert into correspo (Nombre,direccion,fecha_e,tema,sintesis,sintesis2,sintesis3,sintesis4,sintesis5,sindicato,respuesta,provincia,expte) values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')";
char* sqlupdate = "update correspo set Nombre='%s',direccion='%s',fecha_e='%s',tema='%s',sintesis='%s',sintesis2='%s',sintesis3='%s',sintesis4='%s',sintesis5='%s',sindicato='%s',respuesta='%s',provincia='%s' where expte ='%s'";
char* sql;
char fecha[10];
SYSTEMTIME stime;
HRESULT hr;
ICommandText* pICommandText = NULL;
IRowset* pIRowset = NULL;
long pRowsCant;
if (!m_pIdbDatabase) return 0;
stime.wDay = m_FechaEntrada.day;
stime.wMonth = m_FechaEntrada.month;
stime.wYear = m_FechaEntrada.year;
::GetDateFormat(LOCALE_SYSTEM_DEFAULT,0,&stime,NULL,fecha,300);
if (m_ID != 0)
sql = sqlupdate;
else
sql = sqlinsert;
for(;;)
{
sprintf(SqlStr,sql,m_Nombre,m_Direccion,fecha,m_Tema,m_Sintesis1,m_Sintesis2,m_Sintesis3,m_Sintesis4,m_Sintesis5,m_Sindicato,m_Respuesta,m_Provincia,m_Expediente);
db_CharToWChar(SqlStr);
hr = m_pIdbDatabase->_ICommand()->QueryInterface(IID_ICommandText,(void**)&pICommandText);
hr = pICommandText->SetCommandText(DBGUID_DEFAULT,(OLECHAR*)SqlStr);
hr = pICommandText->Release();
hr = m_pIdbDatabase->_ICommand()->Execute(NULL,IID_IRowset,NULL,&pRowsCant,(IUnknown **)&pIRowset);
break;
}
return ERR_NONE;
}
bool CClientDB::InsertInventoryData( const char* pszName, S_InventoryData Data )
{
ICommandText* pICommandText = NULL;
HRESULT hr;
LONG cNumRows = 0;
WCHAR wCmdString[1024];
wsprintfW(wCmdString, L"INSERT INTO INVENTORY ( name, realindex, virtualindex ) VALUES ( '%S', %d, %d )", pszName, Data.lRealIndex, Data.lVirtualIndex );
pICommandText = CreateCommand ( m_pIDBInitialize ,wCmdString );
if( pICommandText == NULL )
return false;
if(FAILED(hr = pICommandText->Execute(
NULL,
IID_NULL,
NULL,
&cNumRows,
NULL)))
{
LogString( LOG_NORMAL,"Fail CreateInventoryTable\n" );
}
pICommandText->Release();
return true;
}
bool CClientDB::UpdateInventoryData( const char* pszName, S_InventoryData Data )
{
ICommandText* pICommandText = NULL;
HRESULT hr;
LONG cNumRows = 0;
WCHAR wCmdString[1024];
wsprintfW(wCmdString, L"UPDATE INVENTORY SET virtualindex = %d WHERE name = '%S' AND realindex = %d",Data.lVirtualIndex , pszName ,Data.lRealIndex );
pICommandText = CreateCommand ( m_pIDBInitialize ,wCmdString );
if( pICommandText == NULL )
return false;
if(FAILED(hr = pICommandText->Execute(
NULL,
IID_NULL,
NULL,
&cNumRows,
NULL)))
{
LogString( LOG_NORMAL,"Fail CreateInventoryTable\n" );
pICommandText->Release();
return false;
}
pICommandText->Release();
if( cNumRows == 0 )
return false;
return true;
}
//*----------------------------------------------------------------------------
/// 매번 새로 만들려고 시도한다.. 이미 만들어져 있다면 Fail
//*----------------------------------------------------------------------------
bool CClientDB::CreateInventoryTable()
{
ICommandText* pICommandText = NULL;
HRESULT hr;
LONG cNumRows = 0;
WCHAR wCmdString[] = L"CREATE TABLE INVENTORY ( name TEXT(30), realindex long , virtualindex long )";
pICommandText = CreateCommand ( m_pIDBInitialize ,wCmdString );
if( pICommandText == NULL )
return false;
if(FAILED(hr = pICommandText->Execute(
NULL,
IID_NULL,
NULL,
&cNumRows,
NULL)))
{
LogString( LOG_NORMAL,"Fail CreateInventoryTable\n" );
}
pICommandText->Release();
return true;
}
/********************************************************************
SqlCommandExecuteQuery - executes a SQL command and returns the results if desired
NOTE: ppirs and pcRoes are optional
********************************************************************/
extern "C" HRESULT DAPI SqlCommandExecuteQuery(
__in IDBCreateCommand* pidbCommand,
__in __sql_command LPCWSTR wzSql,
__out IRowset** ppirs,
__out DBROWCOUNT* pcRows
)
{
Assert(pidbCommand);
HRESULT hr = S_OK;
ICommandText* picmdText = NULL;
ICommand* picmd = NULL;
DBROWCOUNT cRows = 0;
if (pcRows)
{
*pcRows = NULL;
}
//
// create the command
//
hr = pidbCommand->CreateCommand(NULL, IID_ICommand, (IUnknown**)&picmd);
ExitOnFailure(hr, "failed to create command to execute session");
//
// set the sql text into the command
//
hr = picmd->QueryInterface(IID_ICommandText, (LPVOID*)&picmdText);
ExitOnFailure(hr, "failed to get command text object for command");
hr = picmdText->SetCommandText(DBGUID_DEFAULT , wzSql);
ExitOnFailure1(hr, "failed to set SQL string: %ls", wzSql);
//
// execute the command
//
hr = picmd->Execute(NULL, (ppirs) ? IID_IRowset : IID_NULL, NULL, &cRows, reinterpret_cast<IUnknown**>(ppirs));
ExitOnFailure1(hr, "failed to execute SQL string: %ls", wzSql);
if (DB_S_ERRORSOCCURRED == hr)
{
hr = E_FAIL;
}
if (pcRows)
{
*pcRows = cRows;
}
LExit:
ReleaseObject(picmd);
ReleaseObject(picmdText);
return hr;
}
DWORD CCarta::LoadExpte()
{
// preparar consulta
DWORD dr;
DWORD rc; // Reference count
long pRowsCant;
HRESULT hr;
IRowset* pIRowset = NULL;
ICommandText* pICommandText = NULL;
CMemArray memarray;
DBDATAINFO* dbdatainfo;
dr = db_GetdbDataInfo(MAKELONG(EXPTE_CINDEX,CARTAS_TINDEX),this,&dbdatainfo);
if (dr != ERR_NONE) return dr;
dr = memarray.SetMaxSize(1000);
if (dr != ERR_NONE) return dr;
sprintf(CorrespoSQL,"select top 1 * from correspo where ucase(%s) = ucase('%s')",dbdatainfo->name,m_Expediente);
db_CharToWChar(CorrespoSQL);
Clear();
for(;;)
{
hr = m_pIdbDatabase->_ICommand()->QueryInterface(IID_ICommandText,(void**)&pICommandText);
if (hr != S_OK) break;
hr = pICommandText->SetCommandText(DBGUID_DEFAULT,(OLECHAR*)CorrespoSQL);
if (hr != S_OK) break;
hr = m_pIdbDatabase->_ICommand()->Execute(NULL,IID_IRowset,NULL,&pRowsCant,(IUnknown **)&pIRowset);
if (hr != S_OK) break;
if (pRowsCant == 0)
{
dr = ERR_NOTFOUND;
break;
}
dr = memarray.SetDBInterfaz(this);
dr = memarray.SetData(pIRowset,0);
if (memarray.GetRowCount() != 0) memarray.GetData(0);
break;
};
if (pICommandText) rc = pICommandText->Release();
if (pIRowset) rc = pIRowset->Release();
if ((hr == S_OK) && (dr == ERR_NONE)) return ERR_NONE;
if (hr != S_OK) dr = ERR_OLEDBFAIL;
return dr;
}
ICommandText* CClientDB::CreateCommand( IDBInitialize* pIDBInitialize ,LPCWSTR wCmdString )
{
IDBCreateSession* pIDBCreateSession = NULL;
IDBCreateCommand* pIDBCreateCommand = NULL;
ICommandText* pICommandText = NULL;
if( pIDBInitialize == NULL || wCmdString == NULL )
{
return NULL;
}
//Create a new activity from the data source object.
if(FAILED(pIDBInitialize->QueryInterface( IID_IDBCreateSession, (void**) &pIDBCreateSession)))
{
return NULL;
}
if(FAILED(pIDBCreateSession->CreateSession( NULL, IID_IDBCreateCommand, (IUnknown**) &pIDBCreateCommand)))
{
return NULL;
}
pIDBCreateSession->Release();
//Create a Command object.
if(FAILED(pIDBCreateCommand->CreateCommand( NULL, IID_ICommandText, (IUnknown**) &pICommandText)))
{
pIDBCreateCommand->Release();
return NULL;
}
pIDBCreateCommand->Release();
//Set the command text.
if(FAILED(pICommandText->SetCommandText(DBGUID_DBSQL, wCmdString )))
{
pICommandText->Release();
return NULL;
}
return pICommandText;
}
/********************************************************************
SqlSessionExecuteQuery - executes a query and returns the results if desired
NOTE: ppirs and pcRoes and pbstrErrorDescription are optional
********************************************************************/
extern "C" HRESULT DAPI SqlSessionExecuteQuery(
__in IDBCreateSession* pidbSession,
__in __sql_command LPCWSTR wzSql,
__out_opt IRowset** ppirs,
__out_opt DBROWCOUNT* pcRows,
__out_opt BSTR* pbstrErrorDescription
)
{
Assert(pidbSession);
HRESULT hr = S_OK;
IDBCreateCommand* pidbCommand = NULL;
ICommandText* picmdText = NULL;
ICommand* picmd = NULL;
DBROWCOUNT cRows = 0;
if (pcRows)
{
*pcRows = NULL;
}
//
// create the command
//
hr = pidbSession->CreateSession(NULL, IID_IDBCreateCommand, (IUnknown**)&pidbCommand);
ExitOnFailure(hr, "failed to create database session");
hr = pidbCommand->CreateCommand(NULL, IID_ICommand, (IUnknown**)&picmd);
ExitOnFailure(hr, "failed to create command to execute session");
//
// set the sql text into the command
//
hr = picmd->QueryInterface(IID_ICommandText, (LPVOID*)&picmdText);
ExitOnFailure(hr, "failed to get command text object for command");
hr = picmdText->SetCommandText(DBGUID_DEFAULT , wzSql);
ExitOnFailure1(hr, "failed to set SQL string: %ls", wzSql);
//
// execute the command
//
hr = picmd->Execute(NULL, (ppirs) ? IID_IRowset : IID_NULL, NULL, &cRows, reinterpret_cast<IUnknown**>(ppirs));
ExitOnFailure1(hr, "failed to execute SQL string: %ls", wzSql);
if (DB_S_ERRORSOCCURRED == hr)
{
hr = E_FAIL;
}
if (pcRows)
{
*pcRows = cRows;
}
LExit:
if (FAILED(hr) && picmd && pbstrErrorDescription)
{
HRESULT hrGetErrors = SqlGetErrorInfo(picmd, IID_ICommandText, 0x409, NULL, pbstrErrorDescription); // TODO: use current locale instead of always American-English
if (FAILED(hrGetErrors))
{
ReleaseBSTR(*pbstrErrorDescription);
}
}
ReleaseObject(picmd);
ReleaseObject(picmdText);
ReleaseObject(pidbCommand);
return hr;
}
void CClientDB::GetInventoryData( const char* pszName, std::list<S_InventoryData>& Inventory)
{
ICommandText* pICommandText = NULL;
IRowset* pIRowset = NULL;
HRESULT hr;
ULONG cbColOffset = 0;
LONG cNumRows = 0;
LONG cRowsAffected = 0;
int iCount = 0;
unsigned long cRowsObtained;
unsigned long cCount;
HACCESSOR hAccessor;
IAccessor * pIAccessor = NULL;
WCHAR wCmdString[1024];
wsprintfW(wCmdString, L"SELECT realindex,virtualindex FROM INVENTORY WHERE name = '%S'", pszName );
pICommandText = CreateCommand( m_pIDBInitialize ,wCmdString );
if( pICommandText == NULL )
{
assert( 0 && "Fail GetInventoryData");
return;
}
if(FAILED(hr = pICommandText->Execute(
NULL,
IID_IRowset,
NULL,
&cNumRows,
(IUnknown **) &pIRowset)))
{
assert( 0 && "Fail GetInventoryData : ICommandText->Execute");
pICommandText->Release();
return;
}
if( FAILED( pIRowset->QueryInterface( IID_IAccessor, (void **) &pIAccessor)))
{
assert( 0 && "Fail IRowset->QueryInterface" );
pIRowset->Release();
pICommandText->Release();
return;
}
const int nParams = 2;
S_InventoryData Data;
memset( &Data, 0, sizeof( S_InventoryData ));
static DBBINDING ExactBindings [nParams] = {
{
1, // iOrdinal
offsetof (S_InventoryData, lRealIndex), // obValue
0, // No length binding
0, // No Status binding
NULL, // No TypeInfo
NULL, // No Object
NULL, // No Extensions
DBPART_VALUE,
DBMEMOWNER_CLIENTOWNED, // Ignored
DBPARAMIO_NOTPARAM,
sizeof (long),
0,
DBTYPE_I4,
0, // No Precision
0 // No Scale
},
{
2, // iOrdinal
offsetof (S_InventoryData, lVirtualIndex ), // obValue
0, // No length binding
0, // No Status binding
NULL, // No TypeInfo
NULL, // No Object
NULL, // No Extensions
DBPART_VALUE,
DBMEMOWNER_CLIENTOWNED, // Ignored
DBPARAMIO_NOTPARAM,
sizeof(long),
0,
DBTYPE_I4,
0, // No Precision
0 // No Scale
},
};
if( FAILED( hr = pIAccessor->CreateAccessor(
DBACCESSOR_ROWDATA,
nParams,
ExactBindings,
0,
&hAccessor,
NULL)))
{
pIAccessor->Release();
pIRowset->Release();
pICommandText->Release();
assert( 0 && "Fail CreateAccessor");
return;
// Handle errors here.
}
HROW* pRows = new HROW[256];
while( TRUE )
{
if( FAILED( hr = pIRowset->GetNextRows( NULL, 0, 256, &cRowsObtained, &pRows ) ) )
{
CheckErrorGetNextRows( hr );
break;
// Handle errors here.
}
// Make sure some rows were obtained.
if( cRowsObtained == 0 )
{
LogString(LOG_NORMAL,"ROW IS 0");
break;
}
// Get the data for the each of the rows.
for( cCount = 0; cCount < cRowsObtained; cCount++ )
{
// Get the row data needed.
ZeroMemory( &Data, sizeof( Data ));
if( FAILED( hr = pIRowset->GetData( pRows[cCount], hAccessor, (void*)&Data )))
CheckErrorGetData( hr );
else
Inventory.push_back( Data );
}
// Release the rows.
if( FAILED( hr = pIRowset->ReleaseRows(
cRowsObtained,
pRows,
NULL,
NULL,
NULL )))
{
// Handle errors here.
}
memset( &Data, 0 , sizeof( S_InventoryData ));
}
if( FAILED( hr = pIAccessor->ReleaseAccessor( hAccessor, NULL )))
{
// Handle errors here.
}
// Release the IAccessor object.
if( pRows )
delete []pRows;
pIAccessor->Release();
pIRowset->Release();
pICommandText->Release();
}