WinDev et SQL Server sans accès natif

 Depuis la version 2008 R2, SQL Server est devenu aussi intéressant pour les petits     budgets que pour les gros. Dans la version Express, qui est gratuite et librement     distribuable, les bases ne sont plus limitées à 4 Go mais à 10 Go, et il est toujours possible de créer une infinité de bases pour contourner cette limitation.

Le seul problème, c’est que visiblement il faut acheter l’option « accès natif » de WinDev pour pouvoir en tirer le meilleur parti : performances, facilités syntaxiques, utilisation de procédures stockées, variables etc.

Alors, est-il possible d’avoir le beurre et l’argent du beurre, accéder à SQL Server via OLE DB, avoir des performances, une souplesse et une clarté de code comparables à l’accès natif ? Pour nous la réponse a été oui, car on a pu développer un projet d’envergure pour lequel on a commencé par mettre en place notre « boîte à outil » OLE DB/SQL Server.

Sans pouvoir rentrer dans les détails, car il me faudrait une centaine de pages pour ça, voici ce à quoi nous avons pu arriver, les problèmes qu’il a fallu résoudre et quelques pistes pour vous aider à faire de même.

1ère étape : débrider les performances et les fonctionnalités

Quand on utilise OLE DB avec les options par défaut, on part avec des handicaps :

Le type de curseur

Un curseur est un objet permettant de parcourir une table ou un résultat de requête. C’est ce qu’on utilise avec les fonctions HLitPremier/HLitSuivant. Il y en a plusieurs types à choisir avec la propriété OptionsCurseur des connexions WinDev.

Le type de curseur par défaut est dynamique : il reflète les modifications qui interviennent entre l’exécution de la requête et la fin du parcours, et il permet aussi de modifier les données d’origine. Dans le cas de requêtes de modification (UPDATE, DELETE, etc.), l’exécution pourra être de 2 à 10 fois plus longue à cause de l’utilisation de ce type curseur.

La première chose que nous avons faite, c’est donc de définir 3 connexions avec les curseurs les plus intéressants :

• Le curseur par défaut : surtout conservé pour ne pas perturber les fonctions de WinDev
  
• Le curseur Forward Only : pour plus de rapidité sur les modifications et les SELECT qu’on se contente de lire dans un parcours à sens unique
  
• Le curseur Client : il permet de parcourir un résultat complet en local sans accéder à la base, il est adapté pour les modifications et les petits résultats qui ne satureront pas la mémoire du poste client
  

