Comments (4)
jimhester
commented on August 21, 2024
I think this is a common issue for drivers implementation of SQLTable(), they don't seem to return temporary tables regardless if explicitly request them dbListTables(con, table_type = "GLOBAL TEMPORARY") or dbListTables(con, table_type = "LOCAL TEMPORARY") both return NULL for the SQLite, MySQL and PostgreSQL backends using open source drivers.
from odbc.
jimhester
commented on August 21, 2024
Temporary tables seem to be a bit of a mess in ODBC, no one seems to use the standard table_type = "LOCAL TEMPORARY" or table_type = "GLOBAL_TEMPORARY" and they all have separate ways to store and retrieve temporary tables.
I am going to close this for now, not sure there is much we can do about it unfortunately.
from odbc.
parisprogrammer
commented on August 21, 2024
Hello everybody,
Where can I get the list of all values for table_type argument?
from odbc.
samssann
commented on August 21, 2024
SQL Server doesn't show the temporary tables with dbListTables() (which propably uses INFORMATION_SCHEMA.TABLES to list the tables? The temporary tables can be found with SELECT * FROM tempdb.sys.objects, but this does not guarantee that the table accessible from the current session (local temporary).
The following query will return TRUE or FALSE whether the temporary table exists in the users current local session
DBI::dbGetQuery(con, " DECLARE @true bit; DECLARE @false bit; set @true = 'true'; set @false = 'false'; IF OBJECT_ID('tempdb.dbo.#<table_name>') IS NOT NULL SELECT @true ELSE SELECT @false")[1,1]
Microsoft SQL Server Version: 14.00.3192
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
─ Session info ──────────────────────────────────────────────────────────────────────────────────────────────────────────────
setting value
version R version 4.0.2 (2020-06-22)
os Ubuntu 18.04.5 LTS
system x86_64, linux-gnu
ui RStudio
language (EN)
collate C.UTF-8
ctype C.UTF-8
tz Etc/UTC
date 2020-09-21
─ Packages ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
! package * version date lib source
P assertthat 0.2.1 2019-03-21 [?] CRAN (R 4.0.2)
P bit 4.0.4 2020-08-04 [?] CRAN (R 4.0.2)
P bit64 4.0.2 2020-07-30 [?] CRAN (R 4.0.2)
P blob 1.2.1 2020-01-20 [?] CRAN (R 4.0.2)
P cli 2.0.2 2020-02-28 [?] CRAN (R 4.0.2)
P config 0.3 2018-03-27 [?] CRAN (R 4.0.2)
P crayon 1.3.4 2017-09-16 [?] CRAN (R 4.0.2)
P datapasta * 3.1.0 2020-01-17 [?] CRAN (R 4.0.2)
P DBI * 1.1.0 2019-12-15 [?] CRAN (R 4.0.2)
P dbplyr * 1.4.4 2020-05-27 [?] CRAN (R 4.0.2)
P digest 0.6.25 2020-02-23 [?] CRAN (R 4.0.2)
P dplyr * 1.0.2 2020-08-18 [?] CRAN (R 4.0.2)
P ellipsis 0.3.1 2020-05-15 [?] CRAN (R 4.0.2)
P fansi 0.4.1 2020-01-08 [?] CRAN (R 4.0.2)
P generics 0.0.2 2018-11-29 [?] CRAN (R 4.0.2)
P glue * 1.4.1 2020-05-13 [?] CRAN (R 4.0.2)
P hms 0.5.3 2020-01-08 [?] CRAN (R 4.0.2)
P lifecycle 0.2.0 2020-03-06 [?] CRAN (R 4.0.2)
P lubridate * 1.7.9 2020-06-08 [?] CRAN (R 4.0.2)
P magrittr 1.5 2014-11-22 [?] CRAN (R 4.0.2)
P memoise 1.1.0 2017-04-21 [?] CRAN (R 4.0.2)
P odbc 1.2.3 2020-06-18 [?] CRAN (R 4.0.2)
P pillar 1.4.6 2020-07-10 [?] CRAN (R 4.0.2)
P pkgconfig 2.0.3 2019-09-22 [?] CRAN (R 4.0.2)
P prettyunits 1.1.1 2020-01-24 [?] CRAN (R 4.0.2)
P progress * 1.2.2 2019-05-16 [?] CRAN (R 4.0.2)
P purrr * 0.3.4 2020-04-17 [?] CRAN (R 4.0.2)
P R6 2.4.1 2019-11-12 [?] CRAN (R 4.0.2)
P Rcpp 1.0.5 2020-07-06 [?] CRAN (R 4.0.2)
P readr 1.3.1 2018-12-21 [?] CRAN (R 4.0.2)
P renv 0.11.0 2020-06-26 [?] CRAN (R 4.0.2)
P rlang * 0.4.7 2020-07-09 [?] CRAN (R 4.0.2)
P RSQLite 2.2.0 2020-01-07 [?] CRAN (R 4.0.2)
P rstudioapi 0.11 2020-02-07 [?] CRAN (R 4.0.2)
P sessioninfo 1.1.1 2018-11-05 [?] CRAN (R 4.0.2)
P stringi 1.4.6 2020-02-17 [?] CRAN (R 4.0.2)
P stringr * 1.4.0 2019-02-10 [?] CRAN (R 4.0.2)
P tibble 3.0.3 2020-07-10 [?] CRAN (R 4.0.2)
P tidyselect 1.1.0 2020-05-11 [?] CRAN (R 4.0.2)
P utf8 1.1.4 2018-05-24 [?] CRAN (R 4.0.2)
P vctrs 0.3.2 2020-07-15 [?] CRAN (R 4.0.2)
P withr 2.2.0 2020-04-20 [?] CRAN (R 4.0.2)
P yaml 2.2.1 2020-02-01 [?] CRAN (R 4.0.2)
from odbc.
Related Issues (20)
- set up CI/CD for databricks
- Writing to datetime2 column in SQL Server with odbc HOT 1
- `set_odbcsysini()` doesn't do anything
- Determine DATETIME2 precision based on parameter description
- GHA failure on macOS
- configure snowflake error logs in CI
- DBI::dbWriteTable fails when useNativeQuery = TRUE HOT 2
- `odbc::databricks()` does not detect Workbench-managed credentials when rendering Quarto/RMarkdown docs
- new WARNING on CRAN `r-devel-linux-x86_64-fedora-clang` HOT 1
- CRAN NOTE on installed package size on macOS HOT 2
- CRAN WARNINGs on M1 Macs re: `-Wdeprecated-declarations` HOT 4
- Release odbc 1.5.0
- Oracle - dbWriteTable not working with date and timestamp HOT 4
- MSSQL Server dbWriteTable fails without batch_row = nrow(value) HOT 6
- Allow `uid` to be specified when using `authenticator="externalbrowser"` HOT 3
- Package hex logo HOT 4
- dbListFields() fails with non-default schema table with odbc + SQL Server HOT 4
- DBI::dbConnect + odbc::databricks(): DATABRICKS_HOST value issue
- Include additional details in Databricks user agent
- Different behaviour when using dbWriteTable() on Snowflake HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from odbc.