Re: [Gretl-users] Must I always know the number of rows in advance when making an ODBC query?
On Thu, 19 Jul 2018, Peter H. Lemieux wrote:
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?
The way I see it, your solution would be fine if you were going to issue
only one SQL query, but what happens if you execute more than one "data"
statement after the "open" command?
The problem I see with your solution is that in general there's no way to
ensure that data don't spill out at the end, so to speak, because you
could issue more that one query, and the second one could yield more data
than the first one.
And then, there's the whole business of ensuring that the rows match. We
added the facility for ODBC import a long time ago; IMHO, if you could use
csv files, the "join" command would give you a much better tool. One
little-known fact is that, in many cases, gretl handles URLs like local
files. For example, the following works
<hansl>
open http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv
</hansl>
which is sort of neat, but nothing to get excited about. OTOH, this is
really, really cool
<hansl>
nulldata 985
join http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv baths
</hansl>
-------------------------------------------------------
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
-------------------------------------------------------