SQLServer, JDBC et procédures stockées

On ne le repètera jamais assez, migrer une base de données n'a rien d'anodin... Surtout lorsque l'application s'appuie sur des procédures stockées. En effet, chaque SGBD en propose son propre moteur et aucun driver JDBC ne se ressemble...

Le diable est dans les détails

Voici donc quelques exemples de "détails" qui vous éviteront de vous arracher les cheveux. La configuration utilisée ici est SQL Server Express 2014 et le driver JDBC Microsoft 4.0.

Un message peut en cacher un autre

Prenons l'exemple de la procédure stockées suivante :

CREATE PROCEDURE p_maproc(@param INTEGER)
AS
BEGIN

  UPDATE MATABLE SET COL1 = 'VALEUR'

  select 'data1' as RET1, 'data2' as RET2, 'data3' as RET3

END

Rien de bien compliqué, un UPDATE et un SELECT dont le but est de renvoyer un ResultSet avec 3 colonnes de données.

Appelons maintenant cette procédure avec un outil type Squirrel (configuré pour utiliser le driver JDBC) :

Tout se passe bien, le retour est bien constitué des 3 colonnes du SELECT.

Cependant, lorsque cette même procédure est appelée dans une application Java (via le même driver JDBC), l'erreur suivante est renvoyée :

com.microsoft.sqlserver.jdbc.SQLServerException: L'instruction n'a pas renvoyé le jeu de résultat.

En somme, le driver n'a pas pu remonter le ResultSet qu'il attendait...

Ceci est en fait du à la présence de l'instruction UPDATE. Son exécution génère l'émission d'un message appelé DONE_IN_PROC, qui sert à indiquer le nombre de lignes modifiées. Et c'est donc ce message qui est en priorité capté comme retour par le driver JDBC, ce qui l'empêche ensuite de récupérer le vrai ResultSet final.

Pour corriger ce problème, il existe l'instruction

SET NOCOUNT ON

qui permet de désactiver l'envoi des messages DONE_IN_PROC par la base. Notre procédure devient donc :

CREATE PROCEDURE p_maproc(@param INTEGER)
AS
BEGIN

  SET NOCOUNT ON
  UPDATE MATABLE SET COL1 = 'VALEUR'
  SET NOCOUNT OFF

  select 'data1' as RET1, 'data2' as RET2, 'data3' as RET3

END

Il est également possible de configurer directement le serveur de base de données pour ne pas avoir à écrire cette instruction dans toutes les procédures (voir les liens plus bas).

Du bon usage des types utilisateur

Les UDT (user defined type) fonctionnent comme des alias, permettant de créer des types de données personnalisés. Exemple :

CREATE TYPE [dbo].[T_DATEHEURE] FROM [datetime] NULL

Cette instruction va créer un type de données T_DATEHEURE basé sur le type primitif datetime, utilisable dans la base de données courante.

Prenons donc maintenant l'instruction suivante (toujours dans une procédure stockée) :

CREATE PROCEDURE p_maproc(@param INTEGER)
AS
BEGIN

  CREATE TABLE #TABLETEMP (ID int, DATE T_DATEHEURE);
 
  ...

END

Cette fois-ci, l'erreur suivante sera levée :

Msg 2715, Level 16, State 7, Line 1
Colonne, parametre, ou variable #2: Type de données T_DATEHEURE introuvable.

Ceci provient de la nécessité de déclarer également le type de données T_DATEHEURE dans la base de données tempdb, utilisée par SQLServer pour créer les tables temporaires. Ainsi, une fois l'ordre "CREATE TYPE" exécuté sur tempdb, tout fonctionnera normalement...

Hope this helps!

Sources :

• http://www.mssqltips.com/sqlservertip/1226/set-nocount-on-improves-sql-server-stored-procedure-performance/
• http://stackoverflow.com/questions/1483732/set-nocount-on-usage
• http://msdn.microsoft.com/fr-fr/library/ms190763.aspx
• http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-alias-types.aspx

Fichier(s) joint(s) :