心得笔记【4】

最新推荐文章于 2024-11-18 14:51:17 发布

流浪的青春

最新推荐文章于 2024-11-18 14:51:17 发布

阅读量299

点赞数

文章标签： sql server object string sql database command

本文链接：https://blog.csdn.net/qingfengbj/article/details/6831150

版权

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 list
	if (ListReadFromFile(listID, svScriptFile) < 0) then // read list from file
		svErrLog = "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 comments
			bExitLoop = FALSE;
			repeat
				nBegin = StrFind(szSQL, "/*");
				nEnd = StrFind(szSQL, "*/");
				if ((nBegin >= 0) && (nBegin < nEnd)) then
					StrSub(szHead, szSQL, 0, nBegin);
					StrSub(szTail, szSQL, nEnd+2, 65535);
					szSQL = szHead + szTail;
				else
					bExitLoop = TRUE;
				endif;
			until (bExitLoop);
			// Add it to commands
			if (StrLengthChars(szSQL) > 0) then
				ListAddString(listCmd, szSQL, AFTER);
			endif;
			szSQL = "";
		elseif (StrFind(svString, "--") = 0) then
			// It is a comment, so do nothing
		else
			szSQL = szSQL + "\r\n" + svString; 
		endif;
		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={" + 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 command
	nResult = ListGetFirstString(listCmd, svString);
	while (nResult = 0)
		try
			// Execute the call to run the script
			pADOCommObj.CommandText = svString;
			pADOCommObj.Execute(nRecords ,0x81);
		catch
			nRet = 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 trash
	ListDestroy(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 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();	
		
	return TRUE; 		
end;