TbsSQL - documentation

Presentation
SQL statements with placeholders
Synopsis
Hook for TinyButStrong template engine
Deprecated
License
Change log

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);
In this example, the final SQL statement sent to the database will be: UPDATE table1 SET name='boby' WHERE (id=29)

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);
In this example, the SQL statement sent to the database will be: UPDATE table1 SET fd=NULL WHERE (id=29)

Versioning: Jokers for nullable values are supported since TbsSQL version 2.5.

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 your connection.

$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.

3.2 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. Note: This method is not available for ODBC Generic.
$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.3 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 false, which means the cache feature is disabled. Method Execute() is not concerned by the cache feature. You can use constants TBSSQL_1HOUR, TBSSQL_1DAY or TBSSQL_1WEEK in order to easier define the timeout. Use property TempCacheTimeout to force the cache for only one query, whether default cache is enabled or not. Example: $Db->CacheTimeout = 2 * TBSSQL_1DAY; this does enable the cache for all queries with a cache duration of one day.
$Db->TempCacheTimeout	Define the cache timeout in minutes but only for the next query. You can use this property to active the cache for only one query, or to change the cache timeout for a only one query. Default value is false, which means there is no special timeout for the next query. 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. Example: $Db->TempCacheTimeout = TBSSQL_1DAY; $x = $Db->GetRows('SELECT id, name FROM table1 ORDER BY id');
$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.4 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 do 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

TbsSQL is compatible with the TinyButStrong Template Engine.
Use the property $Db->TbsKey in order to have the MergeBlock() method using your TbsSQL instance.
Example : $TBS->MergeBlock('b',$Db->TbsKey,'SELECT * FROM table1');

Versioning: before version 2.5, property $Db->TbsKey doesn't exist and the TBS key is the same for all TbsSQL instance. You must use the keyword 'tbssql' instead of $Db->TbsKey.

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

$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 3.0

Summary

1. Presentation

2. SQL statements with placeholders

3. Synopsis

3.1 Connecting to the database

3.2 Working with SQL

3.3 Using the cache feature

3.4 Working with TinyButStrong template engine

4. Hook for TinyButStrong template engine

5. Deprecated

6. License

7. Change log