1.2.6 Connecting and disconnecting

with-connection

Statement macro

Summary

Within the dynamic scope of the body, establishes the default connection.

Signature

with-connection(connection)
  body 
end [with-connection]

Arguments

connection: An instance of <connection>.

Library

sql

Module

sql

Description

Within the dynamic scope of body, establishes the default connection as connection. The value of connection must be an instance of <connection> returned from the method connect. The result of this macro is the last expression executed within body.

Example:

This example queries the ODBC database library for the number of books published by Addison-Wesley and the number of books published by McGraw-Hill. This example is written in an unorthodox manner (two connections to the same database) to show that SQL statements may be executed against different established connections using the with-connection macro. The <result-set> class is discussed on page 34.

begin
  let db = make (<odbc-database>, name: "library");
  let user = make(<odbc-user>, user: "andrew", 
                  password: "foobar");
  let a-connection :: <connection> = connect(db, user);
  let b-connection :: <connection> = connect(db, user);

  let aw-query = make (<odbc-sql-statement>,
    text: "select count(*)
    from book
    where publisher = "Addison-Wesley"
  let addison-wesley-library :: <result-set> =
    with-connection(a-connection);
      execufte(aw-query),
    end with-connection;
  let first-record = addison-wesley-library[0];
  let addison-wesley-library-count = first-record[0];
  let mh-query = make (<odbc-sql-statement>, test:
        select count(*)
        from book
        where publisher = "McGraw-Hill"
  let mcgraw-hill-library :: <result-set> =
    with-connection(b-connection);
      execute(mh-query),
    end with-connection;
  let first-record = mcgraw-hill-library[0];
  let mcgraw-hill-library-count = first-record[0];

  disconnect-all();
  values(addison-wesley-library-count, 
         mcgraw-hill-library-count);
end;

=> 2
   0