Date: 2008/7/17
Subject: [squeak-dev] ODBC, JOIN table with same column names.
To: squeak-dev@lists.squeakfoundation.org
Hi,
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
names.
ie. I have a table X and table Y.
both tables have columns ID, FIRST_NAME
So when I do the query,
SELECT c1.ID, c1.FIRST_NAME, d1.FIRST_NAME FROM X c1, Y d1 WHERE c1.ID = d1.ID
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?
Thanks,
Eric
-
Empty list, just waiting for new bugs
TODO
- Fix bugs
- Add #atIndex: to ODBCRow
- Prepared Statements
- Get all the types as binary instead of strings
- TinyInt, BigInt, Numeric, Decimal
- Better support for Numeric / Decimal
- Better support for Timestamp
- Timestamp class in Squeak?
Related projects
- Tantalus: Tantalus is an Object-Relational mapping framework for storing objects in relational databases. It's written in the Squeak dialect of Smalltalk.
Feedback
- I tried a quick test with iodbc in linux. Result: a seg fault in ODBCConnection>>basicOpen.
- Can you try again with the last version? (dgd)
- Yes, and it works! Using unixODBC 2.06 and the postgres odbc driver. Very, very cool. (avi)
- I have tested it against DB2 7.2 and it works very well! I tried only read, update, delete, and insert. Let me know what else I should test. (ldt)
- Each odbc type use a different conversion method in Squeak, please try all posible types. The other notSoTested code is the transaction support (dgd)
- I'm on linux and unixODBC. Seems that it cannot find `libodbc32.so', which is an incorrect name. So I have to make a symlink named `odbc32.so' in the plugin directory to the actual libodbc.so, and it works now. (rc)
- I'm running on Win32, and noticed that running lots and lots of queries through this was chewing up my memory like make (1.2 GB of memory!). Finally, after checking through the code a little, I decided that what you need to do is make sure you close each result set after you are finished using them. If you don't, you risk using up all your memory. (cbc)
Screenshots
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 TestDB
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 !
regards
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).
cbc
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.
jim
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.
cbc
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];
close.
list collect: [:each | each at: #CommonName].
row := list first.
item := row at: #CommonName.
con close.
