Class: Bs_MsSql
Source Location: /core/db/Bs_MsSql.class.php
Bs_Object
|
--Bs_Db
|
--Bs_MsSql
This is the API for MSSQL it's an extention of the abstract class Bs_Db.class.php
Author(s):
Version:
- 4.3.$Revision: 1.1.1.1 $ $Date: 2003/07/04 14:04:37 $
Copyright:
|
|
|
Inherited Variables
|
Inherited Methods
|
Class Details
[line 136]
This is the API for MSSQL it's an extention of the abstract class Bs_Db.class.php A good understanding of the built in MSSQL functions in php is needed to create clean, good code using these classes. please RTFphpM first. here i'll try to avoid copying the php doc over and over again. known problems/bugs: 1) 2002-02-27 --andrej SYSTEM CONFIG: php running on the local workstation, connecting to the in-house sql-server. workstation: os : winnt 2000 pro (workstation) build 2195 php : version 4.1.1 php_mssql.dll : 44kb (45056 bytes) mssql: sql server enterprise manager (version unknown) server: os : winnt 2000 server mssql : mssql 2000 i think. PROBLEM: i was connecting using php code to the sql server. everything worked fine. then after issueing the query "SELECT name FROM master.dbo.sysdatabases ORDER BY name" it would not connect anymore. actually i think it was already short before doing this one, i cannot tell anymore. so i dunno if it matters. i went into the the sql server enterprise manager, and was able to re-connect using exactly the same dsn-info. so the sql server was fine. what helped was restarting apache. crap. ########################################################################### see http://www.php.net/manual/en/function.mssql-connect.php for all this and much more... --------------------------------------------------------------------------- Remember, you must build Linux/Unix PHP with Sybase support in order to get MSSQL support. ./configure --with-sybase=/opt/sybase-11.9.2/ For Linux, you can get the required "Adaptive Server Enterprise" Sybase RPMs from http://www.sybase.com/products/databaseservers/linux/ You should then put any MSSQL database you want to connect to in the /opt/sybase-11.9.2/interfaces file, in the format EXAMPLE_Host query tcp ether IP_OR_FQDN PORTNUMBER Where PORTNUMBER is usually (but not always) 1433 In your code, refer to the EXAMPLE_Host in your connect string like this; mssql_connect("EXAMPLE_Hostname","username","Password") or die("Connection error"); --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ###########################################################################
Quick overview of the features of MSSQL
----------------------+------------------------------------------------------------------------
available | comments
----------------------+------------------------------------------------------------------------
transactions | none
fulltext indexing | none
subselects | none
stored procedures | none
triggers | none
foreign keys | none
views | none
----------------------+------------------------------------------------------------------------
not available | comments
----------------------+------------------------------------------------------------------------
'foreign chars' | No support for spaces, foreign and special chars, and reserved words
| in field, table and database names. MS-Access and Oracle support this.
The following php MSSQL functions are *NOT* implemented here because we don't want you to use them: - mssql_fetch_object() => Use fetchRow() which makes use of mysql_fetch_assoc() or mysql_fetch_row()
- mssql_fetch_array() => Use fetchRow() which makes use of mysql_fetch_assoc() or mysql_fetch_row()
- mssql_result() => as you can reas in the manual, this function is not really recommended.
also see the MySql implementation. Also see Bs_Db->getOne()
Tags:
Class Variables
Class Methods
constructor Bs_MsSql [line 195]
method affectedRows [line 556]
Gets the number of rows affected by the data manipulation query. For other queries, this function returns FALSE. RTFM for mysql_affected_rows() !!! If the last query was a DELETE query with no WHERE clause, all of the records will have been deleted from the table but this function will return zero. hint I: use "DELETE FROM tbl WHERE 1=1" hint II: maybe you are looking for "TRUNCATE TABLE table_name" anyway.
Tags:
Overrides Bs_Db::affectedRows() (Gets the number of rows affected by the last data manipulation query.)
method autoCommit [line 234]
last autoCommit(
[bool
$on = FALSE])
|
|
Turn autoCommit on or off
Tags:
Overrides Bs_Db::autoCommit() (Turn autoCommit on or off)
Parameters:
method commit [line 272]
TRUE commit(
[$transactionId
$transactionId = ''])
|
|
Commit the current or given transaction.
Tags:
Overrides Bs_Db::commit() (Commit last query)
Parameters:
method connect [line 323]
int connect(
$dsn
$dsn, [$persistent
$persistent = TRUE])
|
|
Connect to a database server and log in as the specified user. use (select) the given db, if any. NOTE I: If we are already connected to somewhere (no matter where), the existing db connection gets closed first. NOTE II: It's possible to get back an exception but the connection was successfull. Probably the given default database could not be selected => BS_DB_ERROR_CANNOT_SELECT_DB
Tags:
Parameters:
method databaseExists [line 1255]
bool databaseExists(
mixed
$dbName, [bool
$useCache = TRUE])
|
|
Tells whether the database (or databases) exist or not. NOTE I: Databases and tables are file-based in mysql. So they are case sensitive, except on winblows. So for windows we have to do a case insensitive check. NOTE II: This method makes use of fetchDatabaseNames(). So if param $useCache is set to TRUE and fetchDatabaseNames() has been called before, the cached values will be used.
Tags:
Parameters:
method databaseName [line 671]
string databaseName(
resource
$result, int
$offset)
|
|
Get the name of the specified db in a result you got from mysql_list_dbs(). This is a wrapper for mysql_db_name(). I don't think you ever need this method ...
Tags:
Parameters:
method disconnect [line 390]
Log out and disconnect from the database. Note: mssql_close() will not close persistent links (created by mssql_pconnect()).
Tags:
method escapeString [line 1921]
string escapeString(
string
$query)
|
|
Escape string for mssql query. This method overwrites its parent method because stupid mssql wants non-standard escaping.
Tags:
Overrides Bs_Db::escapeString() (Escape string for the query.)
Parameters:
method fetchDatabaseNames [line 921]
mixed &fetchDatabaseNames(
[string
$format = 'vector'], [bool
$useCache = TRUE])
|
|
Returns the available db names. If successfull the data will be cached for further calls. NOTE: You'll see all db's even if you don't have the rights to access them. The returned value is dependent on the $format param. E.g.: 'vector' => array(db1, db2, db3) 'string' => 'db1, db2, db3'
Tags:
Parameters:
method fetchField [line 1106]
object fetchField(
resource
$result, [int
$offset = NULL])
|
|
Returns an object containing field information (of the table structure). This has nothing to do with fetching data from a field, like fetchRow(). You might be looking for something like php's mssql_result(), which is not supported here for reasons mentioned in the header. See comments of unimplemented functions in the head of this file. This is a wrapper for mssql_fetch_field().
Tags:
Parameters:
method fetchFieldNames [line 1010]
&mixed &fetchFieldNames(
string
$tblName, [mixed
$dbName = NULL], [string
$format = 'vector'], [bool
$useCache = TRUE], string
$dbName.)
|
|
Return the field names of the given database table. If successfull the data will be cached for succeeding calls. NOTE: Fields may exist but if you don't have the perms, you may not see them! (that's how mysql is built, not sure about mssql.) The returned value is dependent on the $format param. E.g.: 'vector' => array(field1, field2, field3) 'string' => 'field1, field2, field3' we are using comma instead of space here because some db's allow you to use spaces inside of field names.
Tags:
Parameters:
method fetchRow [line 484]
mixed &fetchRow(
$result
$result, [int
$fetchMode = BS_DB_FETCHMODE_ASSOC])
|
|
Fetch a row and return it as vector or hash depending on the fetchMode. Subsequent calls will return the next row in the result set, or NULL if there are no more rows. If fetchMode is BS_DB_FETCHMODE_ASSOC (default), it returns a hash array that corresponds to the fetched row else if it's BS_DB_FETCHMODE_ORDERED returns a vector starting at offset 0. A clean example usage of this method to fetch rows into an array: while ($row = $this->fetchRow($res, $fetchMode)) { if (isEx($row)) { $row->stackTrace('was here: currentFunction()', __FILE__, __LINE__); return $row; } $ret[] = $row; }
Tags:
Overrides Bs_Db::fetchRow() (Fetch a row and return it as vector or hash depending on the fetchMode.)
Parameters:
method fetchTableNames [line 962]
mixed &fetchTableNames(
[mixed
$dbName = NULL], [string
$format = 'vector'], [bool
$useCache = TRUE], string
$dbName.)
|
|
Return the table names of the given database. If successfull the data will be cached for further calls. NOTE: Tables may exist but if you don't have the perms, you won't see them! The returned value is dependent on the $format param. E.g.: 'vector' => array(table1, table2, table3) 'string' => 'table1, table2, table3'
Tags:
Parameters:
method fieldExists [line 1140]
bool fieldExists(
mixed
$fieldName, string
$tableName, [mixed
$dbName = NULL], [bool
$useCache = TRUE], string
$dbName.)
|
|
Tells whether the db field (or fields) exist or not. NOTE I: Fields are not case sensitive in mysql (dbs and tables are, except on windows). This check is made case sensitive! So if your field is called 'myField', you'll get FALSE for 'myfield'. NOTE II: This method makes use of fetchFieldNames(). So if param $useCache is set to TRUE and fetchFieldNames() has been called before, the cached values will be used.
Tags:
Parameters:
method fieldFlags [line 725]
mixed fieldFlags(
resource
$result, int
$offset, [string
$format = 'string'])
|
|
Get the flags of a field at the specified offset from a result id. This is a wrapper for mysql_field_flags(). The returned value is dependent on the $format param. E.g.: 'string' => 'not_null primary_key blob' 'vector' => array('not_null', 'primary_key', 'blob') 'hash' => array('not_null' => TRUE, 'primary_key' => TRUE, 'unique_key' => FALSE, ...) Please check the docs for mysql_field_flags to get a list of the current flags supported. By now, they are: (***IF THEY HAVE CHANGED, PLEASE UPDATE THE CODE OF THIS METHOD!***) "not_null", "primary_key", "unique_key", "multiple_key", "blob", "unsigned", "zerofill", "binary", "enum", "auto_increment", "timestamp". I have read somewhere that the 'flags' blob, enum and timestamp are deprecated because they are not flags but field types. You're descouraged to use them.
Tags:
Parameters:
method fieldLen [line 691]
int fieldLen(
resource
$result, int
$offset)
|
|
Returns the length of a field at the specified offset from a result id. NOTE: Not the actual value length but the max possible field space: E.g. if a varchar(20) has the value 'foobar' it returns 20 not 6. This is a wrapper for mysql_field_len() AND mysql_fieldlen() .
Tags:
Parameters:
method fieldName [line 609]
string fieldName(
resource
$result, int
$offset)
|
|
Get the field name at the specified offset from a result id. This is a wrapper for mssql_field_name().
Tags:
Parameters:
method fieldType [line 820]
string fieldType(
resource
$result, [int
$offset = 0])
|
|
Get the type of a field at the specified offset from a result id. This is a wrapper for mssql_field_type(). -------------------------------------------------------------------------- CAUTION: mysql used to return wrong names. maybe mssql does the same? please verify. (from mysql:) +----------------------------+ | mysql type | returned as | - ------------+--------------+
| tinyint | int | // php4.0.4pl1 used to return unknown. Corrected in php4.0.5 | smallint | int | | mediumint | int | | int | int | | bigint | int | | float | real | | double | real | | decimal | real | | date | date | | datetime | datetime | | timestamp | timestamp | | year | year | // php4.0.4pl1 used to return unknown. Corrected in php4.0.5 | char | string | | varchar | string | | tinyblob | blob | | blob | blob | | mediumblob | blob | | longblob | blob | | enum | string | | set | string | +----------------------------+ --------------------------------------------------------------------------
Tags:
Parameters:
method freeResult [line 594]
void freeResult(
$result
$result)
|
|
Free the internal resources associated with $result. Only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.
Tags:
Overrides Bs_Db::freeResult() (Free the internal resources associated with $result.)
Parameters:
method getClientInfo [line 1865]
Returns a string that represents the client library version. This method is a wrapper for 'string mysql_get_client_info(void)' and was made available in php405.
Tags:
method getDbStructure [line 1068]
hash &getDbStructure(
[mixed
$dbName = NULL], [bool
$useCache = TRUE], string
$dbName.)
|
|
Return a 2-D array of tables and fieldnames found in the given db. If successfull the data will be cached for succeeding calls. NOTE: Tables and fields may exist but if you don't have the perms, you won't see them! The returned value sample: array( 'table_1' => array('field_1', 'field_2', 'field_3'), 'table_2' => array('field_A', 'field_B', 'field_C', 'field_D') );
Tags:
Parameters:
method getHostInfo [line 1879]
Returns a string describing the type of connection in use, including the server host name. This method is a wrapper for 'string mysql_get_host_info([int link_identifier])' and was made available in php405.
Tags:
method getIniVar [line 1746]
mixed getIniVar(
[string
$key = NULL], [bool
$useCache = TRUE])
|
|
Return the value of the currently used ini setting var. as of mySQL 3.23.36, the following 76 vars are available (with example values): timezone: i have seen the following values 'CEST' 'W. Europe Daylight Time' 'Westeuropõische Sommerzeit' i think that the 'õ' was a 'ä' in the database but was spitted out incorrectly in my telnet session. this makes it hard to find out the used timezone. not for a human, but for a peace of code. this method uses the 'SHOW VARIABLES' syntax.
Tags:
Parameters:
method getOpenTables [line 1593]
mixed getOpenTables(
[mixed
$dbName = NULL], [string
$return = 'vector'], string
$dbName.)
|
|
Returns the currently open tables in $db. This is somehow similar to fetchTableNames() but only returns the open ones. NOTE: Tables may exist and be open but you don't have the perms so you won't see them! This method uses the mySQL 'SHOW OPEN TABLES' syntax. the returned value looks like: 'vector' => array('table1, table2, table3') 'string' => 'table1, table2, table3' 'extended' => array(array('name' => 'table1', 'cached' => '1', 'in_use' => '0'), array('name' => 'table2', 'cached' => '5', 'in_use' => '1')) As you can see, also the numbers are returned as strings, not as int. If the method was not able to read out a number (speaking of cached and in_use), a question mark '?' will be there instead.
Tags:
Parameters:
method getProtocolInfo [line 1893]
Returns the protocol version used by current connection. This method is a wrapper for 'int mysql_get_proto_info([int link_identifier])' and was made available in php405.
Tags:
method getServerInfo [line 1907]
Returns a string that represents the server version number. This method is a wrapper for 'string mysql_get_server_info([int link_identifier])' and was made available in php405.
Tags:
method getStatusVar [line 1837]
mixed getStatusVar(
[string
$key = NULL], [bool
$useCache = TRUE])
|
|
Return the value of the specified status var. as of 3.23.36, the following 54 vars are available (with example values): this method uses the 'SHOW STATUS' syntax.
Tags:
Parameters:
method getTableInfo [line 1329]
array getTableInfo(
string
$tblName, [mixed
$dbName = NULL], string
$dbName.)
|
|
Returns an hash holding information about the given db table. The returned hash (for mySQL 3.23.36) returns these 15 keys (values are examples): mysql> show table status like 'test'; +------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+ | test | MyISAM | Dynamic | 4 | 22 | 88 | 4294967295 | 2048 | 0 | 5 | 2001-04-02 09:06:58 | 2001-04-02 09:07:00 | NULL | | | +------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+---------+
Tags:
Parameters:
method getTableType [line 1453]
string getTableType(
string
$tblName, [string
$dbName = NULL])
|
|
Returns the table type of the specified db table.
Tags:
Parameters:
method hasFieldFlag [line 773]
boolean hasFieldFlag(
resource
$result, int
$offset, string
$flag)
|
|
Tells whether the field has the given flag. The field is specified by offset in a result id. This method uses $this->fieldFlags() to get the data. Check it's documentation!
Tags:
Parameters:
method insertId [line 579]
Returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query on this thread. Returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call this function immediately after the query that generates the value. This method is save, thanks to rick@surveyor.com for the information :)
Tags:
Overrides Bs_Db::insertId() (Get the id generated from the previous INSERT operation.)
method isReservedWord [line 1991]
int isReservedWord(
string
$word)
|
|
Tells if a word is a reserved word for mysql. This is a 'shortcut' for => "7.39 Is MySQL Picky About Reserved Words?" and is great for something like an autmated system checkup. HINT: Make a type conversion (bool) and check for true or false on the returned value. From the manual: A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is an allowed column name), but whitespace is not allowed between a function name and the `(' when using functions whose names are also column names. The following words are explicitly reserved in MySQL. Most of them are forbidden by ANSI SQL92 as column and/or table names (for example, group). A few are reserved because MySQL needs them and is (currently) using a yacc parser:
Tags:
Parameters:
method isValidName [line 1961]
true isValidName(
string
$string)
|
|
Tells if a name for a db, table or field is accepted by mysql or not.
Tags:
Parameters:
method listDatabases [line 839]
resource listDatabases(
)
|
|
returns a result identifier for the available databases. note I: with mssql you see all db's even if you have no access to them. that may be different in other rdbms.
Tags:
method listFields [line 889]
resource listFields(
string
$tableName, [mixed
$dbName = NULL], string
$dbName.)
|
|
Retrieves information about the given tablename. This is a wrapper for mysql_list_fields(). NOTE: Fields may exist but if you don't have the perms, you won't see them!
Tags:
Parameters:
method listTables [line 860]
resource listTables(
[mixed
$dbName = NULL], string
$dbName.)
|
|
returns a result identifier for the available tables in the given database. note I: tables may exist but if you don't have the perms, you won't see them! note II: only 'U'ser tables will be listed. no 'S'ystem stuff.
Tags:
Parameters:
method nativeError [line 2075]
Returns the native error from the previous MySQL operation or '' (empty string) if no error occured. This method is a wrapper for mysql_error().
Tags:
method nativeErrorMsg [line 2061]
Returns the native error text from the previous MsSQL operation or '' (empty string) if no error occured or the error was below the min_message_severity. HINT: Better use nativeErrorCode() before calling this method, or call nativeError() directly.
Tags:
Overrides Bs_Db::nativeErrorMsg() (Returns the native error text from the previous DB operation or '' (empty string) if no error occured.)
method numCols [line 513]
int numCols(
$result
$result)
|
|
Get the number of columns (fields) from a result set.
Tags:
Overrides Bs_Db::numCols() (Get the number of columns (fields) in a result identifier.)
Parameters:
method numRows [line 531]
int numRows(
$result
$result)
|
|
Get the number of rows from a result set.
Tags:
Overrides Bs_Db::numRows() (Get the number of rows from a result identifier.)
Parameters:
method rollback [line 293]
TRUE rollback(
[$transactionId
$transactionId = ''])
|
|
Rollback last query
Tags:
Overrides Bs_Db::rollback() (Rollback last query)
Parameters:
method selectDb [line 432]
bool selectDb(
string
$db)
|
|
Select a MsSQL database. this is a wrapper for mssql_select_db().
Tags:
Parameters:
method serverSupportsTableType [line 1509]
bool serverSupportsTableType(
string
$tableType)
|
|
Tells whether the given table type is supported by the server or not. NOTE: 'MyISAM', 'MERGE' and 'HEAP' are always supported. But if you still check for them, of course TRUE is returned. The default mysql table type currently is MyISAM according to the manual. If you try to create one that's not supported or not compiled into the server, a MyISAM table will be created instead. ISAM is deprecated, it is replaced by MyISAM. BDB, INNOBASE and GEMINI are transaction-safe, the others are not. hrm. i've found an ini var called 'table_type' with the value 'MYISAM'. Prolly you can change the default table type there.
Tags:
Parameters:
method setPointer [line 1937]
mixed setPointer(
$result
$result, int
$absolutPos)
|
|
Sets the internal row pointer of the result id to point to the specified row number. The next fetch call would return that row.
Tags:
Overrides Bs_Db::setPointer() (Set the internal row pointer of the result id to point to the specified row number.)
Parameters:
method startTransaction [line 256]
TRUE startTransaction(
[$transactionId
$transactionId = ''])
|
|
Starts a transaction As optional parameter you can pass a transaction ID. When working with transactions, I run often in the situation, that I have a 'Master' routine calling other sub-routines and some sub-routines start their own transation. Multiple transaction 'starts' don't bother the Db, the transaction just stays open *BUT* the first to call commit or rollback will terminate the transaction! To prevent sub-routines from closing the 'Masters' transaction you may start a transaction and pass a 'transaction ID'. Folloing commits/rollbacks are ignored as long as it dosen't match the 'transaction ID' given at start. NOTE I: Be aware of the highter deadlocking risk, when using 'transaction ID'. NOTE II: Make sure that you end the open transaction when using 'transaction ID'. Otherwise the transaction will *stay open*.
Tags:
Overrides Bs_Db::startTransaction() (Starts a transaction)
Parameters:
method tableExists [line 1188]
bool tableExists(
mixed
$tableName, [mixed
$dbName = NULL], [bool
$useCache = TRUE], string
$dbName.)
|
|
Tells whether the db table (or tables) exist or not. NOTE I: Databases and tables are file-based in mysql. So they are case sensitive, except on winblows. So for windows we have to do a case insensitive check. NOTE II: This method makes use of fetchTableNames(). So if param $useCache is set to TRUE and fetchTableNames() has been called before, the cached values will be used.
Tags:
Parameters:
method tableHasTransactions [line 1472]
bool tableHasTransactions(
string
$tblName, [string
$dbName = NULL])
|
|
Tags:
Parameters:
method tableName [line 628]
string tableName(
resource
$result, int
$offset)
|
|
Returns the name of the table that the field (specifed by an offset) is in. This is a wrapper for mysql_field_table().
Tags:
Parameters:
method tableName2 [line 648]
string tableName2(
resource
$result, int
$offset)
|
|
Get the name of the specified table in a result you got from mysql_list_tables(). This is a wrapper for mysql_tablename().
Tags:
Parameters:
method _dbErrorToBsError [line 2140]
mixed _dbErrorToBsError(
int
$dbError)
|
|
Maps error codes of the current dbms to bs-dbErrorCodes. the way mssql is built it is hard to map our universal db errors to the mssql ones. there are so many... and i cannot find a complete list. maybe we should work with the severity only... but even the manual for that is bad imo.
Tags:
Parameters:
|
|