Ecere SDK/eC Forums
http://ec-lang.org/community/
Print view

[Solved]Reading Excel using ODBC in eC
http://ec-lang.org/community/viewtopic.php?f=13&t=173
Page 1 of 1
Author:  samsam598 [ Sun Aug 28, 2011 8:11 pm ]
Post subject:  [Solved]Reading Excel using ODBC in eC

The original c Source file of this example is from internet.I tested under mingw with pure C and it works perfect.However when I imported the example into eC with a little modification:
1.within Application subclass ,that is

Code: Select all

 
class App:Application
{
    void Main()
    {
        process();
        system("pause");
    }
    ////////below are all functions that the orignial program have
   void process()
   {
       ...
   }
   ...
 
}
 
It compiles but works does not as expected.It seems just throws an exception

Code: Select all

SQLDriverConnect(). [, SQLSTAT=1
When compiles,there are some warnings:

Code: Select all

Default Compiler
Building project excelapp using the Debug configuration...
Generating symbols...
excel.ec
Compiling...
excel.ec
   excel.ec:121:43: warning: incompatible expression sizeof sqlState (uint); expected short
   excel.ec:173:32: warning: incompatible expression (4 + (-22)) (int); expected short
   excel.ec:233:67: warning: incompatible expression sizeof buf (uint); expected short
   excel.ec:257:6: warning: different levels of indirection
   excel.ec:268:6: warning: different levels of indirection
   excel.ec:273:6: warning: different levels of indirection
   excel.ec:278:6: warning: different levels of indirection
excel.c
Writing symbol loader...
excelapp.main.ec
excelapp.main.ec
excelapp.main.c
Linking...

excelapp (Debug) - no error, 7 warnings
This is all I get.I am wondering what makes it not working in eC but the original c program works fine.
At the moment I did not find out the way how to attach the source,I just list the two file below.
Thanks for your help in advance.

Regards,
Sam

original C file:

Code: Select all

/* ------------------------------------------------------------------------------------
 * Program:      odbc_excel
 * Purpose:      C program to demo editing and reading info from an excel file using ODBC.
 * Date:         February, 2003
 * Compiler:     LCC-WIN32
 * Tested On:    Windows 2000 SP3 with Excel 2000
 * Style:        Win32 console application
 * DBMS:         Excel Workbook
 * ODBC:         Assumes Microsoft Excel drivers installed
 * Licence:      If you like this code please go to http://www.thehungersite.com
 * 
 * Authorship:
 *	With amendments by anonytmouse for reading and writing Excel worksheets,
 *	and odbc version 3 compliance this is based upon a program
 *	by David Brown which can be found at:
 *	http://www.john.findlay1.btinternet.co.uk/DataBase/database.htm
 *
 * 	He in turn acknowledges: ...this is based upon the original program
 *	by Rock Cogar... This can be found at:
 *	http://www.john.findlay1.btinternet.co.uk/Snippets/snip.htm
 *
 *	Some of the excel specific code is inspired by an article and C++ code
 *	by Alexander Mikula which can be found at the following locations:
 *	http://www.codeproject.com/database/excel_odbc_write.asp
 *	http://www.codeguru.com/mfc_database/excel_sheets_using_odbc.shtml
 *	
 * ------------------------------------------------------------------------------------ */

/* Note: This code aims to be ODBC Version 3 compliant. ODBC Version 3
 * was first distributed with Office 97 and Windows 98.
 * This involved replacing the SQLAlloc*, SQLFree* and SQLError functions
 * with SQLAllocHandle, SQLFreeHandle and SQLGetDiagRec functions respectively.
 *
 * Note: The Excel ODBC Driver has some limitations. Specifically, it does
 * not support DELETE statements and should not be used to alter an excel
 * file that is already open in another program.
 *
 * Note on mixed data types:
 * As excel columns do not have 'types', the odbc driver scans the first 8 rows
 * to determine the data type of a column. Values that are not of the determined type
 * will return NULL. The 8 rows value can be changed by changing the 
 * TypeGuessRows value in the registry. To force numbers to be entered as text
 * right click a column->click Format Cells->set category to text. However, this will
 * not change existing entries.

 * Information on this and resolutions (IMEX=1 property) can be found at:
 *
 * http://support.microsoft.com/default.aspx?scid=kb;en-us;257819
 * under A Caution about Mixed Data Types (halfway down)
 *
 * http://support.microsoft.com/default.aspx?scid=kb;EN-US;194124 */

#include <windows.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>

#pragma lib <ODBC32.lib>

#define SZLEN 50

void process(void);
void dbError( LPSTR lp, SQLSMALLINT handleType, SQLHANDLE handle);
void dbErrorCleanup( LPSTR lp,SQLSMALLINT handleType, SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt);
void dbCleanup(SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt);
BOOL isFileOpen(LPTSTR fileName);

int main(int argc,char *argv[])
{
	process();

	printf("Press any key to continue...");
	getchar();

	return 0;
}

void process(void)
{
	SQLUINTEGER lngSportID;
	SQLCHAR szSportName[SZLEN + 1]; /* Buffers for our field values */
	SQLCHAR blSportOlympic;
	SQLLEN lenInd[3]; /* Variables to contain column status or length info */

	SQLHENV       henv = SQL_NULL_HANDLE;
	SQLHDBC       hdbc = SQL_NULL_HANDLE;
	SQLHSTMT      hstmt = SQL_NULL_HANDLE;

	SQLRETURN  retcode;
	char       szSql[256];
	const char fileName[] = "sports.xls"; /* This can include a path */
	char	   szdatabase[256];

	/* Connection String Notes: The excel driver is read only by default.
	 * Due to a bug in the driver the FIRSTROWHASNAMES and MAXSCANROWS properties
	 * are not supported.
	 * Therefore the first row of a sheet will always be considered as column headings.
	 * We set EXCLUSIVE to yes so no other program can open the file once
	 * we have connected. However, this will not stop us opening a file that
	 * is already open. The file will be created if it does not exist. */

	sprintf(szdatabase,"DRIVER=Microsoft Excel Driver (*.xls);CREATE_DB=%s;DBQ=%s;READONLY=FALSE;EXCLUSIVE=Yes;",fileName,fileName);


	/* Check that the file is not already open. Attempting to alter
	 * an open excel file will have unpredictable consequences.
	 * However, only reading from an open excel file should be alright. */
	if ( isFileOpen(fileName) ) {
		printf("This program can not alter an open excel file.\n");
		printf("Please close the excel file and try again.\n");
		return;
	}

	/* Create the environment handle */
	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);

	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLAllocHandle(ENV)",SQL_HANDLE_ENV,henv,hdbc,hstmt);
		return;
	}

	/* Tell the odbc manager that we are an odbc version 3 application
	 * This MUST be called before using the environment handle */
	retcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,0);

	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLSetEnvAttr()",SQL_HANDLE_ENV,henv,hdbc,hstmt);
		return;
	}

	/* Create the connection handle */	
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLAllocHandle(DBC)",SQL_HANDLE_ENV,henv,hdbc,hstmt);
		return;
	}

	/* Connect to the database */
	retcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)szdatabase, (short) (strlen(szdatabase)+1), NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

	/* If there was a DSN already set-up instead of SQLDriverConnect you could use
	 *
	 *	retcode = SQLConnect(hdbc, "my_dsn", SQL_NTS, NULL, 0, NULL, 0);
	 */

	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
		dbErrorCleanup( "SQLDriverConnect()",SQL_HANDLE_DBC,henv,hdbc,hstmt);
		return;
	}

	/* Create the statement handle */
	retcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc, &hstmt);

	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLAllocHandle(STMT)",SQL_HANDLE_DBC,henv,hdbc,hstmt);
		return;
	}


	/* Execute a CREATE TABLE sql statement */
	lstrcpy( szSql,"CREATE TABLE [Sports] (SportID NUMBER, SportName TEXT,SportOlympic LOGICAL) ");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

	if (retcode != SQL_SUCCESS) {
		SQLCHAR sqlState[15];

		SQLGetDiagField( SQL_HANDLE_STMT, hstmt,1,SQL_DIAG_SQLSTATE,sqlState,sizeof(sqlState),NULL);

		/* We want to ignore the 'This table already exists...' error.
		 * This error is mapped to SQLSTATE 42S01 in odbc 3 apps. */
		if ( lstrcmpiA(sqlState,"42S01") != 0 ) {
			dbErrorCleanup( " SQLExecDirect()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
			return;
		}
	}

	/* Now we can insert some records. Take note of our emulated autonumber
	 * field. If any inserts fail we consider it non-fatal. */

	lstrcpy( szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, 'Curling' AS newName,TRUE AS newOlympic From [Sports$]");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

	if (retcode != SQL_SUCCESS) {
		dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
	}

	lstrcpy( szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, 'Water Skiing' AS newName,FALSE AS newOlympic From [Sports$]");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

	if (retcode != SQL_SUCCESS) {
		dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
	}

	lstrcpy( szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, 'Road Rage' AS newName,TRUE AS newOlympic From [Sports$]");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

	if (retcode != SQL_SUCCESS) {
		dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
	}


	/* Now let's retrieve the records in our table */

	lstrcpy( szSql,"SELECT SportID, SportName, SportOlympic FROM [Sports$] ORDER BY SportName, SportID"); /* Select statement */
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);

	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLExecDirect()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}

	
	/* Now we bind the columns to variables. This means that after each
	 * successful call to SQLFetch the variables will contain the values
	 * of the columns they are bound to for the current row.
	 * Note the last argument specifies the address of an indicater variable.
	 * This value is filled with the status or length of the column. */

	retcode = SQLBindCol(hstmt, 1,SQL_C_ULONG , &lngSportID, 0, &lenInd[0] );
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLBindCol()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}

	/* Note that data for this column will be truncated if it is longer
	 * than SZLEN */
	retcode = SQLBindCol(hstmt, 2,SQL_C_CHAR , szSportName, SZLEN, &lenInd[1]);
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLBindCol()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}

	retcode = SQLBindCol(hstmt, 3,SQL_C_BIT , &blSportOlympic, 0, &lenInd[2]);
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLBindCol()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}


	/* Now we loop through each record */
	do {
		/* Retrieve a row */
		retcode = SQLFetch(hstmt);

		if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
			dbError( "SQLFetch()",SQL_HANDLE_STMT,hstmt);
		}

		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { 

			/* Check our indicater variables. These are filled with the
			 * status or length of the column. Set default values
			 * if a column is NULL. */
			if (lenInd[0] == SQL_NULL_DATA) lngSportID = 0;
			if (lenInd[1] == SQL_NULL_DATA) lstrcpy(szSportName,"NONE");
			if (lenInd[2] == SQL_NULL_DATA) blSportOlympic = 0;

			/* SQLFetch has filled our variables with the values of the
			 * current record */
			fprintf(stdout, "ID: %d\tName: %s\tOlympic: %s\n", lngSportID, szSportName, (blSportOlympic ? "TRUE" : "FALSE") );
		}

	} while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO);


	/* Cleanup time */
	dbCleanup( henv, hdbc, hstmt );
}


