Thread

1. Connecting website with SQL-database.....

   J.Post <j.post@dtx.nl> — 2000-04-17T14:26:41Z

   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???
   
   
   
   
   

2. Re: Connecting website with SQL-database.....

   Lamar Owen <lamar.owen@wgcr.org> — 2000-04-17T14:50:05Z

   "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
   
   

3. Re: Connecting website with SQL-database.....

   Manuel Lemos <mlemos@acm.org> — 2000-04-17T14:56:26Z

   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
   --
   
   
   

4. Re: Connecting website with SQL-database.....

   Margarita Barvinok <brita@umich.edu> — 2000-04-17T15:48:57Z

   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
   ---------------------------------------------------------
   
   
   
   
   

5. Re: [SQL] Connecting website with SQL-database.....

   Jim Caley <caley@chesco.com> — 2000-04-17T16:22:15Z

   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:
   > 
   > 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???
   
   

6. Re: Connecting website with SQL-database.....

   Joe Shevland <shevlandj@kpi.com.au> — 2000-04-17T23:09:22Z

   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
   
   

7. Re: Connecting website with SQL-database.....

   Titus Brown <titus@caltech.edu> — 2000-04-18T00:17:48Z

   -> 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
   
   

8. Re: Connecting website with SQL-database.....

   Thomas Good <tomg@admin.nrnet.org> — 2000-04-18T11:42:25Z

   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      
   ---------------------------------------------------------------------------
   
   
   

9. psql & java

   Frank Bax <fbax@execulink.com> — 2000-04-18T16:18:50Z

   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
   
   

10. Re: psql & java

    Peter T Mount <peter@retep.org.uk> — 2000-04-18T17:18:49Z

    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
    
    
    

11. Re: Connecting website with SQL-database.....

    Manuel Lemos <mlemos@acm.org> — 2000-04-18T17:39:54Z

    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
    --
    
    
    

12. Re: psql & java

    Guillaume Rousse <guillaume.rousse@univ-reunion.fr> — 2000-04-18T17:46:48Z

    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 Runion
    
    Sleep doesn't exists. Just lack of cafeine.
    
    

13. Re: psql & java

    Joe Shevland <shevlandj@kpi.com.au> — 2000-04-19T01:11:18Z

    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."
    
    

14. Re: Connecting website with SQL-database.....

    alex@pilosoft.com — 2000-04-19T04:08:04Z

    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
    
    
    

15. Re: Connecting website with SQL-database.....

    Manuel Lemos <mlemos@acm.org> — 2000-04-19T07:47:06Z

    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
    --
    
    
    

16. Re: Connecting website with SQL-database.....

    Lincoln Yeoh <lylyeoh@mecomb.com> — 2000-04-20T02:59:07Z

    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.
    
    
    
    

17. Re: Connecting website with SQL-database.....

    Manuel Lemos <mlemos@acm.org> — 2000-04-21T23:02:00Z

    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
    --
    
    
    

18. Re: Connecting website with SQL-database.....

    Lincoln Yeoh <lylyeoh@mecomb.com> — 2000-04-24T02:37:20Z

    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.
    
    
    

19. Re: Connecting website with SQL-database.....

    Manuel Lemos <mlemos@acm.org> — 2000-04-24T06:32:30Z

    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
    --
    
    
    

20. Re: Connecting website with SQL-database.....

    Ed Loehr <eloehr@austin.rr.com> — 2000-04-24T19:10:21Z

    Manuel Lemos wrote:
    > 
    > >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.
    
    The problem on this, from my perspective, is that if you have a transaction
    that consists of 20 queries, and the 14th query fails, then you'd like to
    be able to abort only the *statement*, recovering in the client application
    in whatever way seems appropriate.  Automatically *forcing* a rollback
    takes that recovery choice away.
    
    Regards,
    Ed Loehr
    
    

21. Re: Connecting website with SQL-database.....

    Manuel Lemos <mlemos@acm.org> — 2000-04-25T05:31:00Z

    Hello Ed,
    
    On 24-Apr-00 17:10:21, you wrote:
    
    >Manuel Lemos wrote:
    >> 
    >> >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.
    
    >The problem on this, from my perspective, is that if you have a transaction
    >that consists of 20 queries, and the 14th query fails, then you'd like to
    >be able to abort only the *statement*, recovering in the client application
    >in whatever way seems appropriate.  Automatically *forcing* a rollback
    >takes that recovery choice away.
    
    The problem is that PostgreSQL lacks of transactions check points with which
    you could do what you want.  That doesn't mean that transactions work
    differently from other DBMS.  There's just only one check point to rollback:
    the transaction begining.
    
    
    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
    --
    
    
    

22. Re: Connecting website with SQL-database.....

    Ed Loehr <eloehr@austin.rr.com> — 2000-04-25T14:54:08Z

    Manuel Lemos wrote:
    > 
    > >> >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.
    > 
    > >The problem on this, from my perspective, is that if you have a transaction
    > >that consists of 20 queries, and the 14th query fails, then you'd like to
    > >be able to abort only the *statement*, recovering in the client application
    > >in whatever way seems appropriate.  Automatically *forcing* a rollback
    > >takes that recovery choice away.
    > 
    > The problem is that PostgreSQL lacks of transactions check points with which
    > you could do what you want.  That doesn't mean that transactions work
    > differently from other DBMS.  There's just only one check point to rollback:
    > the transaction begining.
    
    I think the difference with other RDBMS that I and several others were
    highlighting was the *behavior* resulting from a failed statement within a
    transaction (i.e., aborted transaction vs. aborted statement), but I think
    your orthogonal point is still valid.
    
    Regards,
    Ed Loehr