interface for "non-SQL people"

EMS SQL Manager has a visual query builder, but it's a commerical
product, ie it aint free.

pere roca escribi�:

hi,
some nice tool over there to let non-SQL knowing people to construct their
queries? I'm using pgAdmin III but I know some SQL.
there is no other option than constructing an HTML with forms, drop-down
menus...?

IIRC pgAdmin has got a graphical query builder.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Hi,

There is a http://www.activedbsoft.com/overview-querytool.html FlySpeed SQL
Query tool, it's free if you don't need data export and printing. It's
query builder is very good, but the author honestly says that basic
knowledge of SQL concepts is required to to build a query...

pere roca wrote:

some nice tool over there to let non-SQL knowing people to construct
their queries? I'm using pgAdmin III but I know some SQL.
there is no other option than constructing an HTML with forms, drop-down
menus...?

--
View this message in context: http://www.nabble.com/interface-for-%22non-SQL-people%22-tp25775414p25801845.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, Oct 6, 2009 at 3:47 PM, pere roca <peroc79@gmail.com> wrote:

 hi,
 some nice tool over there to let non-SQL knowing people to construct their
queries? I'm using pgAdmin III but I know some SQL.
 there is no other option than constructing an HTML with forms, drop-down
menus...?

Cant help it :-) but:

The #1 tool you have at your disposal is the human brain. I
personally think GUI database tools are counter productive and huge
time wasters. SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database. SQL is a
'man machine interface' :-). It's a very high level language with a
lot of power. The gui 'wrappers' that I've seen actually obfuscate
the concepts.

merlin

On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

The #1 tool you have at your disposal is the human brain.  I
personally think GUI database tools are counter productive and huge
time wasters.  SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database.   SQL is a
'man machine interface' :-).  It's a very high level language with a
lot of power.  The gui 'wrappers' that I've seen actually obfuscate
the concepts.

Amen to that. I'd rather spend a little bit of my time each week
going over correlated subqueries with a user than trying to get good
performance on a reporting server that's hammered by bad queries.
Which is what a lot of query builders basically do.

On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:

On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

The #1 tool you have at your disposal is the human brain. I
personally think GUI database tools are counter productive and huge
time wasters. SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database. SQL is a
'man machine interface' :-). It's a very high level language with a
lot of power. The gui 'wrappers' that I've seen actually obfuscate
the concepts.

Amen to that. I'd rather spend a little bit of my time each week
going over correlated subqueries with a user than trying to get good
performance on a reporting server that's hammered by bad queries.
Which is what a lot of query builders basically do.

Good lord people. Not be helpful much? This guy is obviously not an SQL
guy and nor should he have to be. My guess is he is an analyst sitting
in a company somewhere that just wants to whip together some simple data
entry reports or contact forms.

There is *zero* reason he should have to dig around in the confounded
tar pit of SQL. Not everyone can be an SQL guru.

To answer the question.

Open Office has a forms interface that works with PostgreSQL
You can use MS Access
You have use Kexi (KDE)

There is also one written in GTK and Python but I don't recall the
name...

JD... Who sits in bewilderment

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake <jd@commandprompt.com> wrote:

On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:

On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

The #1 tool you have at your disposal is the human brain.  I
personally think GUI database tools are counter productive and huge
time wasters.  SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database.   SQL is a
'man machine interface' :-).  It's a very high level language with a
lot of power.  The gui 'wrappers' that I've seen actually obfuscate
the concepts.

Amen to that.  I'd rather spend a little bit of my time each week
going over correlated subqueries with a user than trying to get good
performance on a reporting server that's hammered by bad queries.
Which is what a lot of query builders basically do.

Good lord people. Not be helpful much? This guy is obviously not an SQL
guy and nor should he have to be. My guess is he is an analyst sitting
in a company somewhere that just wants to whip together some simple data
entry reports or contact forms.

There is *zero* reason he should have to dig around in the confounded
tar pit of SQL. Not everyone can be an SQL guru.

To answer the question.

Open Office has a forms interface that works with PostgreSQL
You can use MS Access
You have use Kexi (KDE)

There is also one written in GTK and Python but I don't recall the
name...

JD... Who sits in bewilderment

My 2 cents:

