"Paul M Foster" <paulf@xxxxxxxxxxxxxxxxx> wrote in message news:20090812035618.GD2555@xxxxxxxxxxxxxxxxxxxx > On Tue, Aug 11, 2009 at 08:23:21PM -0700, Allen McCabe wrote: > >> I have seen different scripts for working with SQL, and most follow the >> same >> method with on difference. >> >> Variables are defined (host, password, etc.) >> mysql_connect command >> >> //then, the difference >> >> mysql_select_db command >> >> //back to common >> >> $sql = "SELECT ... " >> $result = mysql_query($ql) >> >> Is the database selection necessary, or is that implied with a SELECT or >> other SQL command? > > All major SQL DBMSes can have multiple databases available. A given > database may contain a variety of tables. If you simply start firing SQL > commands at a DBMS, it won't know which database to look in unless you > tell it. By contrast, the connection process in PostgreSQL must include > a database; there is no separate database selection function call. This is not totally accurate. With MySQL you connect to a server which is a container for one or more databases, so you need select_db in order to identify the current database name. With PostgreSQL you connect to a database which is a container for one or more schemas, so you need to issue the "SET search_path TO <schema>" command in order to identify the current schema. Oracle is the same in that you connect to a server which is a container for one or more databases, and unless you give every table a public synonym you must use the "ALTER SESSION SET CURRENT_SCHEMA = <schema>" command to identify the current schema. In all these cases this will allow you to issue an sql query which contains table names which do not have to be qualified with their database/schema names. If you wish to refer to a table which is not in the current database/schema then you must include the database/schema name. I consider the use of the term "schema", as used by PostgreSQL and Oracle, to be inaccurate in that a database table is subordinate to a database, not a schema. That is why it is called a "database" table and not a "schema" table. -- Tony Marston http://www.tonymarston.net http://www.radicore.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
