Connecting website with SQL-database.....

"J.Post" wrote:

I've been looking on the internet for 2 hours for information how to connect
my SQL database to my website. I didn't find what I was looking for, this is
my problem............
I have got a form on my site, that can be filled in by visitors. I want the
results automatically being written in my sql database, does anybody know
how to do this???

For a very easy to use way of doing this, check out AOLserver -- while
it does replace your existing webserver, it is fast and easy to use for
PostgreSQL.

If you are stuck with another webserver, you can probably use PHP, which
is also easy to use, but not as scalable or fast as the AOLserver
solution.

And, you can always use a Perl CGI with either the Perl interface module
distributed with the PostgreSQL distribution, or you can use the DBI/DBD
modules, also from perl.

If you've not written CGI scripts before, expect to speed more than 2
hours getting it to work. If you are comfortable with CGI scripts (or
other web scripting), it shouldn't take very long to get the information
you need from the PostgreSQL documentation and the PostgreSQL
applications listing -- which I can't seem to locate at the moment.
There is a users gallery on www.pgsql.com, but that's not the listing I
remember....

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Hello J.Post,

On 17-Apr-00 12:26:41, you wrote:

I've been looking on the internet for 2 hours for information how to connect
my SQL database to my website. I didn't find what I was looking for, this is
my problem............
I have got a form on my site, that can be filled in by visitors. I want the
results automatically being written in my sql database, does anybody know
how to do this???

Whatever platform you use, PHP is the solution for your problem. It is one
of the most powerful Web programming languages that is able to interface
natively with dozens of types databases including PostgreSQL. The good part
is that it is not tied to any Web server and it is Open Source.

http://www.php.net/

If you want to use a DBMS independent interface with database try Metabase
for PHP. Metabase not only provides database independence in the access
but also in the installation of your database schemas (tables, fields,
indexes, sequences).

With a Metabase schema description language defined in a custom XML format,
you are able to install your schemas portably without having to wonder
exactly how.

The neat part is that if you want later to change your database schema by
adding/removing/changing/renaming tables/fields/indexes/sequences, all you
need to do is to make the changes in your database schema description file.

Then you only need to ask Metabase to upgrade it and it will issue the
necessary SQL commands to alter your database accordingly without affecting
any data that was stored after the database was installed for the first
time or upgraded for the last time.

Here you may find all the source code for Metabase with all the classes,
for the schema parser, database manager, driver classes for different DBMS
including PostgreSQL, example schemas and installation scripts, user manual
and tutorial documents.

http://phpclasses.UpperDesign.com/browse.html/package/20

Enjoy,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

On Mon, 17 Apr 2000, J.Post wrote:

I've been looking on the internet for 2 hours for information how to connect
my SQL database to my website. I didn't find what I was looking for, this is
my problem............
I have got a form on my site, that can be filled in by visitors. I want the
results automatically being written in my sql database, does anybody know
how to do this???

I've wrote C programs (CGI).
I use embedded SQL for interface with database and POST method to get
information from web.

There are many free CGI scripts (C & Perl) on this website:
http://cgi.resourceindex.com/Programs_and_Scripts/

I can share my programs but this is my first experience with CGI/Web/SQL,
so programs are not elegant.

-Rita

--------------------------------------------------------
Margarita Barvinok University of Michigan
System Administrator II Department of Biology
brita@umich.edu
---------------------------------------------------------

One option is to use an application server (such as Enhydra, which is
Open Source). I recently put together a brief cookbook style tutorial
on getting Enhydra running with PostgreSQL. It should soon, hopefully
this week, be published on the Enhydra web site ( http://www.enhydra.org
), or e-mail me off-line and I can send you a copy.