If the poster works in MS Windows, MS Access is the easiest, most
powerful and most flexible solution .... for a client application. I
use it for complex ad hoc projects as well as creating applications.
To keep maintenance simple, should I get hit by the proverbial bus, I
do not use any manually created code (SQL or VB) in my projects. I
use complex combinations of functions, but even those are typed within
the QBE (query by example) window.

Much of MS Access's comparative value comes from the fact that it
treats local tables, select queries and links to external data sources
as if they were all local tables. This means that you can nest
queries easily or join local tables to external tables. You can even
join tables from different database servers. Documentation for MS
Access is plentiful.

Keep in mind that MS Access does not share well and that the files
become corrupt/unusable when they reach 2GB (less admin resources for
tables, queries, etc) in size.

The down-side of GUI clients and ODBC connections is that you will not
be able to use features that are specific to the database server.

Most GUI database front-ends available for unix operating systems
focus on database administration as opposed to serious data analysis.
I tried Kexi and OpenOffice Base briefly, but became frustrated trying
to work with both local and remote data within the same project.

Gnuplot and R provide graphical views of data; but you still have to
write code (not SQL) manually. You also have to export data from the
database server in order to feed the data to these applications.

My rule of thumb for analysis is this: If I'm working in Windows PC,
I use MS Access. If I'm working in *BSD or Linux, I store SQL code in
text files and am careful to use a naming scheme that facilitates
management of those files.

I have tried using Windows and MS Access within virtual machines. It
works for small and simple queries. If you're doing large jobs or
complex analysis, however, you may have issues with memory management.

Here's some PostgreSQL-specific information:

If you are using MS Access as a client to a PostgreSQL server via
ODBC, you should keep the following in mind:
1. In the ODBC data source configuration, use true = -1.
2. In the database server, null values should not be allowed
for boolean (true/false, yes/no) data fields.

Best of luck,

Andrew

On Fri, October 9, 2009 09:06, Andrew Gould wrote:

On Fri, Oct 9, 2009 at 10:15 AM, Joshua D. Drake <jd@commandprompt.com>
wrote:

On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:

On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure@gmail.com>
wrote:

The #1 tool you have at your disposal is the human brain. �I
personally think GUI database tools are counter productive and huge
time wasters. �SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database. � SQL is a
'man machine interface' :-). �It's a very high level language with a
lot of power. �The gui 'wrappers' that I've seen actually obfuscate
the concepts.

Amen to that. �I'd rather spend a little bit of my time each week
going over correlated subqueries with a user than trying to get good
performance on a reporting server that's hammered by bad queries.
Which is what a lot of query builders basically do.

Good lord people. Not be helpful much? This guy is obviously not an SQL
guy and nor should he have to be. My guess is he is an analyst sitting
in a company somewhere that just wants to whip together some simple data
entry reports or contact forms.

There is *zero* reason he should have to dig around in the confounded
tar pit of SQL. Not everyone can be an SQL guru.

To answer the question.

Open Office has a forms interface that works with PostgreSQL
You can use MS Access
You have use Kexi (KDE)

There is also one written in GTK and Python but I don't recall the
name...

JD... Who sits in bewilderment

My 2 cents:

If the poster works in MS Windows, MS Access is the easiest, most
powerful and most flexible solution .... for a client application. I
use it for complex ad hoc projects as well as creating applications.
To keep maintenance simple, should I get hit by the proverbial bus, I
do not use any manually created code (SQL or VB) in my projects. I
use complex combinations of functions, but even those are typed within
the QBE (query by example) window.

Much of MS Access's comparative value comes from the fact that it
treats local tables, select queries and links to external data sources
as if they were all local tables. This means that you can nest
queries easily or join local tables to external tables. You can even
join tables from different database servers. Documentation for MS
Access is plentiful.

Keep in mind that MS Access does not share well and that the files
become corrupt/unusable when they reach 2GB (less admin resources for
tables, queries, etc) in size.

The down-side of GUI clients and ODBC connections is that you will not
be able to use features that are specific to the database server.

Most GUI database front-ends available for unix operating systems
focus on database administration as opposed to serious data analysis.
I tried Kexi and OpenOffice Base briefly, but became frustrated trying
to work with both local and remote data within the same project.

Gnuplot and R provide graphical views of data; but you still have to
write code (not SQL) manually. You also have to export data from the
database server in order to feed the data to these applications.

My rule of thumb for analysis is this: If I'm working in Windows PC,
I use MS Access. If I'm working in *BSD or Linux, I store SQL code in
text files and am careful to use a naming scheme that facilitates
management of those files.

