2:53 a.m.
On Fri, 11 Mar 2011, Leon Unger wrote:
recently I tried to read in several series via odbc *including
one dummy* series and I forgot to change its entries from "No"
and "YES" to "0" and "1".
However, SQL retrieved for all series the correct number of
observations.*BUT ALL SERIES* were corrupted. That's why two
thoughts come up:
1) I know from importing STATA files that GRETL changes string
entries to number entries. Does GRETL use information provided
by the STATA file, or does it this job by itself? If yes, would
it be possible to add this functionality to the odbc read in
process?
2) If one has always to provide numerical entries and e.g. one
series is not correcltly specified then actually only this
series should be corrupted.
As I said previously, I agree with Jack Lucchetti's comment that
getting an ODBC import right is really the responsbility of the
gretl user. But I also said that it was gretl's responsibility to
avoid crashing on an incorrect SQL query.
I've now investigated further, and I'm less inclined to think
there's a bug in gretl's handling of this issue.
Gretl can convert string-valued variables from Stata dta files
into numerical values because a dta file contains a clear
specification of the "original" type of each variable.
But things are different with ODBC. One uses the function
SQLBindCol() to tell ODBC what data type one wants from each data
column (and for all actual data columns gretl specifies the type
SQL_C_DOUBLE). It's then ODBC's job to convert whatever type comes
out of the db into what was requested.
As a test, I constructed a dummy db using mysql, with one "real"
numerical data column and one column containing "yes" and "no"
strings. I then used gretl to read in those two variables via
unixODBC. What I got was basically what you'd expect: the
numerical series came in OK, and the "yes"/"no" column came in as
a series of zeros (zero being the default when no numeric
conversion is possible).
If you're getting general data corruption when one of the data
columns contains strings, then (a) it might help if you were to
give us an exact recipe for reproducing the problem, but (b) maybe
your bug report should go to Microsoft Corp?
Allin Cottrell
5:19 p.m.
New subject: import data via odbc
Hi Allin and Jack,
after trying to replicate the 'sql yes but series no' outcome with that
dummy db (I've sent) I'm of the opinion
that the bug is caused by access, since with mysql you do not encounter
such problems.
Further I agree that the user has to specify a correct sql statement.
But hey,sometimes it may happen to forget something ;-)
Ok. Nevertheless, it's an intersting topic and this time I can give you
more information:
Here come the augmented dummy db
Reading in two 'DOUBLE' varibales and gretl opened in --debug mode gives
And the observations are correct.
Now come the change: instead of '[Data_StringL].Data2' I try to read in
a string variable named '[Data_StringL].DataDu' containing "Yes" and
"No"
(small remark: I would of course always prefer to use the "Yes/No"
datatype already build in in access and it worksjust great with that!
Interesting applications are connected to "Yes/Now/Perhaps" case and
stuff like that)
the result is the following:
I don't now why this happens and I definitely don't want someone to
bother days and hours to handle a problem which can be solved by the
user by simply assigning
numbers to the string entries.
Best
Leon
Am 12.03.2011 03:53, schrieb Allin Cottrell:
On Fri, 11 Mar 2011, Leon Unger wrote:
> recently I tried to read in several series via odbc *including
> one dummy* series and I forgot to change its entries from "No"
> and "YES" to "0" and "1".
> However, SQL retrieved for all series the correct number of
> observations.*BUT ALL SERIES* were corrupted. That's why two
> thoughts come up:
>
> 1) I know from importing STATA files that GRETL changes string
> entries to number entries. Does GRETL use information provided
> by the STATA file, or does it this job by itself? If yes, would
> it be possible to add this functionality to the odbc read in
> process?
>
> 2) If one has always to provide numerical entries and e.g. one
> series is not correcltly specified then actually only this
> series should be corrupted.
As I said previously, I agree with Jack Lucchetti's comment that
getting an ODBC import right is really the responsbility of the
gretl user. But I also said that it was gretl's responsibility to
avoid crashing on an incorrect SQL query.
I've now investigated further, and I'm less inclined to think
there's a bug in gretl's handling of this issue.
Gretl can convert string-valued variables from Stata dta files
into numerical values because a dta file contains a clear
specification of the "original" type of each variable.
But things are different with ODBC. One uses the function
SQLBindCol() to tell ODBC what data type one wants from each data
column (and for all actual data columns gretl specifies the type
SQL_C_DOUBLE). It's then ODBC's job to convert whatever type comes
out of the db into what was requested.
As a test, I constructed a dummy db using mysql, with one "real"
numerical data column and one column containing "yes" and "no"
strings. I then used gretl to read in those two variables via
unixODBC. What I got was basically what you'd expect: the
numerical series came in OK, and the "yes"/"no" column came in as
a series of zeros (zero being the default when no numeric
conversion is possible).
If you're getting general data corruption when one of the data
columns contains strings, then (a) it might help if you were to
give us an exact recipe for reproducing the problem, but (b) maybe
your bug report should go to Microsoft Corp?
Allin Cottrell
_______________________________________________
Gretl-users mailing list
Gretl-users(a)lists.wfu.edu
http://lists.wfu.edu/mailman/listinfo/gretl-users
5113
days inactive
5113
days old
1 comments
2 participants
participants (2)
-
Allin Cottrell -
Leon Unger