// ------------------------------------------------------------------------------------------------------------
void dbError( LPSTR lp, SQLSMALLINT handleType, SQLHANDLE handle) {

	/* Print out error info */

	unsigned char buf[250];
	unsigned char sqlstate[15];

	SQLGetDiagRec( handleType,handle, 1, sqlstate, NULL,buf, sizeof(buf),NULL);
	fprintf(stderr, "%s. %s, SQLSTATE=%s\n",lp, buf, sqlstate);
}


// ------------------------------------------------------------------------------------------------------------
void dbErrorCleanup( LPSTR lp,SQLSMALLINT handleType, SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt) {

	/* Print error info and cleanup */

	SQLHANDLE handle;

	switch (handleType) {
		case SQL_HANDLE_STMT:
			handle = hstmt;
			break;
		case SQL_HANDLE_DBC:
			handle = hdbc;
			break;
		default:
			handle = henv;
	}


	if (handle != SQL_NULL_HANDLE) dbError( lp, handleType, handle  );

	dbCleanup(henv, hdbc, hstmt );
}


// ------------------------------------------------------------------------------------------------------------
void dbCleanup(SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt) {

	/* Disconnect, unbind, and free handles */

	if (hstmt != SQL_NULL_HANDLE) {
		SQLFreeStmt(hstmt, SQL_UNBIND);
		SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
	}

	if (hdbc != SQL_NULL_HANDLE) {
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
	}

	if (henv != SQL_NULL_HANDLE) SQLFreeHandle(SQL_HANDLE_ENV,henv);
}


