FirebirdSQL logo
 Structure du langage SQLÉléments communs du langage 

Types de données Spécial

Type de données SQL_NULL

Ce type de données ne contient pas de données, mais seulement un état : NULL ou NOT NULL.De plus, ce type de données ne peut pas être utilisé lors de la déclaration de champs de table, de variables PSQL, utilisés dans les descriptions de paramètres.Ce type de données a été ajouté pour améliorer la prise en charge des paramètres non typés dans le prédicat IS NULL.Ce problème se produit lors de l’utilisation de filtres excluables lors de l’écriture de requêtes du type suivant :

WHERE col1 = :param1 OR :param1 IS NULL

Une fois traitée, au niveau de l’API, la demande aura l’apparence suivant

WHERE col1 = ? OR ? IS NULL

Dans ce cas, le développeur traite :param1 comme une variable qui est utilisée deux fois, alors qu’au niveau de l’API, la requête contient deux paramètres distincts et indépendants.En plus de cela, le serveur ne peut pas déterminer le type du second paramètre car il est associé à IS NULL.

C’est pour résoudre le ` ? IS NULL` a été ajouté ce type de données spécial SQL_NULL.

Après l’introduction de ce type de données spécial, lors de l’envoi d’une requête et de ses paramètres au serveur, le schéma suivant sera appliqué : l’application transmet les requêtes paramétrées au serveur sous la forme “?”.Il est donc impossible de fusionner une paire de paramètres "identiques" en un seul.Ainsi, par exemple, pour deux filtres (deux paramètres nommés), quatre paramètres positionnels doivent être passés (en supposant en outre que le lecteur a une certaine familiarité avec l’API de Firebird) :

SELECT
  SH.SIZE, SH.COLOUR, SH.PRICE
FROM SHIRTS SH
WHERE (SH.SIZE = ? OR ? IS NULL)
  AND (SH.COLOUR = ? OR ? IS NULL)

Après l’exécution de isc_dsql_describe_bind(), le sqltype des 2ème et 4ème paramètres est défini comme SQL_NULL.Comme indiqué ci-dessus, le serveur Firebird ne dispose d’aucune information sur leur relation avec les 1er et 3ème paramètres — c’est entièrement la prérogative du programmeur.Une fois que les valeurs des 1er et 3e paramètres ont été définies (ou mises à NULL) et que la requête est préparée, chaque paire de XSQLVARs doit être remplie comme suit :

Paramètres définis par l’utilisateur

  • Premier paramètre (comparaison de valeurs) : définition de *sqldata à la valeur passée et de *sqlind à 0 (pour NOT NULL) ;

  • Deuxième paramètre (vérification de NULL) : mettre *sqldata à null (pointeur nul, pas SQL NULL) et *sqlind à 0 (pour NOT NULL).

L’utilisateur a laissé le champ vide

  • Les deux paramètres (vérification de NULL) : mettre *sqldata à null (pointeur nul, pas SQL NULL) et *sqlind à -1 (indication NULL).

En d’autres termes, la valeur du paramètre de comparaison est toujours fixée comme d’habitude.Le paramètre SQL_NULL est également défini, sauf si sqldata est passé comme null.

docnext count = 8

Conversion des types de données

Lorsque vous écrivez une expression ou que vous spécifiez, par exemple, des conditions de comparaison, vous devez essayer d’utiliser des Types de données compatibles.Si vous devez utiliser des données mixtes de différents types, il est conseillé d’effectuer d’abord les conversions de type, puis d’effectuer les opérations.

Lorsque l’on envisage des conversions de type dans Firebird, il faut prêter une grande attention au dialecte dans lequel se trouve la base de données.

Conversion explicite des types de données

Lorsqu’une conversion explicite d’un type à un autre est nécessaire, la fonction CAST est utilisée.

Syntaxe
CAST (<expression> | NULL AS <data_type>)

<data_type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN relname.colname

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

scalar_datatype ::= Voir. Syntaxe des types de données scalaires

blob_datatype ::= Voir. Syntaxe des types de données BLOB

