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
_______________________________________________
Gretl-devel mailing list -- gretl-devel@gretlml.univpm.it
To unsubscribe send an email to gretl-devel-le...@gretlml.univpm.it
Website:
https://gretlml.univpm.it/postorius/lists/gretl-devel.gretlml.univpm.it/
