ColdFusion - Bases de données

ColdFusion | Bases de données

Introduction
Cfquery
Cfqueryparam
1. Attributs du tag cfqueryparam
2. Valeurs possibles pour l'attribut « cfsqltype »

Introduction
Présenter l'utilisation des bases de données avec ColdFusion.

cfquery

xxxxxxxxxx
 
<cfquery
    name = "QUERY_VARIABLE_NAME"
    dataSource = "Nom de la source de données"
    dbtype = "QUERY|HQL"
​
    username = "Mom d'utilisateur"
    password = "Mot de passe"
​
    maxRows = "Nombre"
    blockFactor = "Taille du bloc"
    cachedAfter = "Date"
    cachedWithin = "Temps"
    timeout = "Secondes"
​
    debug = "yes|no"
    result = "RESULT_VARIABLE_NAME">
​
    ormoptions = #Structure des options ORM#

Exemple :

Considérant la table « tblSTATUS » pour cet exemple :

xxxxxxxxxx
 
+-----+------+
| ID  | CODE |
+-----+------+
| 111 | A    |
+-----+------+
| 222 | B    |
+-----+------+
| 333 | C    |
+-----+------+
| 444 | D    |
+-----+------+

xxxxxxxxxx
 
<cfquery name="myQuery" dataSource="myDataSource" result="myQuery_Result">
    SELECT id, code
    FROM tblSTATUS
    WHERE id > <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="222" />
    ORDER BY id DESC
</cfquery>
​
<cfoutput>
    myQuery.columnList : #myQuery.columnList#<br/>
    myQuery.recordCount : #myQuery.recordCount#<br/><br/>
​
    <cfdump var="#myQuery_Result#" /><br/>
​
    <cfloop query="myQuery">
        #myQuery.currentRow# | ID : #myQuery.ID# | CODE : #myQuery.CODE#<br/>
    </cfloop>
</cfoutput>

Voici le résultat affiché dans le navigateur :

myQuery.columnList : CODE,ID
myQuery.recordCount : 2

+---------------+--------------------------------------------------------------+
| struct                                                                       |
+---------------+--------------------------------------------------------------+
| SQL           | SELECT id, code FROM tblSTATUS WHERE id > ? ORDER BY id desc |
+---------------+--------------------------------------------------------------+
| COLUMNLIST    | CODE,ID                                                      |
+---------------+--------------------------------------------------------------+
| SQLPARAMETERS | array                                                        |
|               | -----                                                        |
|               | 1 222                                                        |
+---------------+--------------------------------------------------------------+
| RECORDCOUNT   | 2                                                            |
+---------------+--------------------------------------------------------------+
| EXECUTIONTIME | 1                                                            |
+---------------+--------------------------------------------------------------+
| CACHED        | false                                                        |
+---------------+--------------------------------------------------------------+

1 | ID : 444 | CODE : D
2 | ID : 333 | CODE : C

xxxxxxxxxx
 
myQuery.columnList : CODE,ID
myQuery.recordCount : 2
​
+---------------+--------------------------------------------------------------+
| struct                                                                       |
+---------------+--------------------------------------------------------------+
| SQL           | SELECT id, code FROM tblSTATUS WHERE id > ? ORDER BY id desc |
+---------------+--------------------------------------------------------------+
| COLUMNLIST    | CODE,ID                                                      |
+---------------+--------------------------------------------------------------+
| SQLPARAMETERS | array                                                        |
|               | -----                                                        |
|               | 1 222                                                        |
+---------------+--------------------------------------------------------------+
| RECORDCOUNT   | 2                                                            |
+---------------+--------------------------------------------------------------+
| EXECUTIONTIME | 1                                                            |
+---------------+--------------------------------------------------------------+
| CACHED        | false                                                        |
+---------------+--------------------------------------------------------------+
​
1 | ID : 444 | CODE : D
2 | ID : 333 | CODE : C

Attributs du tag cfquery

Attribut	Requis	Description
name		Nom de la variable pour l'objet requête.
dataSource		Nom de la source de données.
dbtype		Précise si c'est une requête de requête (QUERY) ou une requête HQL.

Attribut	Requis	Description
username		Permet de préciser un nom d'utilisateur pour la connexion à la base de données (surcharge celui de la source de données si déjà définie).
password		Permet de préciser un mot de passe pour la connexion à la base de données (surcharge celui de la source de données si déjà définie).

