Error Could Not Sqlexecdirect
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring
Could Not Sqlexecdirect 'delete From
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask "[rodbc] error: could not sqlexecdirect 'exec" Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join rodbc error could not sqlexecdirect select them; it only takes a minute: Sign up RODBC ERROR: Could not SQLExecDirect in mysql up vote 4 down vote favorite I have been trying to write an R script to query Impala database. Here is the query to
R Odbc Connect
the database: select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA When I run this query manually (read: outside the Rscript via impala-shell), I am able to get the table contents. However, when the same is tried via the R script, I get the following error: [1] "HY000 140 [Cloudera][ImpalaODBC] (140) Unsupported query." [2] "[RODBC] ERROR: Could not SQLExecDirect 'select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA' closing unused RODBC handle 1 Why does the query fail when tried via R? and how do I fix this? Thanks in advance :) Edit 1: The connection script looks as below: library("RODBC"); connection <- odbcConnect("Impala"); query <- "select columnA, max(columnB) from databaseA.tableA where columnC in (select distinct(columnC) from databaseB.tableB ) group by columnA order by columnA"; data <- sqlQuery(connection,query); mysql r impala share|improve this question edited May 14 '15 at 12:53 asked May 11 '15 at 12:46 Gowtham Ganesh 173110 How does your connection script look –R4nc1d May 14 '15 at 12:21 I have updated my question with the connection script. –Gowtham Ganesh May 14 '15 at 12:53 I'm having the exact same problem. My ODBC connection works fine with a simple query, so it's not an ODBC issue. Did you find any solution @GowthamGanesh? –SummerEla May 20 '15 at 4:18 Unfortunately no. I think it doesnt support complex queries like "group by" –Gowtham Ganesh Jul 21 '15 at 11:31 1 I also have the same issue from R to run a stored procedure in SQL. However, I observed that it does complete the stored procedure but it also throws error. I think it has something to do with time, if it crosses certain time, it throws this error.. –Espanta Aug 19 at 7:13 |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up RODBC ERROR: Could not SQLExecDirect 'CREATE TABLE … when doing sqlSave up vote 1 down vote favorite Here is the short story, and it is important to talk about the http://stackoverflow.com/questions/30168057/rodbc-error-could-not-sqlexecdirect-in-mysql setup, I’m using RODBC and FreeTDS to connect to the SQL Azure database. Remember it is SQL Azure database (also happen to be issue for on premise SQL Server, that alarm me to post it here to ask for help or understand the real issue). First, it does not play nice with the database without a cluster key, or a key, so what need to do is addPK = T or longer version addPK = TRUE Solution #1. http://stackoverflow.com/questions/26262423/rodbc-error-could-not-sqlexecdirect-create-table-when-doing-sqlsave two steps approach This will help create the proper table structure, however it’ll still fail you. Don’t worry. Now what we need to do is to append the data sqlSave(ch, USArrests, addPK = T) It will tell you the error, then what you need to do next is to append the data sqlSave(ch, USArrests, tablename = ‘USArrests’, append = T) Now you ready to hail or shout hurray?! Solution #2. That is not me, I’m lazy, it should be done in one command That solution does not seem to be making sense, here is the better version sqlSave(ch, cust, tablename=”dbo.customers”, safer=F, addPK=T) Safer = F does the trick. not sure if this is the bug from FreeTDS or RODBC sqlSave function. http://tiggeranddraggon.tumblr.com/post/98707948606/the-unintuitive-could-not-sqlexecdirect-rodbc-error-and sql-server r azure freetds rodbc share|improve this question edited Oct 9 '14 at 14:20 joran 102k11215270 asked Oct 8 '14 at 17:00 Mike 163 Is this a question? If you want to answer your own question you should post a question, then an answer; not a copy of the linked blog post. –Alex K. Oct 8 '14 at 17:06 Thanks for pointing out, Alex. It is actually a question. the blog post wasn't a real solution, and I dont know what the real issue was there.. just come out try and fail. –Mike Oct 14 '14 at 21:42 To clarify, why the sqlSave failed
by: [ date ] [ thread ] [ subject ] [ author ] Ruixin, I think there are a number of issues here: FIrst of all, ODBC is data base https://stat.ethz.ch/pipermail/r-help/2007-May/132642.html talk. In a data base there are ususally a number of tables. Hence https://www.ibm.com/developerworks/data/library/techarticle/dm-1402db2andr/ you should tell R which table you want to read. the function sqlTables can tell you which tables are available. Second, I seem to remember that the $ in some of these table names caused me some trouble Perhaps sqlFetch(cnct,'Sheet1') or sqlFetch(cnct,'Sheet1$') could do the trick and avoid you the query could not Third, it seems that the driver is determining variable types and passing these to R. It is not very good at that. You might have unexpected results. The bright side is, SQL/ODBC is powerfull. After using named regions in my Excel sheetI had the following query running :) oCE <- odbcConnectExcel('blockdata.2.xls') #sqlTables(oCE) # keep for reference q1 <- sqlQuery(oCE,'select * from linksblok , ((select could not sqlexecdirect * from blok1 union select * from blok2 union select * from blok3 union select * from blok4 union select * from blok5 union select * from blok6) as dit), rechtsblok, seco where linksblok.mainid=dit.mainid and linksblok.mainid=rechtsblok.mainid and dit.oldmonscode=seco.oldmonscode ') On Thursday 24 May 2007 04:43, Ruixin ZHU wrote: > Hi, everyone, > > I try to run as follows: > Z>library("RODBC") > Z>cnct<-odbcConnectExcel("Forbes2000.xls") > Z>cnct > RODB Connection 1 > Details: > case=nochange > DBQ=C:\Program Files\R\R-2.5.0\Forbes2000.xls > DefaultDir=C:\Program Files\R\R-2.5.0 > Driver={Microsoft Excel Driver (*.xls)} > DriverId=790 > MaxBufferSize=2048 > PageTimeout=5 > Z>sqlQuery(cnct, "select * from \"Forbes2000\\$\"") > > However, I got error like this: > [1] "[RODBC] ERROR: Could not SQLExecDirect" > > [2] "42S02 -1305 [Microsoft][ODBC Excel Driver] Microsoft Jet > Êý¾Ý¿âÒýÇæ > ÕÒ²»µ½¶ÔÏó'Forbes2000\\$'¡£Çëȶ¨¶ÔÏóÊÇñ´æÔÚ£¬²¢Õý >ȵØд³öËüµÄÃû³ÆºÍ ¾¶¡£" > > I do can find Forbes2000.xls in directory, C:\Program Files\R\R-2.5.0. > Would anybody help me out? > > Thanks for any suggestions! > _____________________________________________ > Dr.Ruixin ZHU > Shanghai Center for Bioinformation Technology > rxzhu at scbit.org > zhurx at mail.sioc.ac.cn > 86-21-13040647832 > > > [[alternative HTML version deleted]] Previous message: [R] "[RODBC] ERROR: Could not SQLExecDirect" Next message: [R] make check
about a specific product or take a course and get certified. So, what do you want to learn about? Choose a topic Featured topics Mobile development Big data Cognitive computing Cloud Featured destinations Swift @ IBM developerWorks Open Architecture center developerWorks Premium Develop Develop with our trials & downloads Start developing with product trials, free downloads, and IBM Bluemix™ services. Sort by topic or product name and find everything we have to offer. View all downloads & trials Featured products Bluemix Websphere Application Server DB2 Express C MobileFirst Platform Foundation Featured destinations Swift @ IBM developerWorks Open Architecture center API Explorer Connect Find a community and connect Learn from the experts and share with other developers in one of our dev centers. Ask questions and get answers with dW answers. Search for local events in your area. All in developerWorks communities. View communities Featured Developer Centers Jazz.net IBM Cloud Analytics DEV Bluemix API Explorer Featured destinations Swift @ IBM developerWorks Open Architecture center developerWorks Premium Career Concierge dW Open Architecture Center Swift @ IBM LearnInformation Management ContentsIntroduction IntroductionConnectivity optionsQuerying dataStoring dataUsing stored proceduresSummaryDownloadable resourcesRelated topicsComments Using R with databasesA speedy combination: DB2 10.5 with dashDB and R Grant HutchisonPublished on February 06, 2014 IntroductionR is an open source programming language that is excellent for data analysis and graphics. The language was initially created by Ross Ihaka and Robert Gentleman at the University of Auckland in 1993. They wanted to design a language that would help them teach introductory statistics to their students. The design of R was influenced by an existing language called S, from Bell Labs, which was created in the 1970s. R is considered a domain specific language because it was designed primarily for data analysis--and it does this very well. You can use R as an interactive environment, or embed scripts and models into packages and integrate them with other software modules.You can use R to analyze data from many different data sources including external files or databases. This article explains how to connect, query, and update data with relational database servers. You will learn why DB2 v10.5 with dashDB technology is an exc