Data: Database connectivity
[Part 5 of 12]


In this part, we're going to move on from ArcGIS to look at databases: how we connect to them programmatically, and how we talk to them using SQL.


First, let's look at how we connect to a database programmatically. We'll look in general at this, then look more closely at examples using Java.


Screenshot: A slide from the powerpoint

Database Connectivity (powerpoint)

Further info:

 

Java databases overview

For info on JDBC, see this Oracle page.

 

Database JDBC drivers:

MySQL

Oracle

SQL Server

On using Excel and text as a database in Windows, see this KnowledgeBase article.


Once we're connected to our database, we can use SQL to query the database for data.


Further info:

Introductory SQL tutorials at w3Schools.

Useful list of SQL datatypes at w3Schools.

 

Case sensitivity in queries is a tricky issue. See these articles for more info:

Article by Kimberly Tripp and follow-on posts

Article by Steven Smith

Article (on Wayback) by Richard Doering

SQL (powerpoint)


Finally, we'll look at how we do this SQL querying, or equivalent, in Java.


Screenshot: A slide from the powerpoint

Java Queries (powerpoint)

Further info:

 

Getting started with JDBC

 

ResultSet Columns from a metadata call using the the java.sql API.

 

For those who want to write Excel files using Java, your best bet is actually POI (Tutorial).


[Key ideas from this part]