1,392 questions
2
votes
1
answer
68
views
Perl DBI with Mariadb Maxscale
I have set up a Mariadb Maxscale (1 Primary and 2 Replicas) replication cluster for experimentation purposes. As I understand it the main purpose for using Maxscale is that it decides what server to ...
- 962
0
votes
1
answer
76
views
Find/replace with variables
I'm trying to write a perl script to remove strings from a mariadb database that contains articles from a Joomla website. Our AI tool is inserting extra junk in the database that needs to be removed. ...
- 499
0
votes
0
answers
101
views
Can't locate DBD/mysql.pm after upgrading Strawberry Perl on Windows
I recently updated the Strawberry Perl on a Windows system from 5.32 to 5.40, and now I can't connect to my MariaDB database using DBI.
Here is the script:
#!/usr/bin/perl
use strict;
use warnings;
...
- 17
0
votes
1
answer
47
views
R DBI with ODBC to cast datatype for additional database
I'm using below DBI connection with ODBC driver to connect to a Trino database
DBI::dbConnect(
drv = odbc::odbc(),
'trino'
)
It failed to translate as.character().
m_tbl %>% mutate(col1 = as....
- 53
1
vote
2
answers
61
views
Get MAX date from SQLite DB using R
I'm trying to get the MAX(date) from a column using RSQLite. I'm not sure where the issue is, in the db or in R.
library(RSQLite)
library(tidyverse)
library(lubridate)
df <- tibble(date_col = ...
- 2,561
-1
votes
4
answers
117
views
Need to iterate a hash returned from a mysql query
Ok - I have the following member function:
sub GetCategories {
my $sth = $dbh->prepare("SELECT CatID, CatName, CatLink FROM Categories");
$sth->execute() or die $DBI::errstr;
...
- 321
0
votes
1
answer
21
views
Error when trying to write dataframe to oracle database
I'm trying to insert data from a R dataframe into an existing Oracle database table using the dbWriteTable function from ROracle but get the following error: Error in .oci.GetQuery(con, stmt) : ORA-...
- 1
0
votes
0
answers
72
views
Fastest way to get databricks SQL warehouse data into a tibble in RShiny without blocking main thread?
The Problem
I'm working on an RShiny application that gets data from databricks sql warehouse when a user presses a button.
In an uncached situation using DBI it takes about 40 seconds to get the data ...
- 21
2
votes
0
answers
307
views
Issues connecting odbc in newest R Studio update
I recently updated R Studio and odbc/dbi seems to be broken. Running packageVersion("odbc") I see:
[1] ‘1.5.0’ Warning message: package ‘odbc’ was built under R version 4.4.1
My current ...
- 1,779
0
votes
1
answer
60
views
Foreign key constraint lost in PostgreSQL table in Azure when data is added using R Script
I have a table in PostgreSQL database, hosted on Azure cloud. In this table, I have created foreign key constraints on multiiple columns.
I have a script in R which tries to push the contents of a ...
- 9
0
votes
0
answers
13
views
DBI dbExecute not working with Oracle Database [duplicate]
I have a RJDBC-connection with an Oracle Database and run the following example:
dbWriteTable(con, "cars", head(cars, 3))
dbReadTable(con, "cars") # there are 3 rows
This works ...
- 1,003
0
votes
1
answer
71
views
DBI cpan install fails
Trying to install DBI.pm using
cpan install DBI
under bash-4.2 fails on several linux platforms including raspberrypi.
On a GoDaddy server a lot of commands go well until
gcc -c (many options) Perl....
- 62
1
vote
1
answer
116
views
Perl - semicolon in a password for DB connection makes app fail
Our Perl app which is compiled as exe do following:
read lot of params from ini file (including dbname+username+password)
connect to db with specified params and do some maintenance work
Problem: ...
- 440
0
votes
2
answers
102
views
Append new rows / data into data base if it's not there in R
I want to create a table in DWH to build historical information about something, this table get their data from live data base, the update in my table will be only in column in my table if this ...
0
votes
1
answer
36
views
Adding new variables to a table in Azure from R? [closed]
I am storing data in Azure and plan to do a lot of the updates (adding, removing and changing records) from R. This is because I've already written extensive code in R and don't currently have the ...
- 86
1
vote
2
answers
76
views
Best practice for including remote data sources in an R package?
When authoring an R package, it is often good practice to bake in some datasets, usually for educational/tutorial purposes. There's a chapter about this in Hadley Wickham's book about R packages: ...
- 185
1
vote
1
answer
102
views
Perl DBI SQLite script with multiple database handles fails due to locked database if transactions are used
I have an application that uses Perl's DBI module and the SQLite driver. The application processes some images, and stores data about the contents of the images in a database. For some uses, the ...
- 83
1
vote
1
answer
82
views
Rstudio and Snowflake Help, dbGetQuery not working when trying to run Where clause
I am trying to pull in a snowflake table with a where clause where the "Snapshot_Date" is a date and "Date1" is a data value I set earlier in the R code.
However, this statement is ...
- 11
1
vote
1
answer
254
views
Issue interacting via dbplyr with remote SQL tables probably since odbc or DBI or dbplyr package update
I used to have a R code working fine a few months back, which was connecting remotely to a SQL DB and interacting with it via dbplyr package.
Recently, I had to reinstall R/RStudio with all packages. ...
- 794
0
votes
1
answer
225
views
How do I convert a "tbl_df" "tbl" "data.frame" object to a "tbl_Redshift" "tbl_dbi" "tbl_sql" "tbl_lazy" "tbl" object for uploading?
I am very new to using servers for data analysis, and I am completely baffled by what a "tbl_df""tbl""data.frame" object, and "tbl_Redshift" "tbl_dbi" ...
- 43
0
votes
0
answers
42
views
Diacritics issue when retrieving data from a Filemaker database with DBI and ODBC
I am trying to switch from RODBC, which is depreciated, to DBIand odbc packages to retrieve data from a Filemaker database. It is configured to use UTF-8 encoding. I have configured unixodbc and using ...
- 1
0
votes
0
answers
81
views
Error: "Specified SQL Server not found" when connecting SQL Server to R Markdown
I'm getting this error when trying to connect my SQL Server to R Markdown
Error: nanodbc/nanodbc.cpp:1138: 01S00
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Specified SQL server not found.
[...
0
votes
1
answer
85
views
Automatically unlock tables upon control C
I have observed that if I hit control C on a running perl DBI script that the next run of the same script will hang.
I have confirmed with ps that the script is not running after I click control C.
I ...
2
votes
1
answer
117
views
Perl DBI::SQLite: How to Dump a table with column names?
Similar to How to get a list of column names on Sqlite3 database?, but still different:
For debugging purposes I wrote a closure that dumps an SQLite3 table.
The output is not very pretty, but it ...
- 4,315
0
votes
0
answers
46
views
Croatian characters incorrectly retrieved in R via DBI
I'm fetching some data from a SQL Server database. On my machine I get it fine, however certain Croatian characters get retrieved incorrectly for my colleague. Here's a sample usage in R:
...
- 433
2
votes
3
answers
2k
views
rstudio dbi odbc connect Driver for SQL Server
running the following code:
library(DBI)
conn <- DBI::dbConnect(odbc::odbc(),
driver = "ODBC Driver 17 for SQL Server",
database = "xyz",
...
- 159
0
votes
0
answers
150
views
Create new table with bigrquery
I am trying to create a new table in Bigquery, using bigrquery and tables already loaded in bigquery.
I know I could do:
library(tidyverse)
library(bigrquery)
library(DBI)
project_id <- "...
- 105
1
vote
0
answers
1k
views
How to load httpfs duckdb extension
I am trying to load httpfs duckdb extension to access parquet file on s3 but it looks like it cannot load even though the file httpfs.duckdb_extension is in the directory
library(DBI)
library(...
- 729
-1
votes
1
answer
175
views
Encrypt database connection for perl and mysql using dbi
We are connecting to mysql database from perl using DBI. The connection string has dns as $dsn = "DBI:mysql:database=test;host=myhost.portal.com;port=3306;";. But this connection is made ...
- 21.1k
0
votes
0
answers
297
views
Can't connect R with PostgreSQL
I am encountering the following error while connecting RStudio with PostgreSQL.
# Establish a connection to the PostgreSQL database
> con <- dbConnect(dbDriver("PostgreSQL"), dbname = ...
1
vote
1
answer
384
views
CPAN Install DBD::MariaDB failed on OS X 14
I am using a MacBook 14.1.1 Sonoma M2 chip and want my Perl scripts to talk to my MariaDB database. Installing DBI was no problem. However when trying to install DBD::MariaDB I get the following error:...
- 11
1
vote
0
answers
178
views
How to connect Trino with OAuth2 in RStudio?
In Python there is trino.auth.OAuth2Authentication to redirect to web to autheticate.
Is there something similar in R?
- 305
1
vote
0
answers
94
views
Using mysql BIT columns with perl DBI
I converted some TINYINT columns that held 0/1 to BIT, as there would be storage savings. This is on MySQL 5.7, latest DBI, my DBD::mysql is 3 versions old (4.050 - the latest suddenly requires mysql ...
- 1,137
0
votes
0
answers
90
views
How to fetch and create dataframe faster in R
I'm using following query in R, the Query Run time is < 50 sec to fetch millions of records however while converting the output into dataframe using dbGetQuery it takes almost an hour. Please ...
- 477
3
votes
1
answer
608
views
Error passing a parameter to SQL with RMySQL
I have been trying to find the reason of the error in this code, but I have not been successful. I would like to pass a variable to SQL, but it keeps on giving the same error.
A MWE is the following (...
- 187
3
votes
0
answers
1k
views
How can I install DBD::mysql on Debian?
Having issues getting DBD::mysql installed on Debian. This is actually the Debian-based Apache docker image which comes with Perl 5.36. Despite pre-installing libdbd-mysql-perl, libmariadb-dev, ...
- 5,828
0
votes
0
answers
88
views
Can't create connection to Database inside of docker container, host name won't translate
I created an instance of RStudio running inside of a docker container. When working inside of the container, I get the following error when trying to connect to Postgres database: "Error: could ...
- 53
8
votes
1
answer
502
views
R Targets with DBI/SQL database
I have a targets pipeline which should fill and use a DBI database (in this case duckdb. Both SQL as well as duckdb are chosen for performance reasons).
How can I effectively use {targets} in ...
- 10.1k
0
votes
1
answer
63
views
R package DBI - how to add a "WHERE date > 2022-01-01" clause?
I am using the R package DBI to extract data from a database. My query looks like this:
extract <- dbGetQuery(con,
'SELECT
"one column" as onecolumn,
"other column" as ...
- 357
0
votes
1
answer
32
views
Can you run an MS Access event procedure within R using the DBI package?
There is an MS Access database that has a command button which when clicked activates an event procedure. I have tried to call this using dbExecute() but to no avail (I appreciate I was trying to ...
- 133
0
votes
2
answers
148
views
Can connect to Oracle DB via R but can't fetch data
At work I've been trying to connect to an Oracle DB using R. I've been provided the credentials and I successfully managed to connect (using 32bits R version):
library(odbc)
library(DBI)
con <- ...
- 307
1
vote
1
answer
116
views
Perl DBI/DBD error message contains strange characters
I have recently come across the following behaviour and wondered if there was a fix.
I have a Linux perl (V5.16.3) program that connects to an Oracle database (V11g2) using DBI.
The program captures ...
- 647
0
votes
1
answer
314
views
R local database connection: Posit Cloud R Studio vs Local Laptop R Studio
I'm trying to use Posit Cloud to connect to a SQL Server database installed locally on my laptop.
The following, using only RStudio on my laptop,
runs/connects without issue:
# RStudio version: 2023....
- 135
0
votes
0
answers
708
views
Error connecting to SQL Server using ODBC on R
I have successfully created and tested an ODBC Data Source Object for SQL Server on my windows, however when I run the following in RStudio to connect the db:
library(DBI)
con <- dbConnect(odbc::...
- 171
21
votes
2
answers
24k
views
WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version
As I have seen here, Beginning with MySQL 8.0.34, the automatic reconnection feature is deprecated. I have a lot of Perl scripts that connect to a MySQL database with something like:
my $dbh = DBI->...
- 2,154
1
vote
2
answers
1k
views
Getting a SQL compilation error "Compilation memory exhausted." when trying to push an R data frame to Snowflake
I'm using dbWriteTable to create a database table in Snowflake from R. This works nicely when I push a data frame of 350,000 rows, but fails with SQL compilation error: Compilation memory exhausted. ...
- 11
1
vote
1
answer
75
views
How to copy/join few columns from a csv file to an existing SQL table in R?
I have a created a table "SQL_table" in SQL and copied contents from various existing .csv files to "SQL_table" in R using the code given below.
Code:
library(RPostgres)
library(...
1
vote
0
answers
156
views
Mojolicious, Mojo::Promise, DBI, and parallel requests
I am new in Mojolicious and playing around with this simple application to return rows from database. It seems to work fine for a single request, however when I use "xargs -n 1 -P 4" to run ...
- 577
0
votes
1
answer
70
views
How to use dbSendQuery to import different CSV files into 1 SQL database table using R?
Referring to this page,I am using following code trying to insert multiple csv files into SQL database table using R
#Step 1: Libraries
library(RPostgres)
library(data.table)
library(DBI)
#Step 2: ...
- 51
1
vote
1
answer
83
views
How to capture the output of the Pg EXPLAIN command with perl DBI?
Users of our Mojolicious app can submit queries to our Pg database. Some of these queries can be quite large, both in terms of execution time and number of results returned. I'd like to find a way to ...
- 67