//第一部分 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_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); /////////////////////////////////////////////////////////////////////////////// // //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\\tables.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 sc[FS:PAGE]ripts //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; 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_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,svDatabaseNa[FS:PAGE]me, 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;
(本站) |