ODBC to read .dbf files in VC++
-
Hello, I use the ODBC driver "dBase Files" (installed on my station by microsoft application, I guess). hereunder is the code I wrote to access the C:\Documents and Settings\dd\Mes documents\GSTAT.DBF file : #define DBName "DSN=dBASE Files;UID=WD:" ... CDatabase gDB; CRecordset rs(&gDB); CString res; CString ErrMsg; CString CASHT; CString NETROOM; CString DATUM; int NbRecord; CString Path = "C:\\Documents and Settings\\dd\\Mes documents\\" ; CString File = "GSTAT.DBF" ; ErrMsg = ""; res = "SELECT DATUM,CASHT,NETROOM from ["+File+"]"; // res = "SELECT DATUM,CASHT,NETROOM from ["+File+"] WHERE (DATUM = 20010208)"; // res = "SELECT DATUM,CASHT,NETROOM from ["+File+"] WHERE (DATUM = '20010208')"; // gDB.OpenEx(DBName,CDatabase::noOdbcDialog); gDB.OpenEx(DBName,CDatabase::forceOdbcDialog ); TRY { rs.Open( CRecordset::forwardOnly, res ); NbRecord = rs.GetRecordCount(); } CATCH(CDBException, e) { ErrMsg ="**ERR: "+e->m_strError.Left(70); } END_CATCH if (ErrMsg == "") { while(!rs.IsEOF()) { rs.GetFieldValue( "DATUM", DATUM); rs.GetFieldValue( "CASHT", CASHT); rs.GetFieldValue( "NETROOM", NETROOM); rs.MoveNext(); } } rs.Close(); gDB.Close(); Used like this, it works but I have some problems : 1) I am obliged go thru the ODBC display to give again the name of the file, I can't set it by program ... why ? How can I do this and use the noOdbcDialog option (When I try to use it, ODBC tells me that it can't find GSTAT.DBF)? Furthermore, ODBC dialog is based on program directory, not on the path. If I add the Path to the name in File variable, ODBC tells me that it can't find C:\Documents and Settings\dd\Mes documents\GSTAT.DBF ... 2) If I add the clause "WHERE" to my request, no record is returned ... why ? Can't I use it ? Am I obliged to read all records and test the DATUM field by program ? 3) what is the format of the date in the file ? Excel says 08/02/2001, Word says 20010208 and DATUM = 2001-02-06 when debugging my code ... When I use a date in format yyyy-mm-dd or dd/mm/yyyy ODBC tells me that my format is not compatible with data type Thanks in advance DD
-
Hello, I use the ODBC driver "dBase Files" (installed on my station by microsoft application, I guess). hereunder is the code I wrote to access the C:\Documents and Settings\dd\Mes documents\GSTAT.DBF file : #define DBName "DSN=dBASE Files;UID=WD:" ... CDatabase gDB; CRecordset rs(&gDB); CString res; CString ErrMsg; CString CASHT; CString NETROOM; CString DATUM; int NbRecord; CString Path = "C:\\Documents and Settings\\dd\\Mes documents\\" ; CString File = "GSTAT.DBF" ; ErrMsg = ""; res = "SELECT DATUM,CASHT,NETROOM from ["+File+"]"; // res = "SELECT DATUM,CASHT,NETROOM from ["+File+"] WHERE (DATUM = 20010208)"; // res = "SELECT DATUM,CASHT,NETROOM from ["+File+"] WHERE (DATUM = '20010208')"; // gDB.OpenEx(DBName,CDatabase::noOdbcDialog); gDB.OpenEx(DBName,CDatabase::forceOdbcDialog ); TRY { rs.Open( CRecordset::forwardOnly, res ); NbRecord = rs.GetRecordCount(); } CATCH(CDBException, e) { ErrMsg ="**ERR: "+e->m_strError.Left(70); } END_CATCH if (ErrMsg == "") { while(!rs.IsEOF()) { rs.GetFieldValue( "DATUM", DATUM); rs.GetFieldValue( "CASHT", CASHT); rs.GetFieldValue( "NETROOM", NETROOM); rs.MoveNext(); } } rs.Close(); gDB.Close(); Used like this, it works but I have some problems : 1) I am obliged go thru the ODBC display to give again the name of the file, I can't set it by program ... why ? How can I do this and use the noOdbcDialog option (When I try to use it, ODBC tells me that it can't find GSTAT.DBF)? Furthermore, ODBC dialog is based on program directory, not on the path. If I add the Path to the name in File variable, ODBC tells me that it can't find C:\Documents and Settings\dd\Mes documents\GSTAT.DBF ... 2) If I add the clause "WHERE" to my request, no record is returned ... why ? Can't I use it ? Am I obliged to read all records and test the DATUM field by program ? 3) what is the format of the date in the file ? Excel says 08/02/2001, Word says 20010208 and DATUM = 2001-02-06 when debugging my code ... When I use a date in format yyyy-mm-dd or dd/mm/yyyy ODBC tells me that my format is not compatible with data type Thanks in advance DD
Qadddd wrote: 1) I am obliged go thru the ODBC display to give again the name of the file, I can't set it by program ... why ? It sounds like the "dBASE Files" DSN is not configured correctly. If it was, there would be no reason to specifiy filenames, database names, paths, etc in your code. Assuming the database exists, you can create the DSN programatically by using
SQLConfigDataSource(NULL, ODBC_ADD_DSN, ...). Qadddd wrote: res = "SELECT DATUM,CASHT,NETROOM from ["+File+"]"; I don't use .dbf files, but I think thatFileshould be the name of a table within the database. Qadddd wrote: If I add the clause "WHERE" to my request, no record is returned ... why ? What type of column is DATUM? It looks sort of like a date column, which means you use #02/08/2001# to compare against.
A rich person is not the one who has the most, but the one that needs the least.
-
Qadddd wrote: 1) I am obliged go thru the ODBC display to give again the name of the file, I can't set it by program ... why ? It sounds like the "dBASE Files" DSN is not configured correctly. If it was, there would be no reason to specifiy filenames, database names, paths, etc in your code. Assuming the database exists, you can create the DSN programatically by using
SQLConfigDataSource(NULL, ODBC_ADD_DSN, ...). Qadddd wrote: res = "SELECT DATUM,CASHT,NETROOM from ["+File+"]"; I don't use .dbf files, but I think thatFileshould be the name of a table within the database. Qadddd wrote: If I add the clause "WHERE" to my request, no record is returned ... why ? What type of column is DATUM? It looks sort of like a date column, which means you use #02/08/2001# to compare against.
A rich person is not the one who has the most, but the one that needs the least.
DavidCrow wrote: It sounds like the "dBASE Files" DSN is not configured correctly. If it was, there would be no reason to specifiy filenames, database names, paths, etc in your code. yes, as I said myself before, I just discover on ODBC pannel the check saying that the current directory is the home directory for my file, I will go deeper in investigations tonight DavidCrow wrote: Assuming the database exists, you can create the DSN programatically by using SQLConfigDataSource(NULL, ODBC_ADD_DSN, ...). that's a good point ! because when I put my little applications on several stations, I always have to configure manually ODBC, I could do it in a little installation executable. The same, if you have a piece of advice to automate the installation of a new ODBC driver ... I take it . DavidCrow wrote: I don't use .dbf files, but I think that File should be the name of a table within the database. That's what my MySQL statement looks like, but for .dbf files ?? I looked at 2 or 3 examples using this syntax and it seems that I can access the file like this without too many problems ... 2 good reasons to keep it like that until someone tells me that my manner is totally wrong ;-)) DavidCrow wrote: It looks sort of like a date column, which means you use #02/08/2001# to compare against. It's probably the reason, I will test it ASAP Thanks DD