Ainsi, dans notre surcharge de HExécuteRequêteSQL, on ajouté un paramètre permettant de choisir le type de curseur, qui sélectionne en réalité la connexion. Le seul souci de cette approche étant que les objets temporaires de la connexion (comme les tables dont le nom commence par #) ne sont évidemment pas partagés par les 3 connexions, ce qui dans la pratique se gère assez facilement.

Désactiver les « corrections »

Quand on exécute une requête avec HExécuteRequêteSQL, une tentative de traduction est faite automatiquement, probablement pour palier les incompatibilités entre les différents serveurs SQL.

Le problème, c’est que d’une part cette correction est  très lente et dégrade les performances, d’autre part elle limite énormément les commandes possibles en SQL Server.

Notre version de HExécuteRequêteSQL utilise donc toujours l’option hRequêteSansCorrection.

Parcourir les requêtes sans les réexécuter

Par défaut dans WinDev, commencer le parcours d’un résultat de requête implique un rafraîchissement de celui-ci. Dans le cas d’une requête exécutée avec un curseur client, ça revient à charger 2 fois toutes les données au lieu d’une.

Nous avons donc notre propre HLitPremier qui utilise automatiquement l’option hSansRafraîchir, et nous évitons d’utiliser la syntaxe POUR TOUT qui ne permet pas de passer cette option.

Programmabilité

En OLE DB sous WinDev, la programmabilité est limitée sur plusieurs points :

• On n’est pas censé exécuter plusieurs commandes (comme 2 INSERT à la suite) dans un seul bloc de code, sous peine de comportement bizarre (exécution en quadruple, erreur, …)
  
• On n’est pas censé déclarer des variables côté serveur, récupérer leur valeur…
  
• On ne peut pas récupérer le nombre de lignes modifiées par une requête (UPDATE ou DELETE par exemple)
  
• etc.
  

Nous avons trouvé des solutions à tous ces problèmes, mais parfois assez compliquées, il faut utiliser le bon type de curseur, parfois encapsuler son code dans des procédures stockées temporaires (nom commençant par #), et utiliser une table temporaire pour récupérer les variables.

Tout ça nous l’avons encapsulé dans une classe et c’est maintenant assez facile. Mais ce sont des choses dont on n’a pas toujours besoin au final, et nous n’étions pas obligés d’aller si loin.

2ème étape : avoir un code au moins aussi clair qu’avec l’accès natif

Utilisation de l’analyse

Dans WinDev, l’analyse permet d’intégrer le modèle de données dans le code. Sans elle, on devrait cribler le code de chaînes, le databinding serait plus lourd, on n’aurait pas de complétion, etc.

Pour avoir une analyse cohérente avec le schéma SQL réellement présent sur la base, nous avons développé une fonction qui génère le code SQL permettant de créer ou mettre à jour les tables décrites par l’analyse. Quand nous modifions l’analyse, nous n’avons aucun code à changer, c’est cette fonction qui mettra la base à jour. C’est l’équivalent de HCreation() et de WDModFic.exe pour HF. Elle est exécutée au démarrage de l’application et permet de synchroniser la structure de la base avec l’analyse présente dans l’exécutable.

Il y a beaucoup de cas à prendre en compte, par exemple l’ajout d’un index unique sur une table nécessite de supprimer d’éventuels doublons, car contrairement à HF, SQL Server refusera de créer un tel index s’il reste des doublons. Mais globalement cette fonction n’a pas été très difficile à développer et aujourd’hui elle nous facilite grandement la vie.

Grâce à la présence de l’analyse, on a pu écrire des fonctions qui génèrent les requêtes les plus communes, et le code s’en est trouvé très allégé.

Par exemple, pour supprimer les lignes d’une table selon une clé composée dont les 2 premières composantes seraient égales à « key » et 42 :

gSupprimeEnreg(LaTable.LaClé, « key », 42)

Ici la fonction gSupprimeEnreg prend un nombre de paramètres variable, grâce au mot clé MesParamètres, et elle génère une commande DELETE en SQL. C’est l’exemple le plus simple mais nous en avons beaucoup d’autres, l’avantage principal étant qu’on incite le développeur à générer des requêtes « index-friendly » car basées sur la description des clés.

Paramètres de requêtes nommés

Avec l’accès natif de SQL Server, il est possible d’écrire le code suivant :
sdReq est une source de données
sCodeSQL est une chaine = [
SELECT *
FROM Client
WHERE Nom = @ParamNom AND Prenom = @ParamPrenom
]

sdReq.ParamNom = « Smith »
sdReq.ParamPrenom = « John »
HExécuteRequêteSQL(sdReq, sCodeSQL)
L’avantage, c’est que les paramètres sont nommés, sans ordre de passage, et la syntaxe ne nécessite pas d’encadrer ces noms de paramètres par des guillemets.

Et bien avec un peu d’astuce cette syntaxe peut aussi être utilisée avec OLE DB, car WinDev permet d’accéder aux paramètres d’une source de données, et notre version de HExécuteRequêteSQL se charge de les intégrer à la requête.

Le résultat est plus lisible et plus facile à maintenir que par exemple des ChaîneConstruit. De plus, l’ajout de quotes et l’escaping sont gérés automatiquement pour les chaînes.

Dernière étape : fiabiliser

Fiabiliser en effet, car l’accès OLE DB présente quelques pièges assez dangereux.

Éviter la saturation de ports TCP

Le premier problème est que derrière chaque connexion logique à une base se cachent en réalité plusieurs connexions physiques créées dynamiquement en fonctions des besoins : exécution de requêtes, parcours de résultats. En connexion TCP, on peut le constater en surveillant le nombre de ports TCP utilisés, grâce à la commande NETSTAT de Windows.

Pour des raisons de sécurité, quand une connexion est fermée, le port libéré passe à l’état TIME_WAIT, le rendant indisponible pendant 4 minutes. Maintenant, imaginez que vous exécutiez une requête des milliers de fois dans une boucle et qu’à chaque fois un port passe en TIME_WAIT, vous pouvez arriver à une saturation des 4000 ports disponibles par défaut, et soudain vos requêtes échouent, votre navigateur internet ne fonctionne plus, etc.

C’est un problème assez peu connu, pour lequel personne ne semble avoir de solution. Nous avons donc étudié le problème de façon empirique et cherché à savoir dans quels cas OLE DB gaspille des connexions, et dans quels cas il les recycle correctement. Il en ressort qu’il faut utiliser à chaque fois que c’est adapté un curseur de type Forward Only ou Client. Mais pas seulement : nous avons aussi noté que les requêtes de type SELECT agrégats (par exemple avec la fonction SUM) causaient ce genre de gaspillage. La solution était alors de masquer l’agrégat en l’encapsulant dans une sur-requête :
SELECT * FROM (
SELECT SUM(Quantite) AS Total FROM Stock
) SousReq
Évidemment, ça ne vaut le coup que si la requête est à exécuter de nombreuses fois dans un laps de temps assez court.

Éviter HModifie et HSupprime

Pour SQL Server via OLE DB les fonctions HModifie et HSupprime sont basées sur des fonctions de curseur. Ce ne sont pas des requêtes de type UPDATE ou DELETE, mais des appels de fonctions portant sur un curseur.

Le problème d’un tel fonctionnement, c’est que la rubrique parcourue par le curseur n’est pas forcément une clé primaire, et elle peut être modifiée à tout moment par un autre utilisateur.

Prenons pour exemple le code suivant, qui désactive les clients n’ayant pas fait d’achat au moins aussi récent que DateMin :
// Programmation dans le style de HF par opposition au SQL
POUR TOUT Client SUR DateDernierAchat // Clé avec doublon
SI DateDernierAchat >= DateMin ALORS
SORTIR // Fin du parcours car la date minimum est atteinte
FIN
Client.Actif = Faux
HModifie(Client)
FIN
Le problème de cet exemple, outre le fait qu’on puisse le remplacer par une simple requête, c’est qu’entre le moment où le curseur s’est positionné sur la ligne Client et le moment où on appelle HModifie, un achat a pu être enregistré et la valeur de la clé DateDernierAchat a donc pu être modifiée.

Le résultat sera une erreur WLangage (ie. un plantage, ou une exception si vous préférez), car le curseur était basé sur une clé qui est devenue invalide.

On peut facilement le démontrer en ajoutant, juste avant le HModifie, l’exécution d’une requête de type UPDATE pour modifier cette date.

Dans notre cas, nous avons banni HModifie et HSupprime, ne gardant que HAjoute, mais comme notre projet avait la contrainte de rester dans un style HF, nous avons écrit notre propre HModifie qui génère automatiquement une requête de type UPDATE.

Maîtriser le type « source de données »

Le type source de données est un faux ami dans WinDev.

On l’utilise principalement parce qu’il permet de déclarer un résultat de requête localement, qui sera automatiquement libéré quand la variable sera hors de portée.

Mais le problème de ce type de données, c’est qu’il n’a pas réellement la même portée que le résultat de requête qu’il représente. Dans WinDev une requête est nommée, et son nom a toujours une portée globale. Pour comprendre le problème que ça pose, voici un exemple :
PROCEDURE ChargeClients()
sdReq est une source de donnée // contient le nom de requête « sdReq »

HExécuteRequêteSQL(sdReq, « SELECT * FROM Client »)
// On vient de créer la requête « sdReq »
POUR TOUT sdReq
ChargeFacturesClient(sdReq.IDClient)
// Suite à l’appel de ChargeFacturesClient, le parcours plante
FIN
PROCEDURE ChargeFacturesClient(nIDClient)
sdReq est une source de donnée // contient aussi le nom de requête « sdReq »

HExécuteRequêteSQL(sdReq, « SELECT * FROM Facture WHERE IDClient =  » + nIDClient)
// On vient de recréer la requête « sdReq »
// Celle contenant la liste de clients est déjà perdue à ce moment là
POUR TOUT sdReq
ChargeFacture(sdReq)
FIN
// On arrive en fin de portée pour sdReq, la requête « sdReq » va être libérée
Pourquoi le parcours des clients plante-t-il après avoir chargé les factures du premier ?
Parce que dans les deux fonctions on a utilisé une source de données nommée sdReq, et donc implicitement un résultat de requête nommé « sdReq » (avec les guillemets). La requête de chargement des factures a écrasé celle de chargement des clients, alors que les deux ont été déclarées localement !

Notre solution a été de générer un nom de requête unique dans notre version de HExécuteRequêteSQL. La « valeur » d’une source de données est le nom de la requête, donc on peut écrire par exemple :
sdReq est une source de données = « req » + DonneIdentifiant()

Conclusion

Ce qui était une contrainte au départ, à savoir utiliser SQL Server sans surcoût et donc sans accès natif, s’est révélé être un choix tout à fait judicieux, nous permettant d’accéder gratuitement à toute la puissance de SQL Server. Nous tenons là une alternative à HF C/S beaucoup plus puissante mais pas beaucoup plus coûteuse en développement.