Attribut	Requis	Description
maxRows		Valeur par défaut : -1 (Tout) Nombre maximum de lignes à retourner par l'objet requête.
blockFactor		Valeur par défaut : 1
cachedAfter
cachedWithin
timeout

Attribut	Requis	Description
debug
result

Attribut	Requis	Description
ormoptions

Variables préfinies de l'attribut « result »

Nom de la variable	Description
sql	La requête SQL (exemple : `myQuery_Result.sql`).
columnList	Noms de colonnes de la requête, séparés par une virgule (exemple : `myQuery_Result.columnList`).
sqlParameters	Tableau ordonné des valeurs spécifiées dans les tags `cfqueryparam` (exemple : `myQuery_Result.sqlParameters`).
recordCount	Nombre de lignes affectées par la requête de sélection, insertion, mise à jour, ou suppression (exemple : `myQuery_Result.recordCount`).
executionTime	Temps pris pour le traitement de la requête (exemple : `myQuery_Result.executionTime`).
cached	true si le résultat de l'exécution de la requête a été retiré du cache; sinon false (exemple : `myQuery_Result.cached`).

Nom de la variable	Description
ROWID	Oracle uniquement : Le ROWID de la ligne insérée (exemple : `myQuery_Result.ROWID`).
IDENTITYCOL	SQL Server uniquement : L'ID de la ligne insérée (exemple : `myQuery_Result.IDENTITYCOL`).
GENERATED_KEY	MySQL uniquement : L'ID de la ligne insérée (exemple : `myQuery_Result.GENERATED_KEY`).
SYB_IDENTITY	Sybase uniquement : L'ID de la ligne insérée (exemple : `myQuery_Result.SYB_IDENTITY`).
SERIAL_COL	Informix uniquement : L'ID de la ligne insérée (exemple : `myQuery_Result.SERIAL_COL`).

Exemple d'utilisation de l'attribut « result » du tag cfquery (Oracle) :

xxxxxxxxxx
 
<cfquery name="my_query_insert" dataSource="myDataSource" result="result_my_query_insert">
    INSERT INTO tblSTATUS (id, code)
    VALUES (555, 'E')
</cfquery>
​
<cfoutput>
    <cfdump var="#result_my_query_insert#" /><br/>
</cfoutput>

Voici le résultat affiché dans le navigateur :

+---------------+--------------------------------------------------------------+
| struct                                                                       |
+---------------+--------------------------------------------------------------+
| SQL           | INSERT INTO tblSTATUS (id, code) VALUES (555, 'E')           |
+---------------+--------------------------------------------------------------+
| RECORDCOUNT   | 1                                                            |
+---------------+--------------------------------------------------------------+
| ROWID         | AARhaJAABAABhaKAAA                                           |
+---------------+--------------------------------------------------------------+
| GENERATEDKEY  | AARhaJAABAABhaKAAA                                           |
+---------------+--------------------------------------------------------------+
| EXECUTIONTIME | 1                                                            |
+---------------+--------------------------------------------------------------+
| CACHED        | false                                                        |
+---------------+--------------------------------------------------------------+

xxxxxxxxxx
 
+---------------+--------------------------------------------------------------+
| struct                                                                       |
+---------------+--------------------------------------------------------------+
| SQL           | INSERT INTO tblSTATUS (id, code) VALUES (555, 'E')           |
+---------------+--------------------------------------------------------------+
| RECORDCOUNT   | 1                                                            |
+---------------+--------------------------------------------------------------+
| ROWID         | AARhaJAABAABhaKAAA                                           |
+---------------+--------------------------------------------------------------+
| GENERATEDKEY  | AARhaJAABAABhaKAAA                                           |
+---------------+--------------------------------------------------------------+
| EXECUTIONTIME | 1                                                            |
+---------------+--------------------------------------------------------------+
| CACHED        | false                                                        |
+---------------+--------------------------------------------------------------+

Utilisation de la variable « rowid » de l'attribut « result » (Oracle) :