I have tried using Windows and MS Access within virtual machines. It
works for small and simple queries. If you're doing large jobs or
complex analysis, however, you may have issues with memory management.

Here's some PostgreSQL-specific information:

If you are using MS Access as a client to a PostgreSQL server via
ODBC, you should keep the following in mind:
1. In the ODBC data source configuration, use true = -1.
2. In the database server, null values should not be allowed
for boolean (true/false, yes/no) data fields.

Best of luck,

Andrew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I would also add that there are a LOT of performance issues with MS
Access. For one, in some cases filtering out records with a where clause
can be performed on the client side - meaning it grabs all records and
pulls them across the network and then filters out the results only
displyaing the results you want to see. I've seen this happen a lot at my
company, but it may be related to the type of queries being run, so you
may experience different results.

Tim
--
Timothy J. Bruce

Registered Linux User #325725

On Fri, 2009-10-09 at 09:52 -0700, Tim Bruce - Postgres wrote:

I would also add that there are a LOT of performance issues with MS
Access. For one, in some cases filtering out records with a where clause
can be performed on the client side - meaning it grabs all records and
pulls them across the network and then filters out the results only
displyaing the results you want to see. I've seen this happen a lot at my
company, but it may be related to the type of queries being run, so you
may experience different results.

You solve that with pass through queries.

Joshua D. Drake

Tim
--
Timothy J. Bruce

Registered Linux User #325725

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

On Fri, 9 Oct 2009, Joshua D. Drake wrote:

On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:

On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

The #1 tool you have at your disposal is the human brain. I
personally think GUI database tools are counter productive and huge
time wasters. SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database. SQL is a
'man machine interface' :-). It's a very high level language with a
lot of power. The gui 'wrappers' that I've seen actually obfuscate
the concepts.

Amen to that. I'd rather spend a little bit of my time each week
going over correlated subqueries with a user than trying to get good
performance on a reporting server that's hammered by bad queries.
Which is what a lot of query builders basically do.

Good lord people. Not be helpful much?
[...]
JD... Who sits in bewilderment

I'm fairly bewildered as well. I mean, why would someone who is emailing
with an address from a company that presumably should care about how it
looks on the mailing list bother to prefix his answer to a question with
what amounts to an attack on other people in the thread. It'd be a bit
odd, but understandable if the message was an attack only on the answers,
but is just baffling when it effectively includes attacks on the people.

On Fri, 2009-10-09 at 10:32 -0700, Stephan Szabo wrote:

Good lord people. Not be helpful much?
[...]
JD... Who sits in bewilderment

I'm fairly bewildered as well. I mean, why would someone who is emailing
with an address from a company that presumably should care about how it
looks on the mailing list bother to prefix his answer to a question with
what amounts to an attack on other people in the thread. It'd be a bit
odd, but understandable if the message was an attack only on the answers,
but is just baffling when it effectively includes attacks on the people.

Trying hard to understand your point. If you are stating that I should
care what I think about how Command Prompt is represented on the list...
I let our record speak for itself.

In regards to the people I replied to, which would be Scott and Merlin.
We have known each other for a long time. This will be hardly the first
time we have disagreed on approach.

If you think anything I wrote was an attack, I apologize. I admit my
general sensitivities are not as tender as some people would like. I
usually point out the obvious, provide simple solutions and not beat
around the bush (preferring of course to chop the offending weed down).
If you would like flowery prose, sorry. It isn't my talent.

Other than that... welcome to Open Source. We speak our minds, in plain
site. I hope you stay and help us with the best opens source database on
the planet.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

On Fri, Oct 9, 2009 at 12:18 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

On Fri, 2009-10-09 at 09:52 -0700, Tim Bruce - Postgres wrote:

I would also add that there are a LOT of performance issues with MS
Access.  For one, in some cases filtering out records with a where clause
can be performed on the client side - meaning it grabs all records and
pulls them across the network and then filters out the results only
displyaing the results you want to see.  I've seen this happen a lot at my
company, but it may be related to the type of queries being run, so you
may experience different results.

You solve that with pass through queries.

For the benefit of the original poster: Pass through queries in MS
Access will require typing SQL code by hand.

On Fri, Oct 9, 2009 at 1:32 PM, Stephan Szabo
<sszabo@megazone.bigpanda.com> wrote:

