Re: [GENERAL] Connecting website with SQL-database.....
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
Re: [GENERAL] Connecting website with SQL-database.....
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/?[EMAIL PROTECTED] -- E-mail: [EMAIL PROTECTED] URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --
Re: [GENERAL] Connecting website with SQL-database.....
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.
Re: [GENERAL] Connecting website with SQL-database.....
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 (-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 drive
Re: [GENERAL] Connecting website with SQL-database.....
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 (-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. >> >> user_id >> 1 >>users user_id >> >> >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.
Re: [GENERAL] Connecting website with SQL-database.....
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 (-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: > > > > > test > 1 > > > users > > user_id integer 0 >1 > user_name text > > password text > > reminder text > > name text > > email text > > > users_id_index > 1 > user_id > > > > > > user_id > 1 >users user_id > > 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
Re: [GENERAL] Connecting website with SQL-database.....
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 Coordinatortomg@ { 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
---
Re: [GENERAL] Connecting website with SQL-database.....
-> 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
Re: [GENERAL] Connecting website with SQL-database.....
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/?[EMAIL PROTECTED] -- E-mail: [EMAIL PROTECTED] URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --
