[Gretl-devel] Re: Problem with ODBC query
Am 25.03.20 um 13:24 schrieb Allin Cottrell:
On Wed, 25 Mar 2020, atecon(a)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