Click here to Skip to main content
15,072,051 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Here is the basic code taken from Microsoft docs. I had the biggest trouble connecting but found out, as you can see, the length of the DSN, username, and password has to be spot on. I will later put the size in a variable to clean it up. I purposely changed the password to make sure the login/password works through the ODBC GUI (odbcad32.exe) that has a trace option. I is connecting to the SQL and according to SQLExecDirect(), its connecting to the Stored Procedure.
But, the results/sParam1 is zero. No matter what I change, I cannot get a count.
After the code below, the SP is after it. The SP has always worked when I was using Crecordset’s, see below.

C++
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
	retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER;
	if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
	{
		retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
		if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		{
			retcode = SQLConnect(hdbc, (UCHAR *)"WISKY4", 6, (UCHAR *)"Admin", 5,
							(UCHAR *)"SEASEME", 7); 
				if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
				{
					retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
					if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
					{
						retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, 
						SQL_C_SSHORT, SQL_INTEGER, 0, 0, &sParam1, 0, &cbParam1);  
						if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
						{
							retcode = SQLExecDirect(hstmt, 
							(UCHAR *)"{?=CALL [dbo].[count_alarms]}", SQL_NTS);
							while( ( retcode = SQLMoreResults(hstmt) ) != 
												SQL_NO_DATA)
								;
								s1.Format("Return Results = %d", sParam1);
								s2.Format("cbReturn Results = %d", cbParam1);
							
						}
					}
				}
			}
		}
	}
}

