[ASE 12.0.3] Comment tester les valeurs nulles (T-SQL) [Résolu]

[msomso]

Bonjour
Suite à ma migration ASE de V11.0 en V12.0, je suis confrontée aux erreurs d'exécutions de procédures stockées ( plus de 1500 procédures à migrer).
Ces problèmes sont liés, me seble-t-il à la gestion des varchar et plus particulièrement de valeurs nulles ou composées d'espaces uniquement.
Par exemple:
En V11 : right('abcd ',2) donne ''cd' alors qu'en V12 il donne 'd '. Je constate aussi que le résultat des tests de type :

Code :

"if (@var is null or ltrim(rtrim(@var)) !="")

n'est pas le même en V11 qu'en V12. S'agît-il du paramétrage ou de vraie évolution de ASE ?

Vu le nombre de mes procédures, je me demande si cela ne pouvait se régler par un paramétrage (option un peu "magique" du serveur.

J'ai fait de nombreux tests (trop pour les citer tous), en voici un le plus simple:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SET nocount ON
print '                                 AINSINULL OFF'
GO
SET ANSINULL OFF
declare @vnull varchar(1)
SELECT @vnull=''
print "select @vnull=''"
  IF (@vnull IS NOT NULL)   print "TEST IS :  vnull IS NOT null " ELSE print "TEST IS :  vnull IS null "
  IF (@vnull != NULL    )   print "TEST =  :  vnull != null "     ELSE print "TEST =  :  vnull =null "
SELECT @vnull=NULL
print "***"
SELECT @vnull=NULL
print "select @vnull=null"
  IF (@vnull IS NOT NULL)   print "TEST IS :  vnull IS NOT null " ELSE print "TEST IS :  vnull IS null "
  IF (@vnull != NULL    )   print "TEST =  :  vnull != null "     ELSE print "TEST =  :  vnull =null "
 
--
SET ANSINULL ON
SELECT @vnull=''
print ""
print '                                AINSINULL ON'
print "select @vnull=''"
  IF (@vnull IS NOT NULL)   print "TEST IS :  vnull IS NOT null " ELSE print "TEST IS :  vnull IS null "
  IF (@vnull != NULL    )   print "TEST =  :  vnull != null "     ELSE print "TEST =  :  vnull =null "
print "***"
SELECT @vnull=NULL
print "select @vnull=null"
  IF (@vnull IS NOT NULL)   print "TEST IS :  vnull IS NOT null " ELSE print "TEST IS :  vnull IS null "
  IF (@vnull != NULL    )   print "TEST =  :  vnull != null "     ELSE print "TEST =  :  vnull =null "
GO

Ce script "croise" les 2 modes AINSINULL/2 types d'initialisation/2 types de tests.
Le résultat varie en fonction du mode (je peux l'admettre) mais aussi en fonction du type d'affectation: ='' et =null produisent par la suite des réultats différents du test de la valeur nulle.
Je ne trouve pas d'explication au résultat obtenu:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
                                 AINSINULL OFF
SELECT @vnull=''
TEST IS :  vnull IS NOT NULL 
TEST =  :  vnull != NULL 
***
SELECT @vnull=NULL
TEST IS :  vnull IS NULL 
TEST =  :  vnull =NULL 
 
                                AINSINULL ON
SELECT @vnull=''
TEST IS :  vnull IS NOT NULL 
TEST =  :  vnull =NULL 
***
SELECT @vnull=NULL
TEST IS :  vnull IS NULL 
TEST =  :  vnull =NULL

Je présente ce cas d'école, parce que je cherche à comprendre pourquoi mes procédures en V12 ne donnent pas le même résultat qu'en V11. Pour ce qui concerne ce premier test, le résultat en V11 et V12 est identique. J'en ai fait d'autres où je trouve de différences entre versions. Mais avant d'en parler, pouvez-vous m'aider à interpréter le résultat cité ci-dessus ?

Dans notre application écrite pour V11:

• la plupart de tests est faite avec = null / != null.
• les affectation sont faits souvent par ='' (et non =null)
• la valeur AINSINULL par défaut (selon la doc) est bien OFF

Cela ne devait donc pas bien marcher (cf. mon test) et pourtant ça fonctionne. Je ne l'explique pas au vu de mon résultat.

Est-ce qu'on peut paramétrer AINSINULL au niveau du serveur ?
De façon générale, comment connaitre la valeur courante d'une option "set" sans avoir à tester et deviner par déduction ?

Merci d'avance
msomso
P.S.
En pièces jointes le code sql est résultat en V11 et V12.

[mpeppler]

Sur un serveur 12.5.3 j'ai:

Code :

1
2
3
4
5
 
[21] SYBASE.master.1> SELECT RIGHT('abcd ', 2);
 
 -----
 d

donc il semble qu'il s'agit d'une évolution (ou que le précédent comportement soit considéré comme un bug).

Pour les problème de NULL il faudra attendre demain - je regarderai cela d'un peu plus près.

Michael

[mpeppler]

Pour les NULLs (et non pas pour les nulls

Lorsque ANSINULL est en marche, on ne peut jamais utiliser = pour faire une comparaison avec NULL, ce qui veut dire que ton test:

Code :

1
2
 
  IF (@vnull != NULL    )   print "TEST =  :  vnull != null "     ELSE print "TEST =  :  vnull =null "

n'est pas complet. Considère ceci:

Code :

1
2
3
4
5
6
7
8
 
[46] LOCALHOST.my_db.1> SET ansinull ON;
[47] LOCALHOST.my_db.1> declare @f int
[47] LOCALHOST.my_db.2> SELECT case when @f = NULL then "null" when @f != NULL then "not null" else "ni l'un ni l'autre" end;
 
 ------------------
 ni l'un ni l'autre
[48] LOCALHOST.my_db.1>

Comme je n'ai rien assigné à @f cette valeur est NULL. Les tests = et != sont tous les deux faux!
Si ne mets ANSINULL off:

Code :

1
2
3
4
5
6
7
 
[51] LOCALHOST.my_db.1> declare @f int
[51] LOCALHOST.my_db.2> SELECT case when @f = NULL then "null" when @f != NULL then "not null" else "ni l'un ni l'autre" end;
 
 ------------------
 NULL
[52] LOCALHOST.my_db.1>

Pour ce qui est des varchar() où l'on assigne '':

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
[53] LOCALHOST.my_db.1> declare @f varchar(1)
[53] LOCALHOST.my_db.2> SELECT @f = ''
[53] LOCALHOST.my_db.3> SELECT char_length(@f)
[53] LOCALHOST.my_db.4> SELECT case when @f = NULL then "null" when @f != NULL then "not n
ull" else "ni l'un ni l'autre" end;
 
 -----------
           1
 
 
 ------------------
 NOT NULL
[54] LOCALHOST.my_db.1>

Un varchar() n'est jamais vide. Il contient au moins un espace, mais il contient au plus un "trailing" espace (je ne sais comment on dit cela en français - la chaine "aabbcc " devient "aabbcc ").

Donc un varchar() auquel on assigne '' devient ' ' est n'est en conséquence pas NULL.

En résumé: pour tester la nullitude d'une valeur/variable il est très fortement recommendé d'utiliser IS NULL/IS NOT NULL pour éviter toute confusion.

Michael

[msomso]

Bonjour

il semble qu'il s'agit d'une évolution (ou que le précédent comportement soit considéré comme un bug)

Dans le premier temps, moi aussi, j'ai crû qu'il s'agissait d'une correction d'un bug de V11 en V12. J'ai fait donc des corrections au cas par cas.
Mais par la suite j'ai découvert d'autres incompatibilités.
Par exemple, pour une @var variable varchar(1) étant nulle ou contenant un espace, le test :

Code :

IF (@var != NULL AND ltrim(rtrim(@var)) !="")

retourne VRAI en V12 et FAUX en V11.

Notre @var est un paramétre IN d'une procédure stockée "P1".
Cette procédure est appellée par Power Builder qui lui affecte la valeur d'un champ sur écran (champ "vide" en occurence). Elle appelle ensuite la procédure stockée "P2". La variable @var ne subit aucune transformation entre IHM et "P2".
Le but du test est d'exclure du traitement les cas de valeurs nulls ou composées uniquement d'un blanc. Ceci marche en V11, mais en V12 le traitement se declenche et se termine en erreur car il ne s'attend pas à cette valeur compte tenu du test avant son appel.

Je pense qu'après avoir corrigé ce cas, j'en trouverai d'autres cas: j'ai >600 tests de nulls avec =/!= dans mes procs.
Personnellement, je teste tjrs par is null/is not null , mais je voudrais éviter des corrections en masse du code existant. D'autant plus que même en V12 le résultat depend, semblait-il, de type d'affectation: selon mon test cité au début, select @var='' ou select @var=null ne donnent pas la même chose dans le mode ainsinull off.

Je voudrais donc trouver une solution générale à mon problème de migration de procédures stockées.

Merci
msomso
P.S.
Michael, je reprends tes tests et je vais continuer mes refléxions.
Est-il possible de positionner option AINSINULL au niveau serveur ?

[mpeppler]

En fait, la solution semble assez simple:

Code :

1
2
3
4
5
6
7
8
9
10
 
[25] LOCALHOST.master.1> declare @f varchar(1)
[25] LOCALHOST.master.2> SELECT @f = ''
[25] LOCALHOST.master.3> SELECT rtrim(ltrim(@f));
(1 row affected)
 
 -
 NULL
 
(1 row affected)

Donc ton test:

Code :

1
2
 
IF (@var IS NOT NULL AND ltrim(rtrim(@var)) IS NOT NULL)

et tu devrais avoir le bon comportement.

Pour ANSINULL il faut que je regarde - en principe c'est un paramètre de connexion. Il peut être positionné par défaut pour une session "OpenClient" via le fichier ocs.cfg (%SYBASE%/OCS-12_0/config/ocs.cfg), mais ce n'est de loins pas sûr que cela marche avec PowerBuilder.

Michael

[msomso]

Bonsoir
En effet ce que tu suggéres Michael, fonctionne !
Moi, entre temps j'ai appliqué le test de acsii(@var) != null and ascii(@var) != 32.
Le problème s'est éclairci dans ma tête: il fallait tester/exclure la chaîne vide (= null ou bien composé d'un espace).

Ouff !

Ceci étant dit, je rencontre d'autres "particularité", divergeances dans le test des valeurs null entre V11 et V12. Je commence à me résigner à les corriger au cas par cas.

Merci
msomso
Saurais-tu me dire comment faire un CD avec la doc complète de V12 à partir du site que tu m'as indiqué: http://sybooks.sybase.com/nav/detail.do?docset=1145

[mpeppler]

Envoyé par msomso

Saurais-tu me dire comment faire un CD avec la doc complète de V12 à partir du site que tu m'as indiqué: http://sybooks.sybase.com/nav/detail.do?docset=1145

Je crois qu'il faut télécharger chaque PDF (ou au moins ceux qui sont importants - cad le manuel de référence, le guide admin système, le troubleshooting guide, et le performance et tuning).

Michael

[msomso]

Merci beaucoup
msosmo