在vc中如何使用 SQL Script File

Question

在vc中如何使用 SQL Script File,我已经编写好的脚本

Answer 1

能不能这样：
在vc中用打开文本文件的方式（fopen)打开脚本文件。
然后在vc中执行她的语句。

Answer 2

你最好建立的基于WINDOWS格式的脚本
WINDOWS格式的脚本是以GO来区分脚本语句的
然后打开你需要的SQL Script File
在建立了数据连接以后就可以根据读取的脚本以GO来划分 
一条一条SQL语句的执行了   FILE *hFile=NULL;
//    CHAR pbCommand[8192];
char *pbCommand;
DWORD dwCmdLen;
    BYTE ch;
int j;
//BOOL BeginExecute( IDBInitialize *pIDBInitialize=NULL;
IDBCreateSession*    pIDBCreateSession=NULL;
    IDBCreateCommand*    pIDBCreateCommand=NULL;
ICommandText*        pICommandText=NULL; IDBProperties*       pIDBProperties     = NULL;
DBPROP               InitProperties[4];
DBPROPSET            rgInitPropSet[1];
HRESULT              hr;
ULONG                i;
WCHAR                wszBuff[256];      // String Buffer (WCHAR)
// WCHAR                wCmdString[200]=OLESTR("insert into SeedPublics.dbo.mytest(id1,name1) values('1','");

IRowset*             pIRowset           = NULL; //Initialize the COM library.
    CoInitialize(NULL);    //Obtain access to the SQLOLEDB provider.
    hr = CoCreateInstance(&CLSID_SQLOLEDB, 
                          NULL, 
                          CLSCTX_INPROC_SERVER,
                          &IID_IDBInitialize, 
                          (void **) &pIDBInitialize);
    if(FAILED(hr))
    {
//printf("Failed to get IDBInitialize interface.\n");
goto done;
    } //end if
    
    //Initialize the property values needed 
    //to establish the connection.    for(i = 0; i < 4; i++) 
        VariantInit(&InitProperties[i].vValue);
    //Server name.
    InitProperties[0].dwPropertyID  = DBPROP_INIT_DATASOURCE;
    InitProperties[0].vValue.vt     = VT_BSTR;
    MultiByteToWideChar(CP_ACP, 0, gbServer, -1, wszBuff, 512);
    InitProperties[0].vValue.bstrVal= SysAllocString(wszBuff);
    InitProperties[0].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[0].colid         = DB_NULLID;
//Database.
    InitProperties[1].dwPropertyID  = DBPROP_INIT_CATALOG;
    InitProperties[1].vValue.vt     = VT_BSTR;
    MultiByteToWideChar(CP_ACP, 0, gbDatabase, -1, wszBuff, 512);
    InitProperties[1].vValue.bstrVal= SysAllocString(wszBuff);
    InitProperties[1].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[1].colid         = DB_NULLID;
//Username (login).
    InitProperties[2].dwPropertyID  = DBPROP_AUTH_USERID; 
    InitProperties[2].vValue.vt     = VT_BSTR;
    MultiByteToWideChar(CP_ACP, 0, gbLogName, -1, wszBuff, 512);
    InitProperties[2].vValue.bstrVal= SysAllocString(wszBuff);
    InitProperties[2].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[2].colid         = DB_NULLID;
//Password.
    InitProperties[3].dwPropertyID  = DBPROP_AUTH_PASSWORD;
    InitProperties[3].vValue.vt     = VT_BSTR;
    MultiByteToWideChar(CP_ACP, 0, gbPassword, -1, wszBuff, 512);
    InitProperties[3].vValue.bstrVal= SysAllocString(wszBuff);
InitProperties[3].dwOptions     = DBPROPOPTIONS_REQUIRED;
    InitProperties[3].colid         = DB_NULLID;//    Now that the properties are set, construct the DBPROPSET structure
