Do you know what is this queri problem?

[eslamais]

source("http://bioconductor.org/biocLite.R")
biocLite("GEOmetadb")

library(GEOmetadb)

###################################################################################################
# Connect with the DB
###################################################################################################

con <- dbConnect(SQLite(),'GEOmetadb.sqlite')

sql <- paste("SELECT DISTINCT ",
"gpl.ID ",
"gds.platform_technology_type ",
"FROM ",
"gpl ",
"JOIN ","gds_subset ON gds.gds = gds_subset.gds ",
"JOIN ","gpl ON gds.gpl = gpl.gpl ",
"WHERE ",
"gds_subset.type LIKE 'time' ",
"WHERE ",
"gpl.technology LIKE 'high-throughput sequencing' ",sep="" )

It gave me this error:
Error in sqliteSendQuery(con, statement, bind.data) :
error in statement: near ".": syntax error

[eslamais]

Error in sqliteSendQuery(con, statement, bind.data) :
error in statement: near ".": syntax error

[mastal]

I am not sure what is causing the error message, because the code you showed doesn't go as far as submitting the sql query, but there are a couple of problems with your SQL SELECT statement.

You have 2 WHERE clauses in the SELECT statement. What you should have is the two parts of the WHERE clause separated by AND. So remove the second 'WHERE' and replace with 'AND'.

Your second JOIN statement looks like it is a typo and you should probably have

JOIN ","gds ON gds.gpl = gpl.gpl ",

instead of
JOIN ","gpl ON gds.gpl = gpl.gpl ",

Have a look at the examples in the documentation for GEOmetadb.

https://www.bioconductor.org/packages/release/bioc/vignettes/GEOmetadb/inst/doc/GEOmetadb.html#getting-the-geometadb-database

[eslamais]

THANKS MASTAL

I changed the code as you said

however, I still get the error

sql <- paste("SELECT DISTINCT ",
"gpl.ID ",
"gds.platform_technology_type ",
"FROM ",
"gpl ",
"JOIN ","gds_subset ON gds.gds = gds_subset.gds ",
"JOIN ","gds ON gds.gpl = gpl.gpl ",
"WHERE ",
"gds_subset.type LIKE 'time' ",
"AND ",
"gpl.technology LIKE 'high-throughput sequencing' ",sep="" )



rs <- dbGetQuery(con,sql)
rs

[mastal]

Are you running R interactively, and if so, at what stage are you getting the error message?

[eslamais]

I am using R studio if that you mean.

I changed the code a little, now there is no error but also no result.

But I am seeing some result in tables, I want to see ll of them an dI am sure there are more than a few row including this condition.

> sql <- paste ("SELECT DISTINCT ",
+ "gpl.ID, gds.platform_technology_type ",
+ "FROM ",
+ "gpl ",
+ "JOIN ",
+ " gds ON gds.gpl = gpl.gpl ",
+ "JOIN ",
+ "gds_subset ON gds.gds = gds_subset.gds ",
+ "WHERE ",
+ " gds_subset.type LIKE '%time%' AND ",
+ "gpl.technology LIKE '%high-throughput%'",sep="")
> rs <- dbGetQuery(con,sql)
> dim(rs)
[1] 0 2
> rs
[1] ID platform_technology_type
<0 rows> (or 0-length row.names)

[eslamais]

I think it actually worked.

I want to see how many rna-seq platform are in GEO that are time series and apparntly nothing.

Unless the coding is wrong and has to indicate something else.

[mastal]

The thing to do is to try a simple sql command that should return a small number of results.

For example:
sql <- SELECT * FROM gpl LIMIT 10

And when reporting errors with R, you should always give the results of sessionInfo().