---
title: "odbc query problem"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{odbc query problem}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

### Description of the problem

If a query is fetched against a odbc based database connection, an error occurs if long data types (`varchar(max)`, `nvarchar(max)`, `text`, `ntext`, `xml`, `geography`, `geometry`, `hierarchyid`, `binary`, `image` or `rowversion`) are not put at the end of the select statement. As it is not in our power to change this shortcoming, we replaced the original cryptic warning by an informative one mentioning the columns that should be put in the end of the select statement.

__This problem has been solved from `odbc 1.2.3`__, so we recommend installing an update of this package. Shortly the adapted warning and this vignette will be removed from `inbodb`.

### Some background on SQL Server and odbc data types

It seems that the error corresponds to the native data types in the SQL Server database (with the exception that data type `xml` causes an error in Windows OS but not in Unix OS). These data types are however translated to odbc data types that do not always correspond to the native types, and the latter are OS dependent. E.g. the native data type `nvarchar(max)` becomes odbc type `ntext` in Windows OS and `nvarchar` in Unix OS. The data types that are shown in RStudio in the Connections pane, are the odbc data types.

In Windows OS, luckily, native long data types always correspond to odbc long data types, and vice versa (despite the fact that de exact data types do not always correspond). In Unix OS, the native long data types `nvarchar(max)` and `varchar(max)` correspond to the odbc 'short' data types `nvarchar` and `varchar`. So in Windows the data types shown in the RStudio Connection pane can be used to determine which columns should be placed in the end of the select statement. In Unix, the native data types should be checked (or should additionally be checked for `nvarchar(max)` and `varchar(max)`).

### Consulting data types

The native data types can be consulted:

- directly in SQL Server via applications such as Microsoft SQL Server Management Studio or Azure Data Studio
- in R by querying separate tables with `dbGetQuery(con, "sp_columns 'table_name'")` (with `con` the connection to the database and `table_name` the name of the table)

The odbc data types can be consulted:

- in the Connections pane in RStudio
- in R by querying separate tables with `odbc::odbcConnectionColumns(con, "table_name")` (with `con` the connection to the database and `table_name` the name of the table)
- once you know the corresponding ID's in column `data_type`, these ID's can be consulted for a specific query using the following code:

```{r eval=FALSE}
rs <- dbSendQuery(con, query)
odbc::dbColumnInfo(rs)
```