After getting an understanding as to how to get PostgreSQL working with
Enhydra, you may want to go through their "Getting Started" tutorial (
see the bottom of the page at
http://www.enhydra.org/software/documentation/enhydra/index.html ). The
DiscRack example in the tutorial shows more specifically what you're
asking for -- how to use an HTML form to get user info into your DB.

Regards,
Jim

"J.Post" wrote:

As another alternative, Java Server Pages (http://www.javasoft.com/jsp) are
great if you're into Java as you can embed the required (JDBC) code directly
into your HTML (like ASP's but way way better IMHO) or alternatively package the
logic into JavaBeans (or components) that can be called from your webpages.

If you're ultra keen I can even post you some JSP's that demonstrate this...

Regards,
Joe

Margarita Barvinok wrote:

On Mon, 17 Apr 2000, J.Post wrote:

I've been looking on the internet for 2 hours for information how to connect
my SQL database to my website. I didn't find what I was looking for, this is
my problem............
I have got a form on my site, that can be filled in by visitors. I want the
results automatically being written in my sql database, does anybody know
how to do this???

I've wrote C programs (CGI).
I use embedded SQL for interface with database and POST method to get
information from web.

There are many free CGI scripts (C & Perl) on this website:
http://cgi.resourceindex.com/Programs_and_Scripts/

I can share my programs but this is my first experience with CGI/Web/SQL,
so programs are not elegant.

-Rita

--------------------------------------------------------
Margarita Barvinok University of Michigan
System Administrator II Department of Biology
brita@umich.edu
---------------------------------------------------------

--
Joe Shevland
Principal Consultant
KPI Logistics Pty Ltd
http://www.kpi.com.au
mailto:shevlandj@kpi.com.au

-> On 17-Apr-00 12:26:41, you wrote:
->
-> >I've been looking on the internet for 2 hours for information how to connect
-> >my SQL database to my website. I didn't find what I was looking for, this is
-> >my problem............
-> >I have got a form on my site, that can be filled in by visitors. I want the
-> >results automatically being written in my sql database, does anybody know
-> >how to do this???
->
-> Whatever platform you use, PHP is the solution for your problem. It is one
-> of the most powerful Web programming languages that is able to interface
-> natively with dozens of types databases including PostgreSQL. The good part
-> is that it is not tied to any Web server and it is Open Source.
->
-> http://www.php.net/

[ munch ]

For a good discussion of some of the alternatives (centered around but
not limited to AOLserver) you can visit photo.net/wtr/, the Web/db
bulletin board.

Not that I recommend anything in particular for your specific situation,
but it's always a good idea to get an idea of what's out there.

cheers,
--titus

On Mon, 17 Apr 2000, Titus Brown wrote:

-> >I have got a form on my site, that can be filled in by visitors. I want the
-> >results automatically being written in my sql database, does anybody know
-> >how to do this???

There is more than one way to do it!

-> Whatever platform you use, PHP is the solution for your problem. It is one
-> of the most powerful Web programming languages that is able to interface
-> natively with dozens of types databases including PostgreSQL. The good part
-> is that it is not tied to any Web server and it is Open Source.

So is perl...for example, or Zope...

For a good discussion of some of the alternatives (centered around but
not limited to AOLserver) you can visit photo.net/wtr/, the Web/db
bulletin board.

Not that I recommend anything in particular for your specific situation,
but it's always a good idea to get an idea of what's out there.

cheers,
--titus

Right!

My 2 cents:

I use Perl DBI::DBD with the "singing, dancing" CGI module. I'm happy with it.
If you'd like, visit my site: www.opensystems.org and the links will assist
you in downloading the various perl modules. There are samples there too.
(DBI and CGI).

A DBI mailing list exists as well:
http://www.isc.org/dbi-lists.html

If you want to skip using the CGI module and code HTML directly into a
perl-DBI script, but need some examples: download SQL Ledger from Dieter
Simader's page: www.simtax.ca/acc
This is a small but robust accounting package that uses Perl and Apache.

Lots of luck!
Tom

---------------------------------------------------------------------------
North Richmond Community Mental Health Center
---------------------------------------------------------------------------
Thomas Good, MIS Coordinator tomg@ { admin | q8 } .nrnet.org
Phone: 718-354-5528
Fax: 718-354-5056
---------------------------------------------------------------------------
North Richmond Systems PostgreSQL s l a c k w a r e
Are Powered By: RDBMS |---------- linux
---------------------------------------------------------------------------

I want to write a Java applet that retrieves/updates data in a psql
database. I am running Apache and psql on a single OpenBSD server. I
currently use PHP for dynamic pages; which has been fine up to now for
presenting data, but I can see the day coming where some processing at the
client side would be useful; so I want to take a closer look at java. I
have been told by someone who knows little more than I do, that I need
servlets to get at my data with java applets. When I go to the apache
site, I see references to Jakarta, Tomcat, Java-Apache, JSERV and JSSI.
But I couldn't find summaries that tell what these things are. Can someone
tell me exactly which of the above components (and more?) I need to install
to make this work? How do they fit together? Do I also need JDBC? Does
JDBC require ODBC?

Frank

On Tue, 18 Apr 2000, Frank Bax wrote:

I want to write a Java applet that retrieves/updates data in a psql
database. I am running Apache and psql on a single OpenBSD server. I
currently use PHP for dynamic pages; which has been fine up to now for
presenting data, but I can see the day coming where some processing at the
client side would be useful; so I want to take a closer look at java. I
have been told by someone who knows little more than I do, that I need
servlets to get at my data with java applets. When I go to the apache
site, I see references to Jakarta, Tomcat, Java-Apache, JSERV and JSSI.

Ok, I've not played with servlets (had no need) but here goes (and
someone correct me if I'm wrong):

Java-Apache is the name of the project to integrate Java with Apache.

JSERV is a web server that embeds java into web pages which is processed
on the server. This is not JavaScript, but similar to PHP or ASP.

Not sure what JSSI is. I have heared of Jakarta & Tomcat, but thats all.

But I couldn't find summaries that tell what these things are. Can someone
tell me exactly which of the above components (and more?) I need to install
to make this work? How do they fit together?

Do I also need JDBC?

To access postgres from Java, you will need JDBC.

Does JDBC require ODBC?

No. There are four types of JDBC driver, and ours is Type 4, which means
its written in pure java, and will run anywhere.

Peter

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

Hello Thomas,

On 18-Apr-00 09:42:25, you wrote:

For a good discussion of some of the alternatives (centered around but
not limited to AOLserver) you can visit photo.net/wtr/, the Web/db
bulletin board.

Not that I recommend anything in particular for your specific situation,
but it's always a good idea to get an idea of what's out there.

I use Perl DBI::DBD with the "singing, dancing" CGI module. I'm happy with
it. If you'd like, visit my site: www.opensystems.org and the links will
assist you in downloading the various perl modules. There are samples there
too.
(DBI and CGI).

I may be mistaken, but the last time that I looked at Perl DBI, it didn't
seem to a complete database abstraction layer than it is needed. For
instance, you want retrieve data from date fields the results come
formatted in a database dependent way. This means that your DBI
applications can't really be that much database independent as you still
have to handle datatype differences in the application code.

With this Metabase package in PHP date fields are always returned formatted
in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
whatever processing you want with dates formatted this way, but it's always
DBMS independent.

Another thing that seems to be lacking in DBI and other database abstraction
layers is support for DBMS independent schema installation. I mean if you
want to install a given database schema (tables, fields, indexes,
sequences) you still have to hand code database dependent SQL commands to
create them.

As I explained before, with this Metabase PHP package you only need to
describe the database schema in a custom XML format that looks like this:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<database>

<name>test</name>
<create>1</create>

<table>
<name>users</name>
<declaration>
<field> <name>user_id</name> <type>integer</type> <default>0</default> <notnull>1</notnull> </field>
<field> <name>user_name</name> <type>text</type> </field>
<field> <name>password</name> <type>text</type> </field>
<field> <name>reminder</name> <type>text</type> </field>
<field> <name>name</name> <type>text</type> </field>
<field> <name>email</name> <type>text</type> </field>
<index>
<name>users_id_index</name>
<unique>1</unique>
<field> <name>user_id</name> </field>
</index>
</declaration>
</table>

<sequence>
<name>user_id</name>
<start>1</start>
<on> <table>users</table> <field>user_id</field> </on>
</sequence>

</database>

Metabase will install this schema description on any SQL based database.
Furthermore, if you change the schema later you may tell Metabase to apply
the changes without affected any data that was added to the database
afterwards.

There are other neat features like support for requesting just a range of
rows of a SELECT query. In some DBMS it would be as simple as specifying
the LIMIT clause, but it is not that simple in many others. Metabase
abstracts all that for you because those are desirable features that all
database abstraction layers should provide.

As I mentioned before, you may find more information about it here:

http://phpclasses.UpperDesign.com/browse.html/package/20

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

Le mar, 18 avr 2000, Peter Mount a �crit :

On Tue, 18 Apr 2000, Frank Bax wrote:

I want to write a Java applet that retrieves/updates data in a psql
database. I am running Apache and psql on a single OpenBSD server. I
currently use PHP for dynamic pages; which has been fine up to now for
presenting data, but I can see the day coming where some processing at the
client side would be useful; so I want to take a closer look at java. I
have been told by someone who knows little more than I do, that I need
servlets to get at my data with java applets. When I go to the apache
site, I see references to Jakarta, Tomcat, Java-Apache, JSERV and JSSI.

Ok, I've not played with servlets (had no need) but here goes (and
someone correct me if I'm wrong):

Java-Apache is the name of the project to integrate Java with Apache.

JSERV is a web server that embeds java into web pages which is processed
on the server. This is not JavaScript, but similar to PHP or ASP.

No. JServ is a servlet engine for Apache. It doesn't support JSP alone, and
it's not compliant with last version of servlet API. Tomcat, a part of Jakarta
project, is its successor : it's both a servlet (2.2 compliant) engine and a
JSP (1.1) engine.

Not sure what JSSI is. I have heared of Jakarta & Tomcat, but thats all.

It's Java Server-Side Include, the java equivalent of traditionnal SSI.

But I couldn't find summaries that tell what these things are. Can someone
tell me exactly which of the above components (and more?) I need to install
to make this work? How do they fit together?

You'll need PostgreSQL for storing the data, JDBC driver to access them, Tomcat
to process your servlets and JSP pages, Apache eventually if you also have
plain HTML, as Tomcat is stand-alone capable. I don't know for JSSI, but for
accessing data, this already largely enough.

--
Guillaume Rousse
Iremia - Universit� de la R�union

Sleep doesn't exists. Just lack of cafeine.

The only thing I'd add to all of this is that applets can do your
client-side processing for you if you like... your applet needs
to open a URLConnection to a servlet on the webserver, and this
way you can communicate via SSL, plain ol' TCP/IP, object input/output
streams etc. But effectively the servlet you can think of like
a CGI application running on the server. JDBC can also take place
from the applet to the server, but there's issues between how IE's
VM and Netscape's VM instantiate JDBC driver's (IE does not, as
per usual, follow the standard).

Regards,
Joe

Guillaume Rousse wrote:

Le mar, 18 avr 2000, Peter Mount a �crit :

On Tue, 18 Apr 2000, Frank Bax wrote:

I want to write a Java applet that retrieves/updates data in a psql
database. I am running Apache and psql on a single OpenBSD server. I
currently use PHP for dynamic pages; which has been fine up to now for
presenting data, but I can see the day coming where some processing at the
client side would be useful; so I want to take a closer look at java. I
have been told by someone who knows little more than I do, that I need
servlets to get at my data with java applets. When I go to the apache
site, I see references to Jakarta, Tomcat, Java-Apache, JSERV and JSSI.

Ok, I've not played with servlets (had no need) but here goes (and
someone correct me if I'm wrong):

Java-Apache is the name of the project to integrate Java with Apache.

JSERV is a web server that embeds java into web pages which is processed
on the server. This is not JavaScript, but similar to PHP or ASP.

No. JServ is a servlet engine for Apache. It doesn't support JSP alone, and
it's not compliant with last version of servlet API. Tomcat, a part of Jakarta
project, is its successor : it's both a servlet (2.2 compliant) engine and a
JSP (1.1) engine.

Not sure what JSSI is. I have heared of Jakarta & Tomcat, but thats all.

It's Java Server-Side Include, the java equivalent of traditionnal SSI.

But I couldn't find summaries that tell what these things are. Can someone
tell me exactly which of the above components (and more?) I need to install
to make this work? How do they fit together?

You'll need PostgreSQL for storing the data, JDBC driver to access them, Tomcat
to process your servlets and JSP pages, Apache eventually if you also have
plain HTML, as Tomcat is stand-alone capable. I don't know for JSSI, but for
accessing data, this already largely enough.

--
Guillaume Rousse
Iremia - Universit� de la R�union

Sleep doesn't exists. Just lack of cafeine.

--
Joe Shevland
Principal Consultant
KPI Logistics Pty Ltd
http://www.kpi.com.au
mailto:shevlandj@kpi.com.au

"I'm not under the alkafluence of inkahol that some thinkle peep I am.
It's just the drunker I sit here the longer I get."

On 18 Apr 2000, Manuel Lemos wrote:

I may be mistaken, but the last time that I looked at Perl DBI, it didn't
seem to a complete database abstraction layer than it is needed. For
instance, you want retrieve data from date fields the results come
formatted in a database dependent way. This means that your DBI
applications can't really be that much database independent as you still
have to handle datatype differences in the application code.

I have used another database abstraction layer, that wants to be
all-singing, all-dancing. It is called ODBC. It sucked.

There are add-ons to DBI which allow you to further abstract from your
database, if you choose so. For most of them, you need to still write
database-specific code yourself, it just gives you a cleaner interface on
how to do it. I believe that in general, this is the superior approach
instead of trying to abstract it all in the system/driver code.

The developer always knows what database-dependent features he is using,
and should appropriately abstract them into different file).

With this Metabase package in PHP date fields are always returned formatted
in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
whatever processing you want with dates formatted this way, but it's always
DBMS independent.

Reformatting things every time kills performance. Then again, since you
are using PHP, you are probably not worried about performance that much.

Another thing that seems to be lacking in DBI and other database abstraction
layers is support for DBMS independent schema installation. I mean if you
want to install a given database schema (tables, fields, indexes,
sequences) you still have to hand code database dependent SQL commands to
create them.

Because of the great variety in types, refint restrictions and other
restrictions supported by databases (and don't get me started on SQL
standards), its hard for _driver_ to know what exactly you want to create.
DBI drivers now provide information on types the database supports and
more-or-less standardized 'description' of them, but its up to you to make
a use of it.

As I explained before, with this Metabase PHP package you only need to
describe the database schema in a custom XML format that looks like this:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<database>

<name>test</name>
<create>1</create>

<table>
<name>users</name>
<declaration>
<field> <name>user_id</name> <type>integer</type> <default>0</default> <notnull>1</notnull> </field>
<field> <name>user_name</name> <type>text</type> </field>
<field> <name>password</name> <type>text</type> </field>
<field> <name>reminder</name> <type>text</type> </field>
<field> <name>name</name> <type>text</type> </field>
<field> <name>email</name> <type>text</type> </field>
<index>
<name>users_id_index</name>
<unique>1</unique>
<field> <name>user_id</name> </field>
</index>
</declaration>
</table>

<sequence>
<name>user_id</name>
<start>1</start>
<on> <table>users</table> <field>user_id</field> </on>
</sequence>
</database>

What if database doesn't support named sequences? (i.e. it only has
'sequence' as column type, but you can't create a sequence with a name).

Metabase will install this schema description on any SQL based database.
Furthermore, if you change the schema later you may tell Metabase to apply
the changes without affected any data that was added to the database
afterwards.

Sounds like a pipedream. (Or like ER/win tool, which is probably what you
_really_ want to use if you have tens of tables which periodically need
revision).

There are other neat features like support for requesting just a range of
rows of a SELECT query. In some DBMS it would be as simple as specifying
the LIMIT clause, but it is not that simple in many others. Metabase
abstracts all that for you because those are desirable features that all
database abstraction layers should provide.

If database doesn't support something, it is not necessarily a feature to
transparently provide emulation for it. Sometimes failing with an error
and forcing programmer to provide emulation code or forcing programmer to
ASK for emulation is the right thing.

-alex

Hello Alex,

On 19-Apr-00 02:08:04, you wrote:

I may be mistaken, but the last time that I looked at Perl DBI, it didn't
seem to a complete database abstraction layer than it is needed. For
instance, you want retrieve data from date fields the results come
formatted in a database dependent way. This means that your DBI
applications can't really be that much database independent as you still
have to handle datatype differences in the application code.

I have used another database abstraction layer, that wants to be
all-singing, all-dancing. It is called ODBC. It sucked.

Because there is to much overhead in datatype conversion among other
things. Not all database abstraction layers do that because often it
is not needed.

There are add-ons to DBI which allow you to further abstract from your
database, if you choose so. For most of them, you need to still write
database-specific code yourself, it just gives you a cleaner interface on
how to do it. I believe that in general, this is the superior approach
instead of trying to abstract it all in the system/driver code.

What you are saying is that DBI "could" be more confortable for the
programmer but it currently it isn't. I don't see how that makes DBI
superior. It's like saying that bicycles are superior to cars because they
have less wheels!?!

The developer always knows what database-dependent features he is using,
and should appropriately abstract them into different file).

Web developers are not DBA. Most of them where forced into being database
developing just because they needed some persistent storage that can be
searched efficiently from their Web sites. Most Web developers are not that
experienced so they are not expected to know how all database dependent
features work in each DBMS.

Database abstraction layers are good because they save Web developers from
the pain of learning more than they need to know about each DBMS.

With this Metabase package in PHP date fields are always returned formatted
in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
whatever processing you want with dates formatted this way, but it's always
DBMS independent.

Reformatting things every time kills performance. Then again, since you

I don't thing you thought througly about what you said. Look at the format
again. Fields have fixed sizes and so they are always in the same
position. If you want to extract sub fields from it, you just need to
fetch a substring of the same size from the same position furthermore.

Other than that, if you just want to compare dates you just need to do
string comparision as the fields appear in descreasing order of relevance
for time computations. This means among other things that for databases that
don't support native date/time fields, you may compare and sort as fixed
size text fields.

are using PHP, you are probably not worried about performance that much.

I am concerned about performance but I am not obcessed. If I were obcessed
I would not be using nor PHP nor Perl. I would be developing Web
applications as server modules in C.

Anyway, I suppose you don't know PHP that well these days. I don't need to
be obcessed by performance when using PHP because I only need to use single
native commands to process dates.

Besides, if I was that much worried today you have PHP/Zend
optimizer/compiler engine that boosts the performance of CPU intensive PHP
scripts so high, that in Web based database applications you would more
worried with database server and network I/O performance.

Another thing that seems to be lacking in DBI and other database
abstraction layers is support for DBMS independent schema installation. I
mean if you want to install a given database schema (tables, fields,
indexes, sequences) you still have to hand code database dependent SQL
commands to create them.

Because of the great variety in types, refint restrictions and other
restrictions supported by databases (and don't get me started on SQL
standards), its hard for _driver_ to know what exactly you want to create.

Only if your database applications need to use any other types than else
than: text, integer, boolean, float, decimal, date, time, timestamp . Maybe
I am forgetting something.

DBI drivers now provide information on types the database supports and
more-or-less standardized 'description' of them, but its up to you to make
a use of it.

That's the pain of job. If your application have to deal with data type
differences at that level, your application will still be too much database
dependent.

<sequence>
<name>user_id</name>
<start>1</start>
<on> <table>users</table> <field>user_id</field> </on>
</sequence>
</database>

What if database doesn't support named sequences? (i.e. it only has
'sequence' as column type, but you can't create a sequence with a name).

Use a single table to emulate the sequence. That's how the MySQL and MS
SQL Metabase drivers implement sequences: using separate tables with auto
incremented fields. If a database does not have those two, sequence calls
fail returning an error.

Metabase will install this schema description on any SQL based database.
Furthermore, if you change the schema later you may tell Metabase to apply
the changes without affected any data that was added to the database
afterwards.

Sounds like a pipedream. (Or like ER/win tool, which is probably what you
_really_ want to use if you have tens of tables which periodically need
revision).

Metabase does this now and well. It's not a pipedream.

Like you said, Perl/DBI could implement but it doesn't. Anyway, if there
is interest by the Perl/DBI or other communities that use other languages I
am willing to cooperate so the knowlegde built-in Metabase could be reused
and people could do other things like expanding applications in different
languages but using the database schemas.

Other interesting things could be done like reverse-engineering the schemas
of installed databases so they can be easily ported to other DBMS.

These are just ideas that could be constructively implemented if people is
willing to cooperate instead of wasting energies arguing which is the best
language or database abstraction layer.

Metabase is available in PHP code for free here:

http://phpclasses.UpperDesign.com/browse.html/package/20

You just need to mail me if you are willing to cooperate.

There are other neat features like support for requesting just a range of
rows of a SELECT query. In some DBMS it would be as simple as specifying
the LIMIT clause, but it is not that simple in many others. Metabase
abstracts all that for you because those are desirable features that all
database abstraction layers should provide.

If database doesn't support something, it is not necessarily a feature to
transparently provide emulation for it. Sometimes failing with an error
and forcing programmer to provide emulation code or forcing programmer to
ASK for emulation is the right thing.

Metabase does not abstract everything in all supported databases. If
some feature like sequences as I mentioned above, or for instance
transactions are not supported at all, they are not emulated. For
instance, if you need transactions, there is no magic that will emulate
that for instance in MySQL. So, use another database.

You can query any Metabase driver if a particular feature is supported or
not. If you still try to call functions that depend on unsupported feature
they will fail returning an error.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

At 04:26 PM 17-04-2000 +0200, J.Post wrote:

I've been looking on the internet for 2 hours for information how to connect
my SQL database to my website. I didn't find what I was looking for, this is
my problem............
I have got a form on my site, that can be filled in by visitors. I want the
results automatically being written in my sql database, does anybody know
how to do this???

Yep. Many ways to do it. Which programming language are you familiar with?

Options:
PHP,Perl,Python, Tcl, C++, etc.

I personally use perl because of the great modules for cgi and database:
CGI, DBI(DBD) etc.

The database module allows easy nondatabase specific quoting of data, so
that people cannot try to be naughty and execute their SQL statements on
your database.

Not filtering stuff properly before passing it to the database engine is a
very common problem.

In this year alone I've already encountered a number of sites where this is
possible. For one site I could log in as anyone (actually could do more,
but I left it at that ;) ), another see more records than supposed to, so
on and so forth. For an internally developed site I was able to update
everybody's password. Naturally I reported the problems to the site owners
who were rather grateful.