La valeur de la variable « rowid » peut être utilisée pour récupérer les données de la ligne insérée (particulièrement utile quand les données de la ligne insérée ne permettent pas d'identifier de façon unique cette ligne dans la table).

xxxxxxxxxx
 
<cfquery name="myQuery" dataSource="myDataSource">
    SELECT id, code
    FROM tblSTATUS
    WHERE rowid = '#result_my_query_insert.rowid#'
</cfquery>
​
<cfoutput>
    <cfloop query="myQuery">
        #myQuery.currentRow# | ID : #myQuery.ID# | CODE : #myQuery.CODE#<br/>
    </cfloop>
</cfoutput>

Voici le résultat affiché dans le navigateur :

xxxxxxxxxx
 
1 | ID : 555 | CODE : E

Variables préfinies par le tag cfquery

Nom de la variable	Description
columnList	Noms de colonnes de la requête (séparés par une virgule) (exemple : `myquery.columnList`).
recordCount	Nombre de lignes (enregistrements) retournées par la requête (exemple : `myquery.recordCount`).
currentRow	Numéro de la ligne de la requête qui est entrain d'être traitée dans une boucle (exemple : `myquery.currentRow`).

Remarques :

a) J'ai remarqué qu'il y a parfois un peu de confusion lorsqu'on utilise le tag cfquery :

xxxxxxxxxx
 
<cfquery name="myQuery">SELECT 1 FROM DUAL</cfquery>

Notes : En CF9 l'attribut datasource est devenu optionnel, vu qu'on peut définir une source de données globale pour toute l'application (dans le fichier « application.cfc »).

Dans l'exemple précédent, le nom « myQuery » est le nom d'une variable. En fait, ce code pourrait être écrit :

xxxxxxxxxx
 
<cfset myQuery = "" />
<cfquery name="myQuery">SELECT 1 FROM DUAL</cfquery>

Dans ce sens, rien ne vous empêche d'écrire :

xxxxxxxxxx
 
<cfquery name="REQUEST.myTemplate.myQuery">SELECT 1 FROM DUAL</cfquery>

Cela revient à écrire :

xxxxxxxxxx
 
<cfset REQUEST.myTemplate = StructNew() />
<cfquery name="REQUEST.myTemplate.myQuery">SELECT 1 FROM DUAL</cfquery>

Notes : Cette pratique (spécialiser les variables par page ou fonctionnalité) peut être intéressante si vous voulez bien encapsuler vos traitement (ainsi éviter une utilisation accidentel d'une autre variable) et au même temps si vous allez avoir un moyen facile de passer des paramètres entre composants qui ne partagent pas le scope VARIABLES.

b) pour les fonctions, je recommande la syntaxe explicite suivante (question de sécuriser le code) :

xxxxxxxxxx
 
<cffunction name="doQuery">
    <cfset var myQuery = "" />
    <cfquery name="myQuery">SELECT 1 FROM DUAL</cfquery>
</cffunction>

Mais je trouve cette syntaxe un peu contraignante, surtout lorsqu'il y a plusieurs variables à initialiser, et en plus il faut mettre toutes les déclarations des variables au top de la fonction juste après les tags CFARGUMENT (uniquement pour les versions antérieures à CF9).

CF9 ajoute un nouveau scope LOCAL qui est privé au code de la fonction, c.-à-d., les variables déclarées dans ce scope ne sont pas visibles à l'extérieur de la fonction.

xxxxxxxxxx
 
<cffunction name="doQuery">
    <cfquery name="LOCAL.myQuery">SELECT 1 FROM DUAL</cfquery>
</cffunction>

Pour les versions antérieures à CF9, je recommande la syntaxe suivante :

xxxxxxxxxx
 
<cffunction name="doQuery">
    <cfset var LOCAL = StructNew() />
    <cfquery name="LOCAL.myQuery">SELECT 1 FROM DUAL</cfquery>
</cffunction>

cfqueryparam

xxxxxxxxxx
 
<cfqueryparam
    value = "valeur du paramètre"
    cfsqltype = "type du paramètre"
​
    null = "[yes|no] Si yes, le tag ignore la valeur du paramètre."
​
    list = "[yes|no] Indique si la valeur du paramètre est une liste."
    separator = "Séparateur (caractère) des valeurs dans la liste."
​
    maxLength = "longueur maximale du paramètre"
    scale = "nombre de décimales"

Attributs du tag cfqueryparam

Attribut	Requis	Description
value		Valeur du paramètre.
cfsqltype		- Valeurs possibles : Voir la liste en bas - Valeur par défaut : CF_SQL_VARCHAR Type du paramètre.

Attribut	Requis	Description
null		- Valeurs possibles : [no\|yes] - Valeur par défaut : no Si yes, le tag ignore la valeur du paramètre.

