New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
std::bad_alloc
error
#71
Comments
Do you get the same error with |
Yup. Results:
|
Can you return the value of the following, it is possible there is a data type issue with the column you are trying to read. odbc:::connection_sql_columns(con@ptr, table_name="tblEmpenhos") |
Also could you possibly try using the current CRAN version of odbc and see if the error recapitulates there. Would be useful to know if this is a new bug or not. |
Sure.
|
2): With the CRAN version, I get:
|
That is a very large table, so it is possible the allocation is actually too large for your memory. Could you try selecting a subset of the columns and see if you get the same error? dbGetQuery(con, "SELECT DataInicial, DataFinal from tblEmpenhos") Alternatively it is possible that |
Sure, it is a big table, but I was hoping to use the new version of dplyr with it (hence the issue there). Before, I was using RODBC, and I switched to DBI/dplyr/odbc to avail of the new dplyr database capabilities. It was my understanding that I could do that without actually loading the table into memory...is there something basic I'm doing wrong? |
Running
|
Yup:
(Sorry, I only got back to this today because it's a database in work) |
Please verify if it is a specific column causing the bad allocation or just the total number of tables returned. A helper function could be useful for doing this. columns <- function(con, table, idx) {
fields <- dbListFields(con, table)
fields <- fields[idx]
sql <-
paste0(
"SELECT TOP 10 ",
paste0(dbQuoteIdentifier(con, fields), collapse = ", "),
" FROM ", dbQuoteIdentifier(con, table))
dbGetQuery(con, sql)
}
start <- 1
end <- seq(5, 120, 5)
for (e in end) {
cat(e, "\n")
columns(con, "tblEmpenhos", seq(start, e))
} |
I get |
Yes, but when does it throw the error, you need to pinpoint why you are getting the bad allocation, either because a specific column is resulting in add a bad allocation or too many columns are being retrieved at once. Add a |
I think I've found it. I get the error at 64, which from the earlier post above, is:
It's the only |
Ok that definitely helps, do you get the bad alloc error for any rows in the column? If you run the following command does it always give you a bad_alloc error? dbGetQuery(con, "SELECT TOP 10 Ds_Observacao_Anexo FROM tblEmpenhos TABLESAMPLE ( 10 ROWS )") |
Sorry my SQL above was incorrect, should be correct now. |
Yeah, I get an error every time I try that code. |
Could you retrieve the data from that column (via RODBC or something else) and paste a sample of it here. I cannot reproduce the error with a simple |
It could be, although I get the data back with RODBC (I've had to use XX on some things and not use all the print out of the factor levels, data privacy etc):
Assuming it is an issue with a particular column, is there a way to ignore this while using odbc and the new dplyr to work with the base? (I understand you may not know on the dplyr end, but Hadley sent me here so I'd rather check everything before I go back an reopen the issue) I get this bad allocation error as soon as I try to connect dplyr to the base. |
Yes you can use x <- tbl(con, "tblEmpenhos") %>% select(-Ds_Observacao_Anexo)
x Alternatively you can provide a SQL query of just the columns you are interested in. x <- tbl(con, sql("SELECT DataInicial, DataFinal from tblEmpenhos"))
x |
Also what are the values of |
|
I am unfortunately unable to reproduce the letters <- c(letters, "\u00e4", "\u00f6", "\u00fc")
t1 <- replicate(1000, paste0(letters[sample(length(letters), size=32768, replace=T)], collapse = ""))
dbWriteTable(con, "test", data.frame(a = t1, stringsAsFactors=F), fieldTypes = c(a = "ntext"), overwrite=T)
x <- dbReadTable(con, "test") Could you return the result of the following, which might indicate how the column definition differs from what I have done above? dbGetQuery(con, "exec sp_columns tblEmpenhos") |
Is it ok if I send it to the email on your profile? It's very big to paste, and I also don't want to put too much information on it here. |
You can just put the information for the |
Ok, all of that information is identical to what I see. I do not think I can debug this issue further without the ability to reproduce it locally, I would suggest you use the workarounds suggested above. If you are able to produce a more minimal example using generated data that exhibits the behavior I can try to fix the issue. |
Ok @jimhester, thanks for all your help. |
Migrating from dplyr #2866:
Hi, I'm having an Error in
new_result(connection@ptr, statement) : std::bad_alloc
error (raised in #2323 -- I've also followed the instructions there and I'm using the dev version of odbc) when I try to use dplyr/dblpyr with a Microsoft SQL Database. I've read that this may be to do with Rcpp, so I've re-installed dplyr, dblpyr and Rcpp without success.I can connect to the base with no problems:
And
dbListFields(con, "tblEmpenhos")
gives me all the correct column names. But using this gives the error:I thought this error had to do with memory allocation, but that can't be right in this case. Is there anything else I could check?
The text was updated successfully, but these errors were encountered: