Section: Profiles and configuration
| ^ jDb: accessing to SQL database | Executing queries » |
− Table of content
To be able to access a database, you need of course to specify the connection parameters
in a configuration file. This file is profiles.ini.php in var/config/. See
the corresponding chapter to know how to use this
file.
You can define several connections named profiles. Thus, you can define connection for the production database, development database, or also the different bases on which lies your application.
Here is an example in profiles.ini.php file:
[jdb:default]
driver="mysqli"
database="jelix"
host= "localhost"
user= "jelix"
password= "jelix"
persistent= on
force_encoding=true
The connection type to indicate in profile names is jdb.
In a section, you have several parameters. Their number and name can be different according to the driver used, but some of them can be used in all profiles.
- driver: it indicates the driver name
- table_prefix: allow to indicate a prefix for all table when using a DAO
with a profile indicating a table prefix, this prefix is added automatically to the
table names indicating in the dao files. When you construct your SQL queries, you
should use the
prefixTable()method of thejDbConnectionobject. This method takes one parameter: the table name.
mysql and mysqli profile ¶
Possible parameters:
- driver : should be "mysqli" (using the new PHP API mysqli) or "mysql" (using the old PHP API mysql, not recommended)
- database : the database name
- host : the server name
- user et password : the login and the password to use for the connection
- persistent : says if the connection should be persistent ("on") or not ("off")
- force_encoding : says if the current charset should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.
For mysqli, since Jelix 1.6.11, it is possible to configure a SSL access:
- ssl : 0 or 1. Activate or not the SSL connection
- ssl_key_pem : path to the private SSL key
- ssl_cert_pem : path to the SSL certificat
- ssl_cacert_pem : path to the certificat of the certificat authority.
Note: prefer to use mysqli, as the API used by the "mysql" plugin has been deprecated into PHP.
postgresql profile ¶
Possible parameters:
- driver : should be "pgsql"
- database : the database name
- host : the server name. If you give an empty value, the connection will be set over an unix socket.
- port : TCP port to use for the connection. Don't indicate this parameter if you want to use the default port.
- user and password : the username and the password to use for the connection. Don't indicate this parameters if you want to use the default user/password indicated in environment variable in the operating system.
- service: the name of the postgresql service (so database, host, port, user and password should not be set). Supported since Jelix 1.6.6.
- persistent : says if the connection should be persistent ("on") or not ("off")
- force_encoding : says if the current character set should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.
- timeout : Number of second allowed before a timeout.
- single_transaction : if set to on, all queries executed in a same page will be sent in a same transaction (between a BEGIN; and a COMMIT;). Default: off
- search_path: the list of schema where table are getting from, if the default schema of the connection doesn't correspond to the schema used by the application
- pg_options: additionnal connections parameters, like
--application_name=foo
sqlite3/sqlite profile ¶
Possible parameters:
- driver: should be "sqlite3" (or "sqlite" for legacy sqlite2 databases)
- database: the database file.
- persistent: says if the connection should be persistent ("on") or not ("off")
- extensions: list of sqlite extension to load (separated by a coma)
- busytimeout: integer for the busytimeout option of Sqlite
For the database file, you can use 3 syntaxes:
- a simple file name (ex: mybase.sqlite). The file will be in the
var/db/sqlite3/directory (for sqlite3) or invar/db/sqlite/(for sqlite 2). - a partial path + filename, using a shorcut like "app:", "lib:" or "var:" to indicate a path inside the application directory, the lib directory, or the var directory. ex: "app:../db/mybase.sqlite".
- a full path on the file system (Jelix 1.6.4+). Ex: /opt/foo/bar.sqlite.
Do not forget that these files and directories must have read and write permissions for your webserver user.
PDO profile ¶
You can indicate to use PDO for the connection, by just adding the parameter usepdo=on. You should then have at least these parameters:
- driver: the PDO driver name
- host, user, password (except for sqlite of course)
- database: the database name. To indicate the path for a sqlite database, you can use shortcuts like "app:", "lib:" or "var:" to indicate path of the application, the lib directory or the var directory.
- force_encoding: says if the current charset should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.
JDb will then build the corresponding DSN for PDO.
If you need to specify specific parameters into the DSN, you should use an other notation, without using the parameter usepdo:
- driver: should be "pdo"
- dsn: contains all parameters for the connection as indicated in the PDO documentation on php.net.
- user et password: the login and the password to use for the connection, if needed.
- force_encoding
Example:
[jdb:bar]
driver=pdo
dsn= "mysql:host=localhost;dbname=test"
user=
password=
mssql/sqlsrv profile ¶
Possible parameters:
- driver : should be "sqlsrv" ("mssql" is deprecated)
- database : the database name
- host : the server name.
- port : TCP port to use for the connection.
- user and password : the username and the password to use for the connection.
- force_encoding : says if the current character set should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.
oci profile ¶
Possible parameters:
- driver : should be "oci"
- persistent: says if the connection should be persistent ("on") or not ("off")
- user and password : the username and the password to use for the connection.
- force_encoding : says if the current character set should be specified during the connection. Try to set it to "on" if you have some encoding issues with your retrieved data.
For connection parameters, provide a connection string :
- **dsn*: the connection string
Or separate parameters:
- database : the database name
- host : the server name.
- port : TCP port to use for the connection.
