[Gretl-devel] Re: Problem with ODBC query
On Sat, 21 Mar 2020, Riccardo (Jack) Lucchetti wrote:
On Sat, 21 Mar 2020, Allin Cottrell wrote:
> On Sat, 21 Mar 2020, Artur Tarassow wrote:
>
>> Sorry, but I've explored some further issues:
>>
>> 1) "DATUM" is a date string in the format YYYY-MM-DD. This format seems
to
>> cause trouble as an error occurs for the query:
>> QUERY: "SELECT DATUM FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10"
>>
>> <output>
>> SQL query: 'SELECT DATUM FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10'
>> SQLConnect(dbc,...): SQL_SUCCESS
>> Number of columns = 1
>> col 1 (DATUM): data_type invalid, size 10, digits 0, invalid 'nullable'
>> value!
>
> Hold it there! What's the actual SQL data type of this column? Obviously
> it's not recognized by gretl (though "invalid" is probably too strong a
> judgment). You say it's a "date string", and gretl recognizes these
string
> types: SQL_CHAR, SQL_VARCHAR, SQL_WCHAR and SQL_WVARCHAR.
>
> We don't currently handle the SQL_DATE type. That's something I'm working
> on right now, but it doesn't sound as if this is an SQL_DATE column.
I've used the workaround of using the functions SQL YEAR(), MONTH() and DAY()
in my SELECT statements to go around that, and it's worked ok.
Ah, good idea!
But now (in git, not yet snapshots) I've made a start at supporting
the SQL_DATE data type (which contains year, month and day)
natively. Here's what should happen now:
* If a DATE column is imported as plain data, you get an 8-digit
number YYYYMMDD, i.e. the date in ISO 8601 "basic" format.
* If a DATE column is treated as an observation column, with a
format of "%s", it comes through as a string, "YYYY-MM-DD", which
should work for placing the observation time-wise for the common
time-series frequencies.
A little example follows. First, here's the creation of a db table:
<sql>
create table FOO (
KLIMA decimal (4,1) not null,
obsdate date
);
INSERT INTO FOO (obsdate, KLIMA) values ('2020-01-01', 102.2);
INSERT INTO FOO (obsdate, KLIMA) values ('2020-02-01', 102.5);
INSERT INTO FOO (obsdate, KLIMA) values ('2020-03-01', 99.7);
INSERT INTO FOO (obsdate, KLIMA) values ('2020-04-01', 102.6);
INSERT INTO FOO (obsdate, KLIMA) values ('2020-05-01', 101.5);
</sql>
Second, the hansl script. Note that the single "%s" conversion in
the obs-format directive below tells gretl to use just the
first-mentioned column, "obsdate", to place the observations.
<hansl>
nulldata 8
setobs 12 2020:01
string DSN = <your_dsn>
string USER = <your_username>
string PW = <your_password>
open dsn=@DSN user=@USER password=@PW --odbc
string QRY = "SELECT obsdate,KLIMA FROM FOO"
data klima obs-format="%s" query=QRY --odbc --verbose
print klima -o
</hansl>
The gretl dataset contains 8 observations and there are just 5
values of KLIMA in the db, but that's not a problem because gretl
knows where to put the 5 values.
Allin