Thread: Connecting website with SQL-database.....
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???
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???
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 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 ---------------------------------------------------------------------------
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
--
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 --
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
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 --
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