On Fri, 9 Oct 2009, Joshua D. Drake wrote:

On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote:

On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

The #1 tool you have at your disposal is the human brain.  I
personally think GUI database tools are counter productive and huge
time wasters.  SQL requires lateral thinking but once you have your
head around how joins work and the general syntax of queries you
should have no problem getting data out of your database.   SQL is a
'man machine interface' :-).  It's a very high level language with a
lot of power.  The gui 'wrappers' that I've seen actually obfuscate
the concepts.

Amen to that.  I'd rather spend a little bit of my time each week
going over correlated subqueries with a user than trying to get good
performance on a reporting server that's hammered by bad queries.
Which is what a lot of query builders basically do.

Good lord people. Not be helpful much?
[...]
JD... Who sits in bewilderment

I'm fairly bewildered as well. I mean, why would someone who is emailing
with an address from a company that presumably should care about how it
looks on the mailing list bother to prefix his answer to a question with
what amounts to an attack on other people in the thread. It'd be a bit
odd, but understandable if the message was an attack only on the answers,
but is just baffling when it effectively includes attacks on the people.

In fairness to jd, the OP asked: "what is the best form of 'x'", and I
responded: 'y'! While he is still (naturally) wrong, I don't think
his statement reflects at all badly to himself or his company. Quite
the opposite in fact, it was rather helpful.

It's only natural that because I've made it one of my life's goals to
annoyingly gripe about how use of GUI query design tools (and their
evil cousin, the ERD), has decayed the principles and general welfare
of database development, I should only expect to get poked a bit here
and there.

merlin

pere roca wrote:

hi,
some nice tool over there to let non-SQL knowing people to construct their
queries? I'm using pgAdmin III but I know some SQL.
there is no other option than constructing an HTML with forms, drop-down
menus...?

thanks,
pERE

Your best bet which is not free is Crystal Reports it can do all the
above you list. It can automate creation of HTML, email, export Excel,
ad hoc filters in drop downs, and text inputs, Graphs and all kinds of
other stuff.
http://www.sap.com/solutions/sapbusinessobjects/sme/reporting-dashboarding/index.epx

its designed to be easy to use for the NON have not a clue
SQL/programmer people.

There are other software packages out there. Note I have never used any
of these can not comment...
http://www.inetsoft.com/products/StyleReportEE/
http://www.pentaho.com/products/reporting/
http://www.inetsoftware.de/products/crystal-clear
http://www.actuate.com/products/
http://www.agata.org.br/
http://jasperforge.org/plugins/project/project_home.php?projectname=jasperreports

oracle handles html output with packages ..i would assume pg would have similar sgml output capability?
crystal has been limited to ODBC dsn (datasources) althogh sap seem to be open to a more Opensource approach using
JDBC based data sources

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

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
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: Sat, 10 Oct 2009 21:36:19 -0400
From: justin@emproshunts.com
To: peroc79@gmail.com
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] interface for "non-SQL people"

pere roca wrote:

hi,
some nice tool over there to let non-SQL knowing people to construct their
queries? I'm using pgAdmin III but I know some SQL.
there is no other option than constructing an HTML with forms, drop-down
menus...?

thanks,
pERE

Your best bet which is not free is Crystal Reports it can do all the
above you list. It can automate creation of HTML, email, export Excel,
ad hoc filters in drop downs, and text inputs, Graphs and all kinds of
other stuff.
http://www.sap.com/solutions/sapbusinessobjects/sme/reporting-dashboarding/index.epx

its designed to be easy to use for the NON have not a clue
SQL/programmer people.

There are other software packages out there. Note I have never used any
of these can not comment...
http://www.inetsoft.com/products/StyleReportEE/
http://www.pentaho.com/products/reporting/
http://www.inetsoftware.de/products/crystal-clear
http://www.actuate.com/products/
http://www.agata.org.br/
http://jasperforge.org/plugins/project/project_home.php?projectname=jasperreports

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
http://clk.atdmt.com/GBL/go/171222985/direct/01/

Martin Gainty wrote:

Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede
unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig.
Diese Nachricht dient lediglich dem Austausch von Informationen und
entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten
Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt
uebernehmen.

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.

Confidentiality disclaimers on messages posted to a public forum are rather
pointless, especially if in languages other than the /lingua franca/ of the forum.

--
Lew