Thread: Viable alternatives to SQL?

Many sites let you search databases of information, but the search
queries are very limited.

I'm creating a site that'll allow arbitrary SQL queries to my data (I
realize I'll need to handle injection attacks).

Are there other viable ways to query data? I read a little on
"Business System 12" (BS12), Tutorial D, and even something called
T-SQL (I think), but they all seem theoretical and not fully
implemented.

I want a query language that non-techies can use easily, but also
supports arbitrarily complex queries. Does such a language exist?

--
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.

Hibernate is a ORM technology which configures bean classes which will construct DB statements based on the method(create,replace,update,delete) from the bean class attributes(columns)

to quote
"Hibernate allows you to express queries in its own portable SQL extension (HQL), as well as in native SQL, or with an object-oriented Criteria and Example API."

Hibernate currently supports the following databases

• Microsoft SQL Server 2005/2000
• Oracle
• Microsoft Access
• Firebird
• PostgreSQL
• DB2 UDB
• MySQL
• SQLite

https://www.hibernate.org/

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Thu, 27 Aug 2009 06:43:51 -0700
> Subject: [GENERAL] Viable alternatives to SQL?
> From: kelly.terry.jones@gmail.com
> To: pgsql-general@postgresql.org
>
> Many sites let you search databases of information, but the search
> queries are very limited.
>
> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).
>
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
>
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?
>
> --
> We're just a Bunch Of Regular Guys, a collective group that's trying
> to understand and assimilate technology. We feel that resistance to
> new ideas and technology is unwise and ultimately futile.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Windows Live: Keep your friends up to date with what you do online. Find out more.

On Thu, Aug 27, 2009 at 8:43 AM, Kelly Jones<kelly.terry.jones@gmail.com> wrote:
> Many sites let you search databases of information, but the search
> queries are very limited.
>
> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).
>
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
>
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?
>

Yeah, it's called SQL....  Seriously, you're two requirements are
rather contradictory.   We have many end users who use SQL, learning
just enough to get the job done when we have this kind of problem.

Having said that, is there a reason why you are not looking at end
user query interfaces such as Hyperion or Cognos?  You might also look
at natural language or controlled vocabulary interfaces; if the
queries are domain specific (eg. medical) then you can often pick off
some low hanging fruit fairly quickly.  If the problem domain is non
specific then I don't think you'll be able to meet these requirements
without throwing a lot of money at the problem (The Cyc guys come to
mind ;-)...

--
Peter Hunsberger

On Thu, Aug 27, 2009 at 06:43:51AM -0700, Kelly Jones wrote:
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
>
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?

I've never heard of the first two, as far as I can recall. T-SQL is quite
common, however (http://en.wikipedia.org/wiki/Transact-SQL), but although I've
never used it, I seriously doubt it will make things any more user friendly.

Several business intelligence applications try to allow users to generate
their own queries by providing a query builder system of some sort. These are
far from simple drop-in systems, in general. For instance, I'm using Pentaho
(http://www.pentaho.com) in a project, to allow users to write their own
reports. I've had to create a sort of schema "metadata" thing, which tells
Pentaho what my database looks like, how various tables relate to each other,
etc., and creates a set of objects my users will immediately understand, along
with some method to turn those objects into database queries. Pentaho allows
the users to drag and drop those objects into a report, creating filters and
sorting constraints, different groupings, etc., in a fairly user-friendly way.
When they run the report, Pentaho translates their input into SQL.

Anyway, you might try using a reporting package rather than trying to write
your own query interface and provide for user-friendly error reporting, decent
security constraints, etc.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Joshua Tolley wrote:
> I've never heard of the first two, as far as I can recall. T-SQL is quite
> common, however (http://en.wikipedia.org/wiki/Transact-SQL), but although I've
> never used it, I seriously doubt it will make things any more user friendly.
>

TransactSQL, sometimes called TSQL, is MS SQL Server's equivalent of
PL/PGSQL.   I doubt this is what the OP was referring to.

On Thu, Aug 27, 2009 at 9:43 AM, Kelly Jones<kelly.terry.jones@gmail.com> wrote:
> Many sites let you search databases of information, but the search
> queries are very limited.
>
> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).
>
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
>
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?

SQL is your best bet.  It is probably the most natural language for
non-programmers to learn and understand.  It scales in difficulty with
the problem you are trying to solve and is _much_ easier for non
technical user than something like an ORM.  That said, if your site
allows arbitrary queries, sql injection is not your only problem.  It
is trivially easy to write a query that can DOS your server (with SQL
access I could take any PostgreSQL server down in about 1
second)....so you need to think about how exactly who your are going
to allow access to your database and how they can access it.

merlin