links to this page:    
View this PageEdit this PageUploads to this PageHistory of this PageTop of the SwikiRecent ChangesSearch the SwikiHelp Guide
ODBC for Squeak
Last updated at 5:14 pm UTC on 22 February 2010
UNDER DEVELOPMENT! - last version: 15/Dec/2005

Author : Diego Gomez Deck

Very simple framework for ODBC support in Squeak. Use FFI to talk to ODBC32.dll, no other DLL is necessary.

It works in Windows and is reported that works in Unix (see feedback).


How to test

  | con |
  con := ODBCConnection dsn:'TestDB' user:'' password:''.
  (con query: 'select field1, field2 from Client') results do:[:row | Transcript show: row; cr].
  (con query: 'select field1, field2 from Client2') results upToEnd.

  con close.

How to test with Excel


con := ODBCConnection dsn:'DSNExcel' user:'' password:''.
(con query: 'select * from [Sheet1$]') results upToEnd.  
con close.

[Sheet1$] refers to Sheet1 of the Excel book.


Reported by: From: Eric Hochmeister
Date: 2008/7/17
Subject: [squeak-dev] ODBC, JOIN table with same column names.
To: squeak-dev@lists.squeakfoundation.org


I'm using the ODBC driver for squeak and I was running into an issue
with JOIN tables where the 2 tables I'm joining have identical column
ie. I have a table X and table Y.
both tables have columns ID, FIRST_NAME

So when I do the query,


I get back rows which only have ID and FIRST_NAME (2 columns), rather than 3.

I'm just curious if this is a known bug, or if I'm missing something,
ie. not doing this correctly. Has anyone else run in to this issue,
or do you solve it differently?




Related projects



Uploaded Image: odbc-screenshot.jpeg

Here I have some hints for Windows users (and those who are not so familiar with Squeak).

If you have ACCESS, you probably have the FPNWIND database too.
To use it for a test do the following.

a) get the ODBC change set archive and unpack it

b) start your squeak, open the file-list (under tools)

c) locate the change-set and file it in

d) open settings/control panel/administrative tools/data sources (ODBC)

e) create a new ODBC source to the sample database fpnwind.mdb named TestDBUploaded Image: odbc-source.gif

The test then executes as follows:

   | con |
  Transcript open. "may be omitted if Transcript window is already open"
  con := ODBCConnection dsn:'TestDB' user:'' password:''.
  (con query: 'select CustomerID,CompanyName from Customers') results do:[:row | 
      Transcript show: row; cr
  con close.

This results in the following output in the Transcript window:

an ODBCRow(#CompanyName->'Alfreds Futterkiste' #CustomerID->'ALFKI' )
an ODBCRow(#CompanyName->'Ana Trujillo Emparedados y helados' #CustomerID->'ANATR' )
an ODBCRow(#CompanyName->'Antonio Moreno Taquería' #CustomerID->'ANTON' )
an ODBCRow(#CompanyName->'Around the Horn' #CustomerID->'AROUT' )
an ODBCRow(#CompanyName->'Berglunds snabbköp' #CustomerID->'BERGS' )

BTW Diego: Everything went fine from the beginning. Thank you !

Gerald Zincke

Does anybody have a simple sample for updating and inserting with this ODBC interface?
Thank you
ODBC for Squeak

Sure. Try:

con := ODBCConnection dsn:'TestDB' user:'' password:''.
(con query: 'insert into Customers (CustomerID,CompanyName) values (2003,'Some Great Company Name')') execute.
con close.

If you want to update a row, change the statement to be an update statements (similarly, change it to be delete for deleting rows).

Thank you! I've tried it out but have yet one obstacle:
How should I get data out of the ODBCRow? It may sound stupid but I really can't find out the solution.


You accomplish this by using #at: (just like a Dictionary). So, anODBCRow at: #CompanyName would return the company name value for a specific row. Note that the keys need to be symbols. This means that if you are accessing a standard SQL database with _'s in the column names, you need to make the string into symbols and use that. For instance: anODBCRow at: 'COMPANY_NAME' asSymbol.

For Squeak Newbies (like me)
1. Create the connection
2. Create an ordered list to hold the results (easier to work with)
3. Put the results in the ordered list
4. Read the first row, column named 'CommonName' into item.
5. Close connections.

con := ODBCConnection dsn:'Plants' user: password:.
row := ODBCRow.
list := OrderedCollection new.
(con query: 'select CommonName from Plants') results
do: [:row | list add: row];
list collect: [:each | each at: #CommonName].
row := list first.
item := row at: #CommonName.
con close.