prototype
BOOL DB_ExecuteSQLScript(STRING
,
STRING
,
STRING
,
STRING
,
STRING);
prototype BOOL DB_DoesDatabaseExist(STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_CheckConnection(STRING , STRING , STRING , STRING);
prototype BOOL DB_CreateDatabase(STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AttachDatabase(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_RestoreDatabase(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AddLogin(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_DropLoginNoException(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_GrantDBAccess(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AddRoleMember(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AddSrvRoleMember(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_ChangePassword(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_GetInstances(BYREF LIST);
prototype BOOL DB_GetUserDataBase(STRING , STRING , STRING , BOOL , BYREF LIST);
///
//
// Function : DB_DoesDatabaseExist
//
// Purpose : This function will determine whether a given database exists .
//
// Arguments : svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this , but for SQL server you just send in " SQL Server "
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
//
// Usage :
// if (DB_DoesDatabaseExist( " SQLServer " , " MyDatabase " , " SQL Server " , " sa " , " saPassword " ) = FALSE) then
//
///
function BOOL DB_DoesDatabaseExist(svServerName , svDatabaseName , svDriver , svUserName , svUserPassword)
OBJECT pADOConnObj , pADORecordSetObj;
STRING szADOConnObjID , szADORecordSetObjID , szConnString , szSQL;
BOOL bExists;
begin
bExists = FALSE;
try
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = " ADODB.Connection " ;
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + svDriver + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database=master " ;
// Open the ADO Connection
pADOConnObj . Open (szConnString);
// Create ADO Recordset object for the return
szADORecordSetObjID = " ADODB.Recordset " ;
set pADORecordSetObj = CreateObject(szADORecordSetObjID);
// Set some ADO Recordset properties
pADORecordSetObj . CursorType = 3 ;
pADORecordSetObj . ActiveConnection = pADOConnObj;
// Create the SQL string to retrieve the database if it exists
szSQL = " Select name from sysdatabases where name=' " + svDatabaseName + " ' " ;
// Use the recordset to see if the database exists
pADORecordSetObj . Open (szSQL);
if (pADORecordSetObj . RecordCount = 1 ) then
bExists = TRUE;
endif;
catch
// ErrorHandler(Err . Number , " DB_DoesDatabaseExist " , Err . Description , WARNING);
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return FALSE;
endcatch;
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return bExists;
end;
///
//
// Function : DB_CheckConnection
//
//
///
function BOOL DB_CheckConnection(svServerName , svDriver , svUserName , svUserPassword)
OBJECT pADOConnObj , pADORecordSetObj;
STRING szADOConnObjID , szADORecordSetObjID , szConnString , szSQL;
BOOL bExists;
begin
bExists = FALSE;
try
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = " ADODB.Connection " ;
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + svDriver + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database=master " ;
// Open the ADO Connection
pADOConnObj . Open (szConnString);
// Create ADO Recordset object for the return
szADORecordSetObjID = " ADODB.Recordset " ;
set pADORecordSetObj = CreateObject(szADORecordSetObjID);
// Set some ADO Recordset properties
pADORecordSetObj . CursorType = 3 ;
pADORecordSetObj . ActiveConnection = pADOConnObj;
// Create the SQL string to retrieve the database if it exists
szSQL = " Select name from sysdatabases where name=' " + " master " + " ' " ;
// Use the recordset to see if the database exists
pADORecordSetObj . Open (szSQL);
if (pADORecordSetObj . RecordCount = 1 ) then
bExists = TRUE;
endif;
catch
bExists = FALSE;
// Clean up
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
endcatch;
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return bExists;
end;
///
//
// Function : DB_CreateDatabase
//
// Purpose : This function will create a new database .
//
// Arguments : svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this , but for SQL server you just send in " SQL Server "
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
//
// Usage :
// if (DB_CreateDatabase( " SQLServer " , " MyDatabase " , " SQL Server " , " sa " , " saPassword " ) = FALSE) then
//
///
function BOOL DB_CreateDatabase(svServerName , svDatabaseName , svDriver , svUserName , svUserPassword)
STRING szADOCommObjID , szADOObjID , szConnSting;
OBJECT pADOObj , pADOCommObj;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnSting = " driver={ " + svDriver + " }; " ;
szConnSting = szConnSting + " server= " + svServerName + " ; " ;
szConnSting = szConnSting + " uid= " + svUserName + " ; " ;
szConnSting = szConnSting + " pwd= " + svUserPassword;
// Open the ADO Connection
pADOObj . Open (szConnSting);
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to create the database
pADOCommObj . CommandText = " Create database " + svDatabaseName;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_CreateDatabase " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
// 第二部分
///
//
// Function : DB_ExecuteSQLScript
//
// Purpose : This function will execute a SQL script .
//
// Arguments : svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this , but for SQL server you just send in " SQL Server "
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
// svScriptFile - A fully qualified path to a SQL script file
//
// Usage :
// DB_ExecuteSQLScript( " SQLServer " , " MyDatabase " , " SQL Server " , " sa " , " saPassword " , " C:Scripts ables.sql " )
//
// Notes : You could change this function to accept the script as a string
// instead of the file . With a file , you can add the scripts
// as files in the installation , putting them in a directory .
// This would allow them to be reused later , or they can just be
// deleted after each script is run .
//
///
function BOOL DB_ExecuteSQLScript(svServerName , svDatabaseName , svUserName , svUserPassword , svScriptFile)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svLine , szConnString , szSQL , svString;
NUMBER nResult , nError;
LIST listID;
begin
try
// Create an empty string list .
listID = ListCreate (STRINGLIST);
// Read the SQL script file into the list
if (ListReadFromFile(listID , svScriptFile) < 0 ) then // read list from file
MessageBox ( " ERROR: Unable to open SQL script: " + svScriptFile + " . " , SEVERE);
nError = 1 ;
return nError;
endif;
// Go through each list item and add it to a string (which will then hold the script)
szSQL = "" ;
nResult = ListGetFirstString (listID , svString);
while (nResult = 0 )
szSQL = szSQL + " " + svString;
nResult = ListGetNextString (listID , svString);
endwhile;
// Be good and clean up your trash
ListDestroy(listID);
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = szSQL;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_ExecuteSQLScript " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AttachDatabase
//
// Purpose : This function will attach a database to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AttachDatabase(svServerName , svDatabaseName , svDatabasePath , svLogPath , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_attach_db ' "
+ svDatabaseName
+ " ',' "
+ svDatabasePath
+ " ',' "
+ svLogPath
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AttachDatabase " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AttachDatabase
//
// Purpose : This function will attach a database to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_RestoreDatabase(svServerName , svDatabaseName ,
svDatabasePath , svSaveDataPath , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " RESTORE DATABASE " + svDatabaseName + " FROM DISK=' "
+ svDatabasePath
+ " ' WITH MOVE 'xhotel_data' TO ' "
+ svSaveDataPath ^ svDatabaseName + " .mdf', "
+ " MOVE 'xhotel_log' TO ' "
+ svSaveDataPath ^ svDatabaseName + " .ldf' " ;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AttachDatabase " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AddLogin
//
// Purpose : This function will add a login to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AddLogin(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_addlogin ' "
+ svUserName
+ " ',' "
+ svUserPassword
+ " ',' "
+ svDatabaseName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AddLogin " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
// 第三部分
///
//
// Function : DB_AddSrvRoleMember
//
// Purpose : This function will add a login to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AddSrvRoleMember(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svRole)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_addsrvrolemember ' "
+ svUserName
+ " ',' "
+ svRole
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AddSrvRoleMember " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_DropLoginNoException
//
// Purpose : This function will drop a login , but not throw an exception if it
// fails .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_DropLoginNoException(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_droplogin ' "
+ svUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// No exception thrown for this function ... this is a try only
/*
ErrorHandler(Err . Number , " DB_AddLogin " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
*/
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_GrantDBAccess
//
// Purpose : This function grant access to a database .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_GrantDBAccess(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svDBUserName)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_grantdbaccess N' "
+ svUserName
+ " ',N' "
+ svDBUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_GrantDBAccess " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AddRoleMember
//
// Purpose : This function grant access to a database .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AddRoleMember(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svRole , svUserName)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_addrolemember N' "
+ svRole
+ " ',N' "
+ svUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AddRoleMember " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_ChangePassword
//
// Purpose : This function changes a users password .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_ChangePassword(svServerName ,
svSAUsername , svSAPassword , svUserName , svOldPass , svNewPass)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Fix NULLS
if svOldPass = "" then
svOldPass = " NULL " ;
endif;
if svNewPass = "" then
svNewPass = " NULL " ;
endif;
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_password "
+ svOldPass
+ " , "
+ svNewPass
+ " ,' "
+ svUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_ChangePassword " , Err . Description , WARNING);
set pADOCommObj = NOTHING;
set pADOObj = NOTHING;
return FALSE;
endcatch;
set pADOCommObj = NOTHING;
set pADOObj = NOTHING;
return TRUE;
end;
// Function : BOOL DB_GetInstances()
// Description : Checks for an Instance of SQL Server , returns a list of
// of instances if true .
//
// Created By : BrianR
// Created Date : 11 - 12 - 2002
function BOOL DB_GetInstances(lInstances)
STRING szKey , szInstanceName;
NUMBER nRet;
begin
// Set default reg root
RegDBSetDefaultRoot(HKEY_LOCAL_MACHINE);
szKey = " SOFTWARE/MICROSOFT/Microsoft SQL Server " ;
// If key isnt there ... fail
if RegDBKeyExist(szKey) != 1 then
// fail
return FALSE;
endif;
// Create INSTANCE Lists
szInstanceName = " Test1 " ;
ListAddString(lInstances , szInstanceName , AFTER);
szInstanceName = " Test2 " ;
ListAddString(lInstances , szInstanceName , AFTER);
// Return
if ListCount(lInstances) != 0 then
return ListCount(lInstances);
else
return FALSE;
endif;
end;
function BOOL DB_GetUserDataBase(svServerName , svUserName , svUserPassword , sbWinAuth , listDatabase)
OBJECT pADOConnObj , pADORecordSetObj;
STRING szADOConnObjID , szADORecordSetObjID , szConnString , szSQL;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = " ADODB.Connection " ;
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
if (sbWinAuth = FALSE) then
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
else
szConnString = " Provider=SQLOLEDB.1;Integrated Security=SSPI; " ;
szConnString = szConnString + " Persist Security Info=False; " ;
szConnString = szConnString + " Initial Catalog=MASTER;Data Source= " ;
szConnString = szConnString + svServerName;
endif;
// Open the ADO Connection
pADOConnObj . Open (szConnString);
// Create ADO Recordset object for the return
szADORecordSetObjID = " ADODB.Recordset " ;
set pADORecordSetObj = CreateObject(szADORecordSetObjID);
// Set some ADO Recordset properties
pADORecordSetObj . CursorType = 3 ;
pADORecordSetObj . ActiveConnection = pADOConnObj;
// Create the SQL string to retrieve the database if it exists
szSQL = " Select name from sysdatabases where name not in('master') " ;
// Use the recordset to see if the database exists
pADORecordSetObj . Open (szSQL);
while ( ! pADORecordSetObj . EOF ())
if (ListAddString(listDatabase , pADORecordSetObj( " name " ) , AFTER) < 0 ) then
MessageBox( " adsasd " , INFORMATION);
endif;
pADORecordSetObj . MoveNext();
endwhile;
catch
// ErrorHandler(Err . Number , " 检索数据库失败 " , Err . Description , WARNING);
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return FALSE;
endcatch;
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return TRUE;
end;
prototype BOOL DB_DoesDatabaseExist(STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_CheckConnection(STRING , STRING , STRING , STRING);
prototype BOOL DB_CreateDatabase(STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AttachDatabase(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_RestoreDatabase(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AddLogin(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_DropLoginNoException(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_GrantDBAccess(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AddRoleMember(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_AddSrvRoleMember(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_ChangePassword(STRING , STRING , STRING , STRING , STRING , STRING);
prototype BOOL DB_GetInstances(BYREF LIST);
prototype BOOL DB_GetUserDataBase(STRING , STRING , STRING , BOOL , BYREF LIST);
///
//
// Function : DB_DoesDatabaseExist
//
// Purpose : This function will determine whether a given database exists .
//
// Arguments : svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this , but for SQL server you just send in " SQL Server "
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
//
// Usage :
// if (DB_DoesDatabaseExist( " SQLServer " , " MyDatabase " , " SQL Server " , " sa " , " saPassword " ) = FALSE) then
//
///
function BOOL DB_DoesDatabaseExist(svServerName , svDatabaseName , svDriver , svUserName , svUserPassword)
OBJECT pADOConnObj , pADORecordSetObj;
STRING szADOConnObjID , szADORecordSetObjID , szConnString , szSQL;
BOOL bExists;
begin
bExists = FALSE;
try
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = " ADODB.Connection " ;
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + svDriver + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database=master " ;
// Open the ADO Connection
pADOConnObj . Open (szConnString);
// Create ADO Recordset object for the return
szADORecordSetObjID = " ADODB.Recordset " ;
set pADORecordSetObj = CreateObject(szADORecordSetObjID);
// Set some ADO Recordset properties
pADORecordSetObj . CursorType = 3 ;
pADORecordSetObj . ActiveConnection = pADOConnObj;
// Create the SQL string to retrieve the database if it exists
szSQL = " Select name from sysdatabases where name=' " + svDatabaseName + " ' " ;
// Use the recordset to see if the database exists
pADORecordSetObj . Open (szSQL);
if (pADORecordSetObj . RecordCount = 1 ) then
bExists = TRUE;
endif;
catch
// ErrorHandler(Err . Number , " DB_DoesDatabaseExist " , Err . Description , WARNING);
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return FALSE;
endcatch;
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return bExists;
end;
///
//
// Function : DB_CheckConnection
//
//
///
function BOOL DB_CheckConnection(svServerName , svDriver , svUserName , svUserPassword)
OBJECT pADOConnObj , pADORecordSetObj;
STRING szADOConnObjID , szADORecordSetObjID , szConnString , szSQL;
BOOL bExists;
begin
bExists = FALSE;
try
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = " ADODB.Connection " ;
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + svDriver + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database=master " ;
// Open the ADO Connection
pADOConnObj . Open (szConnString);
// Create ADO Recordset object for the return
szADORecordSetObjID = " ADODB.Recordset " ;
set pADORecordSetObj = CreateObject(szADORecordSetObjID);
// Set some ADO Recordset properties
pADORecordSetObj . CursorType = 3 ;
pADORecordSetObj . ActiveConnection = pADOConnObj;
// Create the SQL string to retrieve the database if it exists
szSQL = " Select name from sysdatabases where name=' " + " master " + " ' " ;
// Use the recordset to see if the database exists
pADORecordSetObj . Open (szSQL);
if (pADORecordSetObj . RecordCount = 1 ) then
bExists = TRUE;
endif;
catch
bExists = FALSE;
// Clean up
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
endcatch;
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return bExists;
end;
///
//
// Function : DB_CreateDatabase
//
// Purpose : This function will create a new database .
//
// Arguments : svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this , but for SQL server you just send in " SQL Server "
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
//
// Usage :
// if (DB_CreateDatabase( " SQLServer " , " MyDatabase " , " SQL Server " , " sa " , " saPassword " ) = FALSE) then
//
///
function BOOL DB_CreateDatabase(svServerName , svDatabaseName , svDriver , svUserName , svUserPassword)
STRING szADOCommObjID , szADOObjID , szConnSting;
OBJECT pADOObj , pADOCommObj;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnSting = " driver={ " + svDriver + " }; " ;
szConnSting = szConnSting + " server= " + svServerName + " ; " ;
szConnSting = szConnSting + " uid= " + svUserName + " ; " ;
szConnSting = szConnSting + " pwd= " + svUserPassword;
// Open the ADO Connection
pADOObj . Open (szConnSting);
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to create the database
pADOCommObj . CommandText = " Create database " + svDatabaseName;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_CreateDatabase " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
// 第二部分
///
//
// Function : DB_ExecuteSQLScript
//
// Purpose : This function will execute a SQL script .
//
// Arguments : svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this , but for SQL server you just send in " SQL Server "
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
// svScriptFile - A fully qualified path to a SQL script file
//
// Usage :
// DB_ExecuteSQLScript( " SQLServer " , " MyDatabase " , " SQL Server " , " sa " , " saPassword " , " C:Scripts ables.sql " )
//
// Notes : You could change this function to accept the script as a string
// instead of the file . With a file , you can add the scripts
// as files in the installation , putting them in a directory .
// This would allow them to be reused later , or they can just be
// deleted after each script is run .
//
///
function BOOL DB_ExecuteSQLScript(svServerName , svDatabaseName , svUserName , svUserPassword , svScriptFile)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svLine , szConnString , szSQL , svString;
NUMBER nResult , nError;
LIST listID;
begin
try
// Create an empty string list .
listID = ListCreate (STRINGLIST);
// Read the SQL script file into the list
if (ListReadFromFile(listID , svScriptFile) < 0 ) then // read list from file
MessageBox ( " ERROR: Unable to open SQL script: " + svScriptFile + " . " , SEVERE);
nError = 1 ;
return nError;
endif;
// Go through each list item and add it to a string (which will then hold the script)
szSQL = "" ;
nResult = ListGetFirstString (listID , svString);
while (nResult = 0 )
szSQL = szSQL + " " + svString;
nResult = ListGetNextString (listID , svString);
endwhile;
// Be good and clean up your trash
ListDestroy(listID);
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = szSQL;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_ExecuteSQLScript " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AttachDatabase
//
// Purpose : This function will attach a database to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AttachDatabase(svServerName , svDatabaseName , svDatabasePath , svLogPath , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_attach_db ' "
+ svDatabaseName
+ " ',' "
+ svDatabasePath
+ " ',' "
+ svLogPath
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AttachDatabase " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AttachDatabase
//
// Purpose : This function will attach a database to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_RestoreDatabase(svServerName , svDatabaseName ,
svDatabasePath , svSaveDataPath , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " RESTORE DATABASE " + svDatabaseName + " FROM DISK=' "
+ svDatabasePath
+ " ' WITH MOVE 'xhotel_data' TO ' "
+ svSaveDataPath ^ svDatabaseName + " .mdf', "
+ " MOVE 'xhotel_log' TO ' "
+ svSaveDataPath ^ svDatabaseName + " .ldf' " ;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AttachDatabase " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AddLogin
//
// Purpose : This function will add a login to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AddLogin(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_addlogin ' "
+ svUserName
+ " ',' "
+ svUserPassword
+ " ',' "
+ svDatabaseName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AddLogin " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
// 第三部分
///
//
// Function : DB_AddSrvRoleMember
//
// Purpose : This function will add a login to a server .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AddSrvRoleMember(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svRole)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_addsrvrolemember ' "
+ svUserName
+ " ',' "
+ svRole
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AddSrvRoleMember " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_DropLoginNoException
//
// Purpose : This function will drop a login , but not throw an exception if it
// fails .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_DropLoginNoException(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svUserPassword)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_droplogin ' "
+ svUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// No exception thrown for this function ... this is a try only
/*
ErrorHandler(Err . Number , " DB_AddLogin " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
*/
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_GrantDBAccess
//
// Purpose : This function grant access to a database .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_GrantDBAccess(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svUserName , svDBUserName)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_grantdbaccess N' "
+ svUserName
+ " ',N' "
+ svDBUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_GrantDBAccess " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_AddRoleMember
//
// Purpose : This function grant access to a database .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_AddRoleMember(svServerName , svDatabaseName ,
svSAUsername , svSAPassword , svRole , svUserName)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + svDatabaseName;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_addrolemember N' "
+ svRole
+ " ',N' "
+ svUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_AddRoleMember " , Err . Description , WARNING);
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return FALSE;
endcatch;
set pADOObj = NOTHING;
set pADOCommObj = NOTHING;
return TRUE;
end;
///
//
// Function : DB_ChangePassword
//
// Purpose : This function changes a users password .
//
// Arguments :
//
// Usage :
//
// Notes :
//
///
function BOOL DB_ChangePassword(svServerName ,
svSAUsername , svSAPassword , svUserName , svOldPass , svNewPass)
OBJECT pADOObj , pADOCommObj;
STRING szADOObjID , szADOCommObjID;
STRING svCommand , szConnString , szSQL , svString;
NUMBER nResult;
begin
try
// Fix NULLS
if svOldPass = "" then
svOldPass = " NULL " ;
endif;
if svNewPass = "" then
svNewPass = " NULL " ;
endif;
// Create ADO Connection Object to connect to the SQL server
szADOObjID = " ADODB.Connection " ;
set pADOObj = CreateObject(szADOObjID);
// Create the SQL string to complete the connection
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svSAUsername + " ; " ;
szConnString = szConnString + " pwd= " + svSAPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
// Open the ADO Connection
pADOObj . Open (szConnString);
svCommand = " sp_password "
+ svOldPass
+ " , "
+ svNewPass
+ " ,' "
+ svUserName
+ " ' "
;
// Create the ADO Command object to execute the script
szADOCommObjID = " ADODB.Command " ;
set pADOCommObj = CreateObject(szADOCommObjID);
pADOCommObj . ActiveConnection = pADOObj;
// Execute the call to run the script
pADOCommObj . CommandText = svCommand;
pADOCommObj . Execute();
catch
// ErrorHandler(Err . Number , " DB_ChangePassword " , Err . Description , WARNING);
set pADOCommObj = NOTHING;
set pADOObj = NOTHING;
return FALSE;
endcatch;
set pADOCommObj = NOTHING;
set pADOObj = NOTHING;
return TRUE;
end;
// Function : BOOL DB_GetInstances()
// Description : Checks for an Instance of SQL Server , returns a list of
// of instances if true .
//
// Created By : BrianR
// Created Date : 11 - 12 - 2002
function BOOL DB_GetInstances(lInstances)
STRING szKey , szInstanceName;
NUMBER nRet;
begin
// Set default reg root
RegDBSetDefaultRoot(HKEY_LOCAL_MACHINE);
szKey = " SOFTWARE/MICROSOFT/Microsoft SQL Server " ;
// If key isnt there ... fail
if RegDBKeyExist(szKey) != 1 then
// fail
return FALSE;
endif;
// Create INSTANCE Lists
szInstanceName = " Test1 " ;
ListAddString(lInstances , szInstanceName , AFTER);
szInstanceName = " Test2 " ;
ListAddString(lInstances , szInstanceName , AFTER);
// Return
if ListCount(lInstances) != 0 then
return ListCount(lInstances);
else
return FALSE;
endif;
end;
function BOOL DB_GetUserDataBase(svServerName , svUserName , svUserPassword , sbWinAuth , listDatabase)
OBJECT pADOConnObj , pADORecordSetObj;
STRING szADOConnObjID , szADORecordSetObjID , szConnString , szSQL;
NUMBER nResult;
begin
try
// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = " ADODB.Connection " ;
set pADOConnObj = CreateObject(szADOConnObjID);
// Create the SQL string to complete the connection
if (sbWinAuth = FALSE) then
szConnString = " driver={ " + " SQL Server " + " }; " ;
szConnString = szConnString + " server= " + svServerName + " ; " ;
szConnString = szConnString + " uid= " + svUserName + " ; " ;
szConnString = szConnString + " pwd= " + svUserPassword + " ; " ;
szConnString = szConnString + " database= " + " MASTER " ;
else
szConnString = " Provider=SQLOLEDB.1;Integrated Security=SSPI; " ;
szConnString = szConnString + " Persist Security Info=False; " ;
szConnString = szConnString + " Initial Catalog=MASTER;Data Source= " ;
szConnString = szConnString + svServerName;
endif;
// Open the ADO Connection
pADOConnObj . Open (szConnString);
// Create ADO Recordset object for the return
szADORecordSetObjID = " ADODB.Recordset " ;
set pADORecordSetObj = CreateObject(szADORecordSetObjID);
// Set some ADO Recordset properties
pADORecordSetObj . CursorType = 3 ;
pADORecordSetObj . ActiveConnection = pADOConnObj;
// Create the SQL string to retrieve the database if it exists
szSQL = " Select name from sysdatabases where name not in('master') " ;
// Use the recordset to see if the database exists
pADORecordSetObj . Open (szSQL);
while ( ! pADORecordSetObj . EOF ())
if (ListAddString(listDatabase , pADORecordSetObj( " name " ) , AFTER) < 0 ) then
MessageBox( " adsasd " , INFORMATION);
endif;
pADORecordSetObj . MoveNext();
endwhile;
catch
// ErrorHandler(Err . Number , " 检索数据库失败 " , Err . Description , WARNING);
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return FALSE;
endcatch;
set pADOConnObj = NOTHING;
set pADORecordSetObj = NOTHING;
return TRUE;
end;