SDSU CS 580 Client-Server Programming
Fall Semester, 2002
VisualWorks Database Connection
Previous    Lecture Notes Index    Next    
© 2002, All Rights Reserved, SDSU & Roger Whitney
San Diego State University -- This page last updated 26-Nov-02

Contents of Doc 24, VisualWorks Database Connection



References

VisualWorks Database Application Developer’s Guide, Chapter 7 EXDI Database Interface

Doc 24, VisualWorks Database Connection Slide # 2

VW Database Interface


Load the PostgreSQLEXDI parcel

See the Database section of the Parcel manager


Doc 24, VisualWorks Database Connection Slide # 3
Converting between Types

Smalltalk Type
PostgreSQL type
ByteString
text,
ByteString
varchar(4),
ByteString
char(4),
SmallInteger
integer,
SmallInteger
int2,
SmallInteger
int8,
SmallInteger
oid,
FixedPoint
numeric(6,2),
Double
float,
Double
float4,
Date
date,
Time
time,
String
timestamp,
SmallInteger
interval,
True
bool,
Point
point,
LineSegment
lseg,
Polyline
path,
Rectangle
box,
Circle
circle,
Polyline
polygon,
ByteString
inet,
ByteString
cidr,
ByteString
macaddr


Doc 24, VisualWorks Database Connection Slide # 4

Sample Connection


“Prints all rows of table pg_tables”
   | connection session answer columns |
   connection := PostgreSQLEXDIConnection new.
   connection
      username: 'whitney';
      password: 'notsoEasy';
      environment: 'rugby.sdsu.edu_test';
      connect.
   (session := connection getSession)
      prepare: 'SELECT * FROM pg_tables';
      execute.
   answer := session answer.
   columns := answer columnDescriptions.
   Transcript clear.
   columns do: [:each | Transcript show: each name]
      separatedBy: [Transcript tab].
   Transcript cr.
   [answer atEnd] whileFalse: 
         [| row |
         row := answer next.
         row do: [:each | Transcript print: each] separatedBy: [Transcript tab].
         Transcript cr].
   connection disconnect.
   Transcript flush


Doc 24, VisualWorks Database Connection Slide # 5

Connection


Environment string format: host:port_databaseName

Host

port is optional



Doc 24, VisualWorks Database Connection Slide # 6
Isolating Database Vender Information

ExternalDatabaseConnection isolates the selection of

Setting the Concrete driver

ExternalDatabaseConnection defaultConnection: 
   #PostgreSQLEXDIConnection.

Set the database

ExternalDatabaseConnection defaultEnvironment: 'rugby.sdsu.edu_test'.

Using the defaults
After setting the defaults

ExternalDatabaseConnection new returns the default Driver class

Driver class uses the default environment

connection := ExternalDatabaseConnection new.
connection
   username: 'whitney';
   password: 'idontthnkso';
   connect.
"some work done here"
connection disconnect


Doc 24, VisualWorks Database Connection Slide # 7

Exceptions from Database


Errors in the SQL results in exceptions in Smalltalk

   [(session := connection getSession)
      prepare: 'put your sql here';
      execute] 
         on: connection class externalDatabaseErrorSignal
         do: [:exception | Put your error hanlder code here].
There are several subexceptions that are possible

See the documentation for details

Doc 24, VisualWorks Database Connection Slide # 8

Simple Example


CreationThe Table
title
starttime
abstract




Example assumes set defaults in ExternalDatabaseConnection

connection := ExternalDatabaseConnection new.
connection
   username: 'whitney';
   password: 'idontthnkso';
   connect.
session := connection getSession.
createSQL :=
   'CREATE TABLE events(title text, starttime timestamp, abstract text)'.