// ------------------------------------------------------------------------------------------------------------
BOOL isFileOpen(LPTSTR fileName) {

	/* Check if another program has a file open */

	HANDLE hFile;

	/* Try to open the file with no sharing rights. This will cause
	 * an error if another program has it open */
	hFile = CreateFile(fileName,GENERIC_READ,0,NULL,OPEN_EXISTING,0,NULL);

	if (hFile == INVALID_HANDLE_VALUE) {
		if (GetLastError() == ERROR_SHARING_VIOLATION) return TRUE; /* Someone has it open */
		else return FALSE; /* probable ERROR_FILE_NOT_FOUND */
	}
	else {
		CloseHandle(hFile);
		return FALSE;
	}
}

ec implementation:

Code: Select all

 
#define UNICODE
#ifdef __WIN32__
#define WIN32_LEAN_AND_MEAN
 
//#include <wctype.h>
//#include <windefs.h>
#include <windows.h> 
//#include <windowsx.h>
//#include <w32api.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h> 
#endif 
import "ecere"
 
#define SZLEN 50
 
 
class App:Application
{
   void Main()
   {
      process();
      system("pause");
 
   }
 
 
   void process(void)
{
	SQLUINTEGER lngSportID;
	SQLCHAR szSportName[SZLEN + 1]; /* Buffers for our field values */
	SQLCHAR blSportOlympic;
	SQLLEN lenInd[3]; /* Variables to contain column status or length info */
 
	SQLHENV       henv = SQL_NULL_HANDLE;
	SQLHDBC       hdbc = SQL_NULL_HANDLE;
	SQLHSTMT      hstmt = SQL_NULL_HANDLE;
 
	SQLRETURN  retcode;
	char       szSql[256];
	const char fileName[] = "sports.xls"; /* This can include a path */
	char	   szdatabase[256];
 
	/* Connection String Notes: The excel driver is read only by default.
	 * Due to a bug in the driver the FIRSTROWHASNAMES and MAXSCANROWS properties
	 * are not supported.
	 * Therefore the first row of a sheet will always be considered as column headings.
	 * We set EXCLUSIVE to yes so no other program can open the file once
	 * we have connected. However, this will not stop us opening a file that
	 * is already open. The file will be created if it does not exist. */
 
	sprintf(szdatabase,"DRIVER=Microsoft Excel Driver (*.xls);CREATE_DB=%s;DBQ=%s;READONLY=FALSE;EXCLUSIVE=Yes;",fileName,fileName);
 
 
	/* Check that the file is not already open. Attempting to alter
	 * an open excel file will have unpredictable consequences.
	 * However, only reading from an open excel file should be alright. */
	if ( isFileOpen(fileName) ) {
		printf("This program can not alter an open excel file.\n");
		printf("Please close the excel file and try again.\n");
		return;
	}
 
	/* Create the environment handle */
	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLAllocHandle(ENV)",SQL_HANDLE_ENV,henv,hdbc,hstmt);
		return;
	}
 
