Execute isqlw (SQL query analyzer) using DOS relocation Technology)

Source: Internet
Author: User

The author previously needs to call the SQL query analyzer in a project and return the execution result through the command line. So the author adopted the DOS relocation technology to solve the problem. Now we will briefly explain how to use VC ++ for implementation.
Use a command line to call the SQL Analyzer
The isqlw utility (SQL query analyzer) allows you to enter Transact-SQL statements, system stored procedures, and script files. By setting shortcuts or creating batch files, you can start the pre-configured SQL query analyzer [12]. The following table lists the dependency files of isqlw.exe:
Table 6-1

Pfclnt80.dll pfuttl80.dll semsfc. dll sqlgui. dll sqlqry. dll
Sqlresld. dll sqlsvc. dll sqlunirl. dll OBJMGR. DLL
Directory Resources Directory 1033 Isqlw. rll pfclnt80.rll pfuttl80.rll semsfc. rll
Sqlgui. rll sqlqry. rll sqlsvc. rll OBJMGR. RLL
Directory 2052 Isqlw. rll pfclnt80.rll pfuttl80.rll semsfc. rll
Sqlgui. rll sqlqry. rll sqlsvc. rll OBJMGR. RLL


Syntax parameters for Isqlw running are as follows:
Syntax
Isqlw
[-?] |
[
[-S server_name [\ instance_name]
[-D database]
[-E] [-U user] [-P password]
[{-I input_file} {-o output_file} [-F {U | A | O}]
[-F file_list]
[-C configuration_file]
[-D scripts_directory]
[-T template_directory]
]
Parameters
-?
Display usage information.
-S server_name [\ instance_name]: Specifies the Microsoft SQL Server 2000 instance to connect.
-D database: When isqlw is started, a USE database statement is issued. The default value is your default database.
-E uses a trusted connection without requesting a password.
-U user Logon ID. The logon ID is case sensitive. -P password is the logon password. The default value is NULL.
-I input_file identifies the files that contain a batch of SQL statements or stored procedures. -I and-o options must be specified at the same time. If the-I and-o options are specified, the query in the input file is executed and the results are saved to the output file. User interfaces are not displayed during query execution. After the execution is complete, the process exits. -O output_file identifies the file that receives output from isqlw. -I and-o options must be specified at the same time. The usage is the same as-I.
-F {U | A | O} is the format of the input and output files. Values include Unicode, ANSI, and OEM. If-F is not specified, the automatic mode is used (if the file is in Unicode format, it is opened in Unicode format; otherwise, the file is opened in ANSI format ).
-F file_list: load the listed files to the SQL query analyzer. Use the-f option to load one or more files (separated by a single space ). If multiple files are specified, these files are opened in the same connection context. The file name can contain the directory path where the file resides. Wildcards can be used, such as asterisks (*) in C: \ *. SQL (*).
-C configuration_file uses the settings specified in the configuration file. Other parameters explicitly specified in the command prompt will overwrite the settings in the corresponding configuration file.
-D scripts_directory: rewrite in the registry or use
-C: The default storage Script directory specified in the configuration file. This value is not retained in the registry or configuration file. To view the current value of this option in the SQL query analyzer, click the "Tools" menu and then click the "options" command.
-T template_directory: rewrite the default template directory specified in the registry or in the configuration file specified by-C. This value is not retained in the registry or configuration file. To view the current value of this option in the SQL query analyzer, click the "Tools" menu and then click the "options" command. The ISQLW call method uses a trusted connection to call Isqlw in the following format:
Isqlw.exe-S ""-d "master"-E
The format of calling Isqlw using SQL authentication connection is as follows:
Isqlw.exe-S ""-d "ii"-U "sa"-P ""
Execute SQL scripts from the background using command lines
Isqlw.exe-S ""-d "ii"-U "sa"-P ""-I "c: \ 1. SQL"-o "c: \ output.txt"
Parameter I is the SQL script to be executed, parameter o is the output execution result, and parameter I cannot be omitted.
Note: The parameter S is in upper case, the parameter "" After S indicates that the database server is the local machine, and the parameter E is in upper case.

6.3.2 execute isqlw using DOS relocation Technology
The preceding Method of Using isqlw in the command line is relocated in dos. The specific code is as follows:

[C ++]
CString CSQLDebug: ExecSQL ()
{HANDLE hMutex = NULL;
CString TmpStr;
HANDLE hRead = NULL;
CString StrSQLFile, StrSQLResult, CurrPath;
CurrPath = GetBasePath () + "\" + GetStudentID ();
StrSQLFile = CurrPath + "\" + GetStudentID () + "_ P" + GetID () + ". SQL"; // source code path
StrSQLResult = CurrPath + "\" + GetStudentID () + "_ P" + GetID () + ". txt"; // result path
CString Evn = GetBasePath () + "\ sqlDebug \ isqlw ";
SetCurrentDirectory (CurrPath );
SetEnvironment (); // sets the environment variable
ServerName. TrimLeft ("");
DataBaseName. TrimLeft ("");
UserPwd. TrimLeft ("");
UserName. TrimLeft ("");
CString StrServerName = "\" "+ ServerName + "\"";
CString StrDataBaseName = "\" "+ DataBaseName + "\"";
CString StrUserName = "\" "+ UserName + "\"";
CString StrUserPwd = "\" "+ UserPwd + "\"";
CString StrInput = "\" "+ StrSQLFile + "\"";
CString StrOutput = "\" "+ StrSQLResult + "\"";
CString command; //, TmpStr;
Command = "Cmd.exe/C isqlw-S" + StrServerName + "-d" +
StrDataBaseName + "-U" + StrUserName + "-P" +
StrUserPwd + "-I" + StrInput + "-o" + StrOutput;
Char * cmd = command. GetBuffer (command. GetLength ());
SECURITY_ATTRIBUTES sa;
HANDLE hWrite;
Sa. nLength = sizeof (SECURITY_ATTRIBUTES );
Sa. lpSecurityDescriptor = NULL;
Sa. bInheritHandle = TRUE;
If (! CreatePipe (& hRead, & hWrite, & sa, 0 ))
{
AfxMessageBox ("An error occurred while creating the MPs queue! ", MB_ OK );
Return "";
}
STARTUPINFO si;
PROCESS_INFORMATION pi;
Si. cb = sizeof (STARTUPINFO );
GetStartupInfo (& si );
Si. hStdError = hWrite; // redirects the standard error output of the creation process to the pipeline input.
Si. hStdOutput = hWrite; // redirects the standard output of the creation process to the pipe input.
Si. wShowWindow = SW_HIDE;
Si. dwFlags = STARTF_USESHOWWINDOW | STARTF_USESTDHANDLES;
If (! CreateProcess (NULL, cmd, NULL, NULL, TRUE, NULL, & si, & pi ))
{
CloseHandle (hWrite );
CloseHandle (hRead );
AfxMessageBox ("process creation failed! \ R please try to compile again! ", MB_ OK );
Return "";
}
CloseHandle (hWrite );
CloseHandle (hRead );
// Read the result
CString StrOutPut = ReadOutPutFile (StrSQLResult. GetBuffer (StrSQLResult. GetLength ()));
SetSaveOutPut (StrOutPut );
Return StrOutPut;
}


From chenjieb520-Technical blog of two forests

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.