!!!!! Warning. Following explanations are for the second version of dbc library that is not released yet. !!!!!

The dbc package lets you easily write queries to databases and handle the results.

You have to create a Database. That class provide tools to execute statements on the database. Example of a database named “DerbyDBTest”

import scala.dbc2.exception._
 
import scala.dbc2.Database
import scala.dbc2.ShortSyntax._
 
 
val d = new Database(new scala.dbc2.vendor.Derby(new java.net.URI("jdbc:derby:DerbyDBTest"), true))

To execute a statement “stat1” on the database, create that statement (see below) and then use the Database created before.

val res = d.executeStatement(stat1)

To create tables, there are two ways:

• Table elements definitions list - Define all the columns and table constraints. Specifying data types, default value, constraints.
• Subquery - Define the table as a subquery (see Select part)

With table elements definitions.

val createTable1 = create table 'table1 columns(
				'col1 integer constraint('cons1) not nullVal,
				'col2 character(20) default(1),
				constraint('cons2) check('col1 > 3),
				primary key('col2))
 

As subquery.

val createTable2 = create table 'table2 as(select(*) from('table1) where 'col1 > 100)
 

For an insert statement, specify the columns in which the data will be inserted. Then specify the values with method values. Arguments for method values are Products (Superclass of TupleN), and we use Tuple constructor (..,..,..). Each values corresponds to a row in the table. If you have only one row, you can simply call values and enumerate the different values for that row without constructing a Tuple.

val insertValues1 = insert into 'table1 columns('col1, 'col2) values((5, "hello"), (10, "world"))
val insertValues2 = insert into 'table1 values(15, "sample text")

There is a special case for null. When inserting values definitions, you can use null from Scala instead of nullVal used in other statements.

val insertValues3 = insert into 'table1 values(12, null)
 

To delete values from a table, just specify the where condition (with method where).

val deleteValues = delete from 'PERSON where FIRSTNAME === "Valerie"
 

To update some values, use the method update(tablename) and method set(..., ...). The set method specifies the new value for each field in the table. You can also specify the where condition as usual.

val updateValues1 = update('PERSON) set('MYADDRESS === 3) where 'NAME === "Dugenou"
val updateAddress = update('ADDRESS) set('CP === 1226, 'CITYREF === "Car" || "ouge")

The select statement is the more complex request. The primary methods are select, from, where, groupBy and having. select and from also accept more than one argument. The where method is used as usual. The having method is used as the where method and groupBy (as select and from) and can take more than one argument. In the select method, we can specify the expected type of the columns with method of(datatype). If you specify expected type for the column, you will receive elements of that type. If not, elements will be of type Unknown.

val selectValues1 = select('CITYNAME of character varying(20)) from('CITY) where 'NBINHABITANTS <= 100000
val selectValues2 = select('NAME, 'FIRSTNAME) from('PERSON, 'ADDRESS) where 'MYADDRESS === 'ID and 'CITYREF <> "Lausanne"
val selectValues3 = select(*) from('SPORT)
 

To show the result of a select, execute statement as shown above and then:

for (val i <- res;
     val f <- i.fields) {
        Console.println(f.content.sqlString)
}

val dropTable1 = drop table 'PERSON
 

Unfortunately, we are not able to write request in Scala with exactly the same syntax as in SQL. There are some differences listed below.

• insert into t1 (c1, c2) values ... = insert into t1 columns(c1, c2) values ...
• create table t1 (c1, c2) as ... = create table t1 columns(c1, c2) as ...
• null = nullVal (But not in all the cases)
• with no data = With noData
• with data = With data
• is not = isNot
• cast (target as operand) = cast (target asType operand)
• a.b = a ∗∗ b