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
AmyLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
AmyLAuthor Commented:
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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AmyLAuthor Commented:
I don't see anything obvious in the logs. I will look into SqlClient.
0
sarabandeCommented:
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
0
AmyLAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have an ODBC namespace: System.Data.Odbc
0
sarabandeCommented:
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
0
AmyLAuthor Commented:
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

0
sarabandeCommented:
database.noOdbcDialog;
what is the purpose of this statement? do you want to assign 'true' to the 'member'? noOdbcDialog is an enum constant which can be passed in dwOptions when using CDatabase::OpenEx

if (database.Open("MyDSN", false, false, sDsn))
you may try either

if (database.Open("MyDSN", FALSE, FALSE, "ODBC;UID=user;PWD=pass))

Open in new window

or

if (database.Open(NULL, FALSE, FALSE, sDSN)

Open in new window

or

if (database.OpenEx(sDSN, CDatabase::noOdbcDialog)

Open in new window



if the odbc dialog pops up the database is not quite happy with the credentials. you may check whether the 'user' needs some domain, schema or group name as a prefix. unfortunately I never connected to sqlserver but if you look for 'sqlserver odbc connect string' you should get a lot of working syntax.


I can't seem to keep the DSN dialog from popping up when using the CDatabase.

in the remarks section of  https://msdn.microsoft.com/en-us/library/c689y99f.aspx they explained that the DSN dialog pops up when the connect string (or the information stored with the datasource) is not sufficient. if you still have a disc of the database setup, you may look whether they added readme text how the connect string should be configured. it is even possible that there is an odbc driver included which is better than you were using now.

Sara
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AmyLAuthor Commented:
Thank you for the excellent help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.