	/* Tell the odbc manager that we are an odbc version 3 application
	 * This MUST be called before using the environment handle */
	retcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3,0);
 
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLSetEnvAttr()",SQL_HANDLE_ENV,henv,hdbc,hstmt);
		return;
	}
 
	/* Create the connection handle */	
	retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
 
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLAllocHandle(DBC)",SQL_HANDLE_ENV,henv,hdbc,hstmt);
		return;
	}
 
	/* Connect to the database */
	retcode = SQLDriverConnect(hdbc, NULL, (SQLCHAR *)szdatabase, (short) (strlen(szdatabase)+1), NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
 
	/* If there was a DSN already set-up instead of SQLDriverConnect you could use
	 *
	 *	retcode = SQLConnect(hdbc, "my_dsn", SQL_NTS, NULL, 0, NULL, 0);
	 */
 
	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
		dbErrorCleanup( "SQLDriverConnect()",SQL_HANDLE_DBC,henv,hdbc,hstmt);
		return;
	}
 
	/* Create the statement handle */
	retcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc, &hstmt);
 
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( "SQLAllocHandle(STMT)",SQL_HANDLE_DBC,henv,hdbc,hstmt);
		return;
	}
 
 
	/* Execute a CREATE TABLE sql statement */
	lstrcpy( szSql,"CREATE TABLE [Sports] (SportID NUMBER, SportName TEXT,SportOlympic LOGICAL) ");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);
 
	if (retcode != SQL_SUCCESS) {
		SQLCHAR sqlState[15];
 
		SQLGetDiagField( SQL_HANDLE_STMT, hstmt,1,SQL_DIAG_SQLSTATE,sqlState,sizeof(sqlState),NULL);
 
		/* We want to ignore the 'This table already exists...' error.
		 * This error is mapped to SQLSTATE 42S01 in odbc 3 apps. */
		if ( lstrcmpiA(sqlState,"42S01") != 0 ) {
			dbErrorCleanup( " SQLExecDirect()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
			return;
		}
	}
 
	/* Now we can insert some records. Take note of our emulated autonumber
	 * field. If any inserts fail we consider it non-fatal. */
 
	lstrcpy( szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, 'Curling' AS newName,TRUE AS newOlympic From [Sports$]");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);
 
	if (retcode != SQL_SUCCESS) {
		dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
	}
 
	lstrcpy( szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, 'Water Skiing' AS newName,FALSE AS newOlympic From [Sports$]");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);
 
	if (retcode != SQL_SUCCESS) {
		dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
	}
 
	lstrcpy( szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, 'Road Rage' AS newName,TRUE AS newOlympic From [Sports$]");
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);
 
	if (retcode != SQL_SUCCESS) {
		dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
	}
 
 
	/* Now let's retrieve the records in our table */
 
	lstrcpy( szSql,"SELECT SportID, SportName, SportOlympic FROM [Sports$] ORDER BY SportName, SportID"); /* Select statement */
	retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);
 
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLExecDirect()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}
 
 
	/* Now we bind the columns to variables. This means that after each
	 * successful call to SQLFetch the variables will contain the values
	 * of the columns they are bound to for the current row.
	 * Note the last argument specifies the address of an indicater variable.
	 * This value is filled with the status or length of the column. */
 
	retcode = SQLBindCol(hstmt, 1,SQL_C_ULONG , &lngSportID, 0, &lenInd[0] );
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLBindCol()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}
 
	/* Note that data for this column will be truncated if it is longer
	 * than SZLEN */
	retcode = SQLBindCol(hstmt, 2,SQL_C_CHAR , szSportName, SZLEN, &lenInd[1]);
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLBindCol()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}
 
	retcode = SQLBindCol(hstmt, 3,SQL_C_BIT , &blSportOlympic, 0, &lenInd[2]);
	if (retcode != SQL_SUCCESS) {
		dbErrorCleanup( " SQLBindCol()",SQL_HANDLE_STMT,henv,hdbc,hstmt);
		return;
	}
 
 
	/* Now we loop through each record */
	do {
		/* Retrieve a row */
		retcode = SQLFetch(hstmt);
 
		if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
			dbError( "SQLFetch()",SQL_HANDLE_STMT,hstmt);
		}
 
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) { 
 
			/* Check our indicater variables. These are filled with the
			 * status or length of the column. Set default values
			 * if a column is NULL. */
			if (lenInd[0] == SQL_NULL_DATA) lngSportID = 0;
			if (lenInd[1] == SQL_NULL_DATA) lstrcpy(szSportName,"NONE");
			if (lenInd[2] == SQL_NULL_DATA) blSportOlympic = 0;
 
			/* SQLFetch has filled our variables with the values of the
			 * current record */
			fprintf(stdout, "ID: %d\tName: %s\tOlympic: %s\n", lngSportID, szSportName, (blSportOlympic ? "TRUE" : "FALSE") );
		}
 
	} while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO);
 
 
	/* Cleanup time */
	dbCleanup( henv, hdbc, hstmt );
}
 
 
// ------------------------------------------------------------------------------------------------------------
void dbError( LPSTR lp, SQLSMALLINT handleType, SQLHANDLE handle) {
 
	/* Print out error info */
 
	unsigned char buf[250];
	unsigned char sqlstate[15];
 
	SQLGetDiagRec( handleType,handle, 1, sqlstate, NULL,buf, sizeof(buf),NULL);
	fprintf(stderr, "%s. %s, SQLSTATE=%s\n",lp, buf, sqlstate);
}
 
 
// ------------------------------------------------------------------------------------------------------------
void dbErrorCleanup( LPSTR lp,SQLSMALLINT handleType, SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt) {
 
	/* Print error info and cleanup */
 
	SQLHANDLE handle;
 
	switch (handleType) {
		case SQL_HANDLE_STMT:
			handle = hstmt;
			break;
		case SQL_HANDLE_DBC:
			handle = hdbc;
			break;
		default:
			handle = henv;
	}
 
 
	if (handle != SQL_NULL_HANDLE) dbError( lp, handleType, handle  );
 
	dbCleanup(henv, hdbc, hstmt );
}
 
 
// ------------------------------------------------------------------------------------------------------------
void dbCleanup(SQLHENV henv,SQLHDBC hdbc,SQLHSTMT hstmt) {
 
	/* Disconnect, unbind, and free handles */
 
	if (hstmt != SQL_NULL_HANDLE) {
		SQLFreeStmt(hstmt, SQL_UNBIND);
		SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
	}
 
	if (hdbc != SQL_NULL_HANDLE) {
		SQLDisconnect(hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
	}
 
	if (henv != SQL_NULL_HANDLE) SQLFreeHandle(SQL_HANDLE_ENV,henv);
}
 
 
// ------------------------------------------------------------------------------------------------------------
BOOL isFileOpen(LPTSTR fileName) {
 
	/* Check if another program has a file open */
 
	HANDLE hFile;
 
	/* Try to open the file with no sharing rights. This will cause
	 * an error if another program has it open */
	hFile = CreateFile(fileName,GENERIC_READ,0,NULL,OPEN_EXISTING,0,NULL);
 
	if (hFile == INVALID_HANDLE_VALUE) {
		if (GetLastError() == ERROR_SHARING_VIOLATION) return TRUE; /* Someone has it open */
		else return FALSE; /* probable ERROR_FILE_NOT_FOUND */
	}
	else {
		CloseHandle(hFile);
		return FALSE;
	}
}
 
 
sports.xls

Code: Select all

SportID	SportName	SportOlympic
0	Curling	                TRUE
1	Water Skiing	FALSE
2	Road Rage	                TRUE
3	Curling	                TRUE
4	Water Skiing	FALSE
5	Road Rage	               TRUE
Author:  jerome [ Sun Aug 28, 2011 11:11 pm ]
Post subject:  Re: Reading Excel using ODBC in eC

Hi Sam,

It seems that the #define UNICODE is causing the problems.
These SQL functions must be expecting UTF-16 for all strings passed in when UNICODE is defined, and you are passing ASCII character strings.

If you comment the #define UNICODE it out it works just like the C version.
I noticed GDB often crashes when running the program in Debug mode however, which in turn sometimes causes the IDE to freeze or crash.

About the warnings, the eC compiler does a fair amount of type checking and processing, but it probably does not follow 100% any particular C standard. To the eC compiler's best understanding these lines could be sources of problems, as it indicates. However, the code still gets compiled properly. (Although I don't imply that there are no bugs in the eC compiler that could cause code not to compile properly, there most likely are.)

