Error: Cannot pass NA to dbQuoteIdentifier() in sqldf package in R

Question

Error: Cannot pass NA to dbQuoteIdentifier()

In addition: Warning message:

In field_types[] <- field_types[names(data)] :
  number of items to replace is not a multiple of replacement length

This is the error message i am getting upon trying to run anything with sqldf package today.the same queries which ran yesterday dont run today, what am i doing wrong?

Answer 1

I too encountered the same error:

    ## step1: encountered the error as below while joining two tables
    screens_temp_2 = sqldf("SELECT a.* , b.ue as 'sp_used_ue' , b.te as 
    'sp_used_te'  from screens_temp a left outer join sp_temp b on  
    a.screen_name = b.screen_name ")
    Error: Cannot pass NA to dbQuoteIdentifier()
    In addition: Warning message:
    In field_types[] <- field_types[names(data)] :
    number of items to replace is not a multiple of replacement length
    ##  step2: while checking the column names , this is what i found
   colnames(screens_temp)
     [1] "screen_name" "usv"         "tsv"         "20_ue"       "20_te"      
    [6] "40_ue"       "40_te"       "60_ue"       "60_te"       "80_ue"      
    [11] "80_te"       "100_ue"      "100_te"      "sp_load_ue"  "sp_load_te" 
   [16] "sp_load_ue"  "sp_load_te" 

The above result shows that sp_load_ue and sp_load_te are repeated.

    ## below i corrected the column names:
    colnames(screens_temp) <- c("screen_name", "usv", "tsv", "20_ue", "20_te", "40_ue"   ,    "40_te"   ,    "60_ue"   ,    "60_te"    ,   "80_ue" ,  "80_te"     ,"100_ue"  ,    "100_te"   ,   "sp_load_ue" , "sp_load_te" , "sp_used_ue" , "sp_used_te" )
     write.table(screens_temp, "screens_temp_corrected.csv",  row.names = FALSE ,col.names = TRUE, sep = ",")

    ## again i ran step 1, it worked fine.

Note: I think there is a bug in sqldf due to which it allows column names to be repeated while assigning output to a dataframe. It should throw an error/warning while assigning the output to a dataframe so that the user can rename the columns appropriately.

Answer 2

I had the same problem:

Error: Cannot pass NA to dbQuoteIdentifier()
In addition: Warning message:
In field_types[] <- field_types[names(data)] :
number of items to replace is not a multiple of replacement length

after some research, I noticed I selected the same column twice in one table:

table1<- sqldf("select columnA,
                       columnA,
                       keyA
                from tableA")
table2<- sqldf("select columnB,
                       keyB
                from tableB")

problematicMerge<- sqldf("select a.*, 
                                 b.* 
                          from tableA a join 
                               tableB 
                          on a.keyA = b.keyB")

this was solved by altering table1 to remove the duplicate column (see below: --I suspect aliasing one of the columns to have a different name will also do the trick):

table1<-sqldf("select columnA,
                      keyA
               from tableA")

Hope this helps

Answer 3

I had the same problem yesterday when I was suddenly unable to upload a table from R to an SQLite db on my remote desktop.

lghdb <- dbConnect(SQLite(), 'lgh.db'
dbWriteTable(lghdb, 'SrtrRisks', SrtrRisks)
Error: Cannot pass NA to dbQuoteIdentifier()...

After muddling around for a while, I realized that this error was due to the addressed SQLite database being "locked" due to an uncompleted (not committed) transaction, related to my simultaneous work using the SQLite Browser. The problem disappeared once I committed the pending transaction.

I guess that you must have figured this out, too, since there has been no follow-up to your post. It might be nice for the RSQLite folks to see whether they can return a more helpful error message under these circumstances.

Larry Hunsicker