How to access .dbf file directly?

[lvxs]

I want to access .dbf file directly in ODBC form. But I must construct a DSN
in ODBC control panel. But I don't want to show ODBC dialog to user. So I
want to create virtual and temporary CDatabase and access .dbf file. How
can I do this work?

thanks!

[dll_man]

"lvxs" <lvxs@990.net> wrote:
>
>I want to access .dbf file directly in ODBC form. But I must construct a
DSN
>in ODBC control panel. But I don't want to show ODBC dialog to user. So
I
>want to create virtual and temporary CDatabase and access .dbf file. How
>can I do this work?
>
>thanks!

you can go directly through the odbc api as long as your table is registered.
here's a snippet of a class i once wrote to access a dbf file...it's a bit
choppy...i'm a novice, but you can get the idea of how it works.


// ODBC_Class.cpp: implementation of the ODBC_Class class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "ODBC_Class.h


int ODBC_Class::GetColValue(int position)
{
return pNodeRow->GetElement(position);
}

void ODBC_Class::NewNodeRow(int row_num)
{
RETCODE rtc = SQL_SUCCESS;
SQLHANDLE RowHandle;
SQLCHAR command[256];
SQLINTEGER row_element, element_size;
char temp[20];

strcpy((char *) command, "SELECT * ");
strcat((char *) command, "FROM INT_NODES ");
strcat((char *) command, "WHERE NUMBER = ");
strcat((char *) command, itoa(row_num, temp, 10));

rtc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &RowHandle);
if (rtc == SQL_SUCCESS)
{
rtc = SQLExecDirect(RowHandle, command, SQL_NTS);

if (rtc == SQL_SUCCESS)
{
rtc = SQLFetch(RowHandle);

if (rtc != SQL_NO_DATA_FOUND)
{
for (int n = 1; n <= NumColumns; n++)
{
SQLGetData(RowHandle, n, SQL_C_SLONG, &row_element, 0, &element_size);
pNodeRow->SetElement(n, row_element);
}
}
}
}

if (RowHandle != NULL)
SQLFreeHandle(SQL_HANDLE_STMT, RowHandle);
return;
}


RETCODE ODBC_Class::PrepareStatement()
{
RETCODE rc = SQL_SUCCESS;

if (ConHandle != NULL)
{
if (rc == SQL_SUCCESS)
{
// Define A SELECT SQL Statement
strcpy((char*) statement, "SELECT * ");
strcat((char *) statement, "FROM INT_NODES");
}
}
return rc;
}


// Define The Class Constructor
/////////////////////////////////////////////////
ODBC_Class::ODBC_Class()
{
SQLCHAR DBName[4] = "db1";
SQLHANDLE RowHandle;
SQLCHAR command[256];
RETCODE rc = SQL_SUCCESS;

rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle);

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

if (rc == SQL_SUCCESS)
{
rc = SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &ConHandle);

if (rc == SQL_SUCCESS)
{
rc = SQLConnect(ConHandle, DBName, SQL_NTS,
(SQLCHAR *) "", SQL_NTS, (SQLCHAR *) "", SQL_NTS);

if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
strcpy((char *) command, "SELECT * ");
strcat((char *) command, "FROM INT_NODES ");
strcat((char *) command, "WHERE NUMBER = 1");

rc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &RowHandle);
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
rc = SQLExecDirect(RowHandle, command, SQL_NTS);

if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
//assign a value to the NumColumns member variable.
rc = SQLNumResultCols(RowHandle, &NumColumns);
pNodeRow = new CNodeRow;
pNodeRow->SetSize(NumColumns);

rc = PrepareStatement();
}
}
}
}
}
}
if (RowHandle != NULL)
SQLFreeHandle(SQL_HANDLE_STMT, RowHandle);
}


// Define The Class Destructor
///////////////////////////////////////////////////////////
ODBC_Class::~ODBC_Class()
{
if (pNodeRow != NULL)
delete pNodeRow;

// Free The Connection Handle
if (ConHandle != NULL)
SQLFreeHandle(SQL_HANDLE_DBC, ConHandle);

// Free The Environment Handle
if (EnvHandle != NULL)
SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle);
}


//define the row method
////////////////////////////////////////////////////////////
void ODBC_Class::Row(int row_num)
{
RETCODE rtc = SQL_SUCCESS;
SQLCHAR NUMBER[20];
SQLCHAR XCOORD[20];
SQLCHAR NUMOFNABES[20];
SQLHANDLE RowHandle;

rtc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &RowHandle);

