SqlRender is part of HADES.
This is an R package for rendering parameterized SQL, and translating it to different SQL dialects. SqlRender can also be used as a stand-alone Java library and a command-line executable.
This example shows the use of parameters, as well as SqlRender's {if} ? {then} : {else} syntax:
sql <- render("SELECT * FROM @a; {@b != ''}?{USE @b;}", a = "my_table", b = "my_schema")
will produce the variable sql
containing this value:
"SELECT * FROM my_table; USE my_schema;"
subsequently running this code
sql <- translate(sql, "oracle")
will produce the variable sql
containing this value:
"SELECT * FROM my_table; ALTER SESSION SET current_schema = my_schema;"
The SqlDeveloper Shiny app is included in the SqlRender R package, and allows viewing the rendering and translation on the fly as you develop your SQL. The SqlDeveloper app is also available online here.
The SqlRender package is an R package wrapped around a Java library. The rJava package is used as interface.
The Java library is available as a JAR file.
Running the package requires R with the package rJava installed. Also requires Java 1.6 or higher. It is highly recommended for best performance to use a version of R that is at least version 4 or higher otherwise, unexpected bugs or errors may occur.
In R, to install the latest stable version, install from CRAN:
install.packages("SqlRender")
To install the latest development version, install from GitHub:
install.packages("remotes")
remotes::install_github("ohdsi/SqlRender", ref = "develop")
Once installed, you can try out SqlRender in a Shiny app that comes with the package:
library(SqlRender)
launchSqlRenderDeveloper()
You can fetch the JAR file in the inst/java folder of this repository, or use Maven:
<repository>
<id>ohdsi</id>
<name>repo.ohdsi.org</name>
<url>https://repo.ohdsi.org/nexus/content/repositories/releases</url>
</repository>
<repository>
<id>ohdsi.snapshots</id>
<name>repo.ohdsi.org-snapshots</name>
<url>https://repo.ohdsi.org/nexus/content/repositories/snapshots</url>
<releases>
<enabled>false</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
2: Include the SqlRender dependency in your pom.xml
<dependency>
<groupId>org.ohdsi.sql</groupId>
<artifactId>SqlRender</artifactId>
<version>1.9.2-SNAPSHOT</version>
</dependency>
You can fetch the JAR file in the inst/java folder of this repository, or use Maven as described above. Run this from the command line to get a list of options:
java -jar SqlRender.jar ?
Documentation can be found on the package website.
PDF versions of the documentation are also available: Vignette: Using SqlRender Package manual: SqlRender manual
Read here how you can contribute to this package.
SqlRender is licensed under Apache License 2.0
SqlRender is being developed in R Studio.
Stable. The code is actively being used in several projects.
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.