TbsSQL 3.1

1. Presentation
2. SQL statements with placeholders
3. Synopsis
   1. Connecting to the database
   2. Options and information
   3. Working with SQL
   4. Using the cache feature
   5. Working with TinyButStrong template engine
4. Deprecated
5. License
6. Change log

The goal of TbsSQL is to reduce the code when working with SQL through PHP. It is called a SQL abstraction tool.

The TbsSQL trumps are:
TbsSQL is available for the following databases:

The main asset of TbsSQL is that SQL statement writing is really simplified because you can use placeholders in the SQL for placing item data coming from PHP. And item data are always protected against SQL injection.

Example:

$id = 29;
$name = "boby";
$Db->Execute('UPDATE table1 SET name=@2@ WHERE (id=%1%)', $id, $name);

TbsSQL supports the following jokers in your SQL statements:
    (the following jokers are for argument number 1, but it is the same thing for argument number 2, 3, 4, ...)

-	%1%	The joker will be replaced with 1st argument protected against SQL injection.
-	@1@	The joker will be replaced with 1st argument protected against SQL injection and delimited using the relevant string delimiter of the database.
-	#1#	The joker will be replaced with 1st argument converted into a date value (without time part) using the relevant date format of the database.
-	~1~	The joker will be replaced with 1st argument converted into a date-time value using the relevant date-time format of the database.

Managing NULL values:
You can also use jokers %[1]% , @[1]@ , #[1]# and ~[1]~ to replace the value by the SQL keyword 'NULL' when the value to merge is the PHP value {false} or {null}. Please note that empty string {''} will not be replaced with the SQL keyword 'NULL'.
Example:

$id = 29;
$date = false;
$Db->Execute('UPDATE table1 SET fd=#[2]# WHERE (id=%1%)', $id, $date);

3.1 Connecting to the database

$Db = new clsTbsSQL($srv='',$uid='',$pwd='',$db='',$drv='',$mode=TBSSQL_NORMAL);	Instantiates the class. Giving connection information is optional. Versioning: optional argument $mode is supported since TbsSQL version 2.6.
$Db->Connect($srv,$uid,$pwd,$db,$drv='',$mode=TBSSQL_NORMAL)   or $Db->Connect('glob_var'); Arguments: $srv: server (or connection string for some database type) $uid: user id $db: database name $drv: driver (optional, needed for some database type) $mode: TbsSQL warning mode (see property Mode)	Open a connection to the database. Syntax 1 example: $Db->Connect('localhost','root','xxx','db_prod'); The argument $drv is needed only for some Database Systems. Syntax 2 example: $glob_var = array('srv'=>'localhost','uid'=>'root','pwd'=>'xxx','db'=>'db_prod'); $Db->Connect('glob_var'); 'glob_var' must be the name of a global variable containing a PHP array with the keys described above. The global variable is destroyed just after the connection. Versioning: syntax2 is available since TbsSQL version 2.0. Optional argument $mode is supported since TbsSQL version 2.6. Note: You don't need to call this method if your connection has already been opened before. In this case, you just have to assign the connection id to property ->Id. (example: $Db->Id = $mycn). You don't have to do this assignation when PHP can work automatically with the last opened connection. This is the case for MySQL for example. ODBC: (and SQL server with ODBC) Argument $srv can be a server name, or a connection string. You can define a DSN (Data Source Name) using the connection string: 'DSN=my_dsn'. Examples: $drv = 'my_server'; $drv = 'DSN=my_dsn' $drv = 'DRIVER={SQL Server};SERVER=my_server' $drv = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;'
$Db->Close()	Close the current connection to the database.
$Db->Id	(read/write) The resource Id of the database connection. It is automatically updated by the methods Connect() and Close(). This property can be useful: 1) When you want to use TbsSQL with a connection which is already opened. Thus you just have to set $Db->Id = $MyCurrentConnection instead of using method Connect(). 2) When you'd like to retrieve the connection id for an operation that TbsSQL cannot do. Example: $x = mysql_thread_id($Db->Id);

3.2 Options and information

$Db->Mode	(read/write) default value is TBSSQL_NORMAL. You can choose a value among the following: • TBSSQL_SILENT   TbsSQL never display messages. • TBSSQL_NORMAL   The database error message is displayed only when an error occurs. • TBSSQL_DEBUG   Both database error message and the full SQL statement are recalled but only when an error occurs. • TBSSQL_TRACE   All queries are displayed. You can also add the following values: • TBSSQL_CONSOLE   TbsSQL messages are displayed in a Html popup window. • TBSSQL_GRID   Full data results are displayed in an Html grid. Example: $Db->Mode = TBSSQL_TRACE + TBSSQL_GRID + TBSSQL_CONSOLE; Versioning: constants TBSSQL_* are supported since TbsSQL version 2.5 TBSSQL_CONSOLE and TBSSQL_GRID are supported since version 3.0
$Db->DefaultRowType	(read/write) The type of row returned by default by methods GetRow() and GetRows(). The default value is TBSSQL_ARRAY. Value   Description • TBSSQL_ARRAY   each returned row is an associative PHP array • TBSSQL_OBJECT   each returned row is a primary object (class=stdClass) • a string which is a class name   each returned row is a new instance of the corresponding class • an instance of an object   each returned row is built from a clone of the given object Please note that methods GetRow() and GetRows() have an optional argument which allow to choose the type of returned row.
$Db->ConfInfo()	Display information about the current configuration of TbsSQL. Versioning: method ConfInfo() is supported since TbsSQL version 3.1.

3.3 Working with SQL

$Db->Execute($sql [,$val1, $val2, ...])	Execute the Sql statement. Argument $sql can contain placeholders for $val1, val2, ...
$Db->GetVal($sql [,$val1, $val2, ...])	Returns the value of the first column in the first record of the query. If no record is returned by the query, then the function returns false. Argument $sql can contain placeholders for $val1, val2, ...
$Db->GetRow([$rowtype,] $sql [,$val1, $val2, ...])	Returns the first record returned by the query. Return false in case of no record. You can precise the row type for this query using argument $rowtype. See property DefaultRowType for accepted values. If argument $rowtype is omitted, then the returned row type depends of property DefaultRowType. Argument $sql can contain placeholders for $val1, val2, ...
$Db->GetRows([$rowtype,] $sql [,$val1, $val2, ...])	Returns all records returned by the query. Return an empty array in case of no record. You can precise the row type for this query using argument $rowtype. See property DefaultRowType for accepted values. If argument $rowtype is omitted, then the returned row type depends of property DefaultRowType. Argument $sql can contain placeholders for $val1, val2, ...
$Db->GetList($sql [,$val1, $val2, ...])	Returns a PHP array with first column as keys and second column as values. If only one columns is given by the query then they the values of the array. Example: $Db->GetList('SELECT id,name FROM t_people'); will return something like: array(1=>'Peter', 2=>'Dave', 3=>'Jane' ,...) Argument $sql can contain placeholders for $val1, val2, ...
$Db->GetSql($sql [,$val1, $val2, ...])	Returns the SQL statement with merged arguments. Argument $sql can contain placeholders for $val1, val2, ...
$Db->LastRowId()	Returns the value of the identifier generated by the last INSERT query of your connection. Notes: - This method is not available for ODBC Generic. - For Oracle databases, you must precise the sequence name as the first argument of the method. For example: $x = $Db->LastRowId('my_seq');
$Db->AffectedRows()	Returns the number of rows affected by the last UPDATE or DELETE query of your connection. Note: This method is not available for ODBC Generic.

3.4 Using the cache feature

What is the TbsSQL cache feature? SQL queries results can be saved in cache files in order to save time execution when they are recalled. Cache files are available for a limited duration (timeout) in order to ensure the actualization of the data.
The TbsSQL cache feature is disabled by default. You can enable it simply by setting a positive value to property $Db->CacheTimeout.
Interesting technical notes:
- TbsSQL cache files cannot be read by users because they are PHP files.
- The TbsSQL cache feature will never provide data from another SQL query. Other tools, like ezSQL for example, can theoretically be wrong when identifying a cache file because they are identified only by a hash value (md5). Hash values are not unique ids, even if the probability of doubles is very poor.
- When the cache is enabled, TbsSQL will (by default) automatically get rid of cache files that come from old unused queries.

$Db->CacheTimeout	Defines the cache timeout in minutes for all SQL queries called by methods GetVal(), GetRow(), GetRows() and GetList(). The default value is TBSSQL_DISABLED (or value false for TbsSQL prior to 3.1), which means the cache feature is disabled. Note that method Execute() is not concerned by the cache feature. The simplest way to define the timeout is to use constants: TBSSQL_DISABLED (since TbsSQL version 3.1), this constant is the value false, it makes TbsSQL no neither read or write the cache. TBSSQL_ALWAYS (since TbsSQL version 3.1), this constant is the value 0, it forces TbsSQL to always save the cache for queries. or combine the following other constants: TBSSQL_1WEEK TBSSQL_1DAY TBSSQL_1HOUR TBSSQL_1MINUTE (since TbsSQL version 3.1) Use property TempCacheTimeout to force the cache for only one query, whether default cache is enabled or not. Example: $Db->CacheTimeout = TBSSQL_1WEEK + 2 * TBSSQL_1DAY; this does enable the cache for all queries with a cache duration of one week and two days.
$Db->TempCacheTimeout	Define the cache timeout in minutes but only for the next query. You can use this property to enable, disable or change change the cache timeout for a only one very query. Default value is TBSSQL_DISABLED (or value false for TbsSQL prior to 3.1), which means there is no different timeout for the next query. Example: $Db->TempCacheTimeout = TBSSQL_1DAY; $x = $Db->GetRows('SELECT id, name FROM table1 ORDER BY id'); If the cache is enabled (i.e. CacheTimeout > 0), you can disable the cache only for the next query by setting TempCacheTimeout to TBSSQL_NOCACHE
$Db->CacheAutoClear	When the cache is enabled, TbsSQL can try to delete old cached queries in the cache directory. The default value is TBSSQL_1WEEK.
$Db->CacheDir	This property defines the directory where cached queries are stored. The default value is '.'.
$Db->CacheSuffix	Default value is '' (empty string). This property enables you to add a suffix in the cache file names. You do not need it most of the time. It is designed to separate the cached results for the same SQL queries string, for example when a project may use the same queries in two different Databases.
$Db->CacheTimestamp($sql)	Returns the timestamp of the cache file corresponding to $sql. Returns false if the SQL has no cache file.
$Db->CacheFileName($sql)	Returns the file path of the cache file corresponding to $sql. Returns false if the SQL has no cache file.
$Db->CacheDelete($sql)	Try to delete the cache file corresponding to $sql. Returns true if succeed, otherwise returns false.