if (rtc == SQL_SUCCESS)
{
rtc = SQLPrepare(RowHandle, statement, SQL_NTS);
if (rtc == SQL_SUCCESS)
// rtc = SQLExecDirect(RowHandle, statement, SQL_NTS);
rtc = SQLExecute(RowHandle);
if (rtc != SQL_SUCCESS)
{
cout << "sql execution failed"<< endl;
return;
}

rtc = SQLBindCol(RowHandle, 1, SQL_C_CHAR, (SQLPOINTER)
NUMBER, sizeof(NUMBER), NULL);

rtc = SQLBindCol(RowHandle, 2, SQL_C_CHAR, (SQLPOINTER)
XCOORD, sizeof(XCOORD), NULL);

rtc = SQLBindCol(RowHandle, 4, SQL_C_CHAR, (SQLPOINTER)
NUMOFNABES, sizeof(NUMOFNABES), NULL);

/*

//SQLFetchScroll way
rtc = SQLFetchScroll(RowHandle, SQL_FETCH_ABSOLUTE, (SQLINTEGER)(row_num));
// rtc = SQLFetch(RowHandle);

if (rtc != SQL_NO_DATA_FOUND)
{
cout << NUMBER <<" "<< XCOORD <<" "<< NUMOFNABES << endl;
}
*/


//SQLFetch way...multiple times
int a = 0;
while (rtc != SQL_NO_DATA_FOUND)
{
rtc = SQLFetch(RowHandle);
a++;
if (a == row_num)
{
cout << NUMBER <<" "<< XCOORD <<" "<< NUMOFNABES << endl;
break;
}
}
}

if (RowHandle != NULL)
SQLFreeHandle(SQL_HANDLE_STMT, RowHandle);
return;
}

//define the rowwhere method
////////////////////////////////////////////////////////////
int ODBC_Class::RowWhere(int row_num, int col_num)
{
RETCODE rtc = SQL_SUCCESS;
SQLHANDLE RowHandle;
SQLCHAR command[256];
char temp[20];

strcpy((char *) command, "SELECT * ");
strcat((char *) command, "FROM INT_NODES ");
strcat((char *) command, "WHERE NUMBER = ");
strcat((char *) command, itoa(row_num, temp, 10));

rtc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &RowHandle);
if (rtc == SQL_SUCCESS)
{
if (rtc == SQL_SUCCESS)
{
rtc = SQLExecDirect(RowHandle, command, SQL_NTS);
if (rtc == SQL_SUCCESS)
{
rtc = SQLFetch(RowHandle);
if (rtc != SQL_NO_DATA_FOUND)
{
SQLINTEGER g, sg;
SQLGetData(RowHandle, col_num, SQL_C_SLONG, &g, 0, &sg);
return g;
}
}
}
}

if (RowHandle != NULL)
SQLFreeHandle(SQL_HANDLE_STMT, RowHandle);
return -1;
}


CNodeRow* ODBC_Class::GetNodeRow(int row_num)
{
RETCODE rtc = SQL_SUCCESS;
SQLHANDLE RowHandle;
SQLCHAR command[256];
SQLINTEGER row_element, element_size;
char temp[20];

strcpy((char *) command, "SELECT * ");
strcat((char *) command, "FROM INT_NODES ");
strcat((char *) command, "WHERE NUMBER = ");
strcat((char *) command, itoa(row_num, temp, 10));

rtc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &RowHandle);
if (rtc == SQL_SUCCESS)
{
rtc = SQLExecDirect(RowHandle, command, SQL_NTS);

if (rtc == SQL_SUCCESS)
{
rtc = SQLFetch(RowHandle);

if (rtc != SQL_NO_DATA_FOUND)
{
for (int n = 1; n <= NumColumns; n++)
{
SQLGetData(RowHandle, n, SQL_C_SLONG, &row_element, 0, &element_size);
pNodeRow->SetElement(n, row_element);
}
}
}
}

if (RowHandle != NULL)
SQLFreeHandle(SQL_HANDLE_STMT, RowHandle);

return pNodeRow;
}


void ODBC_Class::InitializeNodeRow(CNodeRow &noderow, int row_num)
{
SQLCHAR command[256];
char temp[20];
strcpy((char *) command, "SELECT * ");
strcat((char *) command, "FROM INT_NODES ");
strcat((char *) command, "WHERE NUMBER = ");
strcat((char *) command, itoa(row_num, temp, 10));

RETCODE rtc = SQL_SUCCESS;
SQLHANDLE RowHandle;
rtc = SQLAllocHandle(SQL_HANDLE_STMT, ConHandle, &RowHandle);
if (rtc == SQL_SUCCESS)
{
rtc = SQLExecDirect(RowHandle, command, SQL_NTS);

if (rtc == SQL_SUCCESS)
{
rtc = SQLFetch(RowHandle);

if (rtc != SQL_NO_DATA_FOUND)
{
noderow.SetSize(NumColumns);
SQLINTEGER row_element, element_size;
for (int n = 1; n <= NumColumns; n++)
{
SQLGetData(RowHandle, n, SQL_C_SLONG, &row_element, 0, &element_size);
noderow.SetElement(n, row_element);
}
}
}
}

if (RowHandle != NULL)
SQLFreeHandle(SQL_HANDLE_STMT, RowHandle);

ASSERT (noderow.GetElement(4) >= 0 &&
noderow.GetElement(4) <= 10);
}