array_datatype ::= Voir. Syntaxe des tableaux

Conversion en domaine

Lors de la conversion vers un domaine, les restrictions déclarées pour celui-ci, telles que NOT NULL ou celles décrites dans CHECK, sont prises en compte et si <expression> échoue, la conversion échouera.Si en plus TYPE OF est spécifié (conversion en type de base), la conversion ignore toute restriction de domaine.Si TYPE OF est utilisé avec le type [VAR]CHAR, le jeu de caractères et le tri sont préservés.

Conversion en type de colonne

Lors de la conversion vers un type de colonne, il est permis d’utiliser une spécification de colonne de table ou de vue.Seul le type de colonne lui-même est utilisé ; dans le cas des types de chaînes de caractères, cela inclut également le jeu de caractères, mais pas le tri.Les contraintes et les valeurs par défaut de la colonne d’origine ne s’appliquent pas.

CREATE TABLE TTT (
  S VARCHAR (40)
  CHARACTER SET UTF8 COLLATE UNICODE_CI_AI);
COMMIT;

/* J'ai beaucoup d'amis (suédois)*/
SELECT
  CAST ('Jag har manga vanner' AS TYPE OF COLUMN TTT.S)
FROM RDB$DATABASE;

Transformations permises pour la fonction CAST

Table 1. Transformations permises pour la fonction CAST
Type d’origine Cast

Type numérique

Type numérique, [VAR]CHAR, BLOB

[VAR]CHAR, BLOB

[VAR]CHAR, BLOB, BOOLEAN, Types numériques, DATE, TIME, TIMESTAMP

DATE, TIME

[VAR]CHAR, BLOB, TIMESTAMP

TIMESTAMP

[VAR]CHAR, BLOB, TIME, DATE

BOOLEAN

[VAR]CHAR, BLOB

Pour convertir des types de données de type chaîne en type BOOLEAN, l’argument chaîne doit être l’un des littéraux prédéfinis du type logique ('true' ou 'false').

Important

Lors de la conversion des types, il faut tenir compte de la possibilité de perte partielle de données, par exemple lors de la conversion du type de données TIMESTAMP en DATE.

Conversion de chaînes de caractères en date et heure

Pour convertir des types de données de type chaîne en DATE, TIME ou TIMESTAMP, l’argument chaîne doit être soit un des littéraux de date et d’heure prédéfinis, soit une représentation de la date dans l’un des formats autorisés.

<date_literal> ::=
  [YYYY<p>]MM<p>DD |
  MM<p>DD[<p>YYYY] |
  DD<p>MM[<p>YYYY] |
  MM<p>DD[<p>YY] |
  DD<p>MM[<p>YY]


<time_literal> := HH[:mm[:SS[.NNNN]]]

<datetime_literal> ::= <date_literal> <time_literal>

<time zone> ::=
    <time zone region> |
    [+/-] <hour displacement> [: <minute displacement>]

<p> ::= whitespace | . | , | - | /    position: absolute;
    float: left;
Table 1. Description du format de la date et de l’heure
Argument Description

datetime_literal

Les littéraux date-heure.

date_literal

Date littérale.

time_literal

Le temps littéral.

YYYY

Une année à quatre chiffres.

YY

Les deux derniers chiffres de l’année (00-99).

MM

Mois.Peut contenir 1 ou 2 chiffres (1-12 ou 01-12). Le mois peut également être une abréviation de trois lettres ou le nom complet du mois en anglais, sans tenir compte de la casse.

DD

Jour.Peut contenir 1 ou 2 chiffres (1-31 ou 01-31).

HH

Heure.Peut contenir 1 ou 2 chiffres (0-23 ou 00-23).

mm

Minute.Peut contenir 1 ou 2 chiffres (0-59 ou 00-59).

SS

Secondes.Peut contenir 1 ou 2 chiffres (0-59 ou 00-59).

NNNN

Dix millièmes de seconde.Peut contenir de 1 à 4 chiffres (0-9999).

p

Séparateur, n’importe lequel des caractères autorisés, les espaces avant et arrière sont ignorés.

time zone region