3.5 Working with TinyButStrong template engine

$Db->TbsKey

(read) Return the TinyButStrong key string for the current connection (see chapter Hook above). This property has always a default value which is unique for each new instance. Most of the time there is no need know the value of this property, since you can use the property directly with TBS. Example: $TBS->MergeBlock('b',$Db->TbsKey,'SELECT * FROM table1'); Versioning: this property is available since TbsSQL version 2.5

$Db->SetTbsKey($key)

Change the value of property $Db->TbsKey. Most of the time, there is no need to use this method because property TbsKey has always a default value which is unique. Use this method only if your application does need a customized key value for the TinyButStrong merging. Example: $Db->SetTbsKey('myconnection'); $TBS->MergeBlock('b', 'myconnection', 'SELECT * FROM table1'); Versioning: this method is available since TbsSQL version 2.5

Those methods are deprecated in TbsSQL version 2.x, and unsupported since version 3.x

$Db->Row1($sql,...*...)	Same as $Db->GetRow()
$Db->Rows($sql,...*...)	Same as $Db->GetRows()
$Db->Value($default,$sql,...*...)	Same as $Db->GetVal() but with a default value.

TbsSQL is a free and open source. It is published under the LGPL license.

Version 3.2, 2010-10-21

- Bug fix: Information of configuration is displayed when a TbsSQL error message occurs.

Version 3.1, 2010-10-11

- Support Oracle databases.
- New methode ConfInfo() displays configuration information at any time.
- Fixed bug: clear and restore the console window when the parent window is actualized.
- Fixed bug: function _SqlDateFrmDb() may causes a PHP error "Notice: Undefined variable: x in ...tbssql_sqlserver_odbc.php on line 461".
- Enhanced configuration information displayed in trace mode.
- New constants: TBSSQL_1MINUTE, TBSSQL_DISABLED, TBSSQL_ALWAYS.

Version 3.0, 2010-08-19

- New cache feature
- Can return records as objects (as stdClass instances, or as your own class instances or as clones of any instances)
- Can return trace in a console, and can trace data in a grid
- New property Version (is it useful?)
- More compatibility with PHP 4
- Can handle dates over the 32bit limits (1970-2038) if PHP => 5.2
- Trace mode now works when TbsSQL is used within TinyButStrong

Version 2.6, 2009-11-26

- Argument $Mode has been added to the class instantiation and to method Connect().
This allows to display error message and trace when the connection is made.
- Display the connection string in trace mode (Mode=TBSSQL_TRACE)
- New database support : ODBC (generic), for both Windows and Unix)
- Support UnixODBC for ODBC Generic and ODBC for SQL Server.
- Use canonical formats for date and time with ODBC Generic and ODBC for SQL Server.
- Support ODBC connection string with ODBC and ODBC Generic for SQL Server.
- Do not use @ with odbc_connect() because some error of connection can make a PHP critical error.
In this case @ avoid any error and warning messages which are uncomfortable.

Version 2.5, 2009-09-16

- New set of jokers that supports NULL values for SQL.
- constants that can be used for property $Db->Mode :
TBSSQL_SILENT
TBSSQL_NORMAL
TBSSQL_DEBUG
TBSSQL_TRACE
- Bug for TbsSQL with MySQL : each TbsSQL instance was always linked to the
last MySQL connection. That was because property $Db->Id was always set to True
instead of the MySQL connection resource.
- New property $Db->TbsKey : enables you to use several instances of TbsSQL with TinyButStrong.
- New method $Db->SetTbsKey() : enables you to customize instances of TbsSQL with TinyButStrong.