//    (rgInitPropSet). The DBPROPSET structure is used to pass an array 
//    of DBPROP structures (InitProperties) to the SetProperties method.    rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
    rgInitPropSet[0].cProperties    = 4;
    rgInitPropSet[0].rgProperties   = InitProperties;
    //Set initialization properties.    hr = pIDBInitialize->lpVtbl->QueryInterface(pIDBInitialize,&IID_IDBProperties, 
                                   (void **)&pIDBProperties);
    if(FAILED(hr))
    {
     //printf("Failed to get IDBProperties interface.\n");
goto done;
    }
    hr = pIDBProperties->lpVtbl->SetProperties(pIDBProperties,1, rgInitPropSet); 
    if(FAILED(hr))
    {
        //printf("Failed to set initialization properties.\n");
goto done;
    } //end if   pIDBProperties->lpVtbl->Release(pIDBProperties);
   pIDBProperties=NULL;
    //Now establish the connection to the data source.
    if(FAILED(pIDBInitialize->lpVtbl->Initialize(pIDBInitialize)))
    {
        //printf("Problem in establishing connection to the data source.\n");
goto done;
    }
    //Create a session object.
    if(FAILED(pIDBInitialize->lpVtbl->QueryInterface(pIDBInitialize,&IID_IDBCreateSession, (void**) &pIDBCreateSession)))
    {
        //printf("Failed to obtain IDBCreateSession interface.\n");
goto done;
    }    if(FAILED(pIDBCreateSession->lpVtbl->CreateSession(pIDBCreateSession,
                                     NULL, 
                                     &IID_IDBCreateCommand, 
                                     (IUnknown**) &pIDBCreateCommand)))
    {
        //printf("pIDBCreateSession->CreateSession failed.\n");
goto done;
    }
  
    //Access the ICommandText interface.
    if(FAILED(pIDBCreateCommand->lpVtbl->CreateCommand(pIDBCreateCommand,
                                    NULL, 
                                    &IID_ICommandText, 
                                    (IUnknown**) &pICommandText)))
    {
        //printf("Failed to access ICommand interface.\n");
goto done;
    } //if(!BeginExecute(pIDBInitialize,pIDBCreateSession,pIDBCreateCommand,pICommandText))
// return FALSE; hFile=fopen(szFilePath,"rb");
if(hFile==NULL)
return FALSE; dwCmdLen=0;
j=1;
pbCommand=(char *)malloc(1000);
while(!feof(hFile))
{
       if (dwCmdLen>0 && dwCmdLen%1000==0) 
   {
   j+=1;
   pbCommand=(char *)realloc(pbCommand,j*1000);
   }
   fread(&ch,1,1,hFile);
   memcpy(pbCommand+dwCmdLen,&ch,1);
   dwCmdLen+=1;
       //pbCommand[dwCmdLen++]=ch;
   //if(dwCmdLen>=8192)
//   goto done;
     
   if(ch == 0x3b)
   {   
          
   if(pbCommand[dwCmdLen-3]==0x3b) 
           {   pbCommand[dwCmdLen-3]='\0';
   //i=lstrlen(pbCommand);
   ExecuteCmd(pICommandText,pbCommand);
          dwCmdLen=0;
   free(pbCommand);
   pbCommand=(char *)malloc(1000);
   j=1;
   }
   }
   
}
free(pbCommand);
fclose(hFile);
//EndExecute(pIDBInitialize,pIDBCreateSession,pIDBCreateCommand,pICommandText); return TRUE;done:
    if(hFile)
fclose(hFile);
if(pICommandText)
        pICommandText->lpVtbl->Release(pICommandText);
if(pIDBCreateCommand)
        pIDBCreateCommand->lpVtbl->Release(pIDBCreateCommand);
if(pIDBCreateSession)
        pIDBCreateSession->lpVtbl->Release(pIDBCreateSession);
    if(pIDBProperties)
pIDBProperties->lpVtbl->Release(pIDBProperties);
if(pIDBInitialize)
pIDBInitialize->lpVtbl->Release(pIDBInitialize);
    //Release the COM library.
    CoUninitialize();    return FALSE;

Answer 3

脚本大概样例是这样的
可以把它作为资源字符串执行，
但是如果脚本又几千行，那还是必须使用SQL Script File
问题就是如何使用 SQL Script Filecreate database My_DB
 ON (NAME=My_dat,
FILENAME='E:\temp\myDB.mdf',
size=100,
maxsize=1024,
Filegrowth=100)
 LOG ON (NAME=My_log,
FILENAME='E:\temp\myDB.ldf',
size=40,
maxsize=100,
filegrowth=10)

Answer 4

象这样的脚本 你应该使用一个特殊符号来标识一条SQL语句的结束
在我给你源代码里 使用的OLEDE连接SQL 在SQL语句中是用GO;来区分每一条SQL语句
分开每一条语句 就可以EXECUTE sql语句了

