On Mon, 16 Mar 2020, Artur Tarassow wrote: 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

Am 16.03.20 um 20:01 schrieb Allin Cottrell: 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: <Terminal> 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 </Terminal> I know that it worked before latest feature implementing fetching string-valued series. Thanks, Artur

Am 21.03.20 um 17:07 schrieb Allin Cottrell: 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, <data ...--odbc> 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

Am 21.03.20 um 17:48 schrieb Artur Tarassow: 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! 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 </output> 2) This workaround casting the string value to ISO8601 date format helps though: QUERY: "SELECT TO_CHAR(TO_DATE(DATUM), 'yyyyMMdd') DATUM FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10" <output> SQL query: 'SELECT TO_CHAR(TO_DATE(DATUM), 'yyyyMMdd') 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 </output> 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), 'yyyyMMdd') AS DATUM, sektor, klima FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10" <output> SQL query: 'SELECT TO_CHAR(TO_DATE(DATUM), 'yyyyMMdd') AS DATUM, sektor, klima FROM OV_AS_LAB_LUMEN.IFO_DATA LIMIT 10' SQLConnect(dbc,...): SQL_SUCCESS Number of columns = 3 col 1 (DATUM): data_type SQL_VARCHAR, size 8, digits 0, invalid 'nullable' value! binding data col 1 to strvals[0] (len = 8) col 2 (SEKTOR): data_type SQL_VARCHAR, size 20, digits 0, invalid 'nullable' value! binding data col 2 to strvals[1] (len = 20) col 3 (KLIMA): data_type SQL_DECIMAL, size 4, digits 1, invalid 'nullable' value! Number of rows (from SQLRowCount): 10 Fetch, row 0: col 0: string data value '20180706' -> 1; col 1: string data value 'gesamt' -> 1; col 2: data value 102.2 Fetch, row 1: col 0: string data value '20170428' -> 2; col 1: string data value 'gesamt' -> 1; col 2: data value 102.5 Fetch, row 2: col 0: string data value '20151021' -> 3; col 1: string data value 'gesamt' -> 1; col 2: data value 100.7 Fetch, row 3: col 0: string data value '20170518' -> 4; col 1: string data value 'gesamt' -> 1; col 2: data value 102.6 . . . Fetch, row 9: col 0: string data value '20170325' -> 10; col 1: string data value 'gesamt' -> 1; col 2: 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 </output> The data set imported to gretl shows that column "klima" consists of NAs only, no string values are returned for "sektor" but a constant instead; and "datum" has as its first entry the string value "gesamt" which actually belongs to series "sektor". <print_dataset> index datum sektor klima 1 1 gesamt 1 2 2 1 3 3 1 4 4 1 5 5 1 6 6 1 7 7 1 8 8 1 9 9 1 10 10 1 </print_dataset> Best, Artur

On Sat, 21 Mar 2020, Allin Cottrell wrote: 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.lucchetti(a)univpm.it http://www2.econ.univpm.it/servizi/hpp/lucchetti -------------------------------------------------------

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

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 Thank you, Artur

Am 25.03.20 um 13:24 schrieb Allin Cottrell: 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

Am 28.03.20 um 10:11 schrieb Riccardo (Jack) Lucchetti: 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

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? Allin

On Sun, 29 Mar 2020, atecon(a)posteo.de wrote: 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.lucchetti(a)univpm.it http://www2.econ.univpm.it/servizi/hpp/lucchetti -------------------------------------------------------

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