Simple Client
Introduction
The JDBC drivers are delivered in JAR format. The JAR file contains a simple JDBC client called Simple Client that can be used to execute SQL queries.
The Simple Client can be started simply with Java, for example:
- CsvJdbc driver: java -jar csvjdbc.jar
- ExcelJdbc driver: java -jar exceljdbc.jar
- JsonJdbc driver: java -jar jsonjdbc.jar
After startup, the client reports its version and shows a prompt:
Simple jdbc client, 1.1
>
The Simple Client supports the following commands:
connect .... Opens a database connection with a JDBC URL
load ....... Loads a JDBC driver by its class name
quit ....... Exits the program
help ....... Shows help
run ........ Executes a command file
select ..... Executes an SQL query
status ..... Shows the current status
trace ...... Shows details of the last error that occurred
First Query
After startup, the default JDBC driver is already loaded. This can be verified with the "status" command and looks like this for the CsvJdbc JAR file, for example:
> status
I have found the following drivers:
Driver: de.softquadrat.jdbc.csv.CsvDriver, 1.0
Your current connection is:
--- none ---
Now a database connection can be opened with the "connect" command. The JDBC URL supported by the driver is specified:
- CsvJdbc driver: 'jdbc:csv:'
- ExcelJdbc driver: 'jdbc:xlsx:'
- JsonJdbc driver: 'jdbc:json:'
> connect 'jdbc:csv:'
Connection successfully opened.
> status
I have found the following drivers:
Driver: de.softquadrat.jdbc.csv.CsvDriver, 1.0
Your current connection is:
datasqill csv driver, 1.0
The single quotes enclosing the JDBC URL are important.
After that, queries can be executed such as:
> SELECT x,y,z
- FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'
- WHERE rownumber <= 8;
x y z
- - -
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Note: For queries that span multiple lines, the Simple Client uses a minus sign as the prompt to indicate continuation.
Run Command
With the "run" command, command files with commands can be executed. For example, a file "cmd.sql" like the following could be started:
load 'de.softquadrat.jdbc.csv.CsvDriver'
connect 'jdbc:csv:'
SELECT x,y,z
FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'
WHERE rownumber <= 8;
> run 'cmd.sql'
load 'de.softquadrat.jdbc.csv.CsvDriver'
Driver "de.softquadrat.jdbc.csv.CsvDriver" loaded successfully.
connect 'jdbc:csv:'
Connection successfully opened.
SELECT x,y,z
FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'
WHERE rownumber <= 8;
x y z
- - -
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Arguments
The Simple Client can be started with command files as arguments. Then the program opens these files one after another and executes the commands contained in them. This can be used to perform initializations and execute standard queries.
If the file "cmd.sql" is passed as an argument, for example, all commands are first executed before the user prompt for input appears:
$ java -jar csvjdbc.jar cmd.sql
Simple jdbc client, 1.1
load 'de.softquadrat.jdbc.csv.CsvDriver'
Driver "de.softquadrat.jdbc.csv.CsvDriver" loaded successfully.
connect 'jdbc:csv:'
Connection successfully opened.
SELECT x,y,z
FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'
WHERE rownumber <= 8;
x y z
- - -
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
>
Note: If the passed command file contains the "quit" command, execution ends directly without switching to interactive user mode afterwards.
CsvJdbc
The following CSV file is used for the examples: Sample CSV
Start JDBC client
java -jar csvjdbc.jar
Output
Simple jdbc client, 1.0
Connect to the driver
connect 'jdbc:csv:'
Output
Connection successfully opened.
Select the first 3 columns from the file chinook.Track.csv and give them the names x, y and z
Read the first 8 rows
SELECT x,y,z
FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'
WHERE rownumber <= 8;
Output
x y z
- - -
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Select the first 3 columns from the file chinook.Track.csv, give them the names x, y and z and skip the header row
Read 8 rows
SELECT x,y,z
FROM "chinook.Track.csv" COLUMNS (x,y,z) SEPARATED BY '|'
WHERE rownumber > 1
AND rownumber <= 9;
Output
x y z
- - -
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
8 Inject The Venom 1
Select the first 3 columns from the file chinook.Track.csv and give them the names TrackId (case sensitive), name (insensitive) and AlbumId (insensitive)
Read the first 8 rows
SELECT "TrackId",name,AlbumId
FROM "chinook.Track.csv" COLUMNS ("TrackId",Name,AlbumId) SEPARATED BY '|'
WHERE rownumber <= 8;
Output
TrackId name albumid
------- ---- -------
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Select 3 columns from the file chinook.Track.csv with the header names in row 1 from the CSV and SQL names TrackId, Name and AlbumId
- The comparison between the entries in the COLUMNS clause and the titles in the CSV in row 1 is case insensitive.
Read the first 8 rows
SELECT TrackId,Name,AlbumId
FROM "chinook.Track.csv" COLUMNS (trackid,Name,AlbumId) HEADLINE 1 SEPARATED BY '|'
WHERE rownumber <= 8;
Output
trackid name albumid
------- ---- -------
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
8 Inject The Venom 1
If you swap the columns Name and trackid in the COLUMNS clause you get the same result as before.
SELECT TrackId,Name,AlbumId
FROM "chinook.Track.csv" COLUMNS (Name,trackid,AlbumId) HEADLINE 1 SEPARATED BY '|'
WHERE rownumber <= 8;
This does not happen when columns are matched with the headers in the CSV (via HEADLINE 1). Thus the supplier can swap the order arbitrarily, as long as the headers (= names) in the header row are always specified the same
ExcelJdbc
The following Excel is used for the examples: Sample Excel
Start JDBC client
java -jar exceljdbc.jar
Output
Simple jdbc client, 1.0
Connect to the driver
connect 'jdbc:xlsx:'
Output
Connection successfully opened.
Select the first 3 columns from the Track worksheet and give them the names x, y and z
Read the first 8 rows
SELECT x,y,z
FROM "chinook.xlsx"."Track" COLUMNS (x,y,z)
WHERE rownumber <= 8;
Output
x y z
- - -
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Select the first 3 columns from the Track worksheet and give them the case sensitive names TrackId, Name and AlbumId
Read the first 8 rows
SELECT "TrackId","Name","AlbumId"
FROM "chinook.xlsx"."Track" COLUMNS ("TrackId","Name","AlbumId")
WHERE ROWNUMBER <= 8;
Output
TrackId Name AlbumId
------- ---- -------
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Select the first 3 columns from the Track worksheet and give them the case insensitive names TrackId, Name and AlbumId
Read the first 8 rows
SELECT TrackId,Name,AlbumId
FROM "chinook.xlsx"."Track" COLUMNS (TrackId,Name,AlbumId)
WHERE ROWNUMBER <= 8;
Output
trackid name albumid
------- ---- -------
TrackId Name AlbumId
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
Select 3 columns from the Track worksheet with the header names in Excel and SQL names TrackId, Name and AlbumId
- The comparison between the entries in the COLUMNS clause and the titles in Excel in row 1 is case insensitive.
Read the first 8 rows
SELECT TrackId,Name,AlbumId
FROM "chinook.xlsx"."Track" COLUMNS (TrackId,Name,AlbumId) HEADLINE 1
WHERE ROWNUMBER <= 8;
Output
trackid name albumid
------- ---- -------
1 For Those About To Rock (We Salute You) 1
2 Balls to the Wall 2
3 Fast As a Shark 3
4 Restless and Wild 3
5 Princess of the Dawn 3
6 Put The Finger On You 1
7 Let's Get It Up 1
8 Inject The Venom 1
Select 3 columns from the Track worksheet with the header names in Excel and SQL names Name, TrackId and Composer
Read the first 8 rows
SELECT Name,TrackId,Composer
FROM "chinook.xlsx"."Track" COLUMNS (TrackId,Name,Composer) HEADLINE 1
WHERE ROWNUMBER <= 8;
Output
name trackid composer
---- ------- --------
For Those About To Rock (We Salute You) 1 Angus Young, Malcolm Young, Brian Johnson
Balls to the Wall 2 null
Fast As a Shark 3 F. Baltes, S. Kaufman, U. Dirkscneider
Restless and Wild 4 F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. Dirkscneider
Princess of the Dawn 5 Deaffy
Put The Finger On You 6 Angus Young, Malcolm Young, Brian Johnson
Let's Get It Up 7 Angus Young, Malcolm Young, Brian Johnson
Inject The Venom 8 Angus Young, Malcolm Young, Brian Johnson
Select a column that does not exist in Excel
Read the first 8 rows
SELECT Name1
FROM "chinook.xlsx"."Track" COLUMNS (TrackId,Name1,Composer) HEADLINE 1
WHERE ROWNUMBER <= 8;
Output
required column name name1 not found in xlsx file [xl/worksheets/sheet1.xml]