Filter inputs to suit your main program before it hits your main program,
then have various filters for your program outputs to databases,HTML, etc.

There are other things you should do as well, but this is one of the main
things.

Cheerio,
Link.

At 03:39 PM 18-04-2000 -0200, Manuel Lemos wrote:

I may be mistaken, but the last time that I looked at Perl DBI, it didn't
seem to a complete database abstraction layer than it is needed. For
instance, you want retrieve data from date fields the results come
formatted in a database dependent way. This means that your DBI
applications can't really be that much database independent as you still
have to handle datatype differences in the application code.

I wish you all the best. And there's a chance you may succeed (tho it looks
real slim from here).

I may be wrong but don't see much hope of you succeeding without chopping
off miscellaneous database specific features which make some people choose
to use those various databases in the first place.

If you put those features in, then you'll be back to square one, the apps
have to deal with them. And maybe some bright spark will come up with an
abstraction layer between Metabase and the app, just to remove them and the
trouble of dealing with them :).

The reason why we're in this mess is because the database people insist on
it being so, and maybe there are good reasons for them to insist on it.

Then there are things like datafield lengths and limits. I prefer them to
be handled by the app, rather than the database buffer overflowing on them,
truncating without warning or something.

But there's still hope as not everyone needs those features. I haven't
needed DECODE for instance, haven't used the built in programming
languages, nor much trigger stuff.

