Comments (3)
jeffreyhorner
commented on August 31, 2024
What you are experiencing has to do with the not so subtle differences between dbSendQuery and dbGetQuery:
dbSendQuery executes the query argument and then returns a MySQLResult object and expects the user to act on that object, by calling fetch() optionally, and finally dbClearResult().
dbGetQuery executes the query argument and immediately returns the query result, implicitly doing the fetch() and dbClearResult() for you.
The subtle difference is that if dbGetQuery sees that you have pending MySQLResults* on the connection argument, then it will implicitly create a new connection for you, execute the query, gather the results, close the new connection, and then return you the results.
In your example you are calling dbSendQuery without ever clearing out the results on the connection. So subsequent calls to dbGetQuery always act on transient connections that you never see.
Here's an example of solely using dbGetQuery to change the character set to utf8:
First make sure there's no pending results
> dbListResults(con)
list()
> dbGetQuery(con,"show variables like 'character_set_%'")
Variable_name Value
1 character_set_client latin1
2 character_set_connection latin1
3 character_set_database latin1
4 character_set_filesystem binary
5 character_set_results latin1
6 character_set_server latin1
7 character_set_system utf8
8 character_sets_dir /usr/share/mysql/charsets/
> dbGetQuery(con,"set names utf8")
NULL
> dbGetQuery(con,"show variables like 'character_set_%'")
Variable_name Value
1 character_set_client utf8
2 character_set_connection utf8
3 character_set_database latin1
4 character_set_filesystem binary
5 character_set_results utf8
6 character_set_server latin1
7 character_set_system utf8
8 character_sets_dir /usr/share/mysql/charsets/
Now let's reproduce your error by using dbSendQuery
> dbListResults(con)
list()
> dbGetQuery(con,"show variables like 'character_set_%'")
Variable_name Value
1 character_set_client latin1
2 character_set_connection latin1
3 character_set_database latin1
4 character_set_filesystem binary
5 character_set_results latin1
6 character_set_server latin1
7 character_set_system utf8
8 character_sets_dir /usr/share/mysql/charsets/
> dbSendQuery(con,"set names utf8")
<MySQLResult:(16695,0,20)>
> dbGetQuery(con,"show variables like 'character_set_%'")
Variable_name Value
1 character_set_client latin1
2 character_set_connection latin1
3 character_set_database latin1
4 character_set_filesystem binary
5 character_set_results latin1
6 character_set_server latin1
7 character_set_system utf8
8 character_sets_dir /usr/share/mysql/charsets/
So we've reproduced the behavior you're seeing. What's happened is that dbGetQuery saw that you had pending results and created a new connection, executed your query for getting the character set variables in a new connection, and showed you the results.
Your original connection actually has executed your query for setting the character set to utf8, but you just have to clear out the results object first like so:
> dbListResults(con)
[[1]]
<MySQLResult:(16695,0,20)>
> dbClearResult(dbListResults(con)[[1]])
[1] TRUE
> dbGetQuery(con,"show variables like 'character_set_%'")
Variable_name Value
1 character_set_client utf8
2 character_set_connection utf8
3 character_set_database latin1
4 character_set_filesystem binary
5 character_set_results utf8
6 character_set_server latin1
7 character_set_system utf8
8 character_sets_dir /usr/share/mysql/charsets/
from rmysql.
bencomp
commented on August 31, 2024
Thanks for answering. I'll remember :)
from rmysql.
rishabasthana5144
commented on August 31, 2024
Hi i need assistance regarding the same issue of encoding. Looking for help!!
from rmysql.
Related Issues (20)
- Error when installing RMysql on RHEL 7.3 HOT 3
- Query table of many DECIMAL columns causes error in terminal HOT 5
- RMysql encoding utf8 issue
- mysqlHasDefault() broken on Linux? HOT 1
- SSL - check status HOT 1
- Date datetype is written as text
- Document default database name in the RMySQL::mysqlHasDefault() function
- dbWriteTable fails without row_names column HOT 1
- Installing with Homebrew on OS X Sierra; cannot find RMySQL.so file it just created?
- Implement RStudio Connection Contract HOT 1
- Rows missing on inserting data frame with large character field
- RMySQL fails build with mariadb >10.4 HOT 12
- GenomicFeatures and RMySQL HOT 4
- Cannot connect to MySQL 8.0.11 HOT 3
- Can't install
- Failure to use ~/.my.cnf beginning with v0.10.14 HOT 3
- Unable to install RMySQL on macOS 10.13.6 HOT 7
- host not read from .my.cnf HOT 15
- connecting using SSL (client.flag=2048) to a RDS DB freezes forever HOT 3
- dbplyr operations not work for RMySQL backend HOT 19
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 rmysql.