Hope this helps :)

Jerome
Author:  samsam598 [ Mon Aug 29, 2011 1:14 am ]
Post subject:  Re: Reading Excel using ODBC in eC

Thanks Jerome!Yes it works after commenting #define UNICODE.
I did not notice whether it crashed DGB under a pure c environment.Will test it later.

I tested to insert new record which has Chinese characters,but turned out the output is not recognizable.Don't know how to improve.

Code: Select all

 
uint16* name1=UTF8toUTF16("小屁孩",null);  
...
sprintf(szSql,"INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, '%p'  AS newName,TRUE AS newOlympic From [Sports$]",name1); 
 
retcode = SQLExecDirect(hstmt, szSql, SQL_NTS);
 
if (retcode != SQL_SUCCESS) {
	dbError( " SQLExecDirect()",SQL_HANDLE_STMT,hstmt);
}    
 
Thanks.
Author:  jerome [ Mon Aug 29, 2011 8:40 am ]
Post subject:  Re: Reading Excel using ODBC in eC

You will need Unicode support if you want to use Chinese...
But perhaps do it on a function basis, i.e. do they have SQLExecDirectW?

Then you need to make sure 'everything' is Unicode, the whole query string (szSql)!
Author:  samsam598 [ Tue Aug 30, 2011 8:03 pm ]
Post subject:  Re: Reading Excel using ODBC in eC

