CODEWITHSUNDEEP
sqljdbcxquerybasexflwor
Nicholas Saunders
Nicholas Saunders

Reputation: 764

How to query an SQLITE database using BaseX and XQuery?

Do I need to install a driver for SQLite, or is it a problem with the query, perhaps?

nicholas $ 
nicholas $ basex sqlite.xq 
Stopped at /home/nicholas/xquery/sqlite.xq, 1/23:
[sql:error] An SQL exception occurred: No suitable driver found for jdbc:sqlite3://localhost:5432/home/nicholas/.local/share/liferea/liferea.db
nicholas $ 
nicholas $ cat sqlite.xq 
let $id := sql:connect("jdbc:sqlite3://localhost:5432/home/nicholas/.local/share/liferea/liferea.db")
return sql:execute($id, "SELECT title, description FROM items LIMIT 3;")
nicholas $

The example query:

let $id := sql:connect("jdbc:postgresql://localhost:5432/coffeehouse")
return sql:execute($id, "SELECT * FROM coffees WHERE price < 10")

I haven't tried with PostgreSQL or MySQL yet.

Upvotes: 0

Views: 341

Answers (1)

amix
amix

Reputation: 133

Yes, you need to install the driver, that matches the database backend, you use.

The (very good) documentation to BaseX states, in the section "SQL Module":

This module uses JDBC to connect to a SQL server. Hence, your JDBC driver will need to be added to the classpath, too. If you work with the full distributions of BaseX, you can copy the driver into the lib directory. To connect to MySQL, for example, download the Connector/J Driver and extract the archive into this directory.

For SQLite this could be xerial/sqlite-jdbc. Adding this JAR to your classpath (or to the lib/ directory in BaseX installation directory) should get you up running.

It also seems, that your syntax may be wrong, as your example lacks any hint of initialization of the driver, as well as using a connection string, that is different to the one, mentioned in the BaseX documentation. But I don't know SQL well enough, so there may be valid variants to those. Testing the documentation's example:

(: Initialize driver :)
sql:init("org.sqlite.JDBC"),
(: Establish a connection :)
let $conn := sql:connect("jdbc:sqlite:database.db")
return (
  (: Create a new table :)
  sql:execute($conn, "drop table if exists person"),
  sql:execute($conn, "create table person (id integer, name string)"),
  (: Run 10 updates :)
  for $i in 1 to 10
  let $q := "insert into person values(" || $i || ", '" || $i || "')"
  return sql:execute($conn, $q),
  (: Return table contents :)
  sql:execute($conn, "select * from person")
)

I got the documented result:

0
0
1
1
1
1
1
1
1
1
1
1
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">1</sql:column>
  <sql:column name="name">1</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">2</sql:column>
  <sql:column name="name">2</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">3</sql:column>
  <sql:column name="name">3</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">4</sql:column>
  <sql:column name="name">4</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">5</sql:column>
  <sql:column name="name">5</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">6</sql:column>
  <sql:column name="name">6</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">7</sql:column>
  <sql:column name="name">7</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">8</sql:column>
  <sql:column name="name">8</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">9</sql:column>
  <sql:column name="name">9</sql:column>
</sql:row>
<sql:row xmlns:sql="http://basex.org/modules/sql">
  <sql:column name="id">10</sql:column>
  <sql:column name="name">10</sql:column>
</sql:row>

Upvotes: 1

Related Questions