dbWriteTable fails for data frame with zero rows

[ggrothendieck]

dbWriteTable fails if the data frame has no rows. (Similar code does work with the RH2 driver for the H2 database.)

> library(RSQLite)
Loading required package: DBI
> con <- dbConnect(SQLite())
> DF <- data.frame(a = numeric(0), b = numeric(0))
> dbWriteTable(con, "DF", DF)
[1] FALSE
Warning message:
In value[[3L]](cond) : bind.data must have non-zero dimensions

[seth]

Fails is maybe a bit strong. It returns FALSE with a warning. What
should it do instead and is it worth making a breaking change for it?

Seth Falcon | @sfalcon | http://userprimary.net/

[jangorecki]

Hi,
Just want to put my comment on that:
while inserting 0 rows data.frame the function should return TRUE without error as it may be a standard process, the same as you would do by SQL query:

INSERT table1 SELECT TOP 0 * FROM table0

and for clarification:
TOP 0 is an MSSQL syntax of Oracle ROWNUM < 1 or SQLite LIMIT 0 (?).

also if target table do not exists the function should create empty table with structure of data.frame.

[ggrothendieck]

Well, it fails to write a zero row table which was the expected behavior. See:
http://stackoverflow.com/questions/24392032/using-insert-statement-in-sqldf-within-r/24392568#24392568
for an example that came up.

If we try it with the H2 database it writes a zero row table as expected:

> library(RH2)
Loading required package: chron
Loading required package: RJDBC
Loading required package: rJava
> con <- dbConnect(H2())
> DF <- data.frame(a = numeric(0), b = numeric(0))
> dbWriteTable(con, "DF", DF)
[1] TRUE
> dbGetQuery(con, "select count(*) from DF")
  COUNT...
1        0

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.