Data entry - forms design or other APIs etc. - what is there?

I'm working on an application for my own use and have been using knoda
as a front end to postgresql but I need a bit more power and flexibility.

I'm quite happy to get into relatively serious programming as I am a
C++/Java programmer, this accounting package is rather out of my
normal line of work though.

What methods are available to produce data entry forms for postgresql
databases? If, for example, one wanted to migrate a system that used
Oracle Forms to Postgresql what would one use? This seems to me to be
an area which is not aired much here and that surprises me because a
database is of no use unless one can get data into it.

I'd prefer something that isn't web based but if that's the only way
to go then I do have a web server (apache) on the system where the
Postgresql database is. It doesn't have to be multiuser really either
as I'm probably going to be the only user.

All I want is a way to produce reasonably functional forms for
entering data into a Postgresql database which will allow me to do the
following:-

Show a database table in a 'table view' type format which will
allow deletion, modification and insertion of rows.

Has 'hooks' so that one can have actions which occur on entry to
and exit from certain fields.

Can calculate and preload some fields.

I don't need sophisticated layout facilities, nor do I need
sophisticated reporting as I think knoda can do all I need on that
front.

Any suggestions or ideas would be very welcome.

--
Chris Green (chris(at)areti(dot)co(dot)uk)

"Never ascribe to malice that which can be explained by incompetence."

On Fri, 21 Jan 2005 18:55:27 +0000, Chris Green <chris(at)areti(dot)co(dot)uk> wrote:
> I'm working on an application for my own use and have been using knoda
> as a front end to postgresql but I need a bit more power and flexibility.
>
> I'm quite happy to get into relatively serious programming as I am a
> C++/Java programmer, this accounting package is rather out of my
> normal line of work though.
>
> What methods are available to produce data entry forms for postgresql
> databases? If, for example, one wanted to migrate a system that used
> Oracle Forms to Postgresql what would one use? This seems to me to be
> an area which is not aired much here and that surprises me because a
> database is of no use unless one can get data into it.

You can use any program that supports ODBC. I'm guessing Crystal
Reports might be the closest thing to Oracle Forms.

Chris

On Friday 21 January 2005 12:55 pm, Chris Green wrote:
> I'm working on an application for my own use and have been using
> knoda as a front end to postgresql but I need a bit more power and
> flexibility.
>
> I'm quite happy to get into relatively serious programming as I am a
> C++/Java programmer, this accounting package is rather out of my
> normal line of work though.
>
> What methods are available to produce data entry forms for postgresql
> databases? If, for example, one wanted to migrate a system that used
> Oracle Forms to Postgresql what would one use? This seems to me to
> be an area which is not aired much here and that surprises me because
> a database is of no use unless one can get data into it.
>
> I'd prefer something that isn't web based but if that's the only way
> to go then I do have a web server (apache) on the system where the
> Postgresql database is. It doesn't have to be multiuser really
> either as I'm probably going to be the only user.
>
> All I want is a way to produce reasonably functional forms for
> entering data into a Postgresql database which will allow me to do
> the following:-
>
> Show a database table in a 'table view' type format which will
> allow deletion, modification and insertion of rows.
>
> Has 'hooks' so that one can have actions which occur on entry to
> and exit from certain fields.
>
> Can calculate and preload some fields.
>
> I don't need sophisticated layout facilities, nor do I need
> sophisticated reporting as I think knoda can do all I need on that
> front.
>
> Any suggestions or ideas would be very welcome.

Many languages have the capacity to access PostgreSQL databases
including Python (with PyGreSQL), Perl (with DBI), PHP (compile in
support), Delphi and Java (with JDBC) to name a few. The selection of
GUI tools for forms depends upon the language.

You can also use other databases applications that make use of ODBC
links such as MS Access and Paradox. (Attempts with Lotus Approach
failed horribly.) At work, I've used MS Access to create several
front-end applications to PostgreSQL database servers.

Gnumeric, a spreadsheet application, is supposed to be able to access
several databases natively (not odbc) via gnomedb. I've gotten gnomedb
to connect to the database; but I can't find any documentation as to
how to get the data into the spreadsheet.

Best of luck,

Andrew Gould

On Fri, Jan 21, 2005 at 06:19:53PM -0600, Andrew L. Gould wrote:
> On Friday 21 January 2005 12:55 pm, Chris Green wrote:
[snip question]
>
> Many languages have the capacity to access PostgreSQL databases
> including Python (with PyGreSQL), Perl (with DBI), PHP (compile in
> support), Delphi and Java (with JDBC) to name a few. The selection of
> GUI tools for forms depends upon the language.
>
Yes, I was hoping for a bit more help than just a language interface
but if I have to I'll go down that route.

> You can also use other databases applications that make use of ODBC
> links such as MS Access and Paradox. (Attempts with Lotus Approach
> failed horribly.) At work, I've used MS Access to create several
> front-end applications to PostgreSQL database servers.
>
Now that *is* a possibility, I have Access at least. Though that
prevents me making a totally Linux based application.

> Gnumeric, a spreadsheet application, is supposed to be able to access
> several databases natively (not odbc) via gnomedb. I've gotten gnomedb
> to connect to the database; but I can't find any documentation as to
> how to get the data into the spreadsheet.
>
Those might be useful too, thanks.

--
Chris Green (chris(at)areti(dot)co(dot)uk)

"Never ascribe to malice that which can be explained by incompetence."

None have mentioned pgaccess yet. I haven't used it on a regular basis, but
at least it builds and runs on my Mac (and therefore, linux, etc.)

Sean

----- Original Message -----
From: "Chris Green" <chris(at)areti(dot)co(dot)uk>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Saturday, January 22, 2005 7:57 AM
Subject: Re: [GENERAL] Data entry - forms design or other APIs etc. - what
is there?

> On Fri, Jan 21, 2005 at 06:19:53PM -0600, Andrew L. Gould wrote:
>> On Friday 21 January 2005 12:55 pm, Chris Green wrote:
> [snip question]
>>
>> Many languages have the capacity to access PostgreSQL databases
>> including Python (with PyGreSQL), Perl (with DBI), PHP (compile in
>> support), Delphi and Java (with JDBC) to name a few. The selection of
>> GUI tools for forms depends upon the language.
>>
> Yes, I was hoping for a bit more help than just a language interface
> but if I have to I'll go down that route.
>
>> You can also use other databases applications that make use of ODBC
>> links such as MS Access and Paradox. (Attempts with Lotus Approach
>> failed horribly.) At work, I've used MS Access to create several
>> front-end applications to PostgreSQL database servers.
>>
> Now that *is* a possibility, I have Access at least. Though that
> prevents me making a totally Linux based application.
>
>
>> Gnumeric, a spreadsheet application, is supposed to be able to access
>> several databases natively (not odbc) via gnomedb. I've gotten gnomedb
>> to connect to the database; but I can't find any documentation as to
>> how to get the data into the spreadsheet.
>>
> Those might be useful too, thanks.
>
> --
> Chris Green (chris(at)areti(dot)co(dot)uk)
>
> "Never ascribe to malice that which can be explained by incompetence."
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

On 21/01/2005 18:55 Chris Green wrote:
> All I want is a way to produce reasonably functional forms for
> entering data into a Postgresql database which will allow me to do the
> following:-
>
> Show a database table in a 'table view' type format which will
> allow deletion, modification and insertion of rows.
>
> Has 'hooks' so that one can have actions which occur on entry to
> and exit from certain fields.
>
> Can calculate and preload some fields.
>
> I don't need sophisticated layout facilities, nor do I need
> sophisticated reporting as I think knoda can do all I need on that
> front.

OpenOffice has forms and database access. It may suit your needs.

--
Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business |
| Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

On Saturday 22 January 2005 06:57 am, Chris Green wrote:
> On Fri, Jan 21, 2005 at 06:19:53PM -0600, Andrew L. Gould wrote:
> > On Friday 21 January 2005 12:55 pm, Chris Green wrote:
>
> [snip question]
>
> > Many languages have the capacity to access PostgreSQL databases
> > including Python (with PyGreSQL), Perl (with DBI), PHP (compile in
> > support), Delphi and Java (with JDBC) to name a few. The selection
> > of GUI tools for forms depends upon the language.
>
> Yes, I was hoping for a bit more help than just a language interface
> but if I have to I'll go down that route.

There are many free GUI's built for database access (many of them
web-based using php); but most of them focus on database
administration.

>
> > You can also use other databases applications that make use of ODBC
> > links such as MS Access and Paradox. (Attempts with Lotus Approach
> > failed horribly.) At work, I've used MS Access to create several
> > front-end applications to PostgreSQL database servers.
>
> Now that *is* a possibility, I have Access at least. Though that
> prevents me making a totally Linux based application.