Un des fuseaux horaires associés à la région

hour displacement

Décalage horaire de l’horloge par rapport à GMT

minute displacement

Décalage horaire pour les minutes par rapport à GMT
Table 2. Linéaires prédéfinis de date et d’heure
Les littéraux Valeur Type de données pour le dialecte 1 Type de données pour le dialecte 3

'NOW'

Date et heure actuelles

TIMESTAMP

TIMESTAMP

'TODAY'

Date actuelle

TIMESTAMP (avec un temps nul)

DATE (date uniquement)

'TOMORROW'

Date de demain

TIMESTAMP (avec un temps nul)

DATE (date uniquement)

'YESTERDAY'

Date d’hier

TIMESTAMP (avec un temps nul)

DATE (date uniquement)

Règles :

  • Au format Année-Mois-Jour, l’année doit obligatoirement comporter 4 chiffres ;

  • Pour les dates au format fin d’année, si un point “.” est utilisé comme séparateur de date, la date est interprétée comme Jour-Mois-Année, pour les autres séparateurs, elle est interprétée comme Mois-Jour-Année ;

  • Si aucune année n’est spécifiée, l’année en cours est prise comme année ;

  • Si seulement deux chiffres de l’année sont spécifiés, Firebird utilise l’algorithme de la fenêtre glissante pour obtenir le siècle. La tâche consiste à interpréter la valeur de l’année à deux caractères comme étant la plus proche de l’année en cours dans l’intervalle des 50 années précédentes et suivantes ;

  • Si aucun élément de temps n’est spécifié, il est supposé être égal à 0.

Lors de l’utilisation de CAST() pour convertir des littéraux de chaîne en type date/heure, la valeur est toujours calculée au moment de l’exécution.

Lors de la conversion de chaînes de caractères avec des valeurs de date et d’heure prédéfinies en type TIMESTAMP, la précision est de 3 décimales (millisecondes).

Tip

Nous recommandons vivement de n’utiliser que des formulaires comportant l’année complète à 4 chiffres dans les littéraux de date afin d’éviter toute confusion.
Example 1. Convertir des chaînes de caractères en date et heure :
SELECT
  CAST('04.12.2014' AS DATE) AS d1, -- DD.MM.YYYY
  CAST('12-04-2014' AS DATE) AS d2, -- MM-DD-YYYY
  CAST('12/04/2014' AS DATE) AS d3, -- MM/DD/YYYY
  CAST('04.12.14' AS DATE) AS d4,   -- DD.MM.YY
  -- DD.MM année en cours
  CAST('04.12' AS DATE) AS d5,
  -- MM/DD année en cours
  CAST('12/4' AS DATE) AS d6,
  CAST('2014/12/04' AS DATE) AS d7, -- YYYY/MM/DD
  CAST('2014.12.04' AS DATE) AS d8, -- YYYY.MM.DD
  CAST('2014-12-04' AS DATE) AS d9, -- YYYY-MM-DD
  CAST('11:37' AS TIME) AS t1, -- HH:mm
  CAST('11:37:12' AS TIME) AS t2, -- HH:mm:ss
  CAST('11:31:12.1234' AS TIME) AS t3, -- HH:mm:ss.nnnn
  -- HH:mm:ss.nnnn +hh
  CAST('11:31:12.1234 +03' AS TIME WITH TIME ZONE) AS t4,
  -- HH:mm:ss.nnnn +hh:mm
  CAST('11:31:12.1234 +03:30' AS TIME WITH TIME ZONE) AS t5,
  -- HH:mm:ss.nnnn tz
  CAST('11:31:12.1234 Europe/Moscow' AS TIME WITH TIME ZONE) AS t5,
  -- HH:mm tz
  CAST('11:31 Europe/Moscow' AS TIME WITH TIME ZONE) AS t6,
  -- DD.MM.YYYY HH:mm
  CAST('04.12.2014 11:37' AS TIMESTAMP) AS dt1,
  -- MM/DD/YYYY HH:mm:ss
  CAST('12/04/2014 11:37:12' AS TIMESTAMP) AS dt2,
  -- DD.MM.YYYY HH:mm:ss.nnnn
  CAST('04.12.2014 11:31:12.1234' AS TIMESTAMP) AS dt3,
  -- YYYY-MM-DD HH:mm:ss.nnnn +hh:mm
  CAST('2014-12-04 11:31:12.1234 +03:00' AS TIMESTAMP WITH TIME ZONE) AS dt4,
  -- DD.MM.YYYY HH:mm:ss.nnnn tz
  CAST('04.12.2014 11:31:12.1234 Europe/Moscow' AS TIMESTAMP WITH TIME ZONE) AS dt5,
  CAST('now' AS DATE) AS d_now,
  CAST('now' AS TIMESTAMP) AS ts_now,
  CAST('now' AS TIMESTAMP WITH TIME ZONE) AS ts_now_tz,
  CAST('today' AS DATE) AS d_today,
  CAST('today' AS TIMESTAMP) AS ts_today,
  CAST('today' AS TIMESTAMP WITH TIME ZONE) AS ts_today_tz,
  CAST('tomorrow' AS DATE) AS d_tomorrow,
  CAST('tomorrow' AS TIMESTAMP) AS ts_tomorrow,
  CAST('tomorrow' AS TIMESTAMP WITH TIME ZONE) AS ts_tomorrow_tz,
  CAST('yesterday' AS DATE) AS d_yesterday,
  CAST('yesterday' AS TIMESTAMP) AS ts_yesterday,
  CAST('yesterday' AS TIMESTAMP WITH TIME ZONE) AS ts_yesterday_tz
