SDI-Squeak database interface

Stefan Matthias Aust sma at 3plus4.de
Sun May 21 14:32:49 UTC 2000


David, thank you for your comments.

At 09:15 21.05.00 -0400, Pennell, David wrote:

>Aren't these URL's in JDBC?  Should Connection be DBConnection
>or SQLConnection?

Sort of, yes.  They start with "jdbc:" and then continue as described, for 
example "jdbc:mysql:bonbon:3306/test".

If I'd to choose a prefix, I'd go for "DB", not SQL.

> > arrays with ints and strings are returned.  You can use
>
>Perhaps an extensible factory framework to support other types,
>like dates, floats, your own types in blobs, etc.

MySQL seems to always return strings, so one has to convert this.  Lacking 
a TimeStamp class and ignoring BLOBs, I currently just used ints and 
string.  I forgot to mention that this can of course be 
extended.  Currently, I use MySqlField objects which represent columns in 
the answered rows to convert a MySQL data type into a similar Smalltalk 
instance.

>How about DBStream?

I like that name.

>I would rather see an SQLStatement base object.  Then SQLConnection
>could have one method to execute the statement (#do: anSQLStatement
>or #execute: anSQLStatement).  SQLStatement might store the string
>as a simple string and subclasses could use more elaborate structures
>for separating platform independent abstractions and/or bind variables.

I'm stil unsure.  As it seems that MySQL doesn't have the concept of 
prepared statements, I might go without real support for now, but as I 
really would like to see other databases supported with the same API, one 
has probably to prototype this with Oracle or another database.

>This keeps you from having to parse every command passed to the
>connection.

Please keep in mind that I just described the public API.  Of course I'd 
delegate the real work to some special object.  But that might be 
private.  My MySQL support has also more classes than described.

>   If you want to provide support for a string substitution
>style of statement, you can always add a conversion method to SQLStatement.

No, it should work like this...

con prepareCommand: 'INSERT INTO personen (name) VALUES (:name)'.
persons do: [:each | con executePreparedCommand: each].

where each elements of the persons collection has a getter method "name" 
which is then used to access the value which is bound to the statement 
before that statement is executed.

In the case of MySQL, this would eventually be a string replacement, but 
for other databases, the normal framework for binding values should be 
used.  So the ":name" must be replaced with "?" and a mapping from name to 
bound variable no 1 need to be defined.  The tricky part is to determine 
the variables types.

Java requires the user to specify it when binding the variable.  I however 
would like to see an automatism which guesses it from the first object to 
be bound and then checks whether the guess was correct, rebinding the 
variable with another type if needed.

Actually, with VisualWorks and Oracle, I noticed that it is even faster to 
internally replace short strings and numbers and then use normal statements 
instead of using Oracle's prepared statement with bind vars.   VisualWorks 
uses OCI btw.

>I have been thinking that it would also be useful to provide a separate
>SQL layer from a DB interface (provider?) layer.

Why?  I consider SQL as the lowest level.  Can there anything be even more 
basic?

On SQL, I'd then add an object->table mapper, but that's a separate project.

>time to ponder this though.  I started tinkering with using Oracle Call
>Interface via FFI a couple of weeks ago.  I got far enough to figure out
>that that route will be painful - especially since I've never coded OCI
>before.

I used OCI once and didn't find it too difficult.  But you have to tinker 
around with all kinds of handles, cursors and have to carefully bookkeep 
all resouces.


bye





More information about the Squeak-dev mailing list