Link to home
Create AccountLog in
Avatar of AmyL
AmyL

asked on

Connecting Native C / C++ to SQL Server

I am trying to make a connection from a C / C++ application to a SQL Server database. I am using Visual Studio 2013, but the version of SQL Server is quite old. (I'm not sure how to tell which version it is, though...I know that the SSMS version is 8).

This line:
"SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL)"
populates sqlstate with 0011E434 and message with 0011EC3C. I can't seem to find any information on what those codes mean.

What is the correct way for me to establish a DB connection?

Thanks,
Amy
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

I'm not sure how to tell which version it is, though
Open SSMS and connect to SQL Server instance and open a new query and run the following command:
SELECT @@VERSION

Open in new window


What is the correct way for me to establish a DB connection?
You may need to post here the portion of code you're using for connecting to SQL Server so we can help you better.
Avatar of AmyL
AmyL

ASKER

Thank you!
The version (per the above query) is: Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)

My connection code looks like this:

SQLHANDLE sqlenvhandle = NULL;
SQLHANDLE sqlconnectionhandle = NULL;
SQLHANDLE sqlstatementhandle = NULL;
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*)"DRIVER={SQL Server};SERVER=localhost;DATABASE=Prod;UID=user;PWD=pass;",
		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 testtable", 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;
		}
	}

FINISHED:
	if (sqlstatementhandle != NULL) {
		SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle);
	}
	if (sqlconnectionhandle != NULL) {
		SQLDisconnect(sqlconnectionhandle);
		SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
	}
	if (sqlenvhandle != NULL) {
		SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
	}

Open in new window

You really have a very old SQL Server instance. MSSQL 2000 isn't supported anymore.
Can you see in the Event Log for a more clear error message?

You can try to use System.Data.SqlClient namespace for work with this old version of SQL Server.
Avatar of AmyL

ASKER

I don't see anything obvious in the logs. I will look into SqlClient.
you can try to connect by ODBC.

open the odbcad32.exe applet and try to create a system datasource pointing to your database.

if that works you can access the database by means of this odbc datasource for example by using mfc CDatabase and CRecordset classes.

Sara
Avatar of AmyL

ASKER

I was able to create a System DSN and test it manually, so I decided to try the CDatabase route. I put some MFC CDatabase code in but I have a couple of questions. The first is: What do I do about windows.h for MFC? I get a compilation error if I include it (saying not to include it for MFC), but I also get compilation errors if I remove it. Also, what should my dsn connection string look like? I found several for Access in CDatabase examples, but didn't really find much for SQL Server. My guess was something like this:

sDsn.Format((SQLWCHAR*)"ODBC;DRIVER={%s};Server=%s;Database=%s;UID=user;PWD=pass;", sDriver, sMc, sFile);

Thanks!
Amy
You have an ODBC namespace: System.Data.Odbc
for mfc sources you normally would include stdafx.h which then includes windows.h. that is independent from using precompiled headers (PCH) via stdafx.h or not. if you turn a win32 program to use mfc, the existing stdafx.h perhaps doesn't include afx.h (or atlafx.h) what is the main header of mfc. the easiest is to create a dummy mfc project (type mfc application) and copy the stdafx.h of this new project to your current project. save the old stdafx.h - if any - somewhere, but actually there are no problems to expect by doing so. after that include the new stdafx.h at top of all your .cpp files (regardless of PCH switched on or off)

what should my dsn connection string look like?
you may try with "ODBC;<dsnname>" what could be sufficient if your datasource already was working.

UID and PWD is ok but I would not add too many other information which may require special syntax or refer to not existing drivers or symbols. if you have a valid odbc datasource you should be able to connect with a minimal connect string.

sDsn.Format((SQLWCHAR*)"ODBC;DRIVER={%s};Server=%s;Database=%s;UID=user;PWD=pass;", sDriver, sMc, sFile);
to cast a char * to SQLWCHAR* probably isn't a good idea. I would configure the mfc project with multi-byte character set rather than with Unicode. but if your gui is Unicode you also could switch to Unicode. if that is the case you should use the L prefix to using wide character literals or use the _T or TEXT macros provided. don't mix wide and narrow characters if it is not absolutely necessary.

note, by using the class wizard (available via the resource editor) you can create a new CRecordset derived class where you would be able to connect to your datasource and if that worked, the wizard would create a class that has a valid connect string in member function GetDefaultConnect.

Sara
Avatar of AmyL

ASKER

Thank you very much! I took your advice and created/configured an MFC application, then copied the stdafx header from that and placed it in my win32 application. Everything thing seems to work almost perfectly, except for one small thing...

This application needs to run from a batch script, so no dialogs, etc. I can't seem to keep the DSN dialog from popping up when using the CDatabase. I'm guessing there is still something wrong with my connection string. Here is what I have:

sDsn.Format("ODBC;DSN='MyDSN';UID=user;PWD=pass;");
	database.noOdbcDialog;
if (database.Open("MyDSN", false, false, sDsn))
	{
		//success
	}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of sarabande
sarabande
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of AmyL

ASKER

Thank you for the excellent help!