ODBC problem

[Filippo Galimberti]

Dear All,
I have a problem with Revolution accessing databases through ODBC. 
I'm running Revolution Studio 2.6.1 build 152 on Windows XP.

1) Background
We are collecting scientific research data in field using up to 25 
iPaq PocketPC at a time. For data collection we run a simple database 
management software on the PPCs, called HanDBase 
(http://www.ddhsoftware.com/). We collect a wide array of data, so we 
had to put about 50 linked databases on the PPCs. HanDBase has an 
ODBC driver. Revolution is used to access the various HanDBase 
databases on the various PPCs through ODBC, to process and store the 
data, to join other data sources (eg, results of lab analysis on 
biological samples), and prepare tables to be submitted for analysis 
to various numerical and statistical packages.

2) The problem
When we read data from HanDBase using ODBC we got a systematic error 
for date and time fields; all other fields are read correctly. The 
date fields are read on all records as the numeric value "20", and 
the time fields are read on all records as the numeric value "18". We 
had this problem in the past, starting with Revolution 2.1. At the 
time, we were interested in date fields only, and so I sorted out a 
workaround to get the required date information without using ODBC, 
and forgot the problem. Now, we also need to read the time fields, 
and no workaround seems possible.
We can read the same HanDBase date and time fields correctly on all 
ODBC programs that we tested, so it seems a Revolution problem. 
HanDBase can show date fields, time fields, and timestamp fields 
(date + time); all three kinds of fields are coded in the database as 
a timestamp; HanDBase technical support confirmed me that the ODBC 
format for date and time field isd the standard ODBC timestamp 
format, as from Microsoft ODBC API specifications 
(http://msdn2.microsoft.com/en-us/library/ms190234.aspx).

3) What I know.
- If I read the data type using revDatabaseColumnTypes() Revolution 
reports all these fields as "TIMESTAMP"; I understand that Revolution 
also recognizes these other data types: "DATE,TIME,DATETIME", but it 
seems to read the data type of all our date and time fields as 
"TIMESTAMP", and this make sense because it is in accordance to the 
HanDBase ODBC format
- In my scripts I use the revDatabaseColumnNamed() function to access 
the fields in the databases, but the problem exists also if I use the 
revDataFromQuery() function
- I suppose the revDatabaseColumnNamed() function to be coded in the 
revdb.dll, because I found no trace of it in the "revdatabase" script

4) Possible solution
I can use vbscript to access my HanDBase databases through ODBC, save 
the data as ASCII files, the read the ASCII files in Revolution and 
follow with the data processing. I made a rough try and it works, but 
it doesn't make much sense. Moreover, I'm not very happy with this 
solution, because I'm already under pressure from my colleagues to 
abandon Revolution, and move our whole data management system to 
something else. But this will mean to port a huge amount of scripts, 
and I'm a biologist, not a programmer, so I would prefer to sort out 
a solution in Revolution.

I will much appreciate your help, thank you in advance. Sorry for the 
long email.

Kind regards
Filippo Galimberti


Filippo Galimberti, PhD
Elephant Seal Research Group
fil_esrg at eleseal.it
www.eleseal.it