Balise (1) [Avatar] Offline
#1
I am trying to figure out the SQuirreL Client shown in the appendix (Figure A.2). Is there a database that I should be downloading to test the connection? If so, where can I find it and how do I specify the URL (in the Change Alias dialog) to point to a database living on a Windows box?

Thanks a bunch
john.mount (79) [Avatar] Offline
#2
Re: How do I set up the H2 database?
To access a DB from SquirreL SQL you need to install a Jar for that database as discussed on page 321 of the appendix.

An easy DB to try is H2 which can be gotten as follow:

A.3.1 Acquiring the H2 database engine
The H2 database engine is a serverless relational database that supports queries in SQL. All you need to do to use H2 is download the “all platforms zip” from http:// www.h2database.com. Just unpack the zip file in some directory you can remember. All you want from H2 is the Java JAR file found in the unzipped bin directory. In our case, the JAR is named h2-1.3.170.jar, or you can use what comes out of their supplied installer. The H2 database will allow us to show how R interacts with a SQL database without having to install a database server.


Part of the trick of H2 is: beyond getting the driver jar and pointing things the jar there is no set-up (you name DB path and it creates it on the filesystem).
kaveht (3) [Avatar] Offline
#3
Re: How do I set up the H2 database?
Sorry - been trying for a few days to replicate what is shown in Appendix A regarding H2 to no success. There isn't enough information to know if the h2test a db on h2.database.com or if h2demodb_h2 resides in h2.database.dcom or neither is true. Also, it difficult to transition from that Appendix and figure out how to connect to other databases. May be that was not the intent of the book; however it would be very helpful to point to a site that will walk a person through connecting to files outside of xlsx and csv. Without that, the discussion around SQL feels mute.
john.mount (79) [Avatar] Offline
#4
Re: How do I set up the H2 database?
I am sorry you are finding it confusing.

Unfortunately there are a lot of steps, because connecting to external software can require installing things (and then telling these things where they are installed). Any example is going to bring in a lot of external context.

Part of the problem of demonstrating working with a database, is to really do it you need a friendly database server to talk to. We try to avoid that by using a non-server database like H2 or SQLite. I happen to like H2, but more people like SQLite.

The appendix sections A.3.1 through A.3.2 describe not just using an H2 database, but also using the browser SQuirrel SQL (which in turn needs to be installed, needs Java, and needs a driver for the database in question installed). A lot of the URLs you are mentioning are where to get software, jars and drivers (not the database).

Here is a simpler example of using H2 with R. I am working on OSX,
so details may be a bit different for Unix or Windows.

1) Make sure you have a current version R and current version Java installed on your system (and know the path to the Java executable).
2) Download the H2 database code in the form of a Java jar from http://www.h2database.com/h2-2014-04-05.zip
3) Unzip the file h2-2014-04-05.zip and note the path to the file bin/h2-1.3.176.jar (you will need this for R).
4) Start R and install and load the RJDBC library (this is the bottom of page 323 of the book):

install.packages('RJDBC')
library('RJDBC')

5) Use R's setwd() command to move to the directory you want to work in (we will assume you have copied h2-1.3.176.jar into this directory.
6) Init a database connection driver:

drv <- JDBC("org.h2.Driver","h2-1.3.176.jar",identifier.quote="'"smilie

7) Create a new file based database and connect to it:

conn <- dbConnect(drv,"jdbc:h2:~/testdb"smilie

This creates a connection to a database associated with the URL "jdbc:h2:~/testdb". By H2 conventions the database is implemented in a couple of files in the directory "~" (home in OSX and Linux) starting with the prefix "testdb". In my case I see the files testdb.h2.db , testdb.trace.db (and a testdb.lock.db while I have an active connection).

smilie Create a table in the database using SQL from R and insert some data:

dbSendUpdate(conn,
"CREATE TABLE example_table ( statusId int, name varchar(125) )")
dbSendUpdate(conn,
"INSERT INTO example_table VALUES (1,'Joe')")
dbSendUpdate(conn,
"INSERT INTO example_table VALUES (2,'Fred')")


9) Read the data

dbGetQuery(conn,"SELECT * FROM example_table")

10) Close the connection.

dbDisconnect(conn)

11) Exit R.

12) Just for fun, restart R and see that the data persisted in the files (must be working in same directory for paths to work):

library('RJDBC')
drv <- JDBC("org.h2.Driver","h2-1.3.176.jar",identifier.quote="'"smilie
conn <- dbConnect(drv,"jdbc:h2:~/testdb"smilie
dbGetQuery(conn,"SELECT * FROM example_table")

13) Disconnect and exit R

dbDisconnect(conn)


This is just a tiny example. To do more you need to read up at least SQL, DBI http://cran.r-project.org/web/packages/DBI/DBI.pdf , and RJDBC http://cran.r-project.org/web/packages/RJDBC/RJDBC.pdf .

If you want to connect to somebody else's pre-existing database they have to:
1) Supply you with a jar containing the JDBC driver code (that is why we are using RJDBC, lots of databases have JDBC drivers).
2) Show you how to form the URL for the dbConnect() command (the URL has some standard syntax, but what goes into the fields is very idiosyncratic per-database). The idea is that a connection URL is again a JDBC concept (not an R concept) so even a non-R person may be able to help.
kaveht (3) [Avatar] Offline
#5
Re: How do I set up the H2 database?
Thanks for the reply. Your explanation was very clear and I could follow it. At the end, I couldn't get SQL Screwdriver to work - so I had to learn PostgreSQL - now I am back on track. smilie
john.mount (79) [Avatar] Offline
#6
Re: How do I set up the H2 database?
You are very welcome. It is a lot of things to set up at once, so if you have a DB and browser that work for you- then that is even better. And PostgreSQL is a great database (one you are likely to encounter in production).
john.mount (79) [Avatar] Offline
#7
Re: How do I set up the H2 database?
Another option: SQLite (uses DBI bindings but avoids RJDBC/Java complications).

If you want to use Python for the write steps you can do as below (only showing Python to get an extra example of cross-language interoperability, you can work this entire example in R). Write steps in Python (from https://docs.python.org/3.1/library/sqlite3.html):

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute("CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)")
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
conn.commit()
conn.close()

(alternate: write steps in R:
library('RSQLite')
drv <- dbDriver("SQLite"smilie
conn <- dbConnect(drv, dbname = 'example.db')
dbSendQuery(conn,
"CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)")
dbSendQuery(conn,
"INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
dbDisconnect(conn)
)



Read steps in R (in the same directory you did the Python work, see getwd(), setwd()):

library('RSQLite')
drv <- dbDriver("SQLite"smilie
conn <- dbConnect(drv, dbname = 'example.db')
print(dbGetQuery(conn,'select * from stocks'))
dbDisconnect(conn)