I tried to replace all non-unicode functions to unicode based but still it does not work properly to show the Chinese characters.I posted help request in MSDN but no one answered me in details except figuring me out the same concerns as what you've figured out.
Author:  jerome [ Tue Aug 30, 2011 9:30 pm ]
Post subject:  Re: Reading Excel using ODBC in eC

Sam, it works perfectly for me, the Chinese characters show up properly in the Excel spreadsheet:

Code: Select all

void SQLExecDirectUTF8(SQLHSTMT stmt, char * text, SQLINTEGER tl)
{
   uint16 * utf16 = UTF8toUTF16(text, null);
   SQLExecDirectW(stmt, utf16, tl);
   delete utf16;
}
...
SQLExecDirectUTF8(hstmt, "INSERT INTO [Sports$] (SportID,SportName,SportOlympic) SELECT IIf( IsNull( MAX(SportID) ),0,MAX(SportID) + 1) AS newID, '小屁孩'  AS newName,TRUE AS newOlympic From [Sports$]", SQL_NTS);
Have you tried something like this?

By the way, if you want to use sprintf, you will need to use '%s' (string) to embed another string, not '%p' (numeric pointer address).

Cheers,

Jerome
Author:  jerome [ Tue Aug 30, 2011 9:39 pm ]
Post subject:  Re: Reading Excel using ODBC in eC

