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.
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:
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.
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.
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()
{
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]}");}
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
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){
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;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.