Column datatype name not returned in dbColumnInfo()

[mukusingh]

dbColumnInfo function does not provide the datatype name of the column

PostgreSQL 9.3

Using JDBC drivers

On calling dbColumnInfo using JDBC drivers, the following column are returned

1. name
2. Sclass
3. type
4. len
5. precision
6. scale
7. nullOK

Example:

> library(DBI)
> library(RPostgreSQL)
> con1 <- dbConnect(RPostgreSQL::PostgreSQL(),
                  port=5432,
                  host='localhost',
                  dbname='postgres',
                  password='postgres',
                  user='postgres')

> dbColumnInfo(dbSendQuery(con1,'select * from RD11 limit 10'))

                      name    Sclass    type len precision scale nullOK
1                     states character VARCHAR  -1        34    -1   TRUE
2       rural_schedule_caste    double  FLOAT8   8        -1    -1   TRUE
3       rural_schedule_tribe    double  FLOAT8   8        -1    -1   TRUE
4 rural_other_backward_caste    double  FLOAT8   8        -1    -1   TRUE
5       urban_schedule_caste    double  FLOAT8   8        -1    -1   TRUE
6       urban_schedule_tribe    double  FLOAT8   8        -1    -1   TRUE
7 urban_other_backward_caste    double  FLOAT8   8        -1    -1   TRUE

Using ODBC package

But Only name and datatype column are returned on calling dbColumnInfo using ODBC . The dataype column only has numbers instead on datatype name unlike JDBC.

> con <- dbConnect(odbc::odbc(),
                 Driver = "PostgreSQL Driver",
                 Database = "postgres",
                 Username = "postgres",
                 Password = "postgres",
                Servername  = "127.0.0.1",
                 Port = 5432)
> dbColumnInfo(dbSendQuery(con1,'select * from RD11 limit 10'))

                      name type
1                     states   12
2       rural_schedule_caste    6
3       rural_schedule_tribe    6
4 rural_other_backward_caste    6
5       urban_schedule_caste    6
6       urban_schedule_tribe    6
7 urban_other_backward_caste    6

[jimhester]

The numbers are ODBC SQL data type codes

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types

They are defined in sql.h.

I agree it would be nicer to have a name, but it is not entirely trivial to do the mapping as databases can define custom types as well.

[mukusingh]

Thank you for the response. The reason why i am concerned about the datatype names is that I am trying to create a calculated column based on two or more existing columns in the table. To my knowledge, most of the databases do not allow a calculated column to be specified in the ALTER TABLE syntax (something like ALTER TABLE tablename add newcol = col1 * col2;). So, i am first trying to do the following:

1. calculate the new column using Select statement inside dbSendQuery
2. fetch the datatype using dbColumnInfo
3. Then create the new column using Alter Table statement
4. Update the values in the new column using Update statement

This procedure was working fine with the JDBC / DBI but i am not sure how i can do the same using ODBC. Please let me know if they is any other way around it.