Answer 5

to  mfkzj() 
谢谢
代码比直接使用资源字符串长太多了，我研究一下怎么使用
使用CDatabase::ExecuteSQL( strSQL ) 可以实现，因为他就一句话，相比之下太长了:-)

Answer 6

isqlw 实用工具（SQL 查询分析器）使您得以输入 Transact-SQL 语句、系统存储过程和脚本文件。通过设置快捷方式或创建批处理文件，可以启动预配置的 SQL 查询分析器。语法
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]
    ]

Answer 7

你只需要注意一下循环读取文件的部分 呵呵 我这是OLEDB连接SQL数据库的程序 所以大了点

Answer 8

#include <afxwin.h>         // MFC core and standard components
#include <afxext.h>         // MFC extensions
#include <afxdisp.h>        // MFC Automation classes
#include <afxdtctl.h>
#include <adoid.h>
#include <adoint.h>
#include <icrsint.h>
#include <atlbase.h>
#include <adoid.h>
#include <adoint.h>
const IID IID_IADOConnection = 
{0x00000550,0x0000,0x0010,{0x80,0x00,0x00,0xAA,0x00,0x6D,0x2E,0xA4}};
const CLSID CLSID_CADOConnection = 
{0x00000514,0x0000,0x0010,{0x80,0x00,0x00,0xAA,0x00,0x6D,0x2E,0xA4}};
BOOL ExecuteDBScript(LPCTSTR szFilePath,LPCTSTR conStr)
{
    FILE *hFile=NULL;
// CFile files;
// CFileException fe;
    CHAR pbCommand[2048];
DWORD dwCmdLen;
    BYTE ch;
int j;
HRESULT              hr;
ADOConnection* m_Conn;
CString Tempstr,Tempstr1;
CComVariant   MyTag;
//Initialize the COM library.
// unsigned char * imgtemp;
// HANDLE filedate;
// DWORD dwBitsSize=1024;
    CoInitialize(NULL);
hr = ::CoCreateInstance((REFCLSID)CLSID_CADOConnection,NULL,CLSCTX_INPROC_SERVER,\
(REFIID)IID_IADOConnection,(LPVOID*)&m_Conn);
 if(FAILED(hr))
    {
goto done;
    } 
if(m_Conn==NULL)
return FALSE;
hr = m_Conn->Open(CComBSTR(conStr),CComBSTR(""),CComBSTR(""),adOpenUnspecified);
if(FAILED(hr))
{
m_Conn->Release();
return FALSE;
}

hFile=fopen(szFilePath,"rb");
if(hFile==NULL)
return FALSE;
dwCmdLen=0;
j=0;
while(!feof(hFile))
{
       fread(&ch,1,1,hFile);
// Tempstr.Format("%s",ch);
// Tempstr1=Tempstr1+Tempstr;
           pbCommand[dwCmdLen++]=ch;
   if(dwCmdLen>=1024)
   goto done;
     
   if(ch == 0x4f)
   {   
          
   if(pbCommand[dwCmdLen-2]==0x47) 
           {   
   pbCommand[dwCmdLen-2]='\0';
//    for(j=0;j<dwCmdLen-3;j++)
// Tempstr=Tempstr+ pbCommand[j];

   MyTag.Clear();
if(m_Conn)
m_Conn->Execute(CComBSTR(pbCommand),&MyTag,-1,NULL);
dwCmdLen=0;
   }
   }
   
}
fclose(hFile);
if(m_Conn)
{
m_Conn->Close();
m_Conn->Release();
} 
CoUninitialize();
return TRUE;done:
    if(hFile)
fclose(hFile);
if(m_Conn)
{
m_Conn->Close();
m_Conn->Release();
}
    //Release the COM library.
    CoUninitialize();    return FALSE;
}
这个是ADO连接的 你可以试试这个

Answer 9

to  mfkzj(): 
搞定了
LPCTSTR szCnn( "DSN=MyDSN;Database=Pubs;UID=sa;PWD=1;" ) ;
LPCTSTR  szFilePath = _T( "E:\\delete\\ddd\\Me.sql" ) ;ExecuteDBScript(szFilePath , szCnn) ;由于这几天在作其他几个东西，所以今天才结贴，见谅