Public JDBC vs Web Services

[mrosgood]

Has anyone seen a publicly accessible JDBC?

Why are we fronting databases with "web services"? Why not just open a port to the database?

What does "web services" buy you, that you cannot do with a database (e.g. H2, Oracle, MSSQL)? Load balancing, hell banning, rate throttling? More authentication options (eg OpenID, OAuth)?

Couldn't those features be added with a JDBC proxy?

---

I see stuff like

City of Seattle, open govt stuff
https://data.seattle.gov
http://dev.socrata.com/console/

Stack Exchange Data Explorer
http://data.stackexchange.com/stackoverflow/queries

SQLShare, publish your datasets to the cloud
https://sqlshare.escience.washington.edu

Generic SQL client
http://sqlzoo.net


And I think "Neat, they ported SquirrelSQL to the web."

But I want to use my own SQL client. Not learn some weird new mutant UI.

And I want to work with the database directly, using SQL and resultset. Not learn some mutant format like JSON or XML or worse .

Imagine a mail service providing a webmail client, but not SMTP & IMAP.


Cheers, Jason





------------------------------------

[mrosgood]

Has anyone seen a publicly accessible JDBC?

Why are we fronting databases with "web services"? Why not just open a port to the database?

What does "web services" buy you, that you cannot do with a database (e.g. H2, Oracle, MSSQL)? Load balancing, hell banning, rate throttling? More authentication options (eg OpenID, OAuth)?

Couldn't those features be added with a JDBC proxy?

---

I see stuff like

City of Seattle, open govt stuff
https://data.seattle.gov
http://dev.socrata.com/console/

Stack Exchange Data Explorer
http://data.stackexchange.com/stackoverflow/queries

SQLShare, publish your datasets to the cloud
https://sqlshare.escience.washington.edu

Generic SQL client
http://sqlzoo.net


And I think "Neat, they ported SquirrelSQL to the web."

But I want to use my own SQL client. Not learn some weird new mutant UI.

And I want to work with the database directly, using SQL and resultset. Not learn some mutant format like JSON or XML or worse .

Imagine a mail service providing a webmail client, but not SMTP & IMAP.


Cheers, Jason





------------------------------------

[mrosgood]

Has anyone seen a publicly accessible JDBC?

Why are we fronting databases with "web services"? Why not just open a port to the database?

What does "web services" buy you, that you cannot do with a database (e.g. H2, Oracle, MSSQL)? Load balancing, hell banning, rate throttling? More authentication options (eg OpenID, OAuth)?

Couldn't those features be added with a JDBC proxy?

---

I see stuff like

City of Seattle, open govt stuff
https://data.seattle.gov
http://dev.socrata.com/console/

Stack Exchange Data Explorer
http://data.stackexchange.com/stackoverflow/queries

SQLShare, publish your datasets to the cloud
https://sqlshare.escience.washington.edu

Generic SQL client
http://sqlzoo.net


And I think "Neat, they ported SquirrelSQL to the web."

But I want to use my own SQL client. Not learn some weird new mutant UI.

And I want to work with the database directly, using SQL and resultset. Not learn some mutant format like JSON or XML or worse .

Imagine a mail service providing a webmail client, but not SMTP & IMAP.


Cheers, Jason





------------------------------------

[Konstantin Ignatyev]

I see WS value in:
- making data easily consumable by browser (json, xml)
- preven SQL injection attacs (if written with appropriate framework, and
not straight PHP)
- authorization - when only subset of records need to be visible to a
person
- data normalization (some of databases are Frankensteins)

Post by mrosgood
**
Has anyone seen a publicly accessible JDBC?
Why are we fronting databases with "web services"? Why not just open a
port to the database?
What does "web services" buy you, that you cannot do with a database (e.g.
H2, Oracle, MSSQL)? Load balancing, hell banning, rate throttling? More
authentication options (eg OpenID, OAuth)?
Couldn't those features be added with a JDBC proxy?
---
I see stuff like
City of Seattle, open govt stuff
https://data.seattle.gov
http://dev.socrata.com/console/
Stack Exchange Data Explorer
http://data.stackexchange.com/stackoverflow/queries
SQLShare, publish your datasets to the cloud
https://sqlshare.escience.washington.edu
Generic SQL client
http://sqlzoo.net
And I think "Neat, they ported SquirrelSQL to the web."
But I want to use my own SQL client. Not learn some weird new mutant UI.
And I want to work with the database directly, using SQL and resultset.
Not learn some mutant format like JSON or XML or worse .
Imagine a mail service providing a webmail client, but not SMTP & IMAP.
Cheers, Jason

--
Konstantin Ignatyev

PS: If this is a typical day on planet Earth, humans will add fifteen
million tons of carbon to the atmosphere, destroy 115 square miles of
tropical rainforest, create seventy-two miles of desert, eliminate between
forty to one hundred species, erode seventy-one million tons of topsoil,
add 2,700 tons of CFCs to the stratosphere, and increase their population
by 263,000