I think perl DBI took the pragmatic approach, well in the spirit of Perl's
more than one way to do it. Messy, but works rather well. The DBI Proxy
thingy was a real saver for one of my friends.

With this Metabase package in PHP date fields are always returned formatted
in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
whatever processing you want with dates formatted this way, but it's always
DBMS independent.

OK this one is nice. Is there also a standard for timezones and finer than
one second resolution?

Transactions for MySQL would be interesting to see.

Plus some people seem to want Postsgresql to do transactions Oracle style,
whereas some might want Oracle to do transactions Postgresql style. So how
about Metabase helping out?

I think if you can put in direct non ODBC support for DB2 and Oracle you
could have a much bigger market for your stuff. Then maybe we could move
apps seamlessly among Postgresql, DB2, Oracle environments.

You'll probably end up with a lot of work just keeping up with changes and
developments though.

Good luck!

Cheerio,

Link.

Hello Lincoln,

On 20-Apr-00 00:59:07, you wrote:

I may be mistaken, but the last time that I looked at Perl DBI, it didn't
seem to a complete database abstraction layer than it is needed. For
instance, you want retrieve data from date fields the results come
formatted in a database dependent way. This means that your DBI
applications can't really be that much database independent as you still
have to handle datatype differences in the application code.

I wish you all the best. And there's a chance you may succeed (tho it looks
real slim from here).

