心得笔记【4】

1，如何执行sql文件

说明：

svDriver一般可写成“SQL Server”。

例子：

ExecuteSQLScript("192.168.24.104", "ELCDB", "SQL Server", "sa", "saPassword", "C:\Scripts\tables.sql", svLog)

 

function BOOL ExecuteSQLScript(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword, svScriptFile, svErrLog)OBJECT pADOObj, pADOCommObj;STRING szADOObjID, szADOCommObjID;STRING svLine, szConnString, szSQL, svString;STRING szHead, szTail;NUMBER nResult, nRecords, nBegin, nEnd;BOOL   bExitLoop;LIST   listID;LIST   listCmd;number nRet;string szErrorLog;begin     szErrorLog = "";nRet = 0; // Create an empty string list.listID = ListCreate(STRINGLIST);listCmd = ListCreate(STRINGLIST);// Read the SQL script file into the listif (ListReadFromFile(listID, svScriptFile) < 0) then // read list from filesvErrLog = "Unable to open SQL script: " + svScriptFile + ".";return -1;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)if (StrFind(svString, "GO") = 0) then// Remove commentsbExitLoop = FALSE;repeatnBegin = StrFind(szSQL, "/*");nEnd = StrFind(szSQL, "*/");if ((nBegin >= 0) && (nBegin < nEnd)) thenStrSub(szHead, szSQL, 0, nBegin);StrSub(szTail, szSQL, nEnd+2, 65535);szSQL = szHead + szTail;elsebExitLoop = TRUE;endif;until (bExitLoop);// Add it to commandsif (StrLengthChars(szSQL) > 0) thenListAddString(listCmd, szSQL, AFTER);endif;szSQL = "";elseif (StrFind(svString, "--") = 0) then// It is a comment, so do nothingelseszSQL = szSQL + "\r\n" + svString; endif;nResult = ListGetNextString(listID, svString);endwhile;// Be good and clean up your trashListDestroy(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 connectionszConnString = "driver={" + svDriver + "};";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 each commandnResult = ListGetFirstString(listCmd, svString);while (nResult = 0)try// Execute the call to run the scriptpADOCommObj.CommandText = svString;pADOCommObj.Execute(nRecords ,0x81);catchnRet = Err.Number;szErrorLog = szErrorLog+ "Error:  " + Err.Description + "\n"+ "Source: " + Err.Source + "\n"+ "Script:\n" + svString + "\n\n";endcatch;nResult = ListGetNextString(listCmd, svString);endwhile;// Be good and clean up your trashListDestroy(listCmd);svErrLog = szErrorLog;  return nRet;end;

2，如何执行sql语句

function BOOL CreateDatabase(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword) STRING szADOCommObjID, szADOObjID, szConnSting;OBJECT pADOObj, pADOCommObj;begin// Create ADO Connection Object to connect to the SQL server               szADOObjID = "ADODB.Connection";set pADOObj = CreateObject(szADOObjID);// Create the SQL string to complete the connectionszConnSting = "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 databasepADOCommObj.CommandText = "Create database " + svDatabaseName;pADOCommObj.Execute();return TRUE; end;