CodeWeavers has MS Office 2000 working in their Crossover Office (WINE)
product. Access locks up under heavy load (clinical data analysis), so
it's not an option for my uses; but your needs may vary.

>
> > Gnumeric, a spreadsheet application, is supposed to be able to
> > access several databases natively (not odbc) via gnomedb. I've
> > gotten gnomedb to connect to the database; but I can't find any
> > documentation as to how to get the data into the spreadsheet.
>
> Those might be useful too, thanks.

Also, take a look at kexi (http://www.kexi-project.org), a KDE
replacement for MS Access. Unfortunately, it's not soup yet.

Good luck,

Andrew Gould

On Sat, Jan 22, 2005 at 08:13:52AM -0500, Sean Davis wrote:
> None have mentioned pgaccess yet. I haven't used it on a regular basis,
> but at least it builds and runs on my Mac (and therefore, linux, etc.)
>
It looks good but I can't work out how to actually download it.

--
Chris Green (chris(at)areti(dot)co(dot)uk)

"Never ascribe to malice that which can be explained by incompetence."

> There are many free GUI's built for database access (many of them
> web-based using php); but most of them focus on database
> administration.

I think the reason for that is that database administration is easier
to parameterize.

There are so many different things that an application might (or should)
do that writing a generalized application development tool is a huge task.

Making it reasonably secure, multi-user aware and web-based adds extra levels
of challenges.

I've been playing around with writing a table-driven web-based database
query/edit tool for the last year and a half. It works fairly well for
some in-house applications and at one of my clients. Once I get through
the major portion of the job for this client (around the end of April,
I hope), I'm hoping to have time to look at what it would take to turn
this into a project that can be released into the open source community.

While it was written (in PHP) with PostgreSQL in mind, I've already
used it with limited sucess with other database back ends, specifically
MySQL and Oracle. I think it should be possible to make it work with
any database for which there is a PEAR implementation in PHP.
--
Mike Nolan

Take a look at Ruby on Rails.

http://www.onlamp.com/pub/a/onlamp/2005/01/20/rails.html

I haven't used it myself yet but looking through the above walkthrough,
seems pretty easy to make data entry web forms.

Chris Green wrote:
> On Fri, Jan 21, 2005 at 06:19:53PM -0600, Andrew L. Gould wrote:
>
>>On Friday 21 January 2005 12:55 pm, Chris Green wrote:
>
> [snip question]
>
>>Many languages have the capacity to access PostgreSQL databases
>>including Python (with PyGreSQL), Perl (with DBI), PHP (compile in
>>support), Delphi and Java (with JDBC) to name a few. The selection of
>>GUI tools for forms depends upon the language.
>>
>
> Yes, I was hoping for a bit more help than just a language interface
> but if I have to I'll go down that route.
>
>
>>You can also use other databases applications that make use of ODBC
>>links such as MS Access and Paradox. (Attempts with Lotus Approach
>>failed horribly.) At work, I've used MS Access to create several
>>front-end applications to PostgreSQL database servers.
>>
>
> Now that *is* a possibility, I have Access at least. Though that
> prevents me making a totally Linux based application.
>
>
>
>>Gnumeric, a spreadsheet application, is supposed to be able to access
>>several databases natively (not odbc) via gnomedb. I've gotten gnomedb
>>to connect to the database; but I can't find any documentation as to
>>how to get the data into the spreadsheet.
>>
>
> Those might be useful too, thanks.
>

Chris Green wrote:

> I'm working on an application for my own use and have been using knoda
> as a front end to postgresql but I need a bit more power and flexibility.
>
> I'm quite happy to get into relatively serious programming as I am a
> C++/Java programmer, this accounting package is rather out of my
> normal line of work though.
Since You think about writing Your own app, did You had a look at Qt ?
It includes a SQL module with the capability to connect to various
DB's (PostgreSQL, MySQL, Oracle, MSSQL and others )
With designer You can build the layout of Your application where You can
use data aware widgets for display and/or editing.

Ulrich Schwab

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm using Runtime Revolution (www.runrev.com; note this is a commercial
product, but there is a free demo) -- not database specific, but it
does have built-in support for PostgreSQL. Note that while it *should*
be able to build apps for Linux, accessing databases from those apps
seems to be broken in the current version; I suspect that will be fixed
in the next release. This is a *very* fast way to build almost any
kind of application, once you've got a feel for how to use it.

