Trying to figure out SqlService

Calling SqlService.open returns an SqlService, does each open() give you a new separate DB connection ?

If not how do i get a second separate connection ?

Thanks.

The open method just returns this. What actually happens is that under the covers a JDBC connection is used per thread.

So since I do specifically want a second connection, is the trick to create a separate thread, which i guess means using an Actor ?

yeap - use two different actors

Do you actually want multiple connections open at the same time in the same thread?

I might be able to do without it. Not sure yet. Does fantom sql provide a way to get an auto-increrment type column in the db? Trying to make my own which is why I need atomic/transaction code.

Does fantom sql provide a way to get an auto-increrment type column in the db?

That is really based on the database, not Fantom I think. The sql test suite (which uses MySql) uses an auto_increment field.

I think only MS databases uses such things. On Oracle you'd typically use a sequencer and a trigger. Thought of using UUID's instead as PK? They are a little more expensive to index, but simpler to insert (can be done in one atomic INSERT).

Well most DB do, but they all call it something different "auto increment" "autoincrement" "sequence" and so on.

It seem JDBC 3.0 might allow this using http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#getGeneratedKeys() but I've never tried it yet (just found out about it)

What I had done in my java implementation years ago, is that I have my own nextVal(name) method, which basically increases a counter in the database(custom counters table) and increment it right after reading it.

Of course the iffy part is to make sure this is reliable ... I was doing that in a synchronized method and also doing all this is one transaction that I would rollback in case of issues ... even then I'm not sure it's 100% safe for example if there are multiple clients using the same DB.

Anyway, I've reimplemented that in fantom here (still working on it): http://bitbucket.org/tcolar/fantomutils/src/d5af9f7092b4/netColarDb/fan/DBUtil.fan except I still have to figure out to make it atomic (assuming it actually helps) , probably with an actor which i need to learn more about first :)

That solution might not be perfect, although I've used it for years without issues, the huge plus is that it's completely SQL standard and works with all DB's I tried.

I really really don't want to write DB specific code ... and one of the main reason I like having a simple ORM / query tool is because SQL is just such a pain to work with (implementations all over the place)

@tcolar

If the database auto generates keys you can get them from the sql::Statement.execute result.

Regarding database incompatibilities, that is what the sql::Dialect if for. We only have a sql::MySqlDialect right now, but if you want to help flush out the API and dialects for different databases that would be great. For example to get the qualifier for auto_increment for the database currently being used, then use the sql::Dialect.auto method.