[session
   prepare: createSQL
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
   connection disconnect.

Doc 24, VisualWorks Database Connection Slide # 9
Insertion

acm := 'INSERT INTO events(title, starttime, abstract) VALUES (''BREW'',  ''11-21-2002 18:30:00'', ''Qualcomm ...'') '.
   
session := connection getSession.
[session
   prepare: acm;
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
   connection disconnect.
   
Connection is obtained as previous slide

The values are in two single quotes


Doc 24, VisualWorks Database Connection Slide # 10
The Count of Rows Affected

PostgreSQL does not implement the rowCount method

PostgreSQLAdditions package on course STORE adds the method

Returns the number of rows add/modifed

acm := 'INSERT INTO events(title, starttime, abstract) VALUES (''BREW'',  ''11-21-2002 18:30:00'', ''Qualcomm ...'') '.
   
session := connection getSession.
[session
   prepare: acm;
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
   connection disconnect.
^session rowCount


Doc 24, VisualWorks Database Connection Slide # 11

Variables in Queries


There are three ways to indicate variables


? as Variable

acm := 'INSERT INTO events(title, starttime, abstract)  VALUES (?, ?, ?)'.
   
[session := connection getSession.
session prepare: acm.
session
   bindInput: #('BREW' '11-21-2002 18:30:00'  'Qualcomm ...');
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
connection disconnect.


Doc 24, VisualWorks Database Connection Slide # 12
:index as Variable

:n represents the n’th location in the input

acm := 'INSERT INTO events(title, starttime, abstract)  VALUES (:1, :2, :3)'.
   
[session := connection getSession.
session prepare: acm.
session
   bindInput: #('BREW' '11-21-2002 18:30:00'  'Qualcomm ...');
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
connection disconnect.

acm := 'INSERT INTO events(title, starttime, abstract)  VALUES (:1, :3, :2)'.
   
[session := connection getSession.
session prepare: acm.
session
   bindInput: #('BREW'   'Qualcomm ...' '11-21-2002 18:30:00');
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
connection disconnect.


Doc 24, VisualWorks Database Connection Slide # 13
Objects and Variables in QueuesClass used in Examples

Smalltalk defineClass: #Event
   superclass: #{Core.Object}
   instanceVariableNames: 'title startTime abstract '
   category: 'DatabseExamples'
   
Event class methodsFor: 'instance creation'
   
title: aString time: aTimeStamp
   ^self new
      title: aString;
      time: aTimeStamp 
   
Event methodsFor: 'accessing'
   
abstract
   ^abstract
   
abstract: aString
   abstract := aString
   
time
   ^startTime
   
time: aString
   startTime := aString
   
title
   ^title
   
title: aString
   title := aString


Doc 24, VisualWorks Database Connection Slide # 14
Using an Object for input

:n indicates which instance variable in the object to select

acm := 'INSERT INTO events(title, starttime, abstract)  VALUES (:1, :2, :3)'.
data := Event title: 'brew' time: '11-21-2002 17:30:00'.
data abstract: 'test'.
   
[session := connection getSession.
session prepare: acm.
session
   bindInput: data;
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
connection disconnect.


Doc 24, VisualWorks Database Connection Slide # 15
Name Input Binding

Positional binding can be error prone

Can indicate a method to get values for variables

Class just needs getter methods

acm := 'INSERT INTO events(title, time, abstract)  
         VALUES (:title, :time, :abstract)'.
data := Event title: 'brew' time: '11-21-2002 17:30:00'.
data abstract: 'test'.
   
[session := connection getSession.
session prepare: acm.
session
   bindInput: data;
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
connection disconnect.


Doc 24, VisualWorks Database Connection Slide # 16

Select Queries


recent := 'SELECT * FROM events WHERE starttime > ''11-21-2002 12:30:00'''.
[session := connection getSession.
session
   prepare: recent;
   execute] 
      on: connection class externalDatabaseErrorSignal
      do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
answer :=session answer.
allRows := answer upToEnd.
connection disconnect.
^allRows


Doc 24, VisualWorks Database Connection Slide # 17
session answer

Returns

If query is INSERT, UPDATE, CREATE etc that does not return result set
If query is SELECT
Some databases permit returning multiple result sets
If query returns multiple result sets
keep sending answer until you get this returned


Doc 24, VisualWorks Database Connection Slide # 18
ExternalDatabaseAnswerStream

Some useful methods

next
Returns the next row
By default a row is an array of columns
upToEnd
Returns an array of all the rows from current to end

atEnd
Returns true if we are at the end of the result set

   [session := connection getSession.
   session
      prepare: recent;
      execute] 
         on: connection class externalDatabaseErrorSignal
         do: [:exception | put handler here].
   answer :=session answer.
   rowArray := answer next.


Doc 24, VisualWorks Database Connection Slide # 19
Getting Objects Back

   recent := 'SELECT * FROM events WHERE starttime > ''11-21-2002 12:30:00'''.
   [session := connection getSession.
   session
      prepare: recent;
      bindOutput: Event new;
      execute] 
         on: connection class externalDatabaseErrorSignal
         do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
   answer :=session answer.
   anEventObject := answer next.
   connection disconnect.
   ^anEventObject
Columns are mapped to instance variables

First column is mapped to first instance variable in definition

If instance variable containing an instance of Object are skipped


Doc 24, VisualWorks Database Connection Slide # 20
Using Column Names as Setters

   [session := connection getSession.
   session
      prepare: recent;
      bindOutputNamed: Event new;
      execute] 
         on: connection class externalDatabaseErrorSignal
         do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
   answer :=session answer.
   anEventObject := answer next.
   connection disconnect.
   ^anEventObject

When using bindOutputNamed: Event new;

The columns names of the table are used to form names of setter methods to set values in the object


Doc 24, VisualWorks Database Connection Slide # 21

Adding SQL to Objects


Object should know how to

Modified Events Table

CREATE TABLE events(title text, starttime timestamp, abstract text, id serial)

Doc 24, VisualWorks Database Connection Slide # 22
New Events Class

Smalltalk defineClass: #Event
   superclass: #{Core.Object}
   indexedType: #none
   private: false
   instanceVariableNames: 'title startTime abstract id '
   classInstanceVariableNames: ''
   imports: ''
   category: 'DatabseExamples'

Event class methodsFor: 'instance creation'

title: aString time: aTimeStamp
   ^self new
      title: aString;
      startTime: aTimeStamp 

Event class methodsFor: 'database connection'

connection
   ^DatabaseProxy connection “DatabaseProxy not shown”


Doc 24, VisualWorks Database Connection Slide # 23
Event class methodsFor: 'sql'

save: anEvent
   | connection saveSQL session |
   connection := self connection.
   saveSQL := 'INSERT INTO events(title, starttime, abstract)  VALUES (?, ?, ?)'.
   
   [session := connection getSession.
   session prepare: saveSQL.
   session
      bindInput: anEvent;
      execute] 
         on: connection class externalDatabaseErrorSignal
         do: [:exception | Dialog warn: exception parameter first dbmsErrorString].
   connection disconnect.
   ^session rowCount 


Doc 24, VisualWorks Database Connection Slide # 24
Event methodsFor: 'accessing'

abstract
   ^abstract

abstract: aString
   abstract := aString

save
   self class save: self

startTime: aTimestamp
   startTime := aTimestamp

time
   ^startTime

title
   ^title

title: aString
   title := aString 


Copyright ©, All rights reserved.
2002 SDSU & Roger Whitney, 5500 Campanile Drive, San Diego, CA 92182-7700 USA.
OpenContent license defines the copyright on this document.

Previous    visitors since 26-Nov-02    Next