I may be wrong but don't see much hope of you succeeding without chopping
off miscellaneous database specific features which make some people choose
to use those various databases in the first place.

It's a trade-off. If you want to take the most of a database non-standard
extensions, don't use database abstraction packages at all. If you want
flexibility in such way that your applications will be more portable,
database abstraction packages are the way to go.

I think perl DBI took the pragmatic approach, well in the spirit of Perl's
more than one way to do it. Messy, but works rather well. The DBI Proxy
thingy was a real saver for one of my friends.

DBI Proxy is an interesting approach to solve some problems, but I didn't
quite follow what that has to do with what we were talking.

With this Metabase package in PHP date fields are always returned formatted
in the industry standard ISO 3166 (YYYY-MM-DD HH:MI:SS). Then you do
whatever processing you want with dates formatted this way, but it's always
DBMS independent.

OK this one is nice. Is there also a standard for timezones and finer than
one second resolution?

No, that's outside the scope of the package to go that far when most
databases differ greatly. It could be an idea to add support to
conditionally enable subsecond time representation or even time zones.

After all it also comes with an option to choose the number of places on
the right of the point for decimal fields. That is an option that should
only be changed before install time. So could be subsecond time fields
eventually with time zones.

Transactions for MySQL would be interesting to see.

I don't know what you got but Metabase MySQL driver does not attempt to
implement emulate transactions. That's too complex. It is well outside
the scope of Metabase.

Plus some people seem to want Postsgresql to do transactions Oracle style,
whereas some might want Oracle to do transactions Postgresql style. So how
about Metabase helping out?

Like other database abstraction packages Metabase only provides 3 functions
to handle transactions: AutoCommit(On/Off), Commit and Rollback.
AutoCommit(Off) implicitly starts a new transaction. AutoCommit(On)
implicitly ends an ongoing transaction commiting any work done. Commit
commits the current transaction if AutoCommit is Off and restarts a new
transaction. Rollback does the same except that it aborts the transaction
instead of commiting.

Whenever there is an error within a transaction whether from a database
statement or some problem in your application, the transaction should be
aborted explicitly by calling rollback.

If for some reason your PHP scripts exit with a transaction in progress,
Metabase uses its registered script shutdown handler to explicitly abort
a pending transaction that was not ended.

This is very important because PHP supports persistent database
connections. This means that the same process may reuse the same database
connection to be used to run different scripts from the same server thread.
If you leave a pending transaction open not only it may cause errors in the
next script that is run by the same server process, but it also may block
other processes to run transactions in the same database.

As for differences in implementations of transactions, I don't know, but
this seems to be the scheme that all the other database abstraction
packages seem to be using.

I think if you can put in direct non ODBC support for DB2 and Oracle you
could have a much bigger market for your stuff. Then maybe we could move
apps seamlessly among Postgresql, DB2, Oracle environments.

