[R] RSQLite and fetching rows from multiple tables

From: Jonathan Greenberg <greenberg_at_ucdavis.edu>
Date: Mon, 21 Jun 2010 15:35:48 -0700


I'm trying to use RSQLite statement to cycle through a large database in "chunks" via the fetch(...,n=20000). As far as I can tell, however, it keeps re-fetching the same beginning set of rows -- any ideas what might be wrong with the following code? The select statement is pulling from multiple tables. I apologize in advance -- the DB is about 4gb so I can't make it easily available, but I'm guessing someone can tell what I'm doing wrong just from the example code below:

        climate_query=dbSendQuery(con,"SELECT
PPT_PRISM.data_vector,TMIN_PRISM.data_vector,TMAX_PRISM.data_vector,RAD_RSUN.data_vector,DEM_25M.data_vector,UWIND_NARR.data_vector,VWIND_NARR.data_vector,PPT_PRISM.date_vector,TMIN_PRISM.date_vector,TMAX_PRISM.date_vector,RAD_RSUN.date_vector,DEM_25M.date_vector,UWIND_NARR.date_vector,VWIND_NARR.date_vector FROM PPT_PRISM,TMIN_PRISM,TMAX_PRISM,RAD_RSUN,DEM_25M,UWIND_NARR,VWIND_NARR")         

	while(!dbHasCompleted(climate_query)){
		climate_data_fetch <- fetch(climate_query, n = db_fetch_n)
                print(climate_data_fetch[1,1]
		print(dbGetRowCount(climate_query))
                # I want to do something with the climate_data_fetch
"chunk" here, write out the results to a new table, and go to the next set of rows.
	}
	dbClearResult(climate_query)

The first print statement shows the same thing over and over again, and the second print statement (dbGetRowCount) eventually exceeds the total number of rows in each of the tables.

Is the issue that I'm selecting from multiple tables at once (they all have the same length)? If so, what would be a better approach to doing this? Is there a way to "control" fetch so it pulls a range of rows?

--j



R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code. Received on Tue 22 Jun 2010 - 01:34:27 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Tue 22 Jun 2010 - 02:00:34 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive