sqliteSendQuery error in AnnotationForge bioconductor package
3
0
Entering edit mode
Stephen H • 0
@stephen-h-7384
Last seen 5.4 years ago
United States

I want to use the `AnnotationForge` package to create my own annotation package but I get an error with the example in the vignette (http://www.bioconductor.org/packages/release/bioc/vignettes/AnnotationForge/inst/doc/SQLForge.pdf) and I've spent the last 2 hours trying to resolve the error with no luck. Anyone have an idea to what the issue may be?

    source("http://www.bioconductor.org/biocLite.R")
    biocLite("AnnotationForge")
    library(AnnotationForge)

    hcg110_IDs = system.file("extdata",
                             "hcg110_ID",
                             package="AnnotationDbi")
    tmpout = tempdir()
    makeDBPackage("HUMANCHIP_DB",
                  affy=FALSE,
                  prefix="hcg110",
                  fileName=hcg110_IDs,
                  baseMapType="gb",
                  outputDir = tmpout,
                  version="1.0.0",
                  manufacturer = "Affymetrix",
                  chipName = "Human Cancer G110 Array",
                  manufacturerUrl = "http://www.affymetrix.com")

I get this error the first time I ran it:

`Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such table: src.accession`

Followed by this one:

`Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: table metadata already exists`

Appreciate any help in advance.

> sessionInfo()

R version 3.1.2 (2014-10-31)
Platform: i686-pc-linux-gnu (32-bit)

locale:
[1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8  
[6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C           
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats4    parallel  stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] AnnotationForge_1.8.2 org.Hs.eg.db_3.0.0    RSQLite_1.0.0         DBI_0.3.1             AnnotationDbi_1.28.1  GenomeInfoDb_1.2.4  
[7] IRanges_2.0.1         S4Vectors_0.4.0       Biobase_2.26.0        BiocGenerics_0.12.1   BiocInstaller_1.16.1 

loaded via a namespace (and not attached):
[1] tools_3.1.2
bioconductor annotationforge annotationdbi annotation r • 4.1k views
ADD COMMENT
1
Entering edit mode
@james-w-macdonald-5106
Last seen 18 hours ago
United States

Rather than building your own, you could just use the one that is supplied by Bioc:

library("BiocInstaller")

biocLite("hcg110.db")
ADD COMMENT
0
Entering edit mode

Hello James. My goal is to create my own annotation package (using the SQLForge.pdf) for a specific chip, which I successfully did last year. However, when I try to rerun the code, I get the stated errors. I used the example from the vignette as a template for my package so I'm trying to troubleshoot what the issue is.

ADD REPLY
1
Entering edit mode

As Dan mentions below, you shouldn't be seeing sqliteSendQuery() any more. And I can run your code without problems:

> makeDBPackage("HUMANCHIP_DB",
+                   affy=FALSE,
+                   prefix="hcg110",
+                   fileName=hcg110_IDs,
+                   baseMapType="gb",
+                   outputDir = tmpout,
+                   version="1.0.0",
+                   manufacturer = "Affymetrix",
+                   chipName = "Human Cancer G110 Array",
+                   manufacturerUrl = "http://www.affymetrix.com")
baseMapType is gb or gbNRef
Prepending Metadata
Creating Genes table
Appending Probes
Found 2059 Probe Accessions
Appending Gene Info
Found 1435 Gene Names
Found 1435 Gene Symbols
Appending Chromosomes
Appending Cytogenetic Locations
Appending Omim
Appending RefSeq
Appending Pubmed
Appending Unigene
Appending ChrLengths
Appending 3 GO tables
Appending 3 GO ALL tables
Appending KEGG
Appending EC
Appending Chromosome Locations
Appending Pfam
Appending Prosite
Appending Alias
Appending Ensembl
Appending Uniprot
Appending Metadata

simplifying probes table
dropping redundant data
Creating package in ./hcg110.db
Warning messages:
1: 'dbBeginTransaction' is deprecated.
Use 'dbBegin' instead.
See help("Deprecated")
2: 'dbBeginTransaction' is deprecated.
Use 'dbBegin' instead.
See help("Deprecated")

> sessionInfo()
R version 3.1.1 (2014-07-10)
Platform: x86_64-unknown-linux-gnu (64-bit)

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats4    parallel  stats     graphics  grDevices utils     datasets
[8] methods   base     

other attached packages:
 [1] AnnotationForge_1.8.2 org.Hs.eg.db_3.0.0    RSQLite_1.0.0        
 [4] DBI_0.3.1             AnnotationDbi_1.28.1  GenomeInfoDb_1.2.4   
 [7] IRanges_2.0.1         S4Vectors_0.4.0       Biobase_2.26.0       
[10] BiocGenerics_0.12.1  

So you can try running traceback() after the error.

ADD REPLY
1
Entering edit mode

An update. As Dan mentions, sqliteSendQuery has been scrubbed from AnnotationForge, but not RSQLite:

> getAnywhere(sqliteSendQuery)
A single object matching ‘sqliteSendQuery’ was found
It was found in the following places
  namespace:RSQLite
with value

function (con, statement, bind.data = NULL)
{
    if (!is.null(bind.data)) {
        if (!is.data.frame(bind.data)) {
            bind.data <- as.data.frame(bind.data)
        }
        if (nrow(bind.data) == 0 || ncol(bind.data) == 0) {
            stop("bind.data must have non-zero dimensions")
        }
    }
    rsId <- .Call(rsqlite_query_send, con@Id, as.character(statement),
        bind.data)
    new("SQLiteResult", Id = rsId)
}
<environment: namespace:RSQLite>

In addition, the second error you see above is because you already have a partially built package in your tmpdir.

> dir(".", "hcg")
[1] "hcg110.db"     "hcg110.sqlite"

> makeDBPackage("HUMANCHIP_DB",
+                   affy=FALSE,
+                   prefix="hcg110",
+                   fileName=hcg110_IDs,
+                   baseMapType="gb",
+                   outputDir = tmpout,
+                   version="1.0.0",
+                   manufacturer = "Affymetrix",
+                   chipName = "Human Cancer G110 Array",
+                   manufacturerUrl = "http://www.affymetrix.com")
Error in sqliteSendQuery(con, statement, bind.data) :
  error in statement: table metadata already exists

And the fix is to remove those files:

> unlink(dir(".", "hcg"), recursive = TRUE)
> makeDBPackage("HUMANCHIP_DB",
+                   affy=FALSE,
+                   prefix="hcg110",
+                   fileName=hcg110_IDs,
+                   baseMapType="gb",
+                   outputDir = tmpout,
+                   version="1.0.0",
+                   manufacturer = "Affymetrix",
+                   chipName = "Human Cancer G110 Array",
+                   manufacturerUrl = "http://www.affymetrix.com")
baseMapType is gb or gbNRef
Prepending Metadata
Creating Genes table
Appending Probes
Found 2059 Probe Accessions
Appending Gene Info
Found 1435 Gene Names
Found 1435 Gene Symbols
Appending Chromosomes
Appending Cytogenetic Locations
Appending Omim
Appending RefSeq
Appending Pubmed
Appending Unigene
Appending ChrLengths
Appending 3 GO tables
Appending 3 GO ALL tables
Appending KEGG
Appending EC
Appending Chromosome Locations
Appending Pfam
Appending Prosite
Appending Alias
Appending Ensembl
Appending Uniprot
Appending Metadata

simplifying probes table
dropping redundant data
Creating package in ./hcg110.db
Warning messages:
1: 'dbBeginTransaction' is deprecated.
Use 'dbBegin' instead.
See help("Deprecated")
2: 'dbBeginTransaction' is deprecated.
Use 'dbBegin' instead.
See help("Deprecated")

 

ADD REPLY
0
Entering edit mode

Yes, I misspoke. The change is that sqliteSendQuery is no longer exported from RSQLite.

ADD REPLY
0
Entering edit mode

Appreciate the walkthrough. Thanks, James.

ADD REPLY
0
Entering edit mode

So James's fix did not work for you?

ADD REPLY
1
Entering edit mode
Dan Tenenbaum ★ 8.2k
@dan-tenenbaum-4256
Last seen 7 months ago
United States

I think you're using an old version of R/Bioconductor. sqliteSendQuery is a function that has been removed from the RSQLite package and current Bioconductor packages no longer call that function. I recommend updating to R-3.1 and Bioconductor 3.0. If you still have problems please include the output of sessionInfo() in future posts to help us see what kind of setup you have.

ADD COMMENT
0
Entering edit mode

Apologies. I've included my session info in the original post. I am current on versions but appreciate the clarification on the RSQlite package.

ADD REPLY
0
Entering edit mode

Are you still getting the bug after updating?  Because I can't reproduce this problem you are reporting.  There were changes that had to be made to AnnotationForge last fall (because DBI changed).  But those changes were made some time ago now.

ADD REPLY
0
Entering edit mode

I was already running R 3.1.2 and Bioconductor version 3.0 when I ran into this bug. Reinstalled AnnotationForge and updated all other packages with no luck.

ADD REPLY
0
Entering edit mode

Hey could you solve your Problem? I have the same issue.

 

ADD REPLY
0
Entering edit mode

The above discussion happened 5 months ago. The current BioC release has changed since then and is now BioC 3.1. Make sure you're using that version of BioC (requires R 3.2.1) and try again. If you still run into problems please show us what you did exactly and the error you got and also provide your sessionInfo(). Thanks!

H.

ADD REPLY
0
Entering edit mode

I'm having the same issue with current versions:

 

makeDBPackage("HUMANCHIP_DB", affy = TRUE, prefix="primeview",fileName="PrimeView.na34.annot.csv", baseMapType="gbNRef",outputDir=".", version = "12.1.0", manufacturer="Affymetrix",manufacturerUrl="http://www.affymetrix.com")
baseMapType is gb or gbNRef

 Show Traceback
 
 Rerun with Debug
 Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such table: src.accession > sessionInfo()
R version 3.2.3 (2015-12-10)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.11.5 (El Capitan)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats4    parallel  stats     graphics  grDevices utils     datasets  methods  
[9] base     

other attached packages:
 [1] BiocInstaller_1.20.3   AnnotationForge_1.12.2 org.Hs.eg.db_3.2.3    
 [4] RSQLite_1.0.0          DBI_0.4-1              AnnotationDbi_1.32.3  
 [7] IRanges_2.4.8          S4Vectors_0.8.11       Biobase_2.30.0        
[10] BiocGenerics_0.16.1   

loaded via a namespace (and not attached):
[1] tools_3.2.3

ADD REPLY
0
Entering edit mode

Those are not current versions. Try upgrading, as it works for me:

> makeDBPackage("HUMANCHIP_DB", affy=T, prefix = "primeview", fileName = "PrimeView.na35.annot.csv", version = "1", baseMapType = "gbNRef", outputDir=".", manufacturer = "Affy",manufacturerUrl = "www.affymetrix.com")
Loading required package: RSQLite
Loading required package: DBI
baseMapType is gb or gbNRef
Prepending Metadata
Creating Genes table
Appending Probes
Found 49372 Probe Accessions
Appending Gene Info
Found 46217 Gene Names
Found 46217 Gene Symbols
Appending Chromosomes
Appending Cytogenetic Locations
Appending Omim
Appending RefSeq
Appending Pubmed
Appending Unigene
Appending ChrLengths
Appending 3 GO tables
Appending 3 GO ALL tables
Appending KEGG
Appending EC
Appending Chromosome Locations
Appending Pfam
Appending Prosite
Appending Alias
Appending Ensembl
Appending Uniprot
Appending Metadata

simplifying probes table
dropping redundant data
Creating package in ./primeview.db
> sessionInfo()
R version 3.3.0 (2016-05-03)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 10586)

locale:
[1] LC_COLLATE=English_United States.1252
[2] LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] parallel  stats4    stats     graphics  grDevices utils     datasets
[8] methods   base     

other attached packages:
[1] RSQLite_1.0.0          DBI_0.4-1              AnnotationForge_1.14.2
[4] AnnotationDbi_1.34.3   IRanges_2.6.0          S4Vectors_0.10.1      
[7] Biobase_2.32.0         BiocGenerics_0.18.0    BiocInstaller_1.22.2  

loaded via a namespace (and not attached):
[1] compiler_3.3.0 tools_3.3.0    XML_3.98-1.4 
ADD REPLY
0
Entering edit mode

Thanks for the timely reply, James! I was actually in the midst of writing you a personal email when I saw this!

I've updated everything now, I believe, and still no dice:

 

makeDBPackage("HUMANCHIP_DB", affy = TRUE, prefix="primeview",fileName="PrimeView.na34.annot.csv", baseMapType="gbNRef",outputDir=".", version = "12.1.0", manufacturer="Affymetrix",manufacturerUrl="http://www.affymetrix.com")
baseMapType is gb or gbNRef

 Show Traceback
 
 Rerun with Debug
 Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such table: src.accession

> sessionInfo()
R version 3.3.0 (2016-05-03)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.11.5 (El Capitan)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats4    parallel  stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RSQLite_1.0.0          DBI_0.4-1              AnnotationForge_1.14.2 AnnotationDbi_1.34.3  
[5] IRanges_2.6.0          S4Vectors_0.10.1       Biobase_2.32.0         BiocGenerics_0.18.0   
[9] BiocInstaller_1.22.2  

loaded via a namespace (and not attached):
[1] tools_3.3.0  XML_3.98-1.4

ADD REPLY
1
Entering edit mode

Well I pretty much never use MacOS, so can't say for sure what the problem is. But you can get the version I just made here. For like 24 hours, after which that link will be dead. You can install by starting R in the dir that you download to, and doing

install.packages("primeview.db_12.1.1.tar.gz", type = "source", repos = NULL)
ADD REPLY
0
Entering edit mode

I believe that worked! Many thanks, James!!

ADD REPLY
0
Entering edit mode
Axel Klenk ▴ 980
@axel-klenk-3224
Last seen 2 hours ago
Switzerland

Hi all,

I'm afraid I have hit the same or a similar issue when trying to rebuild one of my annotation packages today. The script has worked 2015-08-17 but unfortunately throws an error now, on ubuntu, using up-to-date versions. I'm grateful for any help

I have run:

library("AnnotationForge")
pkgPrefix <- "RnAgilentDesign028279"
pkgVersion <- "1.1.0"
makeDBPackage("RATCHIP_DB",
                            affy = FALSE,
                            prefix = pkgPrefix,
                            fileName = "agi_eg.txt",
                            baseMapType = "eg",
                            outputDir = ".",
                            version = pkgVersion,
                            manufacturer = "Agilent",
                            chipName = "Rat 8x60K v1 (028279)",
                            manufacturerUrl = "www.agilent.com",
                            author = "Axel Klenk",
                            maintainer = "Axel Klenk <axel.klenk@actelion.com>")

 

