Dataset alterations when stored in a MyQLS database - sqlSave - Date fields

[jaimemagana]

Hello to all,

I´m a newbe from Spain, currently working with PowerBI, Dax and R. I´m also getting a hands-on introduction to SQL, progress and MySQL. So bare with me if I do not use correct terms to explain things sometimes, though I will try and keep things profesional.

I have a dataset saving problem. I've imported a dataset from our ERP that has dates, numers and strings.

I use ODBC conections (Windowds enviroment) to connect to the ERP (Progrress database) and retrieve the data. That works ok and RStudio shows the dataset info correctly.

I then use another ODBC conector to access a MySQL database save the dataset. When I check the info beeing uploaded, it does not import dates correctly. RStudio does detect date fields, and shows them correctly. When I upload the dataset the field that should have dates only shows the year.

Is there a problem with the way I created the MySQL database?
Is there a way to preserve or define the fields of the dataset as strings or the propper format before submiting?

Here's what I run, and some similar data to what I get as output:

(Names of conetions, fields and databases have been changed to preserve privacy)

#Load libraries - Step1
library(RODBC)
library(dplyr)
library(dbplyr)
library(DBI)

#Conections -step2
erpcon <-(This is working, so I skip this part)
MySQLdb<- odbcConnect("MySQL-Test")

#Generate datasets -step3
Dataset1<-dbGetQuery(erpcon, 'select * from "TableinERP 1"' ')

#Store datasets Step-4
sqlSave(MySQLdb, Dataset1, tablename="Sales", rownames=FALSE, safer=FALSE, append=FALSE)

The data in Dataset1, when previewed in RStudio, is right. No data corrupted or altered.
When I check the MySQL database, date fields are set to Doulbe, and only store the year. Is as if the sqlSave command does not know how to pass dates.

Am I using the right libraries and commands? Is there an easier way?

Regards,

Jaime

[jaimemagana]

I forgot to describe how the MySQL is configured.

There a no talbes. Tables are created when importing with R Studio. When data has to to be updated R Studio clears the table and rewrites.

[jaimemagana]

ok. I found the culprit. Freaking colum names change, and the commands I now use to define the varTypes does not work. (After a los of research, I found hat sqlSave command has a parameter calle VarTypes, and that lets you configure the acctual schema being uploaded when creating the table)

The actual command I am using, after a lot of reading documentatios, is:

sqlSave(MySQLdb, Dataset1, tablename="Sales", rownames=FALSE, safer=FALSE, append=FALSE, varType=varType)

For the varType usage I need to run these first:
varTypes = as.character(Dataset1$TYPE_NAME)
names(varTypes) = as.character(Dataset1$COLUMN_NAME)

That is meant to load a variable with a list of column names and field types. I´m not sure I´m using this right.

What I am completly sure, and just found out, is that sqlSave alters the colum names and erases simbols. Thus:

SMMM-fechacita turns into smmmfechacita when loaded to MySQL. It has to be the library i´m using. I cant believe R Studio cant handle "-" and capital letters in funtions.

Ideas, anyone?

[jaimemagana]

ok. Finally found out what was happening.

• I had missconfigured MySQL ODBC drives installed. I uninstalled them all and re installed latest version
• I set the RStudio setting to utf-8.(this can be done under (TOOLS-> Code ->Saving -> Default Test encoding)
• I installed,just t make sure, MariaDB and MySQL Workbench

Now, the code:

#Load libraries
library(RMySQL)
library(RMariaDB)

#Source conection. Mine is progress, but mySQL is a little ways down.
con <- dbConnect(odbc::odbc(), Driver = "Progress OpenEdge 11.3 Driver", UID = "XXXXX", PWD = "YYYYYY", Port ="1234" , "dbname", timeout = 10)

#lets query
MyDataset <-dbGetQuery(con,' select *from "tablename"')

#Destination conection

con2 <- dbConnect(RMariaDB::MariaDB(), user='aaaaaaaa', password="bbbbbbb", dbname='ccccccccccc', host='domain_or_ip')

#Store dataset in final DB (MySQL)
dbWriteTable(con2 ,"table-youwantto-create", MyDataset, colnames=FALSE)

#With this I was able to create the table, with the needed schema, and store the data
#Hope this helps someone