search for: sqlquery

Dear all, I've encountered a curious problem. I am trying to run an SQL query using sqlQuery() function in RODBC. The query works fine when run in a stand-alone SQL browser (Microsoft Query Analyzer, in particular). However, when I use the exact same thing from sqlQuery() function, I get the following error: Error in "[.data.frame"(data, , ) : not all specified columns exist...

Hi everyone, I am using the sqlQuery function (in RODBC library) to import data from a database into R. My table (called temp) in the database looks like this: category numabc 54469517.307692307692def 36428860.230769230769 I used the following R code to pull data into R:data <-sqlQuery(channel, "select category, num from temp;...

Dear alls, Could someone tell me how to select a subset of string observations (e.g. "females" in a sex column) with sqlQuery in the RODBC library? Indeed, I'm trying to select a subset of observations on my access database with: female<-sqlQuery(mychannel,"SELECT Micromammiferes.sex FROM Micromammiferes WHERE (((Micromammiferes.sex)="females"));") The sql works well in access but in R, I kee...

...e ?paste for more info. Append.SQL <- function(x, channel){ sql="INSERT INTO logger (time, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10) VALUES("d1=strptime(x[2],"%d/%m/%y %H:%M:%S %p '", d1, "' ,", paste(x[3:12], collapse = ", "), ")" ) sqlQuery(channel, sql) } ntry=dim(ti)[1] date() nbefore=sqlQuery(channel,"SELECT COUNT(*) FROM logger") apply(ti, 2, Append.SQL, channel = channel) nafter=sqlQuery(channel,"SELECT COUNT(*) FROM logger") nadded=nafter-nbefore;nadded date() -----------------------------------------------...

Dear List, I have a problem with RODBC on a Paradox-DB, sqlQuery, and special characters in table names. Unfortunately, some of the latter include the underscore <_>. And I am not able to change them. That's not a problem, when I quote the table name: > sqlQuery(channel2, 'SELECT * FROM "anmeldung-alt"') Btw, if I swap ' an...

...t;, "C","C") dbConn<-odbcDriverConnect(connection="connection string") #successfully connects nameToID<-function(name, dbConn){ #dbConn : active db connection formed via odbcDriverConnect #name : a char string sqlQuery(dbConn, paste("select id from table where name='", name, "'", sep="")) } sapply(names, nameToID, dbConn=dbConn) ### Barring better ways to do this, which could involve loading the table into R then working with the problem there (which is possible), I understa...

Hi R-users, I'm trying to retrieve data from MS SQL database with RODBC's sqlQuery-function: temp <- sqlQuery(channel,"select *, (select text from table1 where koodi='paa' and koodi2=paa) as tempor from table2") str(temp) … $ var0 : num NA NA 1.6 NA NA 1.4 NA 1 NA NA ... $ var1 : Factor w/ 45 levels " ","01","01;07...

I have compiled and linked a 64 bit version of R (R 2.9.2) and the corresponding unix ODBC 64 bit package The red highlighted text below is the error I'm getting trying to when invoking a sqlQuery > library(RODBC) > channel <- odbcConnect("OraLSH", <user>, <password>) > sqlQuery(channel,"select sysdate from dual") Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, : negative length vectors are not allowed &gt...

...; with "_" in my SAS data then the column in my database is cowidp 1881501224_2 1881501224_2 Reading into R gives cowidp 1 1881501224_2 2 1881501224_2 as it "should be" - it becomes a factor Is the fact that the "."-part does not seem to work a bug in sqlQuery (or somewhere else) or is it the intention that things containing a "." are converted into numeric values if possible? Regards S?ren

...aving problems with most of SQL sintax. Suppose that Im trying to bring a table "TABLE" with columns "COL1","COL2",.. from schema "SCHEMA". What am I doing wrong? library(RODBC) channel<-odbcConnect("xxx",uid="xxx",pwd="xxx") sqlQuery(channel, "select * from SCHEMA.TABLE") [it works fine] sqlQuery(channel, "select * from SCHEMA.TABLE where COL1>10") [returns [""[RODBC] ERROR: Could not SQLExecDirect" [" (...) :invalid table name"] -- View this message in context: http://www.n...

...rogram I was creating SAS macro vars & passing them into a SQL query to run against the server. There are 3 variables firm, begindt, enddt. # of values for each varies month to month. Is there anyway I could do the same thing in R & pass the afore mentioned values to a query running within sqlQuery? Thanks for any suggestions -- View this message in context: http://r.789695.n4.nabble.com/Paasing-values-to-sqlQuery-like-SAS-macro-tp4643033.html Sent from the R help mailing list archive at Nabble.com.

...uery from within the Access2007 database, it looks like this: SELECT tblDataFieldRawSiteVisit.* FROM tblDataFieldRawSiteVisit WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan Site Mapping Detail")); After inserting that (verbatim) into my R code like this: testData = sqlQuery(db, SELECT tblDataFieldRawSiteVisit.* FROM tblDataFieldRawSiteVisit WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan Site Mapping Detail"));) I get the following error message: Error in source(.trPaths[5], echo = TRUE, max.deparse.length = 150) : C:\Users\sranney\Ap...

