Re: [Gretl-users] Must I always know the number of rows in advance when making an ODBC query?
On 07/19/2018 11:23 AM, Riccardo (Jack) Lucchetti wrote:
On Thu, 19 Jul 2018, Peter H. Lemieux wrote:
> From gretl, if I run
>
> ? open dsn=polls user=phl --odbc
> Connected to ODBC data source 'polls'
> ? data dem rep query="select democrat,republican from generic" --odbc
> ? print dem
> The symbol 'dem' is undefined
>
> However, if I precede the data command with "smpl 1 228", gretl imports
> all 228 rows. Is there a way to import data from SQL that doesn't require
> knowing the number of observations in advance? I'd like just to point
> gretl at a database, give it a query like the above, and have it populate
> the database with all the records it finds? Is that not possible? It
> works like that when importing data from CSV files.
>
> Thanks!
> Peter
Hm, I don't think this is possible at present. The problem is that there is
no way of guessing how many rows the table returned by a SQL query will have
until you execute the query itself.
Would it be possible for to initialise your dataset with a large number
of observations (say, like, "nulldata 100000") and then drop the empty ones?
I thought about trying that. One somewhat inefficient solution would be to
run the query twice, the first time to obtain the number of rows. Then the
client could build the empty data table and execute the query again to fill
it. Given how much caching goes on in DB transactions that approach might
not be all that slow.
You can wrap the input query in another select to get the number of rows:
select count(*) from (input_query) as foo;
so I think it would be relatively easy to add. Unfortunately I don't
program in C, so I don't have the tools to implement this. I'd activate it
with a command-line option to the data command like --fill.
Still, couldn't the client just keep reading records from the DB and adding
them to the open gretl database until it reaches the end? Does it count the
number of rows in a CSV file before reading it?
Peter