On Jan 23, 2005, at 1:17 AM, William Yu wrote:

> Take a look at Ruby on Rails.
>
> http://www.onlamp.com/pub/a/onlamp/2005/01/20/rails.html
>
> I haven't used it myself yet but looking through the above
> walkthrough, seems pretty easy to make data entry web forms.
>
>
> Chris Green wrote:
>> On Fri, Jan 21, 2005 at 06:19:53PM -0600, Andrew L. Gould wrote:
>>> On Friday 21 January 2005 12:55 pm, Chris Green wrote:
>> [snip question]
>>> Many languages have the capacity to access PostgreSQL databases
>>> including Python (with PyGreSQL), Perl (with DBI), PHP (compile in
>>> support), Delphi and Java (with JDBC) to name a few. The selection
>>> of GUI tools for forms depends upon the language.
>>>
>> Yes, I was hoping for a bit more help than just a language interface
>> but if I have to I'll go down that route.
>>> You can also use other databases applications that make use of ODBC
>>> links such as MS Access and Paradox. (Attempts with Lotus Approach
>>> failed horribly.) At work, I've used MS Access to create several
>>> front-end applications to PostgreSQL database servers.
>>>
>> Now that *is* a possibility, I have Access at least. Though that
>> prevents me making a totally Linux based application.
>>> Gnumeric, a spreadsheet application, is supposed to be able to
>>> access several databases natively (not odbc) via gnomedb. I've
>>> gotten gnomedb to connect to the database; but I can't find any
>>> documentation as to how to get the data into the spreadsheet.
>>>
>> Those might be useful too, thanks.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr. <fde101(at)fjrhome(dot)net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB9QsK7aqtWrR9cZoRAtbHAJ4w5kv/qC+Kt+IOUpP6ncq6wJdO3wCdHXts
gqksDNbNduBp2jXaBQQ4oBg=
=cAyE
-----END PGP SIGNATURE-----

___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com

On Fri, 21 Jan 2005 18:55:27 +0000, Chris Green <chris(at)areti(dot)co(dot)uk> wrote:
> > What methods are available to produce data entry forms for postgresql
> > databases? If, for example, one wanted to migrate a system that used
> > Oracle Forms to Postgresql what would one use? This seems to me to be
> > an area which is not aired much here and that surprises me because a
> > database is of no use unless one can get data into it.

gnue-forms (www.gnuenterprise.org) was created by a few of us that liked
Oracle forms but wanted something better (and free :). It supports any
backend supported by our common library. A listing of our backend drivers
dir displays ( in no order)

adodbapi csv dbf informix interbase mysql oracle sapdb
sqlite sybase appserver db2 gadfly ingres ldap odbc
postgresql special sqlrelay

Some drivers are more feature complete than others but most should function.
Connections to backends are transparent to forms and other gnue-common based
apps. So you can create forms on a postgresql backend (we have support for
all 4 python postgresql drivers), change one connections.conf file, and have
the forms work against the other databases listed above.

We also support several front ends including wx, gtk2, win32 native, and
curses(rough but functional in simple cases).

We have a separate gnue-designer tool that lets you drag and drop tables and
fields to create the XML based form files gnue-forms uses. It also supports
wizards to create [single|multi]page master/detail forms. Unlike the last
version of Oracle Forms (6?) I used our master/detail can nest to any level
without trigger kludges. You can also mix and match datasources on the same
form so you could (for whatever reason) create master detail relationships
between tables on separate types of backends (I haven't tested that in years
though) Also unlike Oracle forms our ui system lets you connect multiple
widgets on separate form pages to the same fields in a table, again to reduce
the number of triggers needed.

We do have a trigger system that lets you write triggers in python and
possibly javascript (i've never used the js support) Custom namespaces let
you manipulate data via blockname.fieldname

Most of our tools functionality is embedded in our gnue-common library so you
can use the same datasources and types of access in custom programs as you
can in forms. If you're willing to use python that is :) Common provides
more than just data access abstraction though, and it's description page
doesn't cover all it can do. It contains an application framework, output
libraries for things like generating barcodes or tabular pdf reports,
formatting functions, a trigger system, and lots of other things.

We also have a report tool, and an n-tier application server (with it's own
forms backend driver). All based upon the same common core.

We're happy to answer questions on our mailing list. Or in IRC at
#gnuenterprise on irc.freenode.net

Take Care,
James