|
mpf:db
 | querying MySQL-, PostgreSQL-, Sybase-, ODBC-, ADODB- and other sources using structured query language (SQL) |
| integrated functionality for transactions and persitent (vs. transient) database connections |
| | |
|
| = | [myname]   | |
records may be accessed using a token with the following structure:
{mpf:myname/myattrib}
|
| || | name=[mysqldb] | |
database name is required by MySQL database driver only
|
| || | sql=[mysqlstatement] | |
you may send several sql-statements at a time by using this parameter several times
|
| || | driver=[mysql|odbc|ado|jdbc|...] | |
available database driver types:
| mysql | MySQL-databases |
| msql | Msql-databases |
| sybase | Sybase-databases |
| postgre | PostgreSQL-databases |
| oracle | Oracle-databases |
| odbc | ODBC-sources |
| adodb | ADODB-sources (available on windows platform only) |
| jdbc | JDBC-source (requires JRE/JDK and mpfserver.jar) |
|
| || | host=[myhost] | |
pass the following information using this parameter:
| host for MySQL databases |
| driver information for ADODB sources |
| data source name (DSN) for ODBC-sources |
|
| || | username=[myusername] | |
ask database administrator, if database user name must be set
|
| || | password=[mypassword] | |
ask database administrator, if password must be set
|
| || | persitent | |
persistent connections are currently supported by MySQL- and ODBC-databases (not via ADODB)
|
| || | transaction | |
parameter will be ignored if transactions are not supported by database-driver
|
| || | extract | |
extract all sql statements out of a string containing several of them
|
| || | maxrows=[mynumber] | |
you may restrict the maximum number of records that will be returned
|
| || | recspp=[mynumber] | |
do not forget to implement functionality for page-navigation if this parameter is set
|
| || | page=[mynumber] | |
do not forget to implement functionality for page-navigation if this parameter is set
|
| || | cache=[mysize] | |
cache size is supported by ADODB-sources only
|
| || | class=[classname] | |
name of database-driver class to be loaded for database connection
| ODBC | sun.jdbc.odbc.JdbcOdbcDriver |
| cloudscape | COM.cloudscape.core.JDBCDriver |
| mysql | org.gjt.mm.mysql.Driver |
| ORACLE | oracle.jdbc.driver.OracleDriver |
|
| || | classes=[classpaths] | |
physical location of database-driver class file:
E:/@webservices/@mpfServer-2.0.8/java/mm.mysql.jar
| if you declare several classes to load, separate them with ";" |
|
| || | type=[detail] | |
you may use this parameter if resulting recordset is expected to hold a single record
|
| || | key=[primary key] | |
you may use this parameter to define key for list items - numeric keys will be used if this parameter is missing
|
| | | | |
SQL (structured query language): known restrictions
| you must not use ASC and DESC as names for database table attributes/columns, because they are reserved by SQL for sorting |
| if you want to handle attribute-/column- in returned resultset/recordset by JavaScript, then they must not contain empty spaces and special characters like ;,! |
send a batch-task to database
you may pass several select-statements to databases:
sql=INSERT INTO adressen (ProductName) VALUES ('Hello');||sql=SELECT * FROM adressen;
| if you pass several select-statements only one recordset can be returned - make sure that your "SELECT"-statement for the resultset is the last statement in list so "record_count" and "page_count" have correct values |
use transactions to have defined result
| turn on transaction mode (if supported by database-driver) if you want to make sure that all SQL-statements are executed only if none of them caused an error |
| if a transaction rollback occured and you have a "SELECT"-statement in this transaction - please keep in mind that this statement shows result before rollback, because it is part of this transaction (therefore it is better not do use "SELECT"-statements within transactions) |
transaction support:
| ADODB-sources | yes, if supported by database |
| ODBC-sources | yes, if supported by database |
| MySQL-databases | not supported |
accessing resultset/recordset
| resultset is available only if you send "SELECT"-statement to database (not available for INSERT,UPDATE,DELETE) |
| use name of your database (MyDB) to access recordset/resultset |
| the first pair of tokens will initialize the list (use identifier attribute for this to make sure all records will be shown) |
accessing resultset/recordset meta-data
| {mpf:mydatabase/record_count} | number of records found |
| {mpf:mydatabase/page_count} | number of pages found |
| {mpf:mydatabase/page_no} | actual page |
| {mpf:mydatabase/[...]} | any other attribute from select-statement |
using page-partitioning functionality
| "recspp"-parameter must be set (number of records is limited by "maxrecs"-parameter only, if "recspp"-parameter is missing) |
| to go to next, previous or any other page in recordset pass page-number to "page"-parameter |
| do not forget to define what to do if previous page is zero (and therefore does not exist) |
| do not forget to define what to do if this is last page (and therefore there is no next page) |
| 
Examples 
