ODBC Param. Example

[fdsouth]

I'm writing an application that requires the user to enter a value in an edit box, then queries a database using ODBC for records that meet this value ("SELECT ... WHERE [FIELD] = m_editbox). This sounds fairly simple, but since I'm new at this I would like an example to work from.

Thanks in advance.

 

[Cagliostro]

Tell me please what kind of database do you want to connect? And write a simple query what do you want to get. John Fill

ivfmd@mail.md

 

[fdsouth]

The database table is Pervasive SQL (btrieve). The SELECT statement is as follows:

CString CMAX_Lot_NumberSet::GetDefaultSQL()
{
	CString m_sqlStmt = "SELECT DISTINCT * FROM [Lot Tracking Header] WHERE [Lot Tracking Header].[LOTNUM_70] = " + m_LOTNUM_Param;
	return _T( m_sqlStmt);
}

I have the m_LOTNUM_Param variable tied to the edit box, but I guess the application opens the database and executes the SELECT statement before there is anything in the edit box (upon execution). Therefore, the variable has a NULL value - so the SQL statement returns nothing.

How can I get the app to wait until I'm ready before it opens the table and creates the recordset?

 

[Cagliostro]

in ODBC API the order of calling is

1. Alloc a workspace, use SQLAllocWorkspace/SQLAllocHandle.
MFC has a wraper for it(as I believe) CWorkSpace or CODBCWorkSpace.
2. Using this handle alloc a connection using SQLAllocConnect or SQLAllocHandle.
MFC wrapper -- CDBConnection(also in my opinion)
3. Connect, using SQLConnect or SQLDriverConnect. The last one is much better.
4. Alloc a statement with SQLAllocStmt or SQLAllocHandle.
I think there should exist a wriper, something about CDBRecordSet or CSqlStatement. My advice, is to consult MSDC if you use MFC.
4.1 If you need the results, bind columns using SQLBindColumn.
5. execute, using SQLExecute or SQLExecDirect.
5.1 use SQLFetch to get each record.
6. free the statement handle using SQLFreeStmt or SQLFreeHandle
7. Disconnect from the database using SQLDisconnect
8. Free the connection.
9. Free the workspace John Fill

ivfmd@mail.md

 

[fdsouth]

Does MSDN have examples for this or are there other sites out there that would? I'm fairly new at this so I used the MS VC++ Application Wizard to generate most of the code. It appears that the Wizard prefers to use GetDefaultConnect and GetDefaultSQL to generate the Recordset. I'm not familiar with the SQL functions you listed.

 

[Cagliostro]

In MSDN are a lot of samples in ODBC API and in MFC/ODBC. John Fill

ivfmd@mail.md

 

[StukA]

It looks like you're using a subclass of CRecordset. If so, eliminate the WHERE clause from your SELECT statement. Use the value of your Edit box to set the m_strFilter member of the Recordset class, and Open() the Recordset after you've set the Filter. The filter string is turned into a WHERE clause automatically by the framework.
i.e.
CString CMAX_Lot_NumberSet::GetDefaultSQL()
{
CString m_sqlStmt = "SELECT DISTINCT * FROM
[Lot Tracking Header] ;
return _T( m_sqlStmt);
}

Then somewhere (in initialization, or when they hit the Query button, etc.)
{
(recordset).m_strFilter = [Lot Tracking Header].[LOTNUM_70] = " + m_LOTNUM_Param;
(recordset).Open()
}

 

[fdsouth]

OK, what you said makes sense. I removed the explicit WHERE call in the GetDefaultSQL function. My RecordSet class looks like this:

CMAX_Lot_NumberSet::CMAX_Lot_NumberSet(CDatabase* pdb)
	: CRecordset(pdb)
{
	//{{AFX_FIELD_INIT(CMAX_Lot_NumberSet)
	m_LOTNUM_70 = _T("");
	m_ORDNUM_70 = _T("");
	m_nFields = 2;
	//}}AFX_FIELD_INIT
	m_nDefaultType = snapshot;
}

CString CMAX_Lot_NumberSet::GetDefaultConnect()
{
	return _T("ODBC;DSN=Max for Windows");
}

CString CMAX_Lot_NumberSet::GetDefaultSQL()
{
	CString m_sqlStmt = "SELECT DISTINCT [LOTNUM_70], [ORDNUM_70] "
		                "FROM [Lot Tracking Header] ";
	return _T( m_sqlStmt);
}

void CMAX_Lot_NumberSet::DoFieldExchange(CFieldExchange* pFX)
{
	//{{AFX_FIELD_MAP(CMAX_Lot_NumberSet)
	pFX->SetFieldType(CFieldExchange::outputColumn);
	RFX_Text(pFX, _T("[LOTNUM_70]"), m_LOTNUM_70);
	RFX_Text(pFX, _T("[ORDNUM_70]"), m_ORDNUM_70);
	//}}AFX_FIELD_MAP
}

I added a button that once clicked should set the m_strFilter variable to my WHERE clause. It looks like this:

void CMAX_Lot_NumberView::OnQuery() 
{
	m_pSet->m_strFilter = "[Lot Tracking Header].[LOTNUM_70] = " + m_LOTNUM_Param;
	m_pSet->Open();
}

It still bombed out at runtime when I pressed the Query button.

I'm sorry about throwing all this code at you, but I'm stumped.

Thanks for your patience.