mysql using RMariaDB connects in R Script but not in preview mode for SQL Script

[akvsak]

R version 4.2.0 (2022-04-22 ucrt) -- "Vigorous Calisthenics"

RStudio Version: 2022.02.3 Build 492
"Prairie Trillium" Release (1db809b8, 2022-05-20) for Windows
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) QtWebEngine/5.12.8 Chrome/69.0.3497.128 Safari/537.36

MySQL Server version: 5.5.62 MySQL Community Server (GPL)

RMariaDB version 1.2.1

1. I tried to File --> New --> SQL Script

-- !preview conn <- DBI::dbConnect(drv=RMariaDB::MariaDB(), user = "demo", password = "demo", host = "192.168.56.54", port = 3306, dbname = "sakila")

select 1

It gives an error pop-up "Failed to parse SQL preview comment."

2. If I use the same connection string in an R script.

library(RMariaDB)
conn <- DBI::dbConnect(drv=RMariaDB::MariaDB(), user = "demo", password = "demo", host = "192.168.56.54", port = 3306, dbname = "sakila")
rs <- dbSendQuery(conn, "show tables")
d1 <- dbFetch(rs)
d1

It connects and gives the proper output.

             Tables_in_sakila
1                       actor
2                  actor_info
3                     address
4                    category
5                      cctest
6                        city
7                     country
8                    customer
9               customer_list
10                        emp
11                       film
12                 film_actor
13              film_category
14                  film_list
15                  film_text
16                  inventory
17                   language
18 nicer_but_slower_film_list
19                    payment
20                     rental
21     sales_by_film_category
22             sales_by_store
23                      staff
24                 staff_list
25                      store

3. I even tried to assign con variable directly in another R script

con <- DBI::dbConnect(drv=RMariaDB::MariaDB(), user = "demo", password = "demo", host = "192.168.56.54", port = 3306, dbname = "sakila")

And in the SQL script used

-- !preview conn <- con

SELECT 1

But I select the same error pop-up "Failed to parse SQL preview comment."

What am I doing wrong? Thanks for reading.

[akvsak]

I found the reason after some experimenting.

For some reason in an SQL Script using preview statement, the following statements lead to the parse SQL comment error. "Failed to parse SQL preview comment."

-- !preview conn = con
-- !preview conn <- con
-- !preview conn = DBI::dbConnect(drv=RMariaDB::MariaDB(), user = 'demo', password = 'demo', host = '192.168.56.54', port = 3306, dbname = 'sakila')

The trick is

• not have a space between the conn variable and the = equal to sign
• not use the <- assignment operator

-- !preview conn= con
-- !preview conn= DBI::dbConnect(drv=RMariaDB::MariaDB(), user = 'demo', password = 'demo', host = '192.168.56.54', port = 3306, dbname = 'sakila')

Hope this helps someone!