result* mssql_database::on_execute(const std::string &sqlstr)
{
if (!connection_) {
throw_error("mssql", "no odbc connection established");
}
// create statement handle
SQLHANDLE stmt;
SQLRETURN ret = SQLAllocHandle(SQL_HANDLE_STMT, connection_, &stmt);
throw_error(ret, SQL_HANDLE_DBC, connection_, "mssql", "error on creating sql statement");
// execute statement
// int retry = retries_;
ret = SQLExecDirectA(stmt, (SQLCHAR*)sqlstr.c_str(), SQL_NTS);
/*
do {
ret = SQLExecDirectA(stmt, (SQLCHAR*)sqlstr.c_str(), SQL_NTS);
} while (retry-- && !(SQL_SUCCEEDED(ret) || SQL_NO_DATA));
*/
throw_error(ret, SQL_HANDLE_STMT, stmt, sqlstr, "error on query execute");
return new mssql_result(stmt, true);
}
void TableAdmin::Add( StructAdmin StrAdmin, ODBCManagement& ODBC, SQLHDBC hdbc_loc )
{
if( !hasInit ) return;
// add one record
int MaxId = ODBC.getOneValue( "select max( AdminId ) from Admin;" ) + 1;
char temp[ 1024 ];
sprintf( temp, "insert into Admin( AdminId, AdminName, AdminPassword, AdminType, "
"canManageAdmin, canManageStudent, canSetProblem, canSetPaper, canSetExam, canDeleteGrade, canEditGrade ) "
"values ( %d, \'%s\', \'be9d9be4e8df0f4046100680ff3b209c15715e6c9f1057ca945c08cfc77b2a50\', "
"1, %d, %d, %d, %d, %d, %d, %d );", MaxId, StrAdmin.Name, StrAdmin.canManageAdmin ? 1 : 0,
StrAdmin.canManageStudent ? 1 : 0, StrAdmin.canSetProblem ? 1 : 0, StrAdmin.canSetPaper ? 1 : 0,
StrAdmin.canSetExam ? 1 : 0, StrAdmin.canDeleteGrade ? 1 : 0, StrAdmin.canEditGrade ? 1 : 0 );
SQLHSTMT h;
SQLAllocHandle( SQL_HANDLE_STMT, hdbc_loc, &h );
SQLExecDirectA( h, (SQLCHAR *)temp, SQL_NTS );
SQLFreeHandle( SQL_HANDLE_STMT, h );
delete [ ] SA;
Init( hdbc_loc, AdminName, PasswordHash );
return;
}
int OdbcWrapper::execDML(const char *sql) {
if (SQL_SUCCESS != SQLExecDirectA(sqlstatementhandle, (SQLCHAR *) sql, SQL_NTS)) {
get_error(SQL_HANDLE_STMT, sqlstatementhandle);
SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle);
return -1;
}
return 0;
}
bool TableAdmin::SetNewPassword( SQLHDBC hdbc_loc, string OldHash, string NewHash )
{
if( !JudgePassword( OldHash ) ) return false;
SQLHSTMT h;
strcpy( PasswordHash, NewHash.c_str( ) );
SQLAllocHandle( SQL_HANDLE_STMT, hdbc_loc, &h );
// update Admin set AdminPassword = \' ... \' where AdminName = \' ... \';
SQLExecDirectA( h, (SQLCHAR *)( (string)"update Admin set AdminPassword = \'" + PasswordHash
+ "\' where AdminName = \'" + AdminName + "\';" ).c_str( ), SQL_NTS );
SQLFreeHandle( SQL_HANDLE_STMT, h );
return true;
}
bool TableAdmin::FetchAdminList( ODBCManagement& ODBC, SQLHDBC hdbc_loc )
{
if( !hasInit ) return false;
SA_count = ODBC.getOneValue( (string)"select count( * ) from Admin where AdminName != \'"
+ AdminName +"\' and AdminType != 0;" ); // I only worked out this method
if( !SA_count ) goto SAEND;
else {
SA = new StructAdmin [ SA_count ];
memset( SA, '\0', sizeof( SA ) );
}
SQLHSTMT h;
SQLAllocHandle( SQL_HANDLE_STMT, hdbc_loc, &h );
SQLExecDirectA( h, (SQLCHAR *)( (string)"select AdminName, canManageAdmin, canManageStudent, canSetProblem, "
+ "canSetPaper, canSetExam, canDeleteGrade, canEditGrade from Admin_General where AdminName != \'"
+ AdminName +"\' and AdminType != 0;" ).c_str( ), SQL_NTS );
int i = 0;
while( SQL_SUCCEEDED( SQLFetch( h ) ) ) {
if( !( SQL_SUCCEEDED( SQLGetData( h, 1, SQL_C_CHAR, SA[ i ].Name, 20, NULL ) ) // Last 2 cannot be NULL together
&& SQL_SUCCEEDED( SQLGetData( h, 2, SQL_C_BIT, &(SA[ i ].canManageAdmin), 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 3, SQL_C_BIT, &(SA[ i ].canManageStudent), 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 4, SQL_C_BIT, &(SA[ i ].canSetProblem), 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 5, SQL_C_BIT, &(SA[ i ].canSetPaper), 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 6, SQL_C_BIT, &(SA[ i ].canSetExam), 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 7, SQL_C_BIT, &(SA[ i ].canDeleteGrade), 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 8, SQL_C_BIT, &(SA[ i ].canEditGrade), 0, NULL ) ) ) ) {
MessageBox( NULL, _T( "tAdmin GetData Failed!\r\nIn FetchAdminList( );" ), _T( "Error" ), MB_OK );
return false;
}
else {
/*char temp[ 1024 ];
sprintf( temp, "AdminName: %s\r\ncanManageAdmin: %d\r\n"
"canManageStudent: %d\r\ncanSetProblem: %d\r\ncanSetPaper: %d\r\ncanSetExam: %d\r\n"
"canDeleteGrade: %d\r\ncanEditGrade: %d", SA[ i ].Name, (int)(SA[ i ].canManageAdmin),
(int)(SA[ i ].canManageStudent), (int)(SA[ i ].canSetProblem), (int)(SA[ i ].canSetPaper),
(int)(SA[ i ].canSetExam),(int)(SA[ i ].canDeleteGrade), (int)(SA[ i ].canEditGrade) );
MessageBoxA( NULL, temp, "Succeeded", MB_OK ); */
i ++;
}
}
SQLFreeHandle( SQL_HANDLE_STMT, h );
SAEND:
return true;
}
void TableAdmin::Delete( int line, SQLHDBC hdbc_loc )
{
if( !( hasInit && line < SA_count ) ) return;
// delete one record
SQLHSTMT h;
SQLAllocHandle( SQL_HANDLE_STMT, hdbc_loc, &h );
SQLExecDirectA( h, (SQLCHAR *)( (string)"delete from Admin where AdminName = \'" + SA[ line ].Name + "\';" ).c_str( ), SQL_NTS );
SQLFreeHandle( SQL_HANDLE_STMT, h );
delete [ ] SA;
Init( hdbc_loc, AdminName, PasswordHash );
return;
}
bool TableAdmin::Init( SQLHDBC hdbc_loc, string Name, string Password )
{
hasInit = false;
SQLHSTMT h;
memset( AdminName, '\0', 256 );
strcpy_s( PasswordHash, 255, Password.c_str( ) );
SQLAllocHandle( SQL_HANDLE_STMT, hdbc_loc, &h );
SQLExecDirectA( h, (SQLCHAR *)( "select AdminId, AdminName, canManageAdmin, canManageStudent, "
"canSetProblem, canSetPaper, canSetExam, canDeleteGrade, canEditGrade, AdminType from Admin "
"where AdminName = \'" + Name + "\' and AdminPassword = \'" + Password + "\';" ).c_str( ), SQL_NTS );
if( SQL_SUCCEEDED( SQLFetch( h ) ) ) {
if( !( SQL_SUCCEEDED( SQLGetData( h, 1, SQL_C_LONG, &AdminId, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 2, SQL_C_CHAR, AdminName, 20, NULL ) ) // Last 2 cannot be NULL together
&& SQL_SUCCEEDED( SQLGetData( h, 3, SQL_C_BIT, &canManageAdmin, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 4, SQL_C_BIT, &canManageStudent, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 5, SQL_C_BIT, &canSetProblem, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 6, SQL_C_BIT, &canSetPaper, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 7, SQL_C_BIT, &canSetExam, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 8, SQL_C_BIT, &canDeleteGrade, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 9, SQL_C_BIT, &canEditGrade, 0, NULL ) )
&& SQL_SUCCEEDED( SQLGetData( h, 10, SQL_C_BIT, &AdminType, 0, NULL ) ) ) ) {
MessageBox( NULL, _T( "tAdmin GetData Failed!" ), _T( "Error" ), MB_OK );
return false;
}
/* else {
char temp[ 1024 ];
sprintf( temp, "AdminId: %d\r\nAdminName: %s\r\ncanManageAdmin: %d\r\n"
"canManageStudent: %d\r\ncanSetProblem: %d\r\ncanSetPaper: %d\r\ncanSetExam: %d\r\n"
"canDeleteGrade: %d\r\ncanEditGrade: %d\r\nAdminType: %d", AdminId, AdminName, (int)canManageAdmin,
(int)canManageStudent, (int)canSetProblem, (int)canSetPaper, (int)canSetExam,
(int)canDeleteGrade, (int)canEditGrade, (int)AdminType );
MessageBoxA( NULL, temp, "Succeeded", MB_OK );
} */
}
else {
MessageBox( NULL, _T( "tAdmin Fetch Failed!\r\nIn Init( );" ), _T( "Error" ), MB_OK );
return false;
}
SQLFreeHandle( SQL_HANDLE_STMT, h );
hasInit = true;
return true;
}
void TableAdmin::Edit( int line, StructAdmin StrAdmin, SQLHDBC hdbc_loc )
{
if( !( hasInit && line < SA_count ) ) return;
// edit a record
char temp[ 1024 ];
sprintf( temp, "update Admin set canManageAdmin = %d, canManageStudent = %d, canSetProblem = %d, canSetPaper = %d, "
"canSetExam = %d, canDeleteGrade = %d, canEditGrade = %d where AdminName = \'%s\';",
StrAdmin.canManageAdmin ? 1 : 0, StrAdmin.canManageStudent ? 1 : 0, StrAdmin.canSetProblem ? 1 : 0,
StrAdmin.canSetPaper ? 1 : 0, StrAdmin.canSetExam ? 1 : 0, StrAdmin.canDeleteGrade ? 1 : 0,
StrAdmin.canEditGrade ? 1 : 0, StrAdmin.Name );
SQLHSTMT h;
SQLAllocHandle( SQL_HANDLE_STMT, hdbc_loc, &h );
SQLExecDirectA( h, (SQLCHAR *)temp, SQL_NTS );
SQLFreeHandle( SQL_HANDLE_STMT, h );
delete [ ] SA;
Init( hdbc_loc, AdminName, PasswordHash );
return;
}
/*----------------------------------------------------------------------------------------------
Initialize the database.
----------------------------------------------------------------------------------------------*/
void InitializeDB(const wchar * pszwServer, const wchar * pszwDB, const char * pszInitScript)
{
SqlDb sdb;
SqlStatement sstmt;
HRESULT hr;
char * pszCmd;
char * psz;
char * pchEnd;
int chLeading;
int chTrailing;
Vector<char> vchScript;
FILE * pfile;
RETCODE rc;
struct stat statInit;
// osql -U sa -E -n -b -d %1 <%SQLSCRIPT%
if (stat(pszInitScript, &statInit))
{
fprintf(stderr, "Cannot open Initialization SQL file \"%s\"!\n", pszInitScript);
ThrowHr(WarnHr(E_FAIL));
}
vchScript.Resize(statInit.st_size + 1);
pfile = fopen(pszInitScript, "rb");
if (!pfile)
{
fprintf(stderr, "Cannot open Initialization SQL file \"%s\"!\n", pszInitScript);
ThrowHr(WarnHr(E_FAIL));
}
fread(vchScript.Begin(), 1, statInit.st_size, pfile);
fclose(pfile);
pchEnd = vchScript.Begin() + statInit.st_size;
*pchEnd = '\0';
hr = sdb.Open(pszwServer, pszwDB);
CheckHr(hr);
sstmt.Init(sdb);
// This is needed to allow double quotes in dynamic SQL, which is used by some of the
// stored procedures that FieldWorks defines.
pszCmd = "SET QUOTED_IDENTIFIER OFF";
rc = SQLExecDirectA(sstmt.Hstmt(), reinterpret_cast<SQLCHAR *>(pszCmd), SQL_NTS);
VerifySqlRc(rc, sstmt.Hstmt(), pszCmd);
sstmt.Clear();
// Find next "go" keyword, if any.
// Note that we have to skip over any comments or quoted strings while searching.
// Also we need to convert non-embedded double quotes to single quotes.
bool fSingleQuoted;
bool fDoubleQuoted;
char * pszOpen;
bool fInLineComment;
bool fInsideComment;
int ch;
int ch2;
for (pszCmd = vchScript.Begin(); pszCmd < pchEnd; pszCmd = psz)
{
// Skip leading whitespace.
pszCmd += strspn(pszCmd, " \t\r\n\f\v");
if (pszCmd == pchEnd)
break;
fSingleQuoted = false;
fDoubleQuoted = false;
pszOpen = NULL;
fInLineComment = false;
fInsideComment = false;
for (psz = pszCmd; psz < pchEnd; ++psz)
{
ch = *psz;
ch2 = *(psz + 1);
if (fInLineComment)
{
if (ch == '\n')
fInLineComment = false;
continue;
}
if (fInsideComment)
{
if (ch == '*' && ch2 == '/')
{
fInsideComment = false;
++psz;
}
continue;
}
if (!fSingleQuoted && !fDoubleQuoted)
{
if (ch == '-' && ch2 == '-')
{
fInLineComment = true;
++psz;
continue;
}
else if (ch == '/' && ch2 == '*')
{
fInsideComment = true;
++psz;
continue;
}
}
if (ch == '\'')
{
if (fSingleQuoted)
{
fSingleQuoted = false;
}
else if (fDoubleQuoted)
{
// Retain outer double quotes if embedded single quote.
pszOpen = NULL;
}
else
{
fSingleQuoted = true;
}
}
else if (ch == '"')
{
if (fSingleQuoted)
{
// Do nothing. (?)
}
else if (fDoubleQuoted)
{
if (pszOpen)
{
// Convert double quotes to single quotes if not embedded.
*pszOpen = '\'';
*psz = '\'';
pszOpen = NULL;
}
fDoubleQuoted = false;
}
else
{
fDoubleQuoted = true;
pszOpen = psz;
}
}
else if ((ch == 'g' || ch == 'G') && (ch2 == 'o' || ch2 == 'O') &&
!fSingleQuoted && !fDoubleQuoted)
{
chLeading = (psz > pszCmd) ? *(psz - 1) : ' ';
chTrailing = (psz + 2 < pchEnd) ? *(psz + 2) : ' ';
if (isascii(chLeading) && isspace(chLeading) &&
isascii(chTrailing) && isspace(chTrailing))
{
*psz = '\0';
psz += 2;
break;
}
}
}
sstmt.Init(sdb);
rc = SQLExecDirectA(sstmt.Hstmt(), reinterpret_cast<SQLCHAR *>(pszCmd), strlen(pszCmd));
VerifySqlRc(rc, sstmt.Hstmt(), pszCmd);
sstmt.Clear();
}
sdb.Close();
}
/*----------------------------------------------------------------------------------------------
Create the database. Return 0 if successful, or a nonzero value if an error occurs.
----------------------------------------------------------------------------------------------*/
int CreateDB(const char * pszServer, const char * pszDB, const char * pszOutputDir,
const char * pszInitScript, bool fForceCreate)
{
HRESULT hr;
static const wchar szwMaster[] = L"master";
StrUniBuf stubServer(pszServer);
StrUniBuf stubDatabase(pszDB);
if (stubServer.Overflow() || stubDatabase.Overflow())
{
fprintf(stderr, "Out of memory filling static buffers??\n");
return __LINE__;
}
try
{
SqlDb sdb;
SqlStatement sstmt;
StrAnsiBufBig stabCmd;
hr = sdb.Open(stubServer.Chars(), stubDatabase.Chars());
if (SUCCEEDED(hr))
{
sdb.Close();
if (fForceCreate)
{
// osql /U sa /E /n /b /Q "DROP DATABASE %1"
hr = sdb.Open(stubServer.Chars(), szwMaster);
if (FAILED(hr))
ThrowHr(WarnHr(hr));
sstmt.Init(sdb);
stabCmd.Format("DROP DATABASE %s", pszDB);
RETCODE rc;
rc = SQLExecDirectA(sstmt.Hstmt(),
reinterpret_cast<SQLCHAR *>(const_cast<char *>(stabCmd.Chars())), SQL_NTS);
VerifySqlRc(rc, sstmt.Hstmt(), stabCmd.Chars());
sstmt.Clear();
sdb.Close();
}
else
{
fprintf(stderr, "The database \"%s\" already exists on the server \"%s\".\n",
pszDB, pszServer);
fprintf(stderr,
"Use the -f command line flag to force recreating this database.\n");
return 1;
}
}
// osql -U sa -E -n -b -Q "CREATE DATABASE %1 ON
// (NAME=%1,FILENAME='%OUTPUT_DIR%\%1.mdf',FILEGROWTH=5MB) LOG ON
// (NAME='%1_Log', FILENAME='%OUTPUT_DIR%\%1_log.ldf',FILEGROWTH=5MB)"
hr = sdb.Open(stubServer.Chars(), szwMaster);
CheckHr(hr);
sstmt.Init(sdb);
if (pszOutputDir)
stabCmd.Format("CREATE DATABASE %s ON (NAME='%s',FILENAME='%s\\%s.mdf') \
LOG ON (NAME='%s_Log',FILENAME='%s\\%s_log.ldf')",
pszDB, pszDB, pszOutputDir, pszDB, pszDB, pszOutputDir, pszDB);
else
stabCmd.Format("CREATE DATABASE %s ", pszDB);
RETCODE rc;
rc = SQLExecDirectA(sstmt.Hstmt(),
reinterpret_cast<SQLCHAR *>(const_cast<char *>(stabCmd.Chars())), SQL_NTS);
VerifySqlRc(rc, sstmt.Hstmt(), stabCmd.Chars());
sstmt.Clear();
sdb.Close();
if (pszInitScript)
{
InitializeDB(stubServer.Chars(), stubDatabase.Chars(), pszInitScript);
}
}
catch (Throwable & thr)
{
fprintf(stderr, "Error %s caught creating database \"%s\" on server \"%s\"!\n",
AsciiHresult(thr.Error()), pszDB, pszServer);
return __LINE__;
}
catch (...)
{
fprintf(stderr, "Error caught creating database \"%s\" on server \"%s\"!\n",
pszDB, pszServer);
return __LINE__;
}
return 0;
}