FROM rdb$database
Note

Comme CAST('NOW' AS TIMESTAMP) renvoie toujours les valeurs réelles de la date et de l’heure, il peut être utilisé pour mesurer les intervalles de temps et la vitesse d’exécution du code dans les procédures PSQL, les déclencheurs et les blocs de code.
Example 2. Utilisation de CAST('NOW' AS TIMESTAMP) pour mesurer la durée d’exécution du code
EXECUTE BLOCK
RETURNS (ms BIGINT)
AS
DECLARE VARIABLE t1 TIME;
DECLARE VARIABLE n BIGINT;
BEGIN
  t1 = CAST('now' AS TIMESTAMP);
  /* Long fonctionnement  */
  SELECT COUNT(*) FROM rdb$types, rdb$types, rdb$types INTO n;
  /*======*/
  ms = DATEDIFF(MILLISECOND FROM t1 TO CAST('now' AS TIMESTAMP));
  SUSPEND;
END
Voir aussi:

Litérales de date et d’heure, Fonctions de conversion de type, CAST.

Conversion implicite des types de données

Dans le dialecte 3, aucune conversion implicite de données n’est possible, ici la fonction CAST doit être spécifiée pour traduire explicitement un type vers un autre.Toutefois, cela ne s’applique pas à l’opération de concaténation, dans laquelle tous les autres Types de données seront implicitement convertis en un type de caractère.

En utilisant le dialecte 1, de nombreuses expressions effectuent une conversion implicite d’un type vers un autre sans utiliser la fonction CAST.Par exemple, dans une expression de sélection en dialecte 1, vous pouvez écrire :

WHERE DOC_DATE < '31.08.2014'

et la conversion de la chaîne en date se fera implicitement.

Dans le dialecte 1, il est possible de mélanger des données entières et des chaînes numériques, les chaînes sont implicitement converties en entiers si cela est possible, par ex :

2 + '1'

s’exécutera correctement.

Dans le dialecte 3, une telle expression provoquera une erreur, elle nécessitera une entrée de la forme suivante :

2 + CAST('1' AS SMALLINT)

Conversion de type implicite pendant la concaténation

Lors de la concaténation de plusieurs éléments de types différents, toutes les données qui ne sont pas des chaînes seront implicitement converties en chaînes, si possible.

Example 1. Conversion de type implicite pendant la concaténation
SELECT 30||' days hath September, April, June and November' CONCAT$
FROM RDB$DATABASE
CONCAT$
------------------------------------------------
30 days hath September, April, June and November