[R] sqldf returns Error: database or disk is full

[Uwe Ligges]

And to answer the question where it is stored, I copy the relevant lines:

(tempfile <- tempfile())
system.time( sqldf(sqlCmds, dbname=tempfile, verbose=TRUE) )

whichb apparently was

sqldf: connection <- dbConnect(m, dbname =
"C:\Users\PIWN01~1\AppData\Local\Temp\RtmpSycE1E\file78e464e72860")

in your case.

Best,
Uwe Ligges


On 27.06.2022 19:44, Bert Gunter wrote:
> You should probably heed the warning messages you received before doing
> anything further.
> 
> Bert
> 
> On Mon, Jun 27, 2022, 2:55 PM Ian Worthington via R-help <
> r-help using r-project.org> wrote:
> 
>> I'm using sqldf() to execute a query using a named dbname.  It appears to
>> create an external db but after 30 minutes or so returns:
>>
>> Error: database or disk is full
>> The disk is not full, so have I hit some internal db size limit?  The
>> external db created is about 610MB and doesn't grow, so maybe it's running
>> out  of room for the result set, where ever that is stored?
>>
>> Code being executed is:
>>    sqlCmds <- c(#"create index ixsip on ipTrace100(source)",
>>                 "create index ixdip on ipTrace100(destination)",
>>                 #"create index ixspo on ipTrace100(SrcPo)",
>>                 "create index ixdpo on ipTrace100(DstPo)",
>>                 #"create index ixno  on ipTrace100(`No.`)",
>>                 # "explain query plan
>>                 "select tr1.`No.` InNo,      tr1.Time InTime, tr1.source
>> InSource, /*tr1.destination InDest,*/ tr1.SrcPo InSrcPo, tr1.DstPo InDstPo,
>>                         min(tr2.`No.`) OutNo, tr2.Time OutTime /*,
>> tr2.source OutSource,*/ /*tr2.destination OutDest,*/ /*tr2.SrcPo OutSrcPo*/
>> /*, tr2.DstPo OutDstPo */
>>                 from main.ipTrace100 tr1
>>                 join main.ipTrace100 tr2
>>                 on tr1.destination = '10.27.187.1' and
>>                    tr1.source = tr2.destination and
>>                    tr1.SrcPo = tr2.DstPo and
>>                    tr1.`No.` < tr2.`No.`
>>                 group by tr1.`No.`"
>>                 )
>>    (tempfile <- tempfile())
>>    system.time( sqldf(sqlCmds, dbname=tempfile, verbose=TRUE) )
>> and console log:
>>
>> |
>> |  |
>>
>>>    system.time( sqldf(sqlCmds, dbname=tempfile, verbose=TRUE) )
>> sqldf: library(RSQLite)
>> sqldf: m <- dbDriver("SQLite")
>> sqldf: connection <- dbConnect(m, dbname =
>> "C:\Users\PIWN01~1\AppData\Local\Temp\RtmpSycE1E\file78e464e72860")
>> sqldf: initExtension(connection)
>> sqldf: dbWriteTable(connection, 'ipTrace100', ipTrace100, row.names =
>> FALSE)
>> sqldf: dbGetQuery(connection, 'create index ixdip on
>> ipTrace100(destination)')
>> Warning in result_fetch(res using ptr, n = n) :
>>    SQL statements must be issued with dbExecute() or dbSendStatement()
>> instead of dbGetQuery() or dbSendQuery().
>> sqldf: dbGetQuery(connection, 'create index ixdpo on ipTrace100(DstPo)')
>> Warning in result_fetch(res using ptr, n = n) :
>>    SQL statements must be issued with dbExecute() or dbSendStatement()
>> instead of dbGetQuery() or dbSendQuery().
>> sqldf: dbGetQuery(connection, 'select tr1.`No.` InNo,      tr1.Time
>> InTime, tr1.source InSource, /*tr1.destination InDest,*/ tr1.SrcPo InSrcPo,
>> tr1.DstPo InDstPo,
>>                         min(tr2.`No.`) OutNo, tr2.Time OutTime /*,
>> tr2.source OutSource,*/ /*tr2.destination OutDest,*/ /*tr2.SrcPo OutSrcPo*/
>> /*, tr2.DstPo OutDstPo */
>>                 from main.ipTrace100 tr1
>>                 join main.ipTrace100 tr2
>>                 on tr1.destination = '10.27.187.1' and
>>                    tr1.source = tr2.destination and
>>                    tr1.SrcPo = tr2.DstPo and
>>                    tr1.`No.` < tr2.`No.`
>>                 group by tr1.`No.`')
>> Error: database or disk is full
>> sqldf: dbDisconnect(connection)
>> sqldf: file.remove(dbname)
>> Timing stopped at: 413.1 1081 1780 |
>>
>>
>> |  |
>>
>>
>>
>>
>> Best wishes / Mejores deseos /  Meilleurs vœux
>>
>> Ian ...
>>          [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> 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.
>>
> 
> 	[[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help using r-project.org mailing list -- To UNSUBSCRIBE and more, see
> 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.