Bowers, C.A. The Culture of Denial: Why the Environmental Movement Needs a
Strategy for Reforming Universities and Public Schools. New York: State
University of New York Press, 1997: (4) (5) (p.206)

[Eric Jain]

Post by mrosgood
Why are we fronting databases with "web services"? Why not just open a port to the database?

I'm sure that would often be the simplest solution, but:

- it's easy to get a server tied up with slow-running queries
- don't want to keep connections open longer that necessary
- not everyone does Java
- firewall issues
- managing and updating database-specific drivers could be troublesome
if you're dealing with several databases

OData is supposed to be the JDBC standard for the Web. Has anyone used it?

[Jason Osgood]

Hi Eric, Konstantin.


Sorry for the triplicate post. Used the web form. Don't know how it happened.


Also, I didn't search deeply enough. Tried "sql proxy" and found SQL Relay. It has many of the features discussed.

http://sqlrelay.sourceforge.net/documentation.html

Post by Konstantin Ignatyev
data normalization (some of databases are Frankensteins)

True. But what database is normalized? PetStore? Northwinds?

I have yet to work with a normalized schema.

That said, transforming WS mediated data back into something useful is typically painful. Just give me the data.

Querying the raw data like Socrata and SQLShare permit is very appealing.

In fact… I've used SQL to work around poorly designed "frameworks" (APIs, web services, or whatever the genius "architect" called it) often enough that it may very well be normal.

Post by Konstantin Ignatyev
prevent SQL injection attacks (if written with appropriate framework, and not straight PHP)

Yes. I'm mostly envisioning read-only. Like something to be consumed by one of those "mashups" the kids are doing these days.

Post by Konstantin Ignatyev
it's easy to get a server tied up with slow-running queries

True. MSSQL and Oracle have the means to kill long running queries. It'd have be to instrumented.

Post by Konstantin Ignatyev
don't want to keep connections open longer that necessary

Do mean client to proxy or proxy to database?

With a select based (eg NIO) proxy server, that can serve 10,000s of connections, I don't see the problem. Just kick the idle clients.

Post by Konstantin Ignatyev
OData is supposed to be the JDBC standard for the Web. Has anyone used it?

Just heard about it. Scanned their docs. I don't see the point. So I'm sure there's throngs of "enterprise architects" who will eagerly embrace it.


Cheers, Jason

[Stan Dyck]

Post by Eric Jain

Post by mrosgood
Why are we fronting databases with "web services"? Why not just open a port to the database?

- it's easy to get a server tied up with slow-running queries
- don't want to keep connections open longer that necessary
- not everyone does Java
- firewall issues
- managing and updating database-specific drivers could be troublesome
if you're dealing with several databases
OData is supposed to be the JDBC standard for the Web. Has anyone used it?
--
Eric Jain
zenobase.com -- What do you want to track today?

There's also the not-insignificant issue of vendor lock-in.

StanD.



------------------------------------

[Jason Osgood]

Hi Stan.

Post by Stan Dyck
There's also the not-insignificant issue of vendor lock-in.

You mean other than JDBC itself?


The high availability JDBC proxy project looks interesting, for trusted users.

http://ha-jdbc.github.io

Add throttling, authentication, (some of the battle tested features swiped from SQL Relay), and ability for proxy to kill long-lived queries...

Why wouldn't someone expose an HA-JDBC instance to the wild?


Cheers, Jason

[Stan Dyck]

Post by Jason Osgood

Post by Stan Dyck
There's also the not-insignificant issue of vendor lock-in.

You mean other than JDBC itself?

I'm not sure I follow. Are you saying that vendor lock-in is not an issue when using JDBC? Because that is not my
experience in any but the most simple projects. You always end up assuming that you are talking to a particular brand of
database. I suppose that if the database became a commodity item you might see a migration towards a truly common
interface but there are a few very large, very wealthy companies who are highly invested in preventing that from
happening, including the one that owns JDBC! (Think about that for a moment, with tin-foil hat in place of course.
America's Cup! Yipee!).

Getting back to your original question about what a web service (I'm limiting this to the RESTful kind) provides that a
database doesn't. One thing which I think is significant is the whole hypermedia thing (see
http://www.ics.uci.edu/~fielding/pubs/dissertation/rest_arch_style.htm#sec_5_2_2 ). Most of the things that get called
web services these days don't take advantage of it but it's pretty nice to be able to link arbitrary things together
arbitrarily.

I also think the whole issue of caching is one where web services has a well thought out story. That linked section from
Fielding's dissertation talks a bit about that as well. High availability isn't the only important issue. Reducing
network latency via distributed caching is something that is easy for a client to implement with a RESTful web service
and can be a huge win for end-user perceived performance.

StanD.


------------------------------------