Currently there is support for MySQL, mSQL, PostgreSQL, Oracle using OCI. The
MS SQL server driver is almost ready. There is somebody working on
Informix driver and possibly Sybase ASE. We could already move seeminglessly
between applications.

You'll probably end up with a lot of work just keeping up with changes and
developments though.

I am not alone. Metabase developments went farther than other database
abstraction packages in the direction of the needs of many Web developers.
Despite it has been under private development over an year, it only has
been publicly released in January 2000.

Despite of that, the lack of such a complete database abstraction package
for PHP is attracting many developers and some are willing to contribute
with new drivers.

I am about to make another public release with some fixes and new drivers.
One important add-on that this release will come is a Metabase DBMS driver
class test suite.

Basically it is a script that uses some techniques of regression testing to
verify the conformance of any driver. This already helped to fix some
subtle bugs in the existing drivers but it will help further to detect faults
in future drivers and problemns when interfacing with new releases of
databases.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--

At 09:02 PM 21-04-2000 -0200, Manuel Lemos wrote:

It's a trade-off. If you want to take the most of a database non-standard
extensions, don't use database abstraction packages at all. If you want
flexibility in such way that your applications will be more portable,
database abstraction packages are the way to go.

Yep. But can enough useful stuff be abstracted to be common amongst most
databases?