...Somehow I'm really missing something as I'm not able to create these sitex_data objects with the database values, neither using list nor assign... Here some code snipplets: >library (RODBC) >channel <- odbcConnectExcel2007 ("biomass_data.xlsx") >site_data <- sqlQuery(channel, "select site_no from [biomass_data $] group by site_no") #Here the values I want to loop through >str(site_data) 'data.frame': 44 obs. of 1 variable: $ site_no: num 4 7 9 10 15 16 17 18 19 20 ... #Here my first try [error message on the line within the loop,...

...previous use of vba macros with R ( I'd prefer elimination, but will take what I can get ). In order to achieve this, I will still have a need to update spreadsheets directly through R scripts. Simple queries seem to work fantastic! But, I am missing something when it comes to writing. sqlQuery( myChan,"insert into [my customers$] ( CUST_ID, NAME, SOCIAL ) VALUES( 5,'robin',5678 ) " ) [1] "[RODBC] ERROR: Could not SQLExecDirect" "S1000 -3035 [Microsoft][ODBC Excel Driver] Operation must use an updateable query." myChan = odbcConnectExcel(&quo...

...ing is incorrect; I just started learning it. But if someone could point me in the right direction or tell me if I am going about this the correct way that would be greatly appreciated! The script I have right now is: >require(RODBC) >channel<-odbcConnect("MeganXE") >stbl<-sqlQuery(channel, 'select plotnum, sampyear, sptype from density') >sqlQuery(channel, 'select count (sampyear, plotnum, sptype) from stbl where sampyear=1995 AND plotnum=1 AND sptype='S' OR sptype='H') I get this error: unexpected symbol in "sqlQuery(channel...)" I...

Hello R users, I would like to use an integer and a date as attributes in sqlQuery, and these arguments are defined in my function. Here is my function: GetReturn<-function(code,date) { channel<-odbcConnect("db","user1","password") ssql<-paste("select * from TABLE Where PF_CODE =",code,"and PF_DATE=",date) mydata&lt...

Hi R users. sorry for missing example and if question is to general but I am wondering if it is possible to execute subqueries in function sqlQuery (package RODBC) with opened connection with Excel or SQL server 2000. I couldn't find any example of this. And if it is possible what should be a correct syntax for this query: SELECT ct,COUNT(*) as n FROM (SELECT COUNT(*) AS ct FROM children GROUP BY family_id) AS x GROUP BY ct; sqlQuery(co...

Dear R-list, I'm queering a M$ Access database with the sqlQuery function from the RODBC library. As I cannot make a working example with a database here is an illustrative example, library(RODBC) mdbConnect<-odbcConnectAccess("S:/data/ ... /databse.mdb") data <- sqlQuery(mdbConnect, "select id, DOB, V1, V2, ..., V1009, V1011, V1013 from so...

Greetings guRus -- I have successfully queried a large (24,445 rows by 281 cols.) in-house database using the following RODBC query (without the line breaks) testout <- sqlQuery(channel, "select idSchedule,EXCL_Total from dbo.vwC1198_2006_RawData_With_CMPL_EXCL") This returns a dataframe of 24445 rows and two columns (as intended), but the following command testout <- sqlFetch(channel,"dbo.vwC1198_2006_RawData_With_CMPL_EXCL",colnames = TRUE,...

...das las bases de datos (1995 a 2014) y todas las tablas (tabla 1 a tabla 3). He podido generar un dataframe que me une las 3 tablas para un año (por ej. 2014) del siguiente modo. library(RODBC) setwd("C:/Users/M/Documents/R data/") base<-odbcConnectAccess("2014") datos1<-sqlQuery(base ,"SELECT * FROM TABLA1_2014") datos2<-sqlQuery(base ,"SELECT * FROM TABLA2_2014") datos3<-sqlQuery(base , "SELECT * FROM TABLA3_2014") base1<-Reduce(function(...) merge (...,by="ID", all=T), list(datos1,datos2,datos3)) Sin embargo, no he podid...