[Gretl-devel] Re: Problem with ODBC query
> On Sun, 29 Mar 2020, Riccardo (Jack) Lucchetti wrote: > > > OK, "--no-align" it is! That's now in git. This is great Allin! Works perfect! The current implementation is very useful for (at least my) daily work. Best wishes and stay all healthy, Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
On Sun, 29 Mar 2020, Riccardo (Jack) Lucchetti wrote: On Sun, 29 Mar 2020, ate...@posteo.de wrote: I guess we could institute "fill from the top" behaviour for ODBC importation if we don't have any information on how the observations should be aligned. Maybe this should be conditional on an option flag? If so, any ideas for the name of the option? Good idea. My candidates are: * fill * fill-dataset * no-alignment * drop-to-dataset * drop Since this is something that should be used with great care, I'd rather go for something a little more scary, such as --no-align. OK, "--no-align" it is! That's now in git. 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/
[Gretl-devel] Re: Problem with ODBC query
On Sun, 29 Mar 2020, ate...@posteo.de wrote: I guess we could institute "fill from the top" behaviour for ODBC importation if we don't have any information on how the observations should be aligned. Maybe this should be conditional on an option flag? If so, any ideas for the name of the option? Good idea. My candidates are: * fill * fill-dataset * no-alignment * drop-to-dataset * drop Since this is something that should be used with great care, I'd rather go for something a little more scary, such as --no-align. --- Riccardo (Jack) Lucchetti Dipartimento di Scienze Economiche e Sociali (DiSES) Università Politecnica delle Marche (formerly known as Università di Ancona) r.lucche...@univpm.it http://www2.econ.univpm.it/servizi/hpp/lucchetti ---___ 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/
[Gretl-devel] Re: Problem with ODBC query
> On Sat, 28 Mar 2020, Riccardo (Jack) Lucchetti wrote: > > > I guess we could institute "fill from the top" behaviour for ODBC > importation if we don't have any information on how the observations > should be aligned. Maybe this should be conditional on an option > flag? If so, any ideas for the name of the option? Good idea. My candidates are: * fill * fill-dataset * no-alignment * drop-to-dataset * drop Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
On Sat, 28 Mar 2020, Riccardo (Jack) Lucchetti wrote: On Sat, 28 Mar 2020, Artur Tarassow wrote: Simple but very nice idea, Jack! Thanks. However, in case one is working with multiple large tables which must be joined, this is not very efficient since the DB needs to process everything twice. True. I guess we could institute "fill from the top" behaviour for ODBC importation if we don't have any information on how the observations should be aligned. Maybe this should be conditional on an option flag? If so, any ideas for the name of the option? 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/
[Gretl-devel] Re: Problem with ODBC query
On Sat, 28 Mar 2020, Artur Tarassow wrote: Simple but very nice idea, Jack! Thanks. However, in case one is working with multiple large tables which must be joined, this is not very efficient since the DB needs to process everything twice. True. --- Riccardo (Jack) Lucchetti Dipartimento di Scienze Economiche e Sociali (DiSES) Università Politecnica delle Marche (formerly known as Università di Ancona) r.lucche...@univpm.it http://www2.econ.univpm.it/servizi/hpp/lucchetti ---___ 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/
[Gretl-devel] Re: Problem with ODBC query
Am 28.03.20 um 10:11 schrieb Riccardo (Jack) Lucchetti:
On Sat, 28 Mar 2020, Artur Tarassow wrote:
Hi Allin,
this issue seems fixed. The query yields the correct results now.
Thank you!
But let me pose another issue: Suppose one wants to fetch
cross-sectional data from the DB but one has no idea of how many rows
the table has. In the past it was possible to initialize a very large
gretl dataset of R rows and to fetch some table with r<=R rows. Gretl
just filled the first r rows with data fetched, and the remaining R-r
rows where all NA and could be dropped in another step.
I've used the following workaround:
clear
open dsn=MyDSN user=jack password=pwd --odbc
nulldata 1
data tmp query="select count(*) from tablename" --odbc
scalar n = tmp[1]
nulldata n --preserve
q1 = sprintf("select whatever from somedata limit %d", $nobs)
data foobar query=q1 --odbc
Simple but very nice idea, Jack! Thanks.
However, in case one is working with multiple large tables which must be
joined, this is not very efficient since the DB needs to process
everything twice.
Artur
___
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/
[Gretl-devel] Re: Problem with ODBC query
On Sat, 28 Mar 2020, Artur Tarassow wrote:
Hi Allin,
this issue seems fixed. The query yields the correct results now. Thank you!
But let me pose another issue: Suppose one wants to fetch cross-sectional
data from the DB but one has no idea of how many rows the table has. In the
past it was possible to initialize a very large gretl dataset of R rows and
to fetch some table with r<=R rows. Gretl just filled the first r rows with
data fetched, and the remaining R-r rows where all NA and could be dropped in
another step.
I've used the following workaround:
clear
open dsn=MyDSN user=jack password=pwd --odbc
nulldata 1
data tmp query="select count(*) from tablename" --odbc
scalar n = tmp[1]
nulldata n --preserve
q1 = sprintf("select whatever from somedata limit %d", $nobs)
data foobar query=q1 --odbc
---
Riccardo (Jack) Lucchetti
Dipartimento di Scienze Economiche e Sociali (DiSES)
Università Politecnica delle Marche
(formerly known as Università di Ancona)
r.lucche...@univpm.it
http://www2.econ.univpm.it/servizi/hpp/lucchetti
---___
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/
[Gretl-devel] Re: Problem with ODBC query
Am 25.03.20 um 13:24 schrieb Allin Cottrell: On Wed, 25 Mar 2020, ate...@posteo.de wrote: On Mon, 23 Mar 2020, atecon(a)posteo.de wrote: I see what you mean (I think!). Getting this right involves some brain-bending mapping between 1-based SQL columns, 0-based C arrays, and 1-based dataset series. But I believe this should now work better in current git. Allin Hi Allin, sorry for another post on this. While the working of SQL_DATE works nicely, I still get wrongly fetched data if the first selected column is of type SQL_DATE, the second is of type SQL_VARCHAR -- the format of the remaining ones does not matter. QUERY 1: WORKS 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 (?) binding col 2 to xt[0] col 3 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 (?) binding col 3 to strvals[1] (len = 20) <> nulldata NOBS -p setobs 7 2018-01-01 --time-series string Q = "SELECT DATUM, KLIMA, SEKTOR FROM ABC.DEF" data klima sektor obs-format="%s" query=Q --odbc --verbose print dataset -o index klima sektor 2018-01-01 1 104.8 gesamt 2018-01-02 2 104.8 gesamt 2018-01-03 3 104.8 gesamt 2018-01-04 4 104.8 gesamt 2018-01-05 5 104.8 gesamt QUERY 2: FAILS <> nulldata NOBS -p setobs 7 2018-01-01 --time-series string Q = "SELECT DATUM, SEKTOR, KLIMA FROM ABC.DEF" data sektor klima obs-format="%s" query=Q --odbc print dataset -o As you can see, "sektor" has no string-values any more and for "klima" are values are missing. index sektor klima 2018-01-01 1 1 2018-01-02 2 1 2018-01-03 3 1 2018-01-04 4 1 2018-01-05 5 1 Thanks for your patience with this, Artur. I now see what was going wrong in this case, and it should be fixed in git. (The actual ODBC import was fine, but the transcription into the gretl dataset was messed up.) Hi Allin, this issue seems fixed. The query yields the correct results now. Thank you! But let me pose another issue: Suppose one wants to fetch cross-sectional data from the DB but one has no idea of how many rows the table has. In the past it was possible to initialize a very large gretl dataset of R rows and to fetch some table with r<=R rows. Gretl just filled the first r rows with data fetched, and the remaining R-r rows where all NA and could be dropped in another step. This made it very easy to define all the structure after having fetched data. By "defining structure" I mean setting via the 'setobs' command the time-series data set afterwards (which is now pretty easy due to the nice handling of the SQL_DATE format) but also setting up the panel data set afterwards. For panel data, the current implementation requires (1) to set up a panel index which needs to implemented via SQL to make the 'obs-format' option to work properly as otherwise gretl doesn't know how to align the data. Or do I miss something here? Thanks, Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
On Wed, 25 Mar 2020, ate...@posteo.de wrote: On Mon, 23 Mar 2020, atecon(a)posteo.de wrote: I see what you mean (I think!). Getting this right involves some brain-bending mapping between 1-based SQL columns, 0-based C arrays, and 1-based dataset series. But I believe this should now work better in current git. Allin Hi Allin, sorry for another post on this. While the working of SQL_DATE works nicely, I still get wrongly fetched data if the first selected column is of type SQL_DATE, the second is of type SQL_VARCHAR -- the format of the remaining ones does not matter. QUERY 1: WORKS 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 (?) binding col 2 to xt[0] col 3 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 (?) binding col 3 to strvals[1] (len = 20) <> nulldata NOBS -p setobs 7 2018-01-01 --time-series string Q = "SELECT DATUM, KLIMA, SEKTOR FROM ABC.DEF" data klima sektor obs-format="%s" query=Q --odbc --verbose print dataset -o indexklima sektor 2018-01-011104.8 gesamt 2018-01-022104.8 gesamt 2018-01-033104.8 gesamt 2018-01-044104.8 gesamt 2018-01-055104.8 gesamt QUERY 2: FAILS <> nulldata NOBS -p setobs 7 2018-01-01 --time-series string Q = "SELECT DATUM, SEKTOR, KLIMA FROM ABC.DEF" data sektor klima obs-format="%s" query=Q --odbc print dataset -o As you can see, "sektor" has no string-values any more and for "klima" are values are missing. index sektorklima 2018-01-0111 2018-01-0221 2018-01-0331 2018-01-0441 2018-01-0551 Thanks for your patience with this, Artur. I now see what was going wrong in this case, and it should be fixed in git. (The actual ODBC import was fine, but the transcription into the gretl dataset was messed up.) 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/
[Gretl-devel] Re: Problem with ODBC query
> On Mon, 23 Mar 2020, atecon(a)posteo.de wrote: > > > I see what you mean (I think!). Getting this right involves some > brain-bending mapping between 1-based SQL columns, 0-based C arrays, > and 1-based dataset series. But I believe this should now work > better in current git. > > Allin Hi Allin, sorry for another post on this. While the working of SQL_DATE works nicely, I still get wrongly fetched data if the first selected column is of type SQL_DATE, the second is of type SQL_VARCHAR -- the format of the remaining ones does not matter. QUERY 1: WORKS 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 (?) binding col 2 to xt[0] col 3 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 (?) binding col 3 to strvals[1] (len = 20) <> nulldata NOBS -p setobs 7 2018-01-01 --time-series string Q = "SELECT DATUM, KLIMA, SEKTOR FROM ABC.DEF" data klima sektor obs-format="%s" query=Q --odbc --verbose print dataset -o indexklima sektor 2018-01-011104.8 gesamt 2018-01-022104.8 gesamt 2018-01-033104.8 gesamt 2018-01-044104.8 gesamt 2018-01-055104.8 gesamt QUERY 2: FAILS <> nulldata NOBS -p setobs 7 2018-01-01 --time-series string Q = "SELECT DATUM, SEKTOR, KLIMA FROM ABC.DEF" data sektor klima obs-format="%s" query=Q --odbc print dataset -o As you can see, "sektor" has no string-values any more and for "klima" are values are missing. index sektorklima 2018-01-0111 2018-01-0221 2018-01-0331 2018-01-0441 2018-01-0551 Thank you, Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
On Mon, 23 Mar 2020, ate...@posteo.de wrote:
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:
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
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
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):
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
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
I see what you mean (I think!). Getting this right involves some
brain-bending mapping between 1-based SQL columns, 0-based C arrays,
and 1-based dataset series. But I believe this should now work
better in current git.
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/
[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:
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
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
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):
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
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
___
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/
[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 -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"
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 MMDD, 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, "-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:
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);
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.
nulldata 8
setobs 12 2020:01
string DSN =
string USER =
string PW =
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
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/
[Gretl-devel] Re: Problem with ODBC query
Hi all, First, for some reason, I did not receive latest replies on the mailing list as emails today. So I just copy and paste Allin's last remark. Sorry, but I've explored some further issues: 1) "DATUM" is a date string in the format -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" 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! Number of rows (from SQLRowCount): 10 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS 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. True, "DATUM" is not of type "date string". According to the DB, column "DATUM" is of the type "DATE". I've attached a screenshot of its details. But I guess that explains the current issue then. My current workaround is to (1) Fetch "DATUM" as casted ISO8601 date, and (2) fetch the remaining columns. The resulting data set looks fine then. Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
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 -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" 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. --- Riccardo (Jack) Lucchetti Dipartimento di Scienze Economiche e Sociali (DiSES) Università Politecnica delle Marche (formerly known as Università di Ancona) r.lucche...@univpm.it http://www2.econ.univpm.it/servizi/hpp/lucchetti ---___ 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/
[Gretl-devel] Re: Problem with ODBC query
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 -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" 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. 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/
[Gretl-devel] Re: Problem with ODBC query
Am 21.03.20 um 17:48 schrieb Artur Tarassow: Am 21.03.20 um 17:07 schrieb Allin Cottrell: On Fri, 20 Mar 2020, Artur Tarassow wrote: I had again a look at this issue. So the connection is there and the correct data gets fetched. However, the resulting series "klima" has only zeros. The error message is "Error executing script: halting". The terminal output is: SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 5' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 5 Fetch, row 0: col 0: data value 102.2 Fetch, row 1: col 0: data value 102.5 Fetch, row 2: col 0: data value 99.7 Fetch, row 3: col 0: data value 102.6 Fetch, row 4: col 0: data value 101.5 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS I know that it worked before latest feature implementing fetching string-valued series. I'm not able to replicate the proble (yet). To make debugging of ODBC a bit easier I've added an ODBC-specific --verbose option to the "data" command. Use that option and you'll get debugging spew in the regular gretl output (not stderr any more). I've also added a little more detail. I'm attaching a little tar.gz file with kit to try out my attempted replication of the problem. (For me the example works OK.) There's a README in the package, but here's the gist: create a tiny database that mimics the data Artur showed, then read from it using gretl. Thanks for your effort, Allin. I think I have it now. The issue is that I have initialized a dataset (via the nulldata command) of length 10 but the returned series has only length 5. When bot the length of the existing data set and the fetched one coincide, all works well. I am pretty sure that up to a month ago or so, it worked well in case the length of the existing data set was larger than that of the newly fetched one. Usually I don't know exactly how long the returned series will be. So I initialize a large data set, fetch data and restrict the data set to valid observations only. _This_ does not seem to work any more and causes trouble. To summarize: 1) In case no data set is open, returns just "Error executing script" -- not very informative I guess ;-) 2) In case the length of the current data set is longer than the fetched one, all values of newly attached data are zero as explained before. 3) In case the length of the current data set is smaller than of the fetched one, the error message "Error executing script" occurs. Sorry, but I've explored some further issues: 1) "DATUM" is a date string in the format -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" 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! Number of rows (from SQLRowCount): 10 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS 2) This workaround casting the string value to ISO8601 date format helps though: QUERY: "SELECT TO_CHAR(TO_DATE(DATUM), 'MMdd') DATUM FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10" SQL query: 'SELECT TO_CHAR(TO_DATE(DATUM), 'MMdd') DATUM FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10' SQLConnect(dbc,...): SQL_SUCCESS Number of columns = 1 col 1 (DATUM): data_type SQL_VARCHAR, size 8, digits 0, invalid 'nullable' value! binding data col 1 to strvals[0] (len = 8) Number of rows (from SQLRowCount): 10 Fetch, row 0: col 0: string data value '20180706' -> 1 Fetch, row 1: col 0: string data value '20190319' -> 2 Fetch, row 2: col 0: string data value '20151021' -> 3 Fetch, row 3: col 0: string data value '20150406' -> 4 Fetch, row 4: col 0: string data value '20170302' -> 5 Fetch, row 5: col 0: string data value '20191027' -> 6 Fetch, row 6: col 0: string data value '20190711' -> 7 Fetch, row 7: col 0: string data value '20190712' -> 8 Fetch, row 8: col 0: string data value '20150207' -> 9 Fetch, row 9: col 0: string data value '20160411' -> 10 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS 3) For "simple" string valued series such as “sektor” everything works fine: QUERY: "SELECT sektor, klima FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10" 4) A very weired case: Even though I cast the date string to ISO8601 format, the following query -- a combination of all 3 series -- fails: QUERY: "SELECT TO_CHAR(TO_DATE(DATUM), 'MMdd') AS DATUM, sektor, klima FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10" SQL query: 'SELECT TO_CHAR(TO_DATE(DATUM), 'MMdd') AS DATUM, sektor, klima FROM OV_AS_LAB_LUMEN.IFO
[Gretl-devel] Re: Problem with ODBC query
On Sat, 21 Mar 2020, Artur Tarassow wrote: Am 21.03.20 um 17:07 schrieb Allin Cottrell: On Fri, 20 Mar 2020, Artur Tarassow wrote: I had again a look at this issue. So the connection is there and the correct data gets fetched. However, the resulting series "klima" has only zeros. The error message is "Error executing script: halting". The terminal output is: SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 5' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 5 Fetch, row 0: col 0: data value 102.2 Fetch, row 1: col 0: data value 102.5 Fetch, row 2: col 0: data value 99.7 Fetch, row 3: col 0: data value 102.6 Fetch, row 4: col 0: data value 101.5 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS I know that it worked before latest feature implementing fetching string-valued series. I'm not able to replicate the proble (yet). To make debugging of ODBC a bit easier I've added an ODBC-specific --verbose option to the "data" command. Use that option and you'll get debugging spew in the regular gretl output (not stderr any more). I've also added a little more detail. I'm attaching a little tar.gz file with kit to try out my attempted replication of the problem. (For me the example works OK.) There's a README in the package, but here's the gist: create a tiny database that mimics the data Artur showed, then read from it using gretl. Thanks for your effort, Allin. I think I have it now. The issue is that I have initialized a dataset (via the nulldata command) of length 10 but the returned series has only length 5. When bot the length of the existing data set and the fetched one coincide, all works well. I am pretty sure that up to a month ago or so, it worked well in case the length of the existing data set was larger than that of the newly fetched one. Usually I don't know exactly how long the returned series will be. So I initialize a large data set, fetch data and restrict the data set to valid observations only. _This_ does not seem to work any more and causes trouble. To summarize: 1) In case no data set is open, returns just "Error executing script" -- not very informative I guess ;-) 2) In case the length of the current data set is longer than the fetched one, all values of newly attached data are zero as explained before. 3) In case the length of the current data set is smaller than of the fetched one, the error message "Error executing script" occurs. Hope that helps, Yes, it does. At least we should ensure appropriate error messages for the various failing cases. I might just mention, to align data when the number of values retrieved via ODBC doesn't equal the length of the dataset or sample range, use of one or more "observations" columns in the db table can be very helpful. That's explained in the gretl ODBC doc. 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/
[Gretl-devel] Re: Problem with ODBC query
Am 21.03.20 um 17:07 schrieb Allin Cottrell: On Fri, 20 Mar 2020, Artur Tarassow wrote: I had again a look at this issue. So the connection is there and the correct data gets fetched. However, the resulting series "klima" has only zeros. The error message is "Error executing script: halting". The terminal output is: SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 5' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 5 Fetch, row 0: col 0: data value 102.2 Fetch, row 1: col 0: data value 102.5 Fetch, row 2: col 0: data value 99.7 Fetch, row 3: col 0: data value 102.6 Fetch, row 4: col 0: data value 101.5 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS I know that it worked before latest feature implementing fetching string-valued series. I'm not able to replicate the proble (yet). To make debugging of ODBC a bit easier I've added an ODBC-specific --verbose option to the "data" command. Use that option and you'll get debugging spew in the regular gretl output (not stderr any more). I've also added a little more detail. I'm attaching a little tar.gz file with kit to try out my attempted replication of the problem. (For me the example works OK.) There's a README in the package, but here's the gist: create a tiny database that mimics the data Artur showed, then read from it using gretl. Thanks for your effort, Allin. I think I have it now. The issue is that I have initialized a dataset (via the nulldata command) of length 10 but the returned series has only length 5. When bot the length of the existing data set and the fetched one coincide, all works well. I am pretty sure that up to a month ago or so, it worked well in case the length of the existing data set was larger than that of the newly fetched one. Usually I don't know exactly how long the returned series will be. So I initialize a large data set, fetch data and restrict the data set to valid observations only. _This_ does not seem to work any more and causes trouble. To summarize: 1) In case no data set is open, returns just "Error executing script" -- not very informative I guess ;-) 2) In case the length of the current data set is longer than the fetched one, all values of newly attached data are zero as explained before. 3) In case the length of the current data set is smaller than of the fetched one, the error message "Error executing script" occurs. Hope that helps, Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
On Fri, 20 Mar 2020, Artur Tarassow wrote: I had again a look at this issue. So the connection is there and the correct data gets fetched. However, the resulting series "klima" has only zeros. The error message is "Error executing script: halting". The terminal output is: SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 5' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 5 Fetch, row 0: col 0: data value 102.2 Fetch, row 1: col 0: data value 102.5 Fetch, row 2: col 0: data value 99.7 Fetch, row 3: col 0: data value 102.6 Fetch, row 4: col 0: data value 101.5 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS I know that it worked before latest feature implementing fetching string-valued series. I'm not able to replicate the proble (yet). To make debugging of ODBC a bit easier I've added an ODBC-specific --verbose option to the "data" command. Use that option and you'll get debugging spew in the regular gretl output (not stderr any more). I've also added a little more detail. I'm attaching a little tar.gz file with kit to try out my attempted replication of the problem. (For me the example works OK.) There's a README in the package, but here's the gist: create a tiny database that mimics the data Artur showed, then read from it using gretl. Allin odbc_decimal.tar.gz Description: application/gzip ___ 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/
[Gretl-devel] Re: Problem with ODBC query
Am 16.03.20 um 20:01 schrieb Allin Cottrell: On Mon, 16 Mar 2020, Artur Tarassow wrote: At least with latest git version on Ubuntu 18.10, I am experiencing problems with ODBC. Both of the following queries are pretty standard, and I've succesfully used the first one before. Stuff also works fine using the isql client via the terminal. open dsn=@DSN user=@USER password=@PW --odbc data klima query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc ERROR: Error executing script: halting data klima query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc The weird thing is that I obtain a new series but which has only zero values which is wrong. SQLDisconnect(dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC, dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV, OD_env): SQL_SUCCESS SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 1886 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS Here "sektor" is a string variable. open dsn=@DSN user=@USER password=@PW --odbc data sektor query="SELECT SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc ERROR: Error executing script: halting data sektor query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc Again I get a new series but all values are missings which is also wrong. SQLDisconnect(dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC, dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV, OD_env): SQL_SUCCESS SQL query: 'SELECT SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA' Number of columns = 1 col 1 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 binding data col 1 to strvals[0] (len = 20) Number of Rows (from SQLRowCount) = 1886 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS Any idea what's going on here? Hard to say without access to the database in question. But if you're using self-compiled gretl you could redefine ODBC_DEBUG in plugin/odbc_import.c, line 247, to 1 and see more of what's going on. It's me again. I hope you're all doing well during this crisis time! I had again a look at this issue. So the connection is there and the correct data gets fetched. However, the resulting series "klima" has only zeros. The error message is "Error executing script: halting". The terminal output is: SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 5' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 5 Fetch, row 0: col 0: data value 102.2 Fetch, row 1: col 0: data value 102.5 Fetch, row 2: col 0: data value 99.7 Fetch, row 3: col 0: data value 102.6 Fetch, row 4: col 0: data value 101.5 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS I know that it worked before latest feature implementing fetching string-valued series. Thanks, Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
Am 16.03.20 um 20:01 schrieb Allin Cottrell: On Mon, 16 Mar 2020, Artur Tarassow wrote: At least with latest git version on Ubuntu 18.10, I am experiencing problems with ODBC. Both of the following queries are pretty standard, and I've succesfully used the first one before. Stuff also works fine using the isql client via the terminal. open dsn=@DSN user=@USER password=@PW --odbc data klima query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc ERROR: Error executing script: halting data klima query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc The weird thing is that I obtain a new series but which has only zero values which is wrong. SQLDisconnect(dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC, dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV, OD_env): SQL_SUCCESS SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 1886 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS Here "sektor" is a string variable. open dsn=@DSN user=@USER password=@PW --odbc data sektor query="SELECT SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc ERROR: Error executing script: halting data sektor query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc Again I get a new series but all values are missings which is also wrong. SQLDisconnect(dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC, dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV, OD_env): SQL_SUCCESS SQL query: 'SELECT SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA' Number of columns = 1 col 1 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 binding data col 1 to strvals[0] (len = 20) Number of Rows (from SQLRowCount) = 1886 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS Any idea what's going on here? Hard to say without access to the database in question. But if you're using self-compiled gretl you could redefine ODBC_DEBUG in plugin/odbc_import.c, line 247, to 1 and see more of what's going on. Thanks, I will try to get more details tomorrow. Artur ___ 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/
[Gretl-devel] Re: Problem with ODBC query
On Mon, 16 Mar 2020, Artur Tarassow wrote: At least with latest git version on Ubuntu 18.10, I am experiencing problems with ODBC. Both of the following queries are pretty standard, and I've succesfully used the first one before. Stuff also works fine using the isql client via the terminal. open dsn=@DSN user=@USER password=@PW --odbc data klima query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc ERROR: Error executing script: halting data klima query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc The weird thing is that I obtain a new series but which has only zero values which is wrong. SQLDisconnect(dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC, dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV, OD_env): SQL_SUCCESS SQL query: 'SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA' Number of columns = 1 col 1 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, nullable 2 Number of Rows (from SQLRowCount) = 1886 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS Here "sektor" is a string variable. open dsn=@DSN user=@USER password=@PW --odbc data sektor query="SELECT SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc ERROR: Error executing script: halting data sektor query="SELECT KLIMA FROM OV_AS_LAB_LUMEN.IFO_DATA" --odbc Again I get a new series but all values are missings which is also wrong. SQLDisconnect(dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC, dbc): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV, OD_env): SQL_SUCCESS SQL query: 'SELECT SEKTOR FROM OV_AS_LAB_LUMEN.IFO_DATA' Number of columns = 1 col 1 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, nullable 2 binding data col 1 to strvals[0] (len = 20) Number of Rows (from SQLRowCount) = 1886 SQLFreeHandle(SQL_HANDLE_STMT): SQL_SUCCESS SQLDisconnect: SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_DBC): SQL_SUCCESS SQLFreeHandle(SQL_HANDLE_ENV): SQL_SUCCESS Any idea what's going on here? Hard to say without access to the database in question. But if you're using self-compiled gretl you could redefine ODBC_DEBUG in plugin/odbc_import.c, line 247, to 1 and see more of what's going on. 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/
