RODBC ERROR: Could not SQLExecDirect in mysql
I have been trying to write an R script to query Impala database. Here is the query to 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
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
|
show 3 more comments
I have been trying to write an R script to query Impala database. Here is the query to 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
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
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 '16 at 7:13
|
show 3 more comments
I have been trying to write an R script to query Impala database. Here is the query to 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
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
I have been trying to write an R script to query Impala database. Here is the query to 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
mysql r impala
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
edited May 14 '15 at 12:53
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
asked May 11 '15 at 12:46
Gowtham Ganesh
225210
225210
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 '16 at 7:13
|
show 3 more comments
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 '16 at 7:13
How does your connection script look
– R4nc1d
May 14 '15 at 12:21
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 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
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
Unfortunately no. I think it doesnt support complex queries like "group by"
– Gowtham Ganesh
Jul 21 '15 at 11:31
1
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 '16 at 7:13
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 '16 at 7:13
|
show 3 more comments
1 Answer
1
active
oldest
votes
You need to install the relevant drivers, please look at the following link
I had the same issue, all i had to do was update the ODBC drivers.
Also if you can update your odbcConnect with the username and password
connection <- odbcConnect("Impala");
to
connection <- odbcConnect("Impala", uid="root", pwd="password")
answered May 14 '15 at 13:08
R4nc1d
1,5871332
1
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f30168057%2frodbc-error-could-not-sqlexecdirect-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to install the relevant drivers, please look at the following link
I had the same issue, all i had to do was update the ODBC drivers.
Also if you can update your odbcConnect with the username and password
connection <- odbcConnect("Impala");
to
connection <- odbcConnect("Impala", uid="root", pwd="password")
answered May 14 '15 at 13:08
R4nc1d
1,5871332
1
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
add a comment |
You need to install the relevant drivers, please look at the following link
I had the same issue, all i had to do was update the ODBC drivers.
Also if you can update your odbcConnect with the username and password
connection <- odbcConnect("Impala");
to
connection <- odbcConnect("Impala", uid="root", pwd="password")
answered May 14 '15 at 13:08
R4nc1d
1,5871332
1
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
add a comment |
You need to install the relevant drivers, please look at the following link
I had the same issue, all i had to do was update the ODBC drivers.
Also if you can update your odbcConnect with the username and password
connection <- odbcConnect("Impala");
to
connection <- odbcConnect("Impala", uid="root", pwd="password")
answered May 14 '15 at 13:08
R4nc1d
1,5871332
You need to install the relevant drivers, please look at the following link
I had the same issue, all i had to do was update the ODBC drivers.
Also if you can update your odbcConnect with the username and password
connection <- odbcConnect("Impala");
to
connection <- odbcConnect("Impala", uid="root", pwd="password")
answered May 14 '15 at 13:08
R4nc1d
1,5871332
answered May 14 '15 at 13:08
R4nc1d
1,5871332
answered May 14 '15 at 13:08
R4nc1d
1,5871332
answered May 14 '15 at 13:08
R4nc1d
1,5871332
1,5871332
1
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
add a comment |
1
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
1
1
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
I did try installing the relevant drivers. However, nothing looks promising. I did try simple queries and they seem to go through fine and the issue comes when I try using complex queries involving joins and the like.
– Gowtham Ganesh
Jun 2 '15 at 7:07
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f30168057%2frodbc-error-could-not-sqlexecdirect-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 '16 at 7:13