SQL Server via C++ Error: Incorrect syntax near '0x2a20'

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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:

C++

#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;
}

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?

Posted 25-Jul-14 4:19am

ASINewbie

Add a Solution

Comments

KarstenK 26-Jul-14 4:04am

It looks like some string mismatch. Work detailed through the different string types.

Always create a instance of the right type. Avoid Type casts UNLESS YOU ARE REALLY sure!!!

And I repeat again: if you provide code write a comment where the error is happening. In my professional code I write an output on every problematic function.

ASINewbie 28-Jul-14 10:13am

i can't figure out where the error is. The code builds properly, I can launch it, it communicates with the server, connects to the database, then returns that error. given that, i think the error is in the query coding being sent to the server:

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

I found an article online, some time after I posted the question. I commented on the other answer given about this. I'm relatively inexperienced, five years ago when I was at the top of my own personal programming game, this task would have been a bit beyond me an would have taken a lot of work, learning, and would have likely taken more time than I have now to finish it. The article I found gave a fix that supposedly works, but at the level I'm at now, I have no clue how to actually use this to fix my problem. I'm quickly relearning everything, but I simply don't have the kind of time I would need. The solution is to convert from multi-byte to unicode by:

char sz[] = "DRIVER={SQL Server};database=..;server=..;uid=.;pwd=..;connect timeout=60;";
wchar_t wsz[256] = { 0 };
int n = MultiByteToWideChar(CP_APC, MB_ERR_INVALID_CHARS, sz, -1, wsz, 256);

If you could show me how to integrate this into my code, it would be a great help. I can't ask on the board I found it on. If I create a free account, I can't see the solution anymore when logged in and it's more than $100 for a paid account... I don't make that kind of money and the company won't pay for it because we usually don't do this sort of work.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-07-25T04:50:00

Solution 1

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

C++

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

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:

C++

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.

Posted 25-Jul-14 4:50am

OriginalGriff

Comments

ASINewbie 25-Jul-14 13:58pm

I tried to go off the example, but it doesn't quite work. I think it casts more through the code that disagrees with the changes. I got this error:

error C2664: 'SQLExecDirectW' : cannot convert parameter 2 from 'const char [20]' to 'SQLWCHAR *'

I found a solution that supposedly will fix the problem, but I haven't touched C++ (or any programming language) in years before the start of this week. The solution is to convert from multi-byte to unicode by:

char sz[] = "DRIVER={SQL Server};database=..;server=..;uid=.;pwd=..;connect timeout=60;";
wchar_t wsz[256] = { 0 };
int n = MultiByteToWideChar(CP_APC, MB_ERR_INVALID_CHARS, sz, -1, wsz, 256);

How would I implement this into my existing code correctly without messing it up any more?