§ 7.4. Query examples

Let us now connect and use an actual database from SML#. SML# version 1.0.1 support PostgreSQL. To use a database, you need to install and start PostgreSQL server.

Here we show a standard step in Linux system to set up PostgreSQL server. For more details, consult PostgreSQL document.

1. Login as postgres.

2. Start PostgreSQL server. The command pg_ctl start -D /usr/local/pgsql/data will do this.

3. Create a PostgreSQL user role by executing command createuser myAccount, where myAccount is your user account.

4. Return to your account, and execute command createdb mydb to create a database called mydb.

5. Use the SQL interpreter to create a table. For example, the table in Section 7.1 can be created as follows.

   $ psql
   mydb# CREATE TABLE Persons (
             name text not null, age int not null, salary int not null      );
   mydb# INSERT INTO Persons VALUES ('Joe', 21, 10000);
   mydb# INSERT INTO Persons VALUES ('Sue', 31, 20000);
   mydb# INSERT INTO Persons VALUES ('Bob', 41, 30000);
   

6. Check that the database can be accessed. You should get the following output.

   $ psql mydb;
   
   mydb=# SELECT * FROM Persons;
   name | age | salary
   ------+-----+--------
   Joe | 21 | 10000
   Sue | 31 | 20000
   Bob | 41 | 20000
   (3 行)

Now let's access this database from SML#. Let the query function defined in Section 7.2 myQuery. In an interactive session, you should get the following.

$ smlsharp;
# val myServer = _sqlserver (dbname="mydb") : {Persons:{name:string, age:int, salary :int};
val myServer = _ : {Persons: {age: int, name: string, salary: int}} server
# val conn = SQL.connet myServer;
val conn = _ : {Persons: {age: int, name: string, salary: int}} conn
# val rel = _sqleval myQuery conn;
val rel = {Persons: {age: int, name: string, salary: int}} rel
# SQL.fetchAll rel;
val it = {{age=32, name="Sue"}, {age=41, name="Bob"}} : {age:int, name: string} list