Comments by Member 13872723 (Top 14 by date)

sorry to say it's quite buggy.
Here's my refined version.

void escapeTicks(std::wstring *s)
{
for (size_t i = 0;(*s)[i] != 0;)
{
if ((*s)[i] == L'\'')
{
(*s).insert(i, L"'");
i += 2;
}
else
{
i++;
}
}
}

Here's my code:
// Republic Database Analytics.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"

#include <windows.h>
#include <sqlext.h>
#include <mbstring.h>
#include <stdio.h>
#include <string>
#include <vector>

SQLRETURN GetResultset();



void DescribeColumns();
struct ColDescription
{
SQLSMALLINT colNumber;
SQLCHAR colName[80];
SQLSMALLINT nameLen;
SQLSMALLINT dataType;
SQLULEN colSize;
SQLSMALLINT decimalDigits;
SQLSMALLINT nullable;
};

using namespace std;
std::vector <coldescription> cols;
SQLHANDLE StmtHandle;
std::vector< std::vector<std::string> > colData;

RETCODE rc; // ODBC return code
SQLRETURN GetColData(int colnum, string& str)
{
SQLCHAR buf[255] = { 0 };
if ((SQLGetData(StmtHandle, colnum, SQL_CHAR, buf, sizeof(buf), NULL)) == SQL_SUCCESS)
str = reinterpret_cast<char*>(buf);
return rc;
}


int main()
{
RETCODE rc; // ODBC return code
//HENV henv;
//2HDBC hdbc; // Connection handle

SQLHANDLE EnvHandle;


rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle);
SQLHANDLE ConHandle = 0;

if (rc == SQL_SUCCESS)
{
rc = SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_UINTEGER);
}

rc = SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &ConHandle);




//SQLAllocEnv(&henv);
//SQLAllocConnect(henv, &hdbc);
// std::wstring ds_name = L"daily workbook"; // Data source name
//unsigned char chr_ds_name[SQL_MAX_DSN_LENGTH]; // Data source name

// _mbscpy_s(chr_ds_name, SQL_MAX_DSN_LENGTH, (const unsigned char *)"daily workbook");

//rc = SQLConnect(hdbc, (SQLWCHAR*)("daily workbook"), SQL_NTS, NULL, 0, NULL, 0);


//strcpy((char *)SQLStmt, "SELECT * FROM categories");
unsigned char chr_ds_name[SQL_MAX_DSN_LENGTH]; // Data source name
//_mbscpy_s(chr_ds_name, SQL_MAX_DSN_LENGTH, (const unsigned char *)"dailyworkbook");



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

SQLHSTMT hstmt;

SQLCHAR SqlState[6];
SQLINTEGER NativeError;
wchar_t Msg[SQL_MAX_MESSAGE_LENGTH];
SQLSMALLINT i, MsgLen;

wchar_t wpb[6];

//SQLGetDiagRec(SQL_HANDLE_DBC, ConHandle, 1, wpb, &NativeError, Msg, sizeof(Msg), &MsgLen);
rc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &StmtHandle);

//SQLCHAR SQLStmt[255] = { 0 };


// strcpy_s((char *)SQLStmt, "SELECT * FROM sheet1");

if (rc == SQL_SUCCESS)
{
wchar_t wob[] = L"SELECT * FROM [Sheet1$]";
rc = SQLExecDirect(StmtHandle, wob, SQL_NTS);

if (rc == SQL_SUCCESS)
{
// GetResultset();
}
else
{
SQLGetDiagRec(SQL_HANDLE_STMT, StmtHandle, 1, wpb, &NativeError, Msg, sizeof(Msg), &MsgLen);
}

if (rc == SQL_SUCCESS)
{
DescribeColumns();
colData.clear();

while (SQLFetch(StmtHandle) == SQL_SUCCESS)
{
std::vector<std::string> col;
std::string data;
int i = 1;


while (GetColData(i, data) == SQL_SUCCESS)
{
col.push_back(data);
++i; // increment the column number

}

colData.push_back(col);
}
}
}



return 0;
}

SQLRETURN Describe(ColDescription& c)
{
return SQLDescribeCol(StmtHandle, c.colNumber,
(SQLWCHAR*)c.colName, sizeof(c.colName), &c.nameLen,
&c.dataType, &c.colSize, &c.decimalDigits, &c.nullable);
}


void DescribeColumns()
{
ColDescription c;
c.colNumber = 1;
cols.clear();

while (Describe(c) == SQL_SUCCESS)
{
cols.push_back(c);
++c.colNumber;
}

}

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?

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

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.

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

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.

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.