[Gretl-devel] Re: Problem with ODBC query
Hi Allin,
thanks for your work. Unfortunately, it only partly works here.
It works fine in case both imported series, here "klima" and "klima"
saldo are both of type SQL_DECIMAL and column "DATUM" is of type SQL_DATE:
<hansl>
nulldata 5 -p
setobs 7 2018-01-01 --time-series
string q = "SELECT DATUM, KLIMA, KLIMA_SALDO FROM OV_AS_LAB_LUMEN.IFO_DATA"
data klima klima_saldo obs-format="%s" query=q --odbc --verbose
</hansl>
<sql-msg>
SQL query: 'SELECT DATUM, KLIMA, KLIMA_SALDO FROM OV_AS_LAB_LUMEN.IFO_DATA ORDER BY
DATUM'
SQLConnect(dbc,...): SQL_SUCCESS
Number of columns = 3
col 1 (DATUM): data_type SQL_DATE, size 10, digits 0, nullable 2 (?)
col 2 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 (?)
col 3 (KLIMA_SALDO): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 (?)
Number of rows (from SQLRowCount): 1886
Fetch, row 0: col 0: 10 bytes (obs='2015-01-01'); col 1: data value 98.7; col 2:
data value 15.7
Fetch, row 1: col 0: 10 bytes (obs='2015-01-02'); col 1: data value 98.7; col 2:
data value 15.7
Fetch, row 2: col 0: 10 bytes (obs='2015-01-03'); col 1: data value 98.7; col 2:
data value 15.7
Fetch, row 3: col 0: 10 bytes (obs='2015-01-04'); col 1: data value 98.7; col 2:
data value 15.7
Fetch, row 4: col 0: 10 bytes (obs='2015-01-05'); col 1: data value 98.7; col 2:
data value 15.7
Fetch, row 5: col 0: 10 bytes (obs='2015-01-06'); col 1: data value 98.7; col 2:
data value 15.7
<sql-msg/>
However, things fail if an additional column is of type SQL_VARCHAR ("SEKTOR").
In this case, the fetched values of "SEKTOR" are all missing. On the other side,
if I only fetch "SEKTOR while "DATUM" acts as the observation-series,
things are ok.
Here is the example having a mix of DECIMAL and VARCHAR series (interestingly, the string
value of "SEKTOR" is correctly fetched but not passed to gretl):
<hansl>
nulldata 5 -p
setobs 7 2018-01-01 --time-series
string q = "SELECT DATUM, KLIMA, KLIMA_SALDO, SEKTOR FROM
OV_AS_LAB_LUMEN.IFO_DATA"
data klima klima_saldo sektor obs-format="%s" query=q --odbc --verbose
</hansl>
<sql-msg>
SQL query: 'SELECT DATUM, KLIMA, KLIMA_SALDO, SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA
ORDER BY DATUM'
SQLConnect(dbc,...): SQL_SUCCESS
Number of columns = 4
col 1 (DATUM): data_type SQL_DATE, size 10, digits 0, nullable 2 (?)
col 2 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 (?)
col 3 (KLIMA_SALDO): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 (?)
col 4 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 (?)
binding data col 4 to strvals[2] (len = 20)
Number of rows (from SQLRowCount): 1886
Fetch, row 0: col 0: 10 bytes (obs='2015-01-01'); col 1: data value 98.7; col 2:
data value 15.7; col 3: string data value 'gesamt' -> 0
Fetch, row 1: col 0: 10 bytes (obs='2015-01-02'); col 1: data value 98.7; col 2:
data value 15.7; col 3: string data value 'gesamt' -> 0
Fetch, row 2: col 0: 10 bytes (obs='2015-01-03'); col 1: data value 98.7; col 2:
data value 15.7; col 3: string data value 'gesamt' -> 0
Fetch, row 3: col 0: 10 bytes (obs='2015-01-04'); col 1: data value 98.7; col 2:
data value 15.7; col 3: string data value 'gesamt' -> 0
Fetch, row 4: col 0: 10 bytes (obs='2015-01-05'); col 1: data value 98.7; col 2:
data value 15.7; col 3: string data value 'gesamt' -> 0
Fetch, row 5: col 0: 10 bytes (obs='2015-01-06'); col 1: data value 98.7; col 2:
data value 15.7; col 3: string data value 'gesamt' -> 0
</>
Thanks,
Artur