Attribut	Requis	Description
list		- Valeurs possibles : [no\|yes] - Valeur par défaut : no Indique si la valeur du paramètre est une liste.
separator		- Valeur par défaut : , (Virgule) Séparateur (caractère) des valeurs dans la liste.

Attribut	Requis	Description
maxLength		Longueur maximale (en caractères) du paramètre.
scale		Nombre de décimales.

Valeurs possibles pour l'attribut « cfsqltype »

Valeur	Description
CF_SQL_CHAR	- Oracle : char, nchar - MSSQL : char
CF_SQL_VARCHAR	- Oracle : varchar2, nvarchar2 - MSSQL : varchar

Valeur	Description
CF_SQL_INTEGER	- Oracle : number - MSSQL : integer
CF_SQL_DECIMAL	- Oracle : number - MSSQL : decimal
CF_SQL_FLOAT	- Oracle : number - MSSQL : real
CF_SQL_NUMERIC	- Oracle : number - MSSQL : numeric

Valeur	Description
CF_SQL_DATE	- Oracle : date - MSSQL : date
CF_SQL_TIMESTAMP	- Oracle : date - MSSQL : timestamp

Valeur	Description
CF_SQL_BLOB	- Oracle : blob, bfile - MSSQL : longvarbinary

Valeur	Description
CF_SQL_CLOB	- Oracle : clob,nclob

Valeur	Description
CF_SQL_BIT	- MSSQL : bit
CF_SQL_TINYINT	- MSSQL : tinyint
CF_SQL_SMALLINT	- MSSQL : smallint
CF_SQL_BIGINT	- MSSQL : bigint
CF_SQL_REAL	- MSSQL : real
CF_SQL_DOUBLE	- MSSQL : double

Valeur	Description
CF_SQL_TIME	- MSSQL : time

Valeur	Description
CF_SQL_MONEY	- MSSQL : double
CF_SQL_MONEY4	- MSSQL : double

Valeur	Description
CF_SQL_IDSTAMP

Valeur	Description
CF_SQL_REFCURSOR

Note de performance :

On signale que l'utilisation de cfqueryparam permet d'avoir des gains de performance.
La première chose à savoir est que le gain de performance n'est pas une particularité de ColdFusion mais une option du SGBD, s'il supporte les "bind variables".
Voici un résumé des fonctionnalités du tag cfqueryparam :

[du coté ColdFusion] forcer le teste sur le type des valeurs (ce teste peut être aussi effectué avec le tag CFPARAM) ;
[du coté ColdFusion] sécuriser les paramètres (attaques par injection SQL), principalement pour les variables qui viennent de l'extérieur du serveur (Scope FORM, URL, …) ;
[du coté SGBD] avoir des gains de performances dans le cas des requêtes dynamiques et répétitives (si le SGBD supporte les BIND VARIABLES).

Pour les gains de performances, si on considère une requête qui ramène des informations pour une ressource donnée :

xxxxxxxxxx
 
<cfquery name="myQuery">
    SELECT  (…)
    FROM    (…)
    WHERE   id = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#RES_ID#" />
</cfquery>

Le tag cfqueryparam va permettre au SGBD (Oracle par exemple) de créer une BIND VARIABLE pour la variable RES_ID et dans ce cas il ne sera pas obligé d'établir un nouveau plan d'exécution lors de la prochaine requête (donc éviter de parser la requête, établir le meilleur plan d'exécution, …).

À défaut de ne pas utiliser cfqueryparam, Oracle va considérer que chaque requête, qui vient avec une valeur différente pour RES_ID, est unique et donc il va établir un nouveau plan d'exécution.

Par contre, il n'est pas nécessaire d'utiliser le tag cfqueryparam lorsque la valeur d'une variable est statique (et on sait d'avance que la requête ne va pas utiliser une autre valeur pour cette variable) :

xxxxxxxxxx
 
<cfquery name="myQuery">
    SELECT  1
    FROM    tblSTATUS
    WHERE   code = <cfqueryparam value="B" cfsqltype="CF_SQL_CHAR" />
</cfquery>

Dans ce cas, mieux privilégier :

xxxxxxxxxx
 
<cfquery name="myQuery">
    SELECT  1
    FROM    status
    WHERE   code = 'B'
</cfquery>

Il n'y a aucun besoin dans ce cas pour qu'Oracle crée une BIND VARIABLE.