Ch.10 SML# feature: seamless SQL integration

§ 10.4. Query examples

Let us now connect and use an actual database from SML#. SML# version 2.0.0 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 10.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 10.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