SQL Server通过C ++错误：'0x2a20'附近的语法不正确 [英] SQL Server via C++ Error: Incorrect syntax near '0x2a20'

问题描述

我正在开发一个需要访问SQL数据库的项目（因为SQL Server无法自行执行各种操作）。所以我使用C ++连接使用以下代码：

I'm working on a project that requires access to a SQL database as well (as various actions that SQL Server cannot do on it's own). So I'm using C++ to connect using the following code:

#include "stdafx.h"

#include <iostream>
#include <windows.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <sql.h>
#include "database.h"

using namespace std;

int main(){
	databaseConnect();
    return 0;
}

void show_error(unsigned int handletype, const SQLHANDLE& handle){
    SQLWCHAR sqlstate[1024];
    SQLWCHAR message[1024];
    if(SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL))
        wcout<<L"Message: "<<message<<L"\nSQLSTATE: "<<sqlstate<<endl;
}

int databaseConnect(){

    SQLHANDLE sqlstatementhandle;
    SQLHANDLE sqlenvhandle;    
    SQLHANDLE sqlconnectionhandle;
    //SQLRETURN retcode;

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlenvhandle))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLSetEnvAttr(sqlenvhandle,SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)) 
        goto FINISHED;

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle, &sqlconnectionhandle))
        goto FINISHED;

    SQLWCHAR retconstring[1024];
    switch(SQLDriverConnect (sqlconnectionhandle, 
        NULL, 
        (SQLWCHAR*)TEXT("DRIVER={SQL Server};SERVER=serverName;DATABASE=dataName;UID=user;PWD=password;Trusted_Connection=no;"), 
        SQL_NTS, 
        retconstring, 
        1024, 
        NULL,
        SQL_DRIVER_NOPROMPT)){
    case SQL_SUCCESS_WITH_INFO:
        show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
        break;
    case SQL_INVALID_HANDLE:
    case SQL_ERROR:
        show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
        goto FINISHED;
    default:
        break;
    }

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle, &sqlstatementhandle))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLWCHAR*)"select * from test", SQL_NTS)){
        show_error(SQL_HANDLE_STMT, sqlstatementhandle);
        goto FINISHED;
    }
    else{
        char name[64];
        char address[64];
        int id;
        while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){
            SQLGetData(sqlstatementhandle, 1, SQL_C_ULONG, &id, 0, NULL);
            SQLGetData(sqlstatementhandle, 2, SQL_C_CHAR, name, 64, NULL);
            SQLGetData(sqlstatementhandle, 3, SQL_C_CHAR, address, 64, NULL);
            cout<<id<<" "<<name<<" "<<address<<endl;
        }
    }
    //scanf_s("...");


FINISHED:
    SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
    SQLDisconnect(sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
   
	
	return 0;
}

我终于设法连接到服务器并解决了登录问题，但现在我得到了这种语法错误：

消息：[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]'0x2a20'附近的语法不正确。

SQLSTATE：42000



如何更正此错误？



我在C ++上非常生疏，多年来没有上课，没有我需要使用它的工作，并且突然之间有这个项目在工作中推动我，因为我是我小公司中唯一一个有任何编程经验的人。任何帮助都是有用的。



作为第二个问题：

最终项目将连接到数据库，搜索添加或修改的条目自上次运行应用程序以来，将数据复制到.txt文件中，然后访问硬盘驱动器上的目录，扫描目录中的图像文件，查找自上次运行应用程序以来添加或修改的文件，生成新目录，然后将新/修改的图像和.txt文件复制到新目录中。所有这些都将实现自动化。有没有比通过SQL的C ++更简单的方法？

I finally managed to connect to the server and resolve the login issues, but now i'm getting this syntax error:
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '0x2a20'.
SQLSTATE: 42000

How can I correct this error?

I am very rusty on C++, not having had my classes in years and not having a job that I needed to use it in, and have all of a sudden had this project pushed on me at work since I'm the only one in my small company with any programming experience. Any help would be useful.

As a secondary question:
The final project will connect to a database, search for entries added or modified since the last time the application ran, copy the data into a .txt file, and then access a directory on a harddrive, scan the image files in the directory for files added or modified since the last time the application ran, generate a new directory, then copy the new/modified images and the .txt file into the new directory. And all this will be automated. Is there an easier way to do this than C++ going through SQL?

推荐答案

2A Hex是'*'字符的ASCII / Unicode值，20 Hex是空间，所以我怀疑在这一行：

2A Hex is the ASCII/Unicode value for the '*' character, and 20 Hex is space, so I suspect that in this line:

if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLWCHAR*)"select * from test", SQL_NTS))

SQLWCHAR *演员正在弄乱数据。



快速查看MSDN示例：http://msdn.microsoft.com/en-us/library/ms715441(v=vs.85).aspx [ ^ ] （在页面底部）显示他们调用它而不涉及强制转换：

the SQLWCHAR* cast is messing up the data.

A quick look at the MSDN samples: http://msdn.microsoft.com/en-us/library/ms715441(v=vs.85).aspx[^] (At the bottom of the page) shows them calling it with no cast involved:

retcode = SQLExecDirect(hstmt,
   "SELECT CUSTID, NAME, PHONE FROM CUSTOMERS ORDER BY 2, 1, 3",
   SQL_NTS);

所以我试着复制这个例子，看看怎么回事。

So I'd try duplicating the example and see how it went.

这篇关于SQL Server通过C ++错误：'0x2a20'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！