baseMapType is eg

Error in sqliteSendQuery(con, statement, bind.data) :
  error in statement: no such table: chipSrc.genes

> traceback()
11: .Call(rsqlite_query_send, con@Id, as.character(statement), bind.data)
10: sqliteSendQuery(con, statement, bind.data)
9: sqliteGetQuery(conn, statement)
8: .local(conn, statement, ...)
7: dbGetQuery(db, paste("UPDATE temp_probe_map SET gene_id = NULL WHERE gene_id NOT IN (SELECT gene_id FROM chipSrc.genes);",
       sep = ""))
6: dbGetQuery(db, paste("UPDATE temp_probe_map SET gene_id = NULL WHERE gene_id NOT IN (SELECT gene_id FROM chipSrc.genes);",
       sep = ""))
5: probe2gene(baseMap = baseMap, baseMapType = baseMapType, otherSrc = otherSrcObjs,
       chipMapSrc = chipMapSrc, chipSrc = chipSrc, pkgName = pkgName,
       outputDir = outputDir, allowHomologyGene = allowHomologyGene)
4: getMapForOtherChipPkg(filePath = fileName, pkgName = prefix,
       chipMapSrc = chipMapSrc, chipSrc = chipSrc, otherSrc = otherSrc,
       baseMapType = baseMapType, outputDir = outputDir)
3: popRATCHIPDB(affy = affy, prefix = prefix, fileName = fileName,
       chipMapSrc = chipMapSrc, chipSrc = chipSrc, metaDataSrc = metaDataSrc,
       otherSrc = otherSrc, baseMapType = baseMapType, outputDir = outputDir,
       printSchema = FALSE)
2: .makeRATCHIP_DB(...)
1: makeDBPackage("RATCHIP_DB", affy = FALSE, prefix = pkgPrefix,
       fileName = "agi_eg.txt", baseMapType = "eg", outputDir = ".",
       version = pkgVersion, manufacturer = "Agilent", chipName = "Rat 8x60K v1 (028279)",
       manufacturerUrl = "www.agilent.com", author = "Axel Klenk",
       maintainer = "Axel Klenk <axel.klenk@actelion.com>")


> sessionInfo()
R version 3.3.1 (2016-06-21)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04 LTS

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8   
 [6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats4    parallel  stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RSQLite_1.0.0          DBI_0.4-1              actRepos_0.2.0         AnnotationForge_1.14.2 AnnotationDbi_1.34.4   IRanges_2.6.1         
[7] S4Vectors_0.10.2       Biobase_2.32.0         BiocGenerics_0.18.0   

loaded via a namespace (and not attached):
[1] tools_3.3.1  XML_3.98-1.4

  

ADD COMMENT
0
Entering edit mode

Hi again,

this is resolved by simply installing the rat.db0 package that was missing on my new PC. The vignette explains clearly that one needs it and why one needs it and I'm sure I have read it last year when I wrote that script -- but not now before re-running it. :-(

(While it's clearly my fault -- could availability of the package be checked for and the error message made a bit clearer?)

Thanks a lot for all this wonderful infrastructure.

ADD REPLY
0
Entering edit mode

Hi Axel,

Thanks for the idea - this was on Marc's TO DO list, but hadn't yet been done. It is now, at least in the devel version of AnnotationForge.

On a different note, if  you have a question, it's better to start a new thread, rather than posting it as an answer in a years-old thread. You aren't actually posting an answer, and it is a really old thread.

ADD REPLY
0
Entering edit mode

Hi James, thanks for the hint, will do it that way next time.

ADD REPLY

Login before adding your answer.

Traffic: 214 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6