By the way, we always planned to have an ODBC driver for EDA =)

You would be most welcomed to contribute work on that! :)

-Jerome
Author:  samsam598 [ Tue Aug 30, 2011 10:04 pm ]
Post subject:  Re: Reading Excel using ODBC in eC

Man,I love you! :D The issue's fixed now.

Yes.I am planning to write a class wrapper for ODBC under windows,but no guarantee I can make it as you can see my knowledge in C is very narrow.Anyway I will try.

Regards,
Sam
Author:  jerome [ Tue Aug 30, 2011 10:19 pm ]
Post subject:  Re: Reading Excel using ODBC in eC

Yes, but ideally it would follow the EDA architecture (EDA - Ecere Data Access - is quite similar in concept to ODBC, in that it gives you a uniform interface through which you can access a variety of database engines).

You could take a quick look at sdk/eda/drivers/sqlite/ - the SQLite driver for EDA.

You basically derive off the base EDA driver classes, and then fill in the blanks with the ODBC API calls:

Code: Select all

import "EDA"
class ODBCDataSource : DataSourceDriver
{
   class_property(name) = "ODBC";
 
   bool Connect(const String locator)
   {
   ...
   }
 
   Database OpenDatabase(const String name, CreateOptions createOptions, DataSource ds)
   {
   ...
   }
...
}
 
class ODBCField : Field
{
...
}
 
class ODBCDatabase : Database
{
...
}
 
class ODBCTable : Table
{
...
}
 
class ODBCRow : DriverRow
{
...
}
Then we can use any ODBC databases through EDA, like in that MovieCollection sample =)
All times are UTC-05:00 Page 1 of 1