Specifying DSN names for the sqlconnectw function in ODBC

Question

0.00/5 (No votes)

See more:

The following is compiling and working:
rc = SQLConnect(ConHandle, (SQLWCHAR*)"dailyworkbook", SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);

The following is compiling but I get a run time access violation:
c = SQLConnect(ConHandle, (SQLWCHAR*)L"dailyworkbook", SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);

I am puzzled. Any insight would be helpful.

What I have tried:

Online help pages. Debugging in Visual studio.

Posted 14-Jun-18 2:35am

Member 13872723

Updated 15-Jun-18 0:39am

Add a Solution

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)

Jochen Arndt · Answer 1 · 2018-06-14T03:06:00

Solution 1

I don't know for sure why the access violation occurs but it is probably due to the UserName and Authentication parameters. When those are NULL, you should also pass zero instead of SQL_NTS for the length:

C++

c = SQLConnect(ConHandle, (SQLWCHAR*)L"dailyworkbook", SQL_NTS, NULL, 0, NULL, 0);

or pass empty strings which may use SQL_NTS or zero

C++

c = SQLConnect(ConHandle, (SQLWCHAR*)L"dailyworkbook", SQL_NTS, (SQLWCHAR*)L"", SQL_NTS, (SQLWCHAR*)L"", SQL_NTS);

If you have to use char or wide strings depends on your project settings. The above assumes that you have a Unicode project.

I can also only guess why you did not get the access violation with the first call but I think this is a potential explanation:
When having a Unicode build and passing a char string, the connect will fail because the server can not be found and the function returns before using the additional parameters.

Posted 14-Jun-18 3:06am

Jochen Arndt

Comments

Member 13872723 15-Jun-18 1:52am

I am now trying:
std::wstring dsn(L"dailyworkbook");
rc = SQLConnectW(ConHandle, (SQLWCHAR*)dsn.c_str(), dsn.length(), (SQLWCHAR*)L"", SQL_NTS, (SQLWCHAR*)L"", SQL_NTS);

The program compiles but run time access violation. If I replace the second parameter with 3/10/3000(any crazy value) the violation doesnt occur, but ODBC reports invalid string or buffer length. If I replace the second parameter with SQL_NTS/lenght()/strlen whatever, i get the access violation.

Jochen Arndt 15-Jun-18 3:02am

What about my first example with NULL and 0?

In any case this should work and compile (with Unicode builds or using SQLConnectW):

SQLWCHAR dsn[] = L"dailyworkbook";
rc = SQLConnect(ConHandle, dsn, SQL_NTS, NULL, 0, NULL, 0);

The ugly casting is required here because the parameters are not defined const while there are not modified by the function. But string literals and std::string::c_str() are const. With C++ it would be better to use const_cast:

std::wstring dsn(L"dailyworkbook");
rc = SQLConnect(ConHandle, const_cast<SQLWCHAR*>(dsn.c_str()), SQL_NTS, NULL, 0, NULL, 0);

Member 13872723 15-Jun-18 5:20am

Hi Jochen
Sorry for the repost but I am still finding my way around here.

I am getting an access violation on both of these. If I replace SQL_NTS with 0, in both cases there is no access violation but odbc says "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

I am getting desperate. Please help me.

Jochen Arndt 15-Jun-18 5:46am

SQL_NTS means Null Terminated String.
That is, the function will determine the string length itself using the corresponding (preceeding) parameter.

If the string parameter is NULL, pass 0 (zero) for the length.
If the string parameter is a valid string (including an empty string), pass the correct length (in characters) or SQL_NTS.

If you pass zero for the DSN length, the name can be off course not found.

The documenattion and all examples are showing it the way from my examples.

So the only other source I can think of is your ConHandle. That will be also used only after the DSN has been found.
Has that been properly allocated?

Member 13872723 15-Jun-18 6:56am

The connection handle is getting initialized properly. (At least it is not null).
I changed my build from Unicode to MultiByteCharSet. Got the access violation again when using SQL_NTS.
Am I missing an environmental setting here?
Thanks in advance

Jochen Arndt 15-Jun-18 7:05am

A non-null value does not mean it is a valid handle.

I suggest to edit your question and add the code lines before the connect call where you allocate the handle and also all related lines. Or check them yourself using the example code at https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlconnect-function?view=sql-server-2017.

Possible sources are passing the wrong size to SQLAllocHandle() and not checking the return value.

To edit your question use the green 'Improve question' link.

And remove your solution because it isn't one and might get therefore down voted.

Member 13872723 15-Jun-18 7:49am

I copied over the whole code from https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlconnect-function?view=sql-server-2017&viewFallbackFrom=sql-server-2017.
Still the samme result from SqlConnect. Violation when using SQL_NTS.

Jochen Arndt 15-Jun-18 8:34am

It is not the SQL_NTS for the DSN because it happens also when using wstrlen(dsn) for a local SQLWCHAR[] or dsn.length() for std::wstring.

So it is either the passed string itself or the handle because the other parameters are NULL with 0.

An access violation is generated when accessing memory that is not assigned to the application. That means a pointer is invalid or a pointer is valid but an offset used with that pointer is too large.

Assuming that the string pointer is valid and it is a NULL terminated string (with SQL_NTS) or the correct length is passed, it can be only the handle.

Again:
The violation occurs only when the DSN can be found. If that fails, the function returns before having the chance to reach the code that results in the violation.

So it can be only the handle or somewhere in the driver.

Or the exception occurs not in the SQLConnect() call but on the next line. Have you checked that (where it occurs)?

Please note that all what I see is a single line of code. I don't know your code before and after that line, and have no access to your database or your system. If the error is not on that specific line of code (which I think meanwhile), it is impossible to help due to the small information I have.

Member 13872723 15-Jun-18 10:57am

I fully appreciate that the violation occurs only when the dsn can be found. THanks a lot for that!!
I have debugged and confirmed that the violation occurs only in SqlConnect().
Here is my code:

int main() {
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;

SQLCHAR * OutConnStr = (SQLCHAR *)malloc(255);
SQLSMALLINT * OutConnStrLen = (SQLSMALLINT *)malloc(255);

// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

// Set the ODBC version environment attribute
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

// Allocate connection handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

// Set login timeout to 5 seconds
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

// Connect to data source
// retcode = SQLConnect(hdbc, (SQLCHAR*) "NorthWind", SQL_NTS, (SQLCHAR*)NULL, 0, NULL, 0);

std::wstring dsn(L"ddd");
// retcode = SQLConnect(hdbc, const_cast<SQLWCHAR*>(dsn.c_str()), 10,NULL, 0, NULL, 0);
retcode = SQLConnect(hdbc, const_cast<SQLWCHAR*>(dsn.c_str()), dsn.length(), NULL, 0, NULL, 0);

// Allocate statement handle
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

// Process data
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

SQLDisconnect(hdbc);
}

SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
}
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}

Member 13872723 17-Jun-18 5:55am

I found the culprit: K7 anti-virus.
I suddenly noticed that the exception was occurring in a K7*.dll.
I uninstalled K7 and everything's working fine now.
Any idea why this is happening?

Jochen Arndt 18-Jun-18 2:58am

Thank you for your feedback and fine to hear that you found the culprit.

I don't know why. You might report it to them.

AV software is hooking into multiple low level system interfaces. I don't like such. When it contains bugs, it can make whole systems unusable or vulnerable. I use only the MS own Defender because they should know best where and how to hook.