Plus some people seem to want Postsgresql to do transactions Oracle style,
whereas some might want Oracle to do transactions Postgresql style. So how
about Metabase helping out?

Like other database abstraction packages Metabase only provides 3 functions
to handle transactions: AutoCommit(On/Off), Commit and Rollback.
AutoCommit(Off) implicitly starts a new transaction. AutoCommit(On)
implicitly ends an ongoing transaction commiting any work done. Commit

But with Oracle if you do a create table an implicit commit occurs. That's
not true for Postgresql.

Plus if you get a warning/error, Postgresql _requires_ you to rollback,
whereas many other databases don't.

Quite a number of people on this list, including me have found these
differences to be significant.

Currently there is support for MySQL, mSQL, PostgreSQL, Oracle using OCI. The
MS SQL server driver is almost ready. There is somebody working on
Informix driver and possibly Sybase ASE. We could already move seeminglessly
between applications.

Cool, someone I know has been looking for Oracle support for PHP. Not sure
why he didn't find it earlier.

Despite of that, the lack of such a complete database abstraction package
for PHP is attracting many developers and some are willing to contribute
with new drivers.

Yep, I found it quite surprising and annoying that I had to recompile php
(version 3) when I wanted to add support for various stuff, and that's not
just for database stuff.

I haven't been keeping up with the php scene much tho - still using perl.
Perl is respectably fast compared to C++. For example string concats
weren't much slower compared to C++. But it's about 13 times slower for
floating point stuff. That's not a big problem for most of my apps tho ;).

All the best with your venture!

Cheerio,

Link.

Hello Lincoln,

On 24-Apr-00 00:37:20, you wrote:

At 09:02 PM 21-04-2000 -0200, Manuel Lemos wrote:

It's a trade-off. If you want to take the most of a database non-standard
extensions, don't use database abstraction packages at all. If you want
flexibility in such way that your applications will be more portable,
database abstraction packages are the way to go.

Yep. But can enough useful stuff be abstracted to be common amongst most
databases?

Sure. There are features that are abstracted like transactions and are not
supported by all DBMS. You may query the Metabase driver to verify which
features are supported.

Plus some people seem to want Postsgresql to do transactions Oracle style,
whereas some might want Oracle to do transactions Postgresql style. So how
about Metabase helping out?

Like other database abstraction packages Metabase only provides 3 functions
to handle transactions: AutoCommit(On/Off), Commit and Rollback.
AutoCommit(Off) implicitly starts a new transaction. AutoCommit(On)
implicitly ends an ongoing transaction commiting any work done. Commit

But with Oracle if you do a create table an implicit commit occurs. That's
not true for Postgresql.

That is not a problem. That is like issuing some query and then commiting. When
you call Commit or AutoCommit(Off) and a commit command is issue. Oracle does
not complain if you commit when there is nothing to commit.

Plus if you get a warning/error, Postgresql _requires_ you to rollback,
whereas many other databases don't.

That's what Metabase expects. When there is an error, you should rollback
before exiting a transaction with AutoCommit(Off).

I noticed the discussion but I could not quite figure what was the problem.

Quite a number of people on this list, including me have found these
differences to be significant.

So, how does ODBC and JDBC drivers handle those differences?

Currently there is support for MySQL, mSQL, PostgreSQL, Oracle using OCI.
The MS SQL server driver is almost ready. There is somebody working on
Informix driver and possibly Sybase ASE. We could already move seeminglessly
between applications.

Cool, someone I know has been looking for Oracle support for PHP. Not sure
why he didn't find it earlier.

Some people want to access Oracle remote servers but they still need client
libraries in the local machine to access it from PHP. Maybe that was the
problem.

Despite of that, the lack of such a complete database abstraction package
for PHP is attracting many developers and some are willing to contribute
with new drivers.

Yep, I found it quite surprising and annoying that I had to recompile php
(version 3) when I wanted to add support for various stuff, and that's not
just for database stuff.

Thats when you to build PHP with modules statically linked. PHP developers
stop providing pre-compiled versions of PHP on Unix because there are
simply to many Unix systems and possible configurations. Under Windows PHP
is always distributed with modules as DLL. There are Unix distributions of
PHP that come with modules as shared libraries but that is known to be
slower due to late binding every time a new server process starts PHP.

I haven't been keeping up with the php scene much tho - still using perl.
Perl is respectably fast compared to C++. For example string concats
weren't much slower compared to C++. But it's about 13 times slower for
floating point stuff. That's not a big problem for most of my apps tho ;).

Zend engine gave a major boost to PHP. With the optimizer, for pure
processing tasks can run as much as 16 times faster under Unix. Under
Windows with the new SAPI based ISAPI drivers the difference can be even
higher.

Anyway, pure processing tasks are not the most common use of PHP database
and network I/O are more often the bottleneck.

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--