After this, I free up any SQL memory allocated from above, just not showing it, too much to read.
Stored Procedures below:
SQL
USE [MyDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[count_transactions]

AS
SELECT COUNT(*) FROM [dbo].[Transaction]
RETURN


I modified below to make sure it would output but in fact, both of these SP work using “exec count_alarms”.
The modified below now produces a header/title and the counts while the above just outputs the count.
SQL
USE [MyDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[count_alarms]

 AS
DECLARE @tableOneCount int 
SELECT @tableOneCount = (SELECT COUNT(*) FROM [dbo].[ALARM_LOG] AS OUTPUT)

SELECT @tableOneCount AS 'TOTAL ALARM RECORDS'
RETURN


Below: This is a modified version of MFC/Crecordset that serves me well. However, CRcordset takes too much overhead and cannot be restarted. In case of the SQL Server going down or the LAN connection going down, CRecordset’s cannot recover and the program needs to be restarted; impossible if this is a remote computer. CDatabase can recover but I cannot access the SP through CDatabase and get a return.
Below is what I have but will erase it if I can get either CDatabase or SQL ODBC commands to work on these SPs.

C++
IMPLEMENT_DYNAMIC(CCOUNT_ALARMS, CRecordset)

CCOUNT_ALARMS::CCOUNT_ALARMS(CDatabase* pdb)
	: CRecordset(pdb)
{
	m_COUNT.Empty();
	m_nFields	= 1;
	m_strParam	= 0;
	m_nDefaultType = ConnectType;
    m_nParams	= 1;
}
CString CCOUNT_ALARMS::GetDefaultConnect()
{
	CString Dsn;
	if (CONNECT_SQL_DB == TRUE && m_SQL_DB_ONLINE == TRUE)
	  Dsn.Format("%s%s", 
(char*)m_dbParam.DbRptString.GetString(),										(char *)m_dbParam.DbLogonStr.GetString( ) );
	if (CONNECT_TYPE_MS_DB == TRUE && CONNECT_SQL_DB == FALSE)
		Dsn.Format("%s%s%s",  (char *)m_dbParam.DbRptString.GetString(),
		 (char *)m_dbParam.DbParamStr.GetString(),  (char *)m_dbParam.DbLogonStr.GetString());
	return(Dsn);
}

CString CCOUNT_ALARMS::GetDefaultSQL()
{
	// argument ? == either  MS ACCESS OR SQL
	if (CONNECT_SQL_DB == TRUE && m_SQL_DB_ONLINE == TRUE)
		return _T( "{CALL [dbo].[count_alarms]}" );
	if (CONNECT_TYPE_MS_DB == TRUE && CONNECT_SQL_DB == FALSE)
		return _T("{CALL [count_alarms]}");
return _T("{CALL [count_alarms]}");// HERE ONLY TO QUIET THE COMPILIER
}

void CCOUNT_ALARMS::DoFieldExchange(CFieldExchange* pFX)
{
	pFX->SetFieldType(CFieldExchange::outputColumn);
	RFX_Text(pFX, _T("[COUNT]"),	m_COUNT);
	pFX->SetFieldType(CFieldExchange::param);
	RFX_Int(pFX, _T("COUNT"), m_strParam);
}

AND THE CODE TO OPERATE THE ABOVE IS RUN THROUGH A THREAD EVERY 5 MINUETS
C++
void CLeveL10View::CountAlarmsDB(void)
{
	int c = 0;

	if (m_FAIL_SAFE_ONLINE == FALSE && m_SQL_DB_ONLINE == FALSE
                && CONNECT_TYPE_SQL_DB == TRUE)
		return;
	if (m_FAIL_SAFE_ONLINE == TRUE && m_SQL_DB_ONLINE == TRUE &&
                           CONNECT_TYPE_SQL_DB == TRUE)
	{
		if (m_FailedDBAtempts > MAX_OFFLINE_FAILURES)
			return;
	}
	if (ALM_COUNTS_IN_PROGRESS == TRUE)
	{
		do
		{
			MyWait((DWORD)500, TRUE);
			if (c++ > 5)
				break;
		} while (ALM_COUNTS_IN_PROGRESS == TRUE);
	}

	ALM_COUNTS_IN_PROGRESS = TRUE;
	if ((m_SQL_DB_ONLINE == TRUE && CONNECT_TYPE_SQL_DB == TRUE) ||   
                                                     CONNECT_TYPE_MS_DB == TRUE)
		if (m_CountAlarms->IsOpen())
			m_CountAlarms->Close();
try
{
	m_CountAlarms->Open(NULL, NULL, CRecordset::executeDirect);
}
catch (CDBException * e)//IF NO STORED PROCEDURE OR NO PROCEDURE TO USE, ERROR BELOW BUT KEEPS ON GOIN
{
	COUNTING_ERROR = TRUE;
	e->Delete();
	TransactionCount = 0l;
	MYYield();
	COUNTS_IN_PROGRESS = FALSE;
	COUNT_TRANS_OK = TRUE;
	return;
}
if (m_CountAlarms->IsBOF() && m_CountAlarms->IsEOF())
{
	COUNTING_ERROR = TRUE;
	AlarmCount = 0l;
	AlarmIncrement = 0l;
	COUNTS_IN_PROGRESS = FALSE;
	COUNT_ALM_TRANS_OK = TRUE;
	m_CountAlarms->Close();
	return;
}
AlarmCount = atol(m_CountAlarms->m_COUNT);
AlarmIncrement = atol(m_CountAlarms->m_COUNT);
AlarmFailSafeIncrement = AlarmIncrement;
COUNT_ALM_TRANS_OK = TRUE;//TO FLAG OnTimer() to get the stats and put it on the screen
m_CountAlarms->Close();
ALM_COUNTS_IN_PROGRESS = FALSE;
return;
}



Anybody have constructive ideas on the ODBC program or CDatabase ideas?

What I have tried:

I have tried CDatabase objects to connect to the SQL DB and run the SP with no luck. I then started the ODBC code to run the SP but cannot get any returns. I changed functions like SQLConnect to SQLDriverConnect with no luck. I have spent 2 days at it and ready to give up and let the program restart if the SQL goes down; putting the EXE in a batch file in the startup directory in Windows 10. I did find a 7,000 page document on SQL and ODBC and I am still going through it.
Sorry about the copy and paste, it didn't come out like I wanted.
Posted
Updated 28-Oct-20 4:28am
v3
Comments
Herman<T>.Instance 20-Oct-20 5:04am
   
Please use codeblocks when dumping your coding environment here.
Gerry Schmitz 21-Oct-20 9:46am
   
I looked into C++ "database programming" years ago ... Compared to a 4GL / LINQ enabled language, it's like coding in assembler. Use a "managed" framework / class library like ADO.NET.
inlandchris1 21-Oct-20 21:18pm
   
Gerry, thanks for the reply and accept all views. However, I only told a tiny bit of this story. I have been using C and C++ for 30 years because its the fastest high level language around next to assembler which is a low level language. My tiny code I put up is part of a 400,000 line count code which is used in a security environment. The hardware limitation is 127 card readers but I started this code from scratch that now has no limit on card readers and up to 4 billion cards. All this is possible because of C++. I need speed and C++ is only limited by the cpu clock speed. I have always been impressed with this language except for MFC. I needed a good looking GUI so 25 years ago, I chose MFC but the CRecordset sucks. This part of the code doesn’t not need CRecordsets because I am only writing to the transaction log and alarm logs. Doing the stored procedure is not writing, hence my trouble. Still pouring over the 7,000 page document but hoping someone has already cracked this problem. Again, thanks for the info.

1 solution

I have finally found it reading the docs:
After you do a fetch, now, get the data from SQLGetData. Works with the output param or input_output param but SP has only an output. AlarmCount has the data (long).
retcode = SQLPrepare(hstmt, (UCHAR *)"{?=CALL count_alarms}", SQL_NTS);
if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
    retcode = SQLExecute(hstmt);
    if(SQLFetch(hstmt) == SQL_SUCCESS)
    {
        SQLGetData(hstmt, 1, SQL_C_ULONG, &AlarmCount, 0, NULL);
    }

}
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900