I. Introduction▲
OLEDB a été conçu pour remplacer ODBC et uniformiser les accès aux bases de données. En fait, OLEDB va bien au-delà et permet d'accéder à toute source de données pour laquelle on dispose d'un provider OLEDB. Il peut s'agir d'un SGBD, mais également d'un fichier Excel…
OLEDB est une API bas niveau, conçue pour donner les performances optimales. Cependant, ces performances viennent au détriment de la facilité d'utilisation. Aussi, Microsoft a défini ADO par dessus OLEDB afin de simplifier son usage.
Dans Delphi, cette couche ADO est elle-même encapsulée dans les composants dbGO.
Or comme on a pu le voir avec le comparatif sur les API d'accès aux données, cet empilement de couches dégrade les performances de façon significative.
Pour retrouver les performances originelles, il faut appeler OLEDB directement, en court-circuitant la couche ADO et l'encapsulation dbGO.
Dans cet article, nous allons voir les principes d'utilisation d'OLEDB. Nous allons développer nos propres composants d'accès aux données afin d'utiliser facilement OLEDB dans nos applications Delphi.
Nous allons effectuer tous les développements avec SQL Server 2005. Cependant, OLEDB étant une API générique, rien n'interdit d'ouvrir la connexion sur un autre SGBD…
Télécharger les sources de l'article▲
Tout au long de cet article, nous allons développer un jeu de composants pour encapsuler les appels à OLEDB. Dans ce tutoriel nous allons voir les bases de OLEDB et écrire la classe TOleDbConnection afin de gérer la connexion OLEDB. Nous allons également développer la classe TOleDbDataSet pour lire et manipuler le résultat d'une requête.
Les développements s'appuieront sur les éléments suivants :
- ETW : Pour tracer les requêtes SQL et bénéficier d'un profiler SQL.
- TMemoryDataSet : Pour mémoriser et manipuler les résultats d'une requête OLEDB.
|
Description |
Emplacement |
|---|---|
|
ETW |
ftp://ftp-developpez.com/fsoriano/archives/etw/delphi/fichiers/etw-sources.zip |
|
OLEDB |
|
|
L'article au format docx |
L'ensemble des codes sources compile avec Turbo Delphi Explorer.
I-A. Organisation du code source▲
Dans le prochain tutoriel, nous verrons comment utiliser OLEDB pour remplir une table SQL très rapidement grâce au chargement en blocs de SQL Server (L'équivalent de DTS, ou de la classe SqlBulkCopy d'ADO.NET).
Aussi, pour les besoins des deux tutoriels, les sources sont organisées selon une hiérarchie à deux niveaux : les classes TCustomOleDbConnection et TCustomOleDbDataset implémentent les fonctionnalités OLEDB communes pour les deux articles.
Ensuite, les classes TOleDbConnection et TOleDbDataSet dérivent des deux précédentes pour implémenter les méthodes spécifiques pour ce tutoriel.
II. Mise en Å“uvre de OLEDB▲
II-A. Connexion/Déconnexion▲
Concrètement, OLEDB est une bibliothèque d'objets COM. Pour l'utiliser, il suffit donc d'importer la TLB dans Delphi, puis d'instancier les objets dont on a besoin comme pour n'importe quel objet COM.
En fait, c'est encore plus simple. L'unité d'import d'OLEDB est déjà fournie en standard dans Delphi : il s'agit de l'unité OleDb.pas que l'on peut utiliser directement.
OLEDB est une API générique, indépendante du SGBD. Cette indépendance est obtenue grâce aux providers OLEDB. Chaque SGBD doit fournir un provider OLEDB qui respecte les spécifications OLEDB. Dans nos applications clientes, on se contente d'instancier le provider correspondant à nos besoins, puis de l'utiliser par l'intermédiaire de différentes interfaces. C'est exactement le même principe que pour ADO.NET.
II-A-1. La classe TOleDbConnection▲
Nous devons commencer par définir une classe dérivée de TCustomConnection pour gérer la connexion OLEDB. Cette dernière est relativement simple à écrire. En fait, il suffit de surcharger les méthodes suivantes :
- DoConnect : Cette méthode est appelée automatiquement par TCustomConnection pour ouvrir la connexion à la base.
- DoDisconnect : Cette méthode est appelée par TCustomConnection pour fermer la connexion.
- GetConnected : Cette fonction renvoie True ou False pour indiquer si la connexion est ouverte.
II-A-2. Ouvrir la connexion : DoConnect▲
Tout commence donc par l'instanciation d'un provider. Sur le principe, il s'agit d'un simple objet COM. Il suffit donc de connaître son CLSID pour l'instancier.
Pour travailler avec SQL Server, on a le choix entre différents providers :
- Microsoft OLEDB provider for SQL Server : C'est le provider historique disponible depuis les débuts d'OLEDB et ADO. Ce dernier ne permet pas d'exploiter les dernières nouveautés de SQL 2005.
- Microsoft OLEDB provider for ODBC Drivers : C'est en fait une couche d'adaptation qui permet à OLEDB de travailler avec un driver ODBC. Cette solution permet d'utiliser OLEDB lorsqu'on ne dispose pas d'un provider OLEDB approprié.
- SQL Native client : Son nom n'est pas très explicite, mais il s'agit bel et bien du nouveau provider OLEDB recommandé pour accéder à une instance SQL 2005. C'est lui que nous allons utiliser.
Son CLSID est le suivant :
CLSID_SQLOLEDB : TGUID = '{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}';On peut ainsi instancier l'objet COM. Ce dernier retourne une interface IDBInitialize permettant d'ouvrir la connexion.
Cependant, avant d'ouvrir la connexion, il faut commencer par définir les propriétés de l'objet (Login, password, nom du serveur…). Les propriétés à initialiser, ainsi que les valeurs à définir sont spécifiques à chaque provider.
Si on garde cette approche, le code va vite devenir difficilement maintenable. La procédure de connexion est spécifique au provider, donc au SGBD. Pour une API générique, ce n'est pas ce qu'il y a de mieux.
Pour simplifier le problème, OLEDB définit et implémente un service indépendant des providers. Il s'agit du service DataLink. Ce dernier n'est rien d'autre qu'une Factory pour les providers. Il permet d'instancier et d'initialiser n'importe quel provider à partir d'une chaîne de connexion, comme pour ADO.
L'ouverture de la connexion est alors grandement facilitée puisque tout est défini dans la chaîne de connexion :
On commence par instancier un objet CLSID_MSDAINITIALIZE. Il s'agit du service OleDb pour le Data link. Ce dernier implémente l'interface IDataInitialize. Elle existe dans deux versions pour Delphi, une version qui utilise la convention SafeCall (IDataInitializeSC), et une version avec la convention StdCall. La version SafeCall gère automatiquement les codes de retour HResult. C'est elle que nous utilisons :
var FDataInitialize : IDataInitializeSC;
...
OleCheck(CoCreateInstance(CLSID_MSDAINITIALIZE, nil, CLSCTX_INPROC_SERVER, IID_IDataInitialize,
FDataInitialize));Lorsqu'on dispose d'IDataInitialize, il n'y a plus qu'à demander l'objet DataSouce (le provider) défini dans la chaîne de connexion. C'est ce qu'on fait en appelant la méthode GetDataSource.
var
Unknown : IUnknown;
FDbINitialize : IDbInitialize;
...
FDataInitialize.GetDataSource(nil, CLSCTX_INPROC_SERVER,
PWideChar(FConnectionString), IID_IDBInitialize, Unknown);
FDbInitialize := Unknown as IDBInitialize;On obtient alors une interface IDbInitialize, qui référence un objet DataSource instancié et préinitialisé. Cependant, la connexion à la base n'est pas ouverte pour autant. Il faut encore l'initialiser en appelant Initialize, et ouvrir une session avec la méthode CreateSession de l'interface IDBCreateSession :
// On ouvre la connexion sur la source de données.
OleDbCheck(FDbInitialize.Initialize);
// Il ne reste plus qu'à créer une session par défaut.
FSession := nil;
OleDbCheck((FDbInitialize as IDBCreateSession).CreateSession(nil, IID_IOpenRowset, FSession));Rassemblons le tout dans la méthode DoConnect. On en profite au passage pour tracer l'ouverture de la connexion avec ETW.
procedure TOleDbConnection.DoConnect;
var
Unknown : IUnknown;
FDataInitialize : IDataInitializeSC;
begin
// Tout d'abord, la chaîne de connexion doit avoir été définie
if FConnectionString = ''
then raise EOleDbException.Create('La chaîne de connexion n''est pas définie !');
SQLLogger.Trace(EVENT_SQL_INFO, 'Ouverture de la connexion : ' +
FConnectionString, TRACE_LEVEL_INFORMATION);
try
// Enfin, on a besoin d'un accès à IMAlloc pour gérer certaines allocations
// de mémoire.
OleCheck(CoGetMalloc(1, FMAlloc));
// L'initialisation de la connexion est faite indirectement,
// par l'intermédiaire du service OLEDB gérant les chaînes de connexion.
// On commence donc par se connecter à ce service, en instanciant un
// objet CLSID_MSDAINITIALIZE.
OleCheck(CoCreateInstance(CLSID_MSDAINITIALIZE, nil, CLSCTX_INPROC_SERVER, IID_IDataInitialize,
FDataInitialize));
// Dans un deuxième temps, on crée l'objet DataSource oledb à partir
// de la chaîne de connexion. L'objet ainsi créé est déjà initialisé.
// Il ne restera plus qu'Ã ouvrir la connexion.
Unknown := nil;
FDataInitialize.GetDataSource(nil, CLSCTX_INPROC_SERVER,
PWideChar(FConnectionString), IID_IDBInitialize, Unknown);
FDbInitialize := Unknown as IDBInitialize;
FDataInitialize := nil; // On n'a plus besoin d'accéder au service !
// On ouvre la connexion sur la source de données.
OleDbCheck(FDbInitialize.Initialize);
// Il ne reste plus qu'à créer une session par défaut.
FSession := nil;
OleDbCheck((FDbInitialize as IDBCreateSession).CreateSession(nil, IID_IOpenRowset, FSession));
// On essaie d'obtenir l'interface ITransactionLocal pour la gestion des
// transaction. Cependant, il se peut que le provider OLEDB ne gère pas les
// transaction et n'implémente pas l'interface ITransactionLocal.
if FSession.QueryInterface(IID_ITransactionLocal, unknown) = S_OK
then FTransaction := unknown as ITransactionLocal
else FTransaction := nil;
// La connexion a été effectuée.
SQLLogger.TraceConnect;
except
on e:exception do
begin
// En cas d'erreur à la connexion, on trace l'exception.
SQLLogger.TraceException(e);
// Puis on nettoie les interfaces.
FSession := nil; // La connexion a échoué.
FDbInitialize := nil;
FDataInitialize := nil;
// On redéclenche l'exception.
raise;
end;
end;
end;Remarque : DoConnect fait également appel à une méthode OleDbCheck. Cette dernière est utilisée pour la gestion des erreurs et sera expliquée plus loin.
II-A-3. Déconnexion : DoDisconnect▲
La déconnexion quant à elle est vraiment très simple à implémenter. Il suffit d'appeler la méthode Uninitialize de l'interface IDBInitialize obtenue au moment de l'ouverture de la connexion.
Cependant, avant de l'appeler il faut veiller à libérer toutes les références aux autres objets OLEDB utilisés par la connexion. En particulier l'objet session.
On implémente DoDisconnect de la façon suivante :
// Est appelée par TCustomConnection pour fermer la connexion.
procedure TCustomOleDbConnection.DoDisconnect;
begin
SQLLogger.Trace(EVENT_SQL_INFO, 'Fermeture de la connexion : ' +
FConnectionString, TRACE_LEVEL_INFORMATION);
try
FTransaction := nil;
// Il faut commencer par libérer l'objet Session.
FSession := nil;
// On ferme la connexion à la source de données.
OleDbCheck(FDbInitialize.Uninitialize);
// Puis il ne reste plus qu'à libérer les interfaces.
FDbInitialize := nil;
// Enfin, on trace la déconnexion.
SQLLogger.TraceDisconnect;
except
on e:exception do
begin
// En cas d'erreur à la connexion, on trace l'exception.
SQLLogger.TraceException(e);
raise; // Et on redéclenche l'exception.
end;
end;
end;II-A-4. Savoir si on est connecté : GetConnected▲
Dans notre mécanisme de connexion, on demande une référence à IDbInitialize, et on crée une session avec IDBCreateSession.CreateSession. Lors de la déconnexion, on libère ces références.
Aussi GetConnected va simplement se contenter de regarder si la référence de la session est définie :
// Indique si la connexion est ouverte.
function TCustomOleDbConnection.GetConnected: Boolean;
begin
result := Assigned(FSession);
end;II-A-5. Comment construire une chaîne de connexion ?▲
On a vu qu'on pouvait ouvrir une connexion et la configurer grâce à une chaîne de connexion. Maintenant, il serait intéressant d'avoir une boîte de dialogue pour construire automatiquement cette chaîne, comme pour ADO.
OLEDB permet de le faire avec les interfaces IDBPromptInitialize et IDataInitialize. IDBPromptInitialize permet d'afficher la boîte de dialogue et retourne un objet provider préinitialisé. IDataInitialise possède une méthode qui retourne la chaîne de connexion correspondant à un objet initialisé. Il suffit donc d'appeler les deux successivement :
// Affiche la boîte de dialogue permettant de construire la chaîne de connexion.
// La méthode renvoie la chaîne de connexion ainsi construite si l'utilisateur
// valide la sélection.
// Autrement, la méthode retourne une chaîne vide.
// <ParenthWnd> peut être utilisé pour indiquer le Handle de la fenêtre parente
// de la boîte de dialogue.
class function TOleDbConnection.PromptConnexionString(
OldConnexionString : widestring ='';
ParenthWnd : cardinal = 0) : widestring;
var
DBPromptInitialize : IDBPromptInitialize;
DBInitialize : IUnknown;
DataInitialize : IDataInitialize;
hResult : integer;
CntStr : PWideChar;
begin
// On instancie les services OLEDB.
OleCheck(CoCreateInstance(CLSID_DATALINKS, nil, CLSCTX_INPROC_SERVER,
IID_IDBPromptInitialize, DBPromptInitialize));
OleCheck(CoCreateInstance(CLSID_MSDAINITIALIZE, nil, CLSCTX_INPROC_SERVER,
IID_IDataInitialize, DataInitialize));
if OldConnexionString<>''
then begin
// Si une chaîne de connexion a été fournie, on va l'utiliser pour initialiser
// la boîte de dialogue.
// Pour cela, on crée l'objet à partir de la chaîne de connexion fournie.
DBInitialize := nil;
DataInitialize.GetDataSource(nil, CLSCTX_INPROC_SERVER,
PWideChar(OldConnexionString), IID_IDBInitialize, DBInitialize);
end
else DBInitialize := nil;
// Puis on appelle le dialogue prédéfini.
// Si une chaîne de connexion initiale a été fournie, elle a permis d'initialiser
// DBInitialize. PromptDataSource va alors s'en servir pour préinitialiser
// la boîte de dialogue.
hResult := DBPromptInitialize.PromptDataSource(nil,
ParenthWnd, // Handle de la fenêtre parent
DBPROMPTOPTIONS_PROPERTYSHEET, // Paramétrage de la connexion
0, // Pas de filtre sur les providers autorisés.
nil, // Pas de filtre sur les providers autorisés.
nil, // Pas de filtre sur les providers autorisés.
IID_IDBInitialize, // IID de l'interface à retourner.
DBInitialize); // Interface de retour.
case hResult of
S_OK: // L'utilisateur a validé la sélection
begin
// On appelle GetInitializationString pour connaître la chaîne de connexion
OleCheck(DataInitialize.GetInitializationString(DBInitialize, true,
CntStr));
result := cntStr;
end;
DB_E_CANCELED: // L'utilisateur a annulé la boîte de dialogue.
result := '';
else OleCheck(hResult);
end;
end;À l'exécution, le résultat est le suivant :
Ça vous rappelle quelque chose ? Eh oui, c'est exactement la boîte de dialogue ADO. En fait, ADO n'étant qu'une surcouche par-dessus OLEDB, c'est ADO qui utilise le dialogue OLEDB.
II-B. Gestion des Erreurs OLEDB et messages d'informations▲
OLEDB est une bibliothèque d'objets COM. Ces derniers vont donc naturellement s'appuyer sur le mécanisme des exceptions COM pour remonter les erreurs. Ainsi, si une erreur survient lors de l'appel d'une méthode, cette dernière va déclencher une exception COM, qui remontera à l'appelant par l'intermédiaire du HResult.
Si on utilise la convention d'appel safecall dans les interfaces, Delphi va automatiquement tester les hresult à notre place, et déclencher les exceptions pour nous. On pourrait donc se contenter de ce principe dans un premier temps.
Cependant, OLEDB utilise un dispositif plus riche pour remonter les erreurs. Lorsqu'on appelle une méthode, si cette dernière échoue, au lieu de remonter une seule erreur, OLEDB remonte une liste d'erreurs selon un mécanisme spécial. Avec la gestion standard des exceptions COM, on ne récupère que la première exception de la liste. Or bien souvent, cette dernière se contente de dire que l'appel a échoué, et c'est la suivante qui explique la cause de l'erreur.
Par exemple, si on essaie d'exécuter une requête SQL qui contient une erreur de syntaxe, OLEDB va retourner une erreur du style : « Erreur lors de la préparation de l'instruction ». Il faut aller fouiller dans la liste des erreurs pour voir que le Prepare a échoué parce que le SGBD a retourné une erreur : « erreur de syntaxe ligne … colonne … ».
Voyons donc comment récupérer la liste complète des erreurs :
Tout d'abord, nous devons faire la gestion des erreurs nous-mêmes, et ne pas utiliser la gestion par défaut faite par Delphi. Cela implique qu'on ne doit utiliser que les interfaces définies avec la convention d'appel stdcall.
Chaque appel d'une méthode d'un objet COM retourne un code de retour : le HResult. Ce dernier est encodé d'une façon particulière. Le bit de poids fort est positionné à 1 pour indiquer une erreur et à 0 en cas de succès. Delphi fournit la fonction Succeeded pour tester si un Hresult donné indique une erreur.
En cas d'erreur, il faut appeler la fonction GetErrorInfo pour obtenir les informations relatives à l'erreur. Cette fonction renvoie une interface IErrorInfo avec le code et le message d'erreur. Ça, c'est la gestion standard des exceptions COM. Comme on peut le voir, il n'est pas possible d'obtenir une liste d'erreur de cette manière.
Avec OLEDB, une fois qu'on a obtenu l'interface IErrorInfo, il suffit en fait de s'en servir pour obtenir l'interface IErrorRecords. Cette dernière permet tout simplement d'obtenir une liste d'erreurs IErrorInfo. Cependant IErrorInfo peut ne pas être suffisant pour décrire complètement une erreur. C'est pourquoi OLEDB prévoit également la méthode GetCustomErrorObject pour que chaque provider puisse fournir sa propre description de l'erreur.
À ce titre, les providers SQL Server définissent l'interface ISQLServerErrorInfo. Cette dernière sert à identifier le message d'erreur, mais aussi le numéro de l'erreur, le numéro de la ligne où elle s'est déclenchée, ainsi que la gravité de l'erreur.
Pour SQL Server, le niveau de gravité est très important. En effet, une commande SQL peut retourner des erreurs, mais également des messages d'information qui ne sont pas des erreurs, mais qui sont malgré tout renvoyés sous la forme d'une erreur de gravité faible.
Par exemple, si on fait un PRINT dans un script, le message du script remonte dans OLEDB sous la forme d'une erreur de gravité 0.
Pour que les choses soient encore un peu plus complexes, pour un PRINT, le message remonte sous la forme d'une erreur, mais le hResult de l'appel ayant déclenché le PRINT indique lui que tout s'est bien passé… De plus, un même appel peut très bien retourner à la fois des messages d'informations et des erreurs.
Ainsi, pour gérer correctement les erreurs avec SQL Server, il faut ignorer la valeur du hResult et toujours essayer de lire la description complète des erreurs. Pour chaque erreur, il faut tester le niveau de gravité pour savoir s'il s'agit d'une erreur ou d'un message d'information. C'est la gestion que nous allons implémenter avec la méthode OleDbCheck.
procedure TCustomOleDbConnection.OleDbCheck(hResult : integer);
var
ErrInfo : IErrorInfo;
ErrorInfo2 : IErrorInfo;
Errors : IErrorRecords;
nbError : cardinal;
i : integer;
Description : WideString;
ErrMsg : widestring;
ProcedureName : widestring;
msg : string;
unknown : IUnknown;
SQLServerErrorInfo : ISQLServerErrorInfo;
SQLServerError : PSSERRORINFO;
SQLServerErrorMessage : PWideChar;
begin
ErrMsg := '';
// Premièrement, on regarde si des informations sont disponibles sur l'erreur en cours.
GetErrorInfo(0, ErrInfo);
if Assigned(ErrInfo) // On a réussi à obtenir des informations.
then begin
// OLEDB étend la gestion des exceptions COM de façon à retourner une liste d'exceptions
// (la liste des exceptions à l'origine de l'erreur) au lieu d'une seule et unique erreur.
// Si on veut connaître la cause de l'erreur, il faut lire la liste complète.
Errors := ErrInfo as IErrorRecords;
// Maintenant, il ne reste plus qu'Ã parcourir la liste des erreurs.
Errors.GetRecordCount(nbError);
for i := 0 to nbError-1 do
begin
// Si on travaille avec SQL Server, on peut obtenir des informations plus complètes sur
// les erreurs avec l'interface ISQLServerErrorInfo. On commence donc par rechercher si
// ces informations sont disponibles. Si le provider OLEDB n'est pas un provider SQL Server,
// l'interface ISQLServerErrorInfo ne sera pas disponible.
unknown := nil;
Errors.GetCustomErrorObject(i, IID_ISQLServerErrorInfo, unknown);
if Assigned(unknown)
then begin
SQLServerErrorInfo := unknown as ISQLServerErrorInfo;
SQLServerError := nil;
SQLServerErrorMessage := nil;
if SQLServerErrorInfo.GetErrorInfo(SQLServerError, SQLServerErrorMessage) = S_OK
then begin
if Assigned(SQLServerError)
then begin
try
// À présent, on construit le message d'erreur à partir des informations :
// Le champ bClass indique le niveau de gravité de l'erreur. Les valeurs
// inférieures à 10 indiquent qu'il s'agit d'un simple message d'information.
// Les valeurs supérieures à 10 désignent les erreurs.
if SQLServerError.bClass <= 10 // Il s'agit d'un message d'information
then begin
// On ajoute le message à la liste des messages d'information.
InfoMessages.Add(SQLServerError.pwszMessage);
end
else begin
// Il s'agit d'une erreur. On met en forme le message d'erreur dans msg.
msg := 'MSG-' + IntToStr(SQLServerError.lNative) + ', '; // Numéro de l'erreur
// Traitement du nom de la procédure stockée à l'origine de l'erreur
ProcedureName := SQLServerError.pwszProcedure;
if ProcedureName<>''
then msg := ProcedureName + ', ';
// Ajout du numéro de ligne de l'erreur
msg := msg + 'Line ' + IntToStr(SQLServerError.wLineNumber) + ', ';
// Enfin, on termine avec le message de l'erreur.
msg := msg + SQLServerError.pwszMessage;
// Il s'agit d'une erreur. On l'ajoute au message complet des erreurs.
ErrMsg := ErrMsg + msg + #13#10;
end;
finally
if Assigned(SQLServerError)
then FMAlloc.Free(SQLServerError);
if Assigned(SQLServerErrorMessage)
then FMAlloc.Free(SQLServerErrorMessage);
end;
end;
end;
end
else begin
// Il ne s'agit pas d'une erreur SQLServer, on traite l'erreur de façon classique.
// On commence par lire l'erreur n°i dans ErrorInfo2
OleCheck(Errors.GetErrorInfo(i, GetSystemDefaultLCID, ErrorInfo2));
OleCheck(ErrorInfo2.GetDescription(Description));
ErrMsg := ErrMsg + Description + #13#10;
end;
end;
end;
// Enfin, il ne reste plus qu'à déclencher l'exception en cas d'erreur.
// Normalement, s'il y a eu une erreur, hResult doit contenir un code
// d'erreur et ErrMsg la description de l'erreur.
if (ErrMsg<>'') or (not Succeeded(hResult))
then begin
if ErrMsg<>''
then raise EOleDbException.Create(ErrMsg) // On déclenche une erreur spécifique OLEDB
else raise EOleSysError.Create('', hResult, 0); // On déclenche une erreur système OLE
end;
end;L'interface IErrorRecords est spécifiée par OLEDB. Cependant l'unité OleDb de Delphi ne contient pas sa déclaration (probablement parce que cette dernière n'est pas compatible OLE Automation). Nous devons donc la déclarer nous même :
type
// L'interface IErrorRecords fait parti de OLEDB. Cependant l'unité OleDb.pas ne la déclare
// pas. On doit donc la déclarer nous-mêmes.
IErrorRecords = interface(IUnknown)
['{0c733a67-2a1c-11ce-ade5-00aa0044773d}']
function AddErrorRecord(pErrorInfo : PErrorInfo;
dwLookupID : cardinal;
pdispparams : pointer;
punkCustomError : IUnknown;
dwDynamicErrorID : cardinal) : HResult; stdcall;
function GetBasicErrorInfo(ulRecordNum : cardinal;
pErrorInfo : PErrorInfo) : HResult; stdcall;
function GetCustomErrorObject(ulRecordNum : cardinal;
const riid : TGUID;
var ppObject : IUnknown) : HResult; stdcall;
function GetErrorInfo(ulRecordNum : cardinal;
lcid : cardinal;
var ppErrorInfo : IErrorInfo) : HResult; stdcall;
function GetErrorParameters(ulRecordNum : cardinal;
pdispparams : pointer) : HResult; stdcall;
function GetRecordCount(var pcRecords : cardinal) : HResult; stdcall;
end;De même l'interface ISQLServerErrorInfo n'est définie que dans le fichier entête de SQL Native Client. On doit donc également la déclarer nous même :
ISQLServerErrorInfo = interface(IUnknown)
['{5CF4CA12-EF21-11d0-97E7-00C04FC2AD98}']
function GetErrorInfo(out ppErrorInfo : PSSERRORINFO;
out Error : PWideChar) : Hresult; stdcall;
end;La méthode GetErrorInfo retourne une structure SSERRORINFO définie de la façon suivante :
// Enregistrement relatif à une erreur SQL Server
PSSERRORINFO = ^SSERRORINFO;
SSERRORINFO = packed record
pwszMessage : PWideChar;
pwszServer : PWideChar;
pwszProcedure : PWideChar;
lNative : cardinal;
bState : byte;
bClass : byte;
wLineNumber : word;
end;Lors des appels aux méthodes OLEDB, il ne restera plus qu'à tester chaque code de retour avec OleDbCheck.
En cas d'erreur, OleDbCheck déclenche une exception avec la description complète de l'erreur. Les messages d'informations sont filtrés et ajoutés à la fin de la liste InfoMessages de TOleDbConnection. Cette liste sera vidée chaque fois qu'on exécute une nouvelle requête avec OpenSQL ou ExecSQL.
Finalement, OleDbCheck est capable de traiter n'importe quelle erreur issue d'un provider OLEDB quelconque. En revanche, elle a été enrichie pour traiter également les spécificités de SQL Server.
Si on veut faire un composant spécialisé pour un autre provider, on devra sûrement traiter d'autres interfaces étendues pour la gestion des erreurs. Dans ce cas, il sera sans doute intéressant de rendre la méthode OleDbCheck virtuelle, afin qu'elle puisse être surchargée dans un composant spécialisé.
II-C. Exécuter une requête ne renvoyant aucun résultat▲
Nous avons vu comment nous connecter à la base, et comment traiter les erreurs. Maintenant on va pouvoir commencer à entrer dans le vif du sujet.
Voyons tout d'abord comment exécuter une requête simple qui ne retourne aucun résultat (nous verrons comment lire les données retournées plus tard) et qui n'accepte aucun paramètre (nous verrons les requêtes paramétrées également plus tard).
La première chose à faire est de créer un objet Command. Pour cela, on utilise l'interface IDBCreateCommand et sa méthode CreateCommand, à partir de la session obtenue lors de l'ouverture de la connexion :
OleDbCheck((FSession as IDBCreateCommand).CreateCommand(nil,
IID_ICommandText, unknown));Puis, on initialise le traitement de la commande avec la requête SQL. Il suffit d'utiliser la méthode SetCommandText de l'interface ICommandText :
cmd := unknown as ICommandText;
cmd.SetCommandText(DBGUID_DEFAULT, PWideChar(SQL));Enfin, il ne reste plus qu'à exécuter la commande :
OleDbCheck(cmd.Execute(nil, IID_NULL, DbParams, nil, nil));DbParams est une structure TDBParams permettant de spécifier les paramètres de la requête. Comme cette dernière n'est pas paramétrée, on l'initialise de la façon suivante :
// Il n'y a pas de paramètres
DbParams.pData := nil;
DbParams.cParamSets := 0;
DbParams.HACCESSOR := 0;IID_NULL est un guid spécial indiquant le type d'interface qu'on souhaite obtenir en retour pour lire les résultats de la requête. Comme la requête ne doit pas renvoyer de données, cette valeur permet d'indiquer qu'on n'attend rien en retour.
Il faut le définir de la façon suivante :
const
IID_NULL: TGUID = '{00000000-0000-0000-0000-000000000000}';Il ne reste plus qu'à réunir le tout dans une méthode ExecSQL sur notre objet TOleDbConnection :
// Execute une commande SQL qui ne retourne pas de données.
// C'est la méthode la plus simple pour exécuter une requête SQL.
procedure TOleDbConnection.ExecSQL(const SQL: widestring; Params : TParams);
var t0 : int64;
cmd : ICommandText;
unknown : IUnknown;
OleDbParams : TOleDbParams;
begin
// Premièrement, on trace le début de la requête.
SQLLogger.TraceSQLBegin(SQL, t0);
try
try
CheckConnected; // On s'assure que la connexion est ouverte.
InfoMessages.Clear; // On réinitialise la liste des messages d'information
// Tout d'abord, il faut créer un objet Command pour exécuter la requête.
OleDbCheck((FSession as IDBCreateCommand).CreateCommand(nil, IID_ICommandText, unknown));
// Ensuite on initialise la requête SQL.
cmd := unknown as ICommandText;
cmd.SetCommandText(DBGUID_DEFAULT, PWideChar(SQL));
OleDbParams := TOleDbParams.Create(Params, cmd, self);
try
// Enfin on exécute la commande, sans attendre de résultats.
OleDbCheck(cmd.Execute(nil, IID_NULL, OleDbParams.Parameters, nil, nil));
// On met à jour la valeur des paramètres de sortie
OleDbParams.UpdateParams(Params);
finally
OleDbParams.Free;
end;
except
on e:exception do
begin
// En cas d'erreur, on trace l'exception.
SQLLogger.TraceException(e);
raise; // Et on redéclenche l'erreur.
end;
end;
finally
SQLLogger.TraceSQLEnd(SQL, t0); // Pour finir, on logue la fin de la requête
end;
end;Bien évidemment, on n'oublie pas d'instrumenter la méthode pour tracer l'exécution de la requête avec ETW. TraceSQLBegin et TraceSQLEnd vont tracer le début et la fin de l'exécution. En cas d'erreur, l'exception est elle-même tracée avec TraceException avant d'être redéclenchée.
II-D. Exécuter une requête de type SELECT▲
Maintenant, on va pouvoir commencer les choses sérieuses : Faire une requête de type SELECT. Ce type de requête est identique aux requêtes précédentes. Cependant, lorsque le serveur rend la main après avoir exécuté la commande, il reste encore à lire les données.
Nous allons définir la fonction OpenSQL dans la classe TOleDbConnection. Cette dernière attendra une requête SQL en entrée et renverra en sortie un objet TDataSet, initialisé et chargé avec le résultat de la requête.
Le code est très similaire à la méthode ExecSQL :
// Execute une requête SQL qui renvoie des données.
// La fonction retourne un DataSet déconnecté, initialisé avec le jeu de données obtenu
function TOleDbConnection.OpenSQL(const SQL: widestring;
Params: TParams; FetchSize : cardinal): TDataSet;
var
t0 : int64;
ds : TOleDbDataSet;
RowSet : IRowSet;
unknown : IUnknown;
cmd : ICommandText;
OleDbParams : TOleDbParams;
begin
ds := nil;
// Premièrement, on trace le début de la requête.
SQLLogger.TraceSQLBegin(SQL, t0);
try
try
CheckConnected; // On s'assure que la connexion est ouverte.
InfoMessages.Clear;
// Il faut créer un objet Command pour exécuter la requête
OleDbCheck((FSession as IDBCreateCommand).CreateCommand(nil, IID_ICommandText, unknown));
// On initialise la requête SQL.
cmd := unknown as ICommandText;
cmd.SetCommandText(DBGUID_DEFAULT, PWideChar(SQL));
unknown := nil;
// Si la requête est paramétrée, il faut initialiser les valeurs des paramètres. C'est le rôle
// de la classe TOleDbParams. Si elle n'attend pas de paramètre, TOleDbParams définit un jeu
// de paramètres vide.
OleDbParams := TOleDbParams.Create(Params, Cmd, self);
try
OleDbCheck(cmd.Execute(nil, IID_IRowset, OleDbParams.Parameters, nil, @unknown));
if Assigned(unknown)
then begin
RowSet := unknown as IRowSet;
unknown := nil;
// La source de données vient de rendre la main. On peut lire les données.
ds := TOleDbDataSet(CreateDataSet); // On instancie le DataSet qui sera retourné.
ds.LoadFromRowSet(RowSet, self, FetchSize); // Lecture des données.
RowSet := nil;
end
else ds := nil;
// Après l'exécution de la requête on met à jour les valeurs des paramètres de sortie.
OleDbParams.UpdateParams(Params);
finally
OleDbParams.Free;
end;
except
on e:exception do
begin
SQLLogger.TraceException(e);
if Assigned(ds)
then ds.Free;
raise;
end;
end;
finally
// Pour finir, on trace la fin de la requête
if Assigned(ds)
then SQLLogger.TraceSQLEnd(SQL + ', ' + IntToStr(ds.RecordCount) + ' Lignes', t0)
else SQLLogger.TraceSQLEnd(SQL, t0, Params);
end;
result := ds;
end;En fait, le code est identique, jusqu'au moment d'exécuter la commande. L'exécution de la commande s'effectue un peu différemment :
// Exécution de la requête sur la source de données.
OleDbCheck(cmd.Execute(nil, IID_IRowset, DbParams, nil, @unknown));
RowSet := unknown as IRowSet;Cette fois, au lieu d'utiliser IID_NULL, on demande une interface IRowSet avec IID_Rowset. C'est cette dernière qui va permettre de lire le résultat.
Ensuite, il faut créer le dataset qui sera renvoyé. Nous allons retourner un objet TMemoryDataSet. Il s'agit du dataset en mémoire que nous avons défini dans l'article précédent.
Le chargement OLEDB va tirer parti de l'organisation interne des données dans TMemoryDataSet pour s'effectuer à la vitesse grand V. Nous allons dériver TMemoryDataSet en TCustomOleDbDataSet et TOleDbDataSet et lui déléguer la gestion de ce chargement :
// La source de données vient de rendre la main. On peut lire les données.
ds := TOleDbDataSet(CreateDataSet); // On instancie le DataSet qui sera retourné.
ds.LoadFromRowSet(unknown as IRowSet, self); // Lecture des données.Voyons comment s'effectue la lecture des données dans LoadFromRowSet :
// Charge les données retournées par RowSet dans le Dataset.
// Le dataset est complètement initialisé : Les champs sont créés, le dataset
// est ouvert et chargé avec les données.
procedure TOleDbDataSet.LoadFromRowSet(RowSet: IRowSet; Cnt : TOleDbConnection; FetchSize : cardinal);
var t0 : int64;
begin
SQLLogger.TraceBegin(EVENT_SQL_START, ' Debut Fetch', t0);
try
Close; // On s'assure que le Dataset est fermé.
FPageSize := FetchSize;
Describe(RowSet as IColumnsInfo, cnt); // Il faut définir les champs en fonction du rowset.
if FetchSize<>0 // On initialise le FetchSize qui nous a été fourni
then FPageSize := FetchSize
else begin // Sinon, on essaie de calculer une valeur adaptée.
FPageSize := 8192 div RecordSize;
if FPageSize < 1
then FPageSize := 1;
end;
SQLLogger.Trace(EVENT_SQL_INFO, Format(' RecordSize=%d, PageSize=%d', [RecordSize, FPageSize]),
TRACE_LEVEL_VERBOSE);
Open; // On doit ouvrir le dataset pour pouvoir charger les données.
FetchAll(RowSet, cnt); // Charge les données du rowset dans le dataset.
First; // On se positionne au début du DataSet.
finally
SQLLogger.TraceEnd(EVENT_SQL_END, ' Fin Fetch', t0);
end;
end;Tout d'abord, la méthode est instrumentée pour indiquer le début et la fin du traitement. De cette façon, on pourra obtenir des traces d'exécution indiquant précisément le temps passé à l'exécution de la requête et le temps passé à lire les données.
Ensuite, on peut voir que le chargement est fait en quatre étapes :
- Premièrement, on définit les champs du DataSet en fonction du résultat de la requête, c'est-à -dire en fonction de la structure des données dans RowSet. On effectue cette opération avec la méthode Describe développée spécialement pour ce rôle.
- Ensuite, on ouvre le DataSet. L'ouverture engendre la création des TField et l'initialisation des buffers internes. À ce moment, le dataset est encore vide.
- Il ne reste alors plus qu'à l'alimenter avec les données. Cette opération est faite avec la méthode FetchAll.
- Enfin, on fait un First pour repositionner le Dataset sur le premier enregistrement et recharger les buffers de la ligne en cours.
II-D-1. Lecture de la structure du jeu de données : Describe▲
OLEDB nous retourne une interface IRowSet pour lire les données. Mais pour pouvoir faire la lecture encore faut-il savoir quels sont les champs à lire !
Pour cela, on dispose de la méthode GetColumnInfo de l'interface IColumnsInfo. Cette dernière retourne un tableau de structures DBCOLUMNINFO décrivant chaque champ à l'intérieur du jeu de résultat.
var
nbColumns : cardinal;
ColumnsInfo : PDBColumnInfo;
infos : PWideChar;
...
// On appelle GetColumnInfo pour obtenir un tableau décrivant la liste des colonnes présentes.
OleDbCheck((RowSet as IColumnsInfo).GetColumnInfo(nbColumns, ColumnsInfo, Infos));En sortie, nbColumns indique le nombre de champs présents dans RowSet, ColumnsInfo pointe sur un tableau de structures DBCOLUMNINFO et infos est un buffer contenant les noms des champs.
Il ne reste plus qu'à parcourir le tableau ColumnsInfo pour créer les TFieldDef du dataset :
procedure TCustomOleDbDataSet.Describe(RowSet: IColumnsInfo; Cnt : TCustomOleDbConnection);
var
nbColumns : cardinal;
ColumnsInfo : PDBColumnInfo;
infos : PWideChar;
Column : PDBColumnInfo;
columnName : widestring;
DataType : TFieldType;
DataSize : cardinal;
i : integer;
n : integer;
IsNullable : boolean;
begin
// On appelle GetColumnInfo pour obtenir un tableau décrivant la liste des colonnes présentes.
Cnt.OleDbCheck(RowSet.GetColumnInfo(nbColumns, ColumnsInfo, Infos));
try
FieldDefs.BeginUpdate;
Column := ColumnsInfo; // Pointe sur la description de la colonne en cours
try
FieldDefs.Clear;
for i := 0 to nbColumns-1 do
begin
DataSize := 0;
// Si la colonne n'a pas été nommée, on lui attribue un nom automatique
// sous la forme colX avec X indiquant le numéro de la colonne (à partir 1)
if Assigned(Column.pwszName)
then columnName := Column.pwszName
else columnName := 'col' + IntToStr(i+1);
if Trim(columnName) = ''
then columnName := 'col' + IntToStr(i+1);
// Ensuite il faut vérifier si le nom n'existe pas déjà et renommer les
// colonnes automatiquement en cas de besoin.
if FieldDefs.IndexOf(columnName)<>-1
then begin
n := 1;
columnName := columnName + '_';
while FieldDefs.IndexOf(columnName + IntToStr(n))<>-1 do
begin
inc(n);
end;
columnName := columnName + IntToStr(n);
end;
// A présent, on peut commencer à créer les champs.
case Column.wType of
DBTYPE_I1, DBTYPE_UI1, DBTYPE_I2: // Entier court signé
DataType := ftSmallint;
DBTYPE_UI2: // Entier court non signé
DataType := ftWord;
DBTYPE_UI4, DBTYPE_I4: // Entier, signé ou non signé
DataType := ftInteger;
DBTYPE_CY: // Type currency.
DataType := ftBCD;
DBTYPE_DATE, DBTYPE_DBDATE, DBTYPE_DBTIME: // Dates
DataType := ftDateTime;
DBTYPE_DBTIMESTAMP: // DateTime précis.
DataType := ftTimeStamp;
DBTYPE_BOOL: // Booléen
DataType := ftBoolean;
DBTYPE_R4, DBTYPE_R8, DBTYPE_DECIMAL, DBTYPE_NUMERIC: // Nombre décimal
DataType := ftFloat;
DBTYPE_I8, DBTYPE_UI8: // Entier long
DataType := ftLargeint;
DBTYPE_GUID: // GUID
begin
DataType := ftGuid;
DataSize := 38;
end;
DBTYPE_BYTES, DBTYPE_UDT: // Binaire
DataType := ftBlob;
DBTYPE_STR: // Chaîne de caractères
begin
// Si la longueur du champ dépasse une certaine longueur, on en fait un memo.
if Column.ulColumnSize<COLUMN_MAXSIZE
then begin
DataType := ftString;
DataSize := Column.ulColumnSize;
end
else DataType := ftMemo;
end;
DBTYPE_WSTR: // Chaîne de caractères Unicode UTF-16.
begin
// Si la longueur du champ dépasse une certaine longueur, on en fait un memo Unicode.
if Column.ulColumnSize <COLUMN_MAXSIZE
then begin
DataType := ftWideString;
DataSize := Column.ulColumnSize;
end
else DataType := ftWideMemo;
end;
else begin
raise EOleDbException.Create(
Format('Le type (%d) de la colonne %s n''est pas supporté !',
[Column.wType, columnName]));
end;
end;
IsNullable := (ColumnsInfo.dwFlags and DBCOLUMNFLAGS_MAYBENULL) <>0;
try
TFieldDef.Create(FieldDefs,
columnName,
DataType,
DataSize,
not IsNullable,
FieldDefs.Count);
except
on e : EDatabaseError do
begin
raise EOleDbException.CreateFmt('Erreur lors de la création du champ %s : %s',
[columnName, e.Message]);
end;
end;
// On peut passer à la colonne suivante :
integer(Column) := integer(Column) + sizeof(DBCOLUMNINFO);
end;
finally
FieldDefs.EndUpdate;
end;
CalcRecordSize; // calcule le buffer de stockage des lignes
finally
Cnt.FMAlloc.Free(ColumnsInfo);
Cnt.FMAlloc.Free(Infos);
end;
end;Le code ci-dessus tient compte de quelques particularités :
- Pour créer les TFieldDef, tous les champs doivent avoir un nom et on ne doit pas avoir deux champs avec le même nom. Or si la requête d'origine retourne des champs calculés sans les avoir nommés, SQL Server va nous renvoyer un jeu de résultats avec des champs sans noms. De même si on fait une jointure sur deux tables et qu'on retourne des champs qui portent le même nom, on obtiendra des champs de même nom dans le jeu de résultats. Aussi, Describe nomme automatiquement les champs sans nom et les renomme en cas de doublons.
- Avec SQL Server, les champs dont la longueur n'est pas limitée (varchar(max), varbinary(max)) remontent comme étant des champs de longueur -1 (ou $FFFFFFFF si on est en non signé). Describe teste ce cas particulier pour en faire des ftMemo.
- Lorsqu'on a fini de définir les TFieldDef, la classe TMemoryDataSet a besoin qu'on appelle CalcRecordSize avant qu'on puisse intervenir directement sur les buffers internes. Cette méthode est appelée automatiquement à l'ouverture du dataset. Cependant par sécurité, il vaut mieux l'appeler dès qu'on a fini de modifier FieldDefs.
- Enfin, lorsqu'on a fini de travailler avec ColumnsInfo, il faut libérer la mémoire qui a été allouée par OLEDB. Cette dernière a été allouée avec le gestionnaire de mémoire COM. Il faut la libérer de la même façon avec IMAlloc.Free.
Remarque sur les types de données utilisés▲
Describe doit effectuer un mapping entre les types de données physiques retournés par OLEDB et les types de données Delphi. Ces derniers ne se correspondent pas strictement. Ce n'est pas très grave, car OLEDB pourra effectuer certaines conversions automatiquement au moment du chargement des données.
On remarquera cependant que :
- Les types dates donnent des champs ftDateTime ou ftTimeStamp. Il n'est pas vraiment nécessaire de les détailler en ftDate, ftDateTime et ftTime puisque SQL Server ne gère qu'un seul type de données.
- Les nombres décimaux deviennent des ftFloat. Ça signifie que les champs de type décimal qui sont stockés en virgule fixe dans la base de données seront convertis en flottant dans Delphi. C'est un peu gênant en soi puisqu'on est alors contraint à travailler avec des flottants alors qu'on voulait des valeurs exactes. Cependant avec l'architecture db de Delphi on n'a pas vraiment d'autres choix. En effet, le type décimal peut être configuré avec un nombre quelconque de chiffres après la virgule. Or la seule façon de gérer ça en virgule fixe serait de passer par le type FMTBcd, puis de n'utiliser que ce type dans tous les calculs. Seulement OLEDB ne gère pas les FMTBcd, il utilise le type DBTYPE_NUMERIC pour gérer les virgules fixes.
- Par contre, le type money (DBTYPE_CY) est un type décimal, en virgule fixe avec 4 décimales. Il correspond exactement au type currency de Delphi. Ce dernier est géré dans Describe en le mappant sur ftBCD. En effet, contrairement à ce qu'on pourrait penser, le ftBCD est en réalité stocké dans les datasets sous la forme d'un currency (ftCurrency est en réalité un double…)
- Pour les GUID, on utilise le type ftGUID. On remarque cependant que pour un GUID, la taille du champ est de 38, alors que normalement un GUID ne fait que 16 octets. C'est parce qu’en réalité, Delphi ne gère pas les GUID comme type de champ db. La classe TGUIDField stocke le guid sous la forme d'un string. On définit donc un champ ftGUID de longueur 38, mais au moment de charger réellement les données, on dira à OLEDB qu'il s'agit d'un champ de type string
II-D-2. Chargement des données : FetchAll▲
Avec OLEDB, le chargement des données dans le dataset va s'effectuer à la vitesse grand V. En effet au lieu de devoir lire les données ligne par ligne et champ par champ, on va simplement demander à OLEDB de remplir directement les buffers utilisés par TMemoryDataSet.
II-D-2-a. Définition du Binding▲
Pour réaliser cette opération, on doit définir un binding entre la structure logique du jeu de données et la structure interne du buffer dans lequel on va le charger. Ce binding est défini par l'intermédiaire d'un Accesseur dans OLEDB.
On crée un accesseur avec la méthode CreateAccessor de l'interface IAccessor. Les accesseurs peuvent être de plusieurs types. Il y a notamment les accesseurs de données (DBACCESSOR_ROWDATA) qui définissent le binding à utiliser pour lire ou écrire les données d'une ligne du jeu de données et les accesseurs pour les paramètres (DBACCESSOR_PARAMETERDATA). Ces derniers servent à lire/écrire les valeurs des paramètres pour les requêtes paramétrées.
Le chargement des données doit ainsi débuter par la création d'un accesseur :
OleDbCheck((RowSet as IAccessor).CreateAccessor(
DBACCESSOR_ROWDATA + DBACCESSOR_OPTIMIZED,
FieldDefs.Count,
@Bindings[0],
RecordSize,
Accessor,
nil));Bindings est un tableau de structures DBBINDING définissant le binding à utiliser pour chaque champ. Il faut le renseigner avant de créer l'accesseur. Pour cela, on définit la méthode InitializeBindings. Cette dernière va initialiser le tableau en fonction des champs définis dans les FieldDefs, et donc de l'organisation des buffers de stockage à l'intérieur de la classe TMemoryDataset :
procedure TCustomOleDbDataSet.InitializeBindings;
var
i : integer;
FieldDef : TFieldDef;
Binding : PDBBinding;
begin
SetLength(Bindings, FieldDefs.Count);
for i := 0 to FieldDefs.Count-1 do
begin
FieldDef := FieldDefs[i];
Binding := @Bindings[i];
Binding.iOrdinal := i+1;
Binding.pTypeInfo := nil;
Binding.obValue := FFieldInfo[i].Offset+8; // Offset de la partie données du champ
Binding.obLength := FFieldInfo[i].Offset+4; // Offset de la partie longueur du champ
Binding.obStatus := FFieldInfo[i].Offset; // offset de la partie status du champ
Binding.cbMaxLen := FFieldInfo[i].Size; // taille de la zone données en octets.
Binding.dwPart := DBPART_VALUE or DBPART_LENGTH or DBPART_STATUS; // Les trois champs sont définis
Binding.pObject := nil;
Binding.pBindExt := nil;
Binding.dwFlags := 0;
Binding.eParamIO := DBPARAMIO_NOTPARAM; // Il ne s'agit pas d'un paramètre
Binding.dwMemOwner:= DBMEMOWNER_CLIENTOWNED; // Le client est propriétaire de la mémoire.
Binding.bPrecision:= 0;
Binding.bScale := 0;
Binding.wType := DBTYPE_STR;
case FieldDef.DataType of
ftString, ftFixedChar: // Chaîne de caractères
Binding.wType := DBTYPE_STR;
ftSmallint : // Entier court signé
Binding.wType := DBTYPE_I2;
ftWord: // Entier court non signé
Binding.wType := DBTYPE_UI2;
ftAutoInc, ftInteger: // Entier signé
Binding.wType := DBTYPE_I4;
ftBoolean: // Booléen
Binding.wType := DBTYPE_BOOL;
ftFloat: // Nombre flottant sur 64 bits
Binding.wType := DBTYPE_R8;
ftCurrency: // Nombre flottant sur 64 bits
Binding.wType := DBTYPE_R8;
ftBCD: // Nombre décimal en virgule fixe (currency)
Binding.wType := DBTYPE_CY;
ftDate, ftTime, ftDateTime : // Date de type TDateTime
Binding.wType := DBTYPE_DATE;
ftTimeStamp: // Date de type TTimeStamp
Binding.wType := DBTYPE_DBTIMESTAMP;
ftLargeint: // entier long 64 bits
Binding.wType := DBTYPE_I8;
ftWideString, ftFixedWideChar: // Chaîne de caractères UNICODE
Binding.wType := DBTYPE_WSTR;
ftMemo: // Le champ est un pointeur sur une chaîne de caractères.
Binding.wType := DBTYPE_STR + DBTYPE_BYREF;
ftBlob: // Le champ est un pointeur sur un tableau binaire
Binding.wType := DBTYPE_BYTES + DBTYPE_BYREF;
ftWideMemo: // Le champ est un pointeur sur une chaîne de caractères Unicode.
Binding.wType := DBTYPE_WSTR + DBTYPE_BYREF;
ftGUID: // Les GUID sont stockés en chaîne de caractères
Binding.wType := DBTYPE_STR;
else raise EOleDbException.Createfmt('Le type du champ %s n''est pas supporté !', [FieldDef.Name]);
end;
end;
end;Les structures DBBINDING sont renseignées de la façon suivante :
|
Champ |
Valeur |
|---|---|
|
iOrdinal |
Indique le numéro de la colonne (à partir de 1). On le définit simplement avec l'index du champ. |
|
obValue |
Cette zone indique où placer la valeur du champ à l'intérieur du buffer de destination. On doit indiquer l'offset de la zone de données par rapport au début du buffer. |
|
obLength |
Cette zone indique où mémoriser la longueur effective du champ pour les champs de longueur variable (varchar, nvarchar…). |
|
obStatus |
Cette zone indique où mémoriser le statut du champ. Il s'agit principalement d'indiquer si le champ possède une valeur ou s'il vaut NULL dans la base de données. |
|
cbMaxLen |
Cette zone indique la longueur maximale disponible à l'intérieur du buffer pour stocker les données du champ. Si la taille réelle du champ dépasse cette valeur, les données seront tronquées. |
|
dwPart |
Il s'agit d'un masque qu'on doit renseigner pour indiquer si les attributs obValue, obLength et obStatus ont été définis dans la structure et donc si leur valeur est valide. |
|
eParamIO |
Ce champ sert pour les accesseurs définissant des paramètres. Il sert à indiquer s'il s'agit d'un paramètre d'entrée ou de sortie. Pour un accesseur de données, on définit la valeur DBPARAMIO_NOTPARAM |
|
dwMemOwner |
Ce champ est important lorsqu'on veut lire les données d'un champ défini par référence (cf. wType). Il permet d'indiquer qui est propriétaire de la zone mémoire pointée par la référence. Avec DBMEMOWNER_CLIENTOWNED, c'est le client qui possède la mémoire et qui sera responsable de sa libération. Pour les champs qui ne sont pas définis par référence, dwMemOwner doit obligatoirement être défini à DBMEMOWNER_CLIENTOWNED. |
|
wType |
Ce champ est très important, c'est lui qui définit le type de données du champ. Il définit ainsi le format de stockage dans le buffer. Si on définit un type différent du type réel du champ, OLEDB se chargera d'effectuer les conversions avant d'alimenter le buffer. Ainsi pour les dates, on peut demander le type DBTYPE_DATE et laisser le provider se débrouiller pour nous fournir un TDateTime. Il est possible de spécifier l'option DBTYPE_BYREF dans le type de données. Ça signifie que le champ ne contient pas directement la valeur, mais un pointeur sur cette valeur. |
Ainsi, on se trouve dans la configuration suivante :
En fait, les structures DBBINDING sont équivalentes à la classe TFieldDef. Elles définissent l'organisation des données à l'intérieur des buffers des lignes du dataset final.
Pour les champs LOB (les champs dont la longueur maximale n'est pas limitée comme les memos, binaires…), on est confronté au fait qu'on ne peut pas connaître à priori la longueur du champ. On ne peut donc pas définir un buffer de taille suffisante pour charger la totalité du LOB. Avec OLEDB, on peut résoudre le problème très facilement grâce aux champs définis par référence.
Lors de la définition du type du binding, on positionne le flag DBTYPE_BYREF. Avec ce dernier, OLEDB ne remplira pas directement notre buffer avec les données du champ, mais définira un pointeur sur un autre buffer qui lui contiendra réellement les données voulues. Le buffer est alloué et rempli par le provider OLEDB. Il faudra simplement qu'on recopie cette valeur dans le dataset.
II-D-2-b. Lecture des données▲
Une fois l'accesseur défini, on peut s'occuper réellement de la lecture des données. Elle s'effectue en deux temps :
Dans un premier temps, on doit appeler la méthode GetNextRows pour demander au provider de lire un certain nombre de lignes auprès du SGBD. Durant cette étape, les lignes sont lues par blocs, mais les données restent à l'intérieur du provider. Elles seront renvoyées au client ligne par ligne lorsque ce dernier les demandes.
Ainsi, le provider commence par lire un certain nombre de lignes avant de les renvoyer au client. Ce nombre de lignes est ce qu'on appelle généralement le « prefetch ».
On effectue ce premier chargement de la façon suivante :
// Dans un premier temps, il faut demander au provider de récupérer les lignes depuis le serveur.
// Elles restent stockées dans des buffers internes au provider.
FetchResult := RowSet.GetNextRows(DB_NULL_HCHAPTER,
0, // On lit les lignes depuis la dernière lecture
FPageSize, // On lit autant de ligne que dans une page du dataset.
nbRow, // Nombre de lignes réellement lues.
RowHandle);
Cnt.OleDbCheck(FetchResult);GetNextRows retourne dans RowHandle un tableau contenant un handle pour chaque ligne qui a été lue. On peut ensuite utiliser ces handles pour obtenir les données des lignes. On remarquera au passage qu'après GetNextRows, on peut accéder directement à n'importe quelle ligne à partir de son handle. Il n'est pas nécessaire de respecter l'ordre séquentiel.
Lorsqu'on dispose du handle d'une ligne, on peut lire ses données grâce à l'accesseur défini précédemment :
Cnt.OleDbCheck(RowSet.GetData(RowHandle[i], Accessor, Data));- Data est un pointeur sur le buffer destiné à recevoir les données de la ligne. C'est-à -dire le buffer de stockage d'une ligne du dataset.
- Accessor désigne l'accesseur qui définit l'organisation du buffer Data.
On peut appeler GetData autant de fois qu'on veut pour une même ligne. Dans l'implémentation de TOleDbDataset, on a choisi de définir un seul accesseur qui regroupe tous les champs de la ligne. Cependant on aurait aussi pu définir un accesseur par champ et charger les champs un par un en appelant GetData autant de fois que ce qu'on a défini d'accesseurs (c'est ce que semble faire ADO).
Lorsqu'on a fini de travailler avec les données d'une ligne, on doit demander au provider de détruire les handles des lignes qu'il a créées. C'est ce qu'on fait avec ReleaseRows :
RowSet.ReleaseRows(nbRow, RowHandle, nil, nil, nil);Cette étape permet également au provider de libérer la mémoire allouée pour stocker les lignes.
Il ne reste plus qu'à réunir le tout pour écrire la méthode FetchAll :
// Charge les données retournées par IRowSet à l'intérieur du dataset ds.
procedure TOleDbDataSet.FetchAll(RowSet: IRowSet; Cnt : TOleDbConnection);
var
Accessor : HACCESSOR;
nbRow : cardinal;
RowHandle : PUintArray;
FetchResult : hResult;
i,j : integer;
FPosition : cardinal;
Data : Pointer;
FIeldData : PFieldData;
FieldOffset : cardinal;
ptrBlob : pointer;
begin
// Premièrement, il faut configurer le binding par rapport à la structure des lignes du dataset.
InitializeBindings;
// Ensuite, on crée un accesseur basé sur ce binding.
Cnt.OleDbCheck((RowSet as IAccessor).CreateAccessor(
DBACCESSOR_ROWDATA + DBACCESSOR_OPTIMIZED,
FieldDefs.Count,
@Bindings[0],
RecordSize,
Accessor,
nil));
try
// À présent, il ne reste plus qu'à lire les lignes.
nbRow := 0;
// Les lignes vont être lues par blocs de FPageSize lignes.
// Il faut commencer par allouer un tableau qui contiendra les handles de ces lignes.
GetMem(RowHandle, FPageSize*sizeof(integer));
try
repeat
// Dans un premier temps, il faut demander au provider de récupérer les lignes depuis le serveur.
// Elles restent stockées dans des buffers internes au provider.
FetchResult := RowSet.GetNextRows(DB_NULL_HCHAPTER,
0, // On lit les lignes depuis la dernière lecture
FPageSize, // On lit autant de ligne que dans une page du dataset.
nbRow, // Nombre de lignes réellement lues.
RowHandle);
Cnt.OleDbCheck(FetchResult);
// Les lignes ont été lues on peut demander au provider d'alimenter notre dataset avec les
// résultats de cette lecture.
for i := 0 to nbRow-1 do
begin
// On calcule le pointeur sur la ligne, dans les pages de stockage de TMemoryDataSet.
Data := GetNewline(FPosition);
// Ensuite on demande à OLEDB de remplir la ligne pointée avec les données, en respectant
// le binding défini dans l'accesseur.
Cnt.OleDbCheck(RowSet.GetData(RowHandle[i], Accessor, Data));
// Une fois les données retournées par le provider, il reste à convertir
// les LOB pour les stocker dans des TMemoryBlobStream.
for j := 0 to FBlobFields.Count -1 do
begin
// On commence par obtenir le pointeur sur les données du champ
FieldOffset := FFieldInfo[cardinal(FBlobFields[j])-1].Offset;
FieldData := GetPFieldData(Data, FieldOffset);
ptrBlob := pointer(FieldData^.Data);
// On recopie les données du Blob à l'intérieur du DataSet.
LoadBlobField(Data, cardinal(FBlobFields[j]), ptrBlob);
// Enfin il ne faut pas oublier de libérer la mémoire allouée par le provider
// OLEDB pour stocker le BLOB. Cette mémoire n'a été allouée que si le champ
// ne vaut pas NULL.
if FieldData.NullStatus = DBSTATUS_S_OK
then cnt.FMAlloc.Free(ptrBlob);
end;
// Enfin, on définit le bookmark de la ligne.
DefineBookmark(FPosition, Data);
end;
// La page vient d'être traitée, on peut dire au provider de libérer les lignes.
RowSet.ReleaseRows(nbRow, RowHandle, nil, nil, nil);
until FetchResult = DB_S_ENDOFROWSET;
finally
Freemem(RowHandle);
end;
finally
// Il reste à libérer l'accesseur.
Cnt.OleDbCheck((RowSet as IAccessor).ReleaseAccessor(Accessor, nil));
end;
end;Comme on peut le constater, les données des lignes sont directement chargées à l'intérieur des buffers de la classe TMemoryDataSet. On appelle GetNewLine pour ajouter une ligne vide dans TMemoryDataSet et obtenir le pointeur sur le buffer correspondant.
Ensuite les données de la ligne sont chargées avec GetData.
Cependant, on doit quand même effectuer une conversion sur les champs LOB. En effet ces derniers ne sont pas stockés correctement puisque le champ du LOB à l'intérieur du buffer de la ligne contient un pointeur sur les données du LOB au lieu d'un numéro de LOB. Cette conversion est faite en appelant LoadBlobField. Une fois le LOB converti, on peut libérer la mémoire qui a été allouée par OLEDB en appelant IMAlloc.Free.
Il ne reste plus qu'à définir son bookmark avec DefineBookmark.
II-E. Requêtes paramétrées▲
Une requête paramétrée est une requête ordinaire, sauf qu'au moment d'exécuter la requête, on doit également fournir la valeur des paramètres.
Pour cela, on passe une structure de type DBPARAMS en paramètre de la méthode execute. Cette dernière se compose d'un buffer qui contient les valeurs des paramètres, ainsi que d'un accesseur décrivant comment interpréter le buffer.
Dans l'architecture db de Delphi, les paramètres sont définis sous la forme d'une collection TParams d'objets TParam. Si on veut garder ce principe, on doit recopier les valeurs de ces paramètres dans un buffer, et créer l'accesseur correspondant.
C'est le rôle de la classe TOleDbParams. L'initialisation du buffer et de son accesseur est réalisée par son constructeur :
constructor TOleDbParams.Create(Params: TParams; ACmd : ICommand; Cnt : TOleDbConnection);
var
i : integer;
Param : TParam;
PData : PFieldData;
Offset : cardinal;
ParamSize : cardinal;
Binding : PDBBINDING;
value : cardinal;
DateValue : TDatetime;
FloatValue : double;
StringValue : string;
WidestringValue : widestring;
begin
FCnt := Cnt;
if Assigned(Params) and (Params.Count >0)
then begin
SetLength(Bindings, Params.Count);
SetLength(FParamInfo, Params.Count);
FCapacity := 4096;
SetLength(FData, 4096);
PData := @FData[0];
Offset := 0;
for i := 0 to Params.Count -1 do
begin
Param := Params[i];
Binding := @Bindings[i];
// Initialisation de l'index des paramètres.
FParamInfo[i].Offset := Offset;
FParamInfo[i].Size := Param.Size;
ParamSize := 0;
// On préinitialise le binding pour le paramètre courant.
Binding.iOrdinal := i+1;
Binding.pTypeInfo := nil;
Binding.obValue := Offset + 8;
Binding.obLength := Offset + 4;
Binding.obStatus := Offset;
Binding.cbMaxLen := Param.Size;
Binding.dwPart := DBPART_VALUE or DBPART_LENGTH or DBPART_STATUS;
Binding.pObject := nil;
Binding.pBindExt := nil;
Binding.dwFlags := 0;
Binding.dwMemOwner:= DBMEMOWNER_CLIENTOWNED;
Binding.bPrecision:= 0;
Binding.bScale := 0;
Binding.wType := DBTYPE_STR; // wType sera redéfini ensuite en fonction du paramètre.
// On définit eParamIO en fonction du type défini pour le paramètre
case Param.ParamType of
ptUnknown,
ptInput: Binding.eParamIO := DBPARAMIO_INPUT;
ptOutput : Binding.eParamIO := DBPARAMIO_OUTPUT;
ptInputOutput : Binding.eParamIO := DBPARAMIO_INPUT or DBPARAMIO_OUTPUT;
end;
// Premièrement on regarde si le paramètre est à NULL. Si c'est le cas, il
// faut indiquer DBSTATUS_S_ISNULL pour le statut du champ.
if Param.IsNull
then PData^.NullStatus := DBSTATUS_S_ISNULL
else PData^.NullStatus := DBSTATUS_S_OK;
// Maintenant, on fait la copie de la valeur du paramètre dans FData en
// fonction du type de ce dernier.
case Param.DataType of
ftWord, ftBoolean:
begin
ParamSize := 2;
CheckSize(Offset + 8 + ParamSize);
value := Param.AsInteger;
move(value, PData^.Data[0], ParamSize);
Binding.wType := DBTYPE_UI2;
end;
ftSmallint:
begin
ParamSize := 2;
CheckSize(Offset + 8 + ParamSize);
value := Param.AsInteger;
move(value, PData^.Data[0], ParamSize);
Binding.wType := DBTYPE_I2;
end;
ftAutoInc, ftInteger, ftLargeint:
begin
ParamSize := 4;
CheckSize(Offset + 8 + ParamSize);
value := Param.AsInteger;
move(value, PData^.Data[0], ParamSize);
Binding.wType := DBTYPE_I4;
end;
ftDateTime:
begin
ParamSize := 8;
CheckSize(Offset + 8 + ParamSize);
DateValue := Param.AsDateTime;
move(DateValue, PData^.Data[0], ParamSize);
Binding.wType := DBTYPE_DATE;
end;
ftDate, ftTime:
begin
ParamSize := 8;
CheckSize(Offset + 8 + ParamSize);
DateValue := Param.AsDate;
move(DateValue, PData^.Data[0], ParamSize);
Binding.wType := DBTYPE_DATE;
end;
ftFloat,
ftCurrency,
ftBCD :
begin
CheckSize(Offset + 8 + ParamSize);
FloatValue := Param.AsFloat;
move(FloatValue, PData^.Data[0], ParamSize);
Binding.wType := DBTYPE_R8;
end;
ftBlob, ftGraphic, ftFmtMemo, ftBytes, ftVarBytes, ftString, ftMemo:
begin
stringValue := Param.AsString;
ParamSize := length(stringValue);
Binding.cbMaxLen := ParamSize;
CheckSize(Offset + 8 + ParamSize);
PData^.LengthValue := ParamSize;
move(stringValue[1], PData^.Data[0], ParamSize);
if (Param.DataType = ftMemo) or (Param.DataType = ftString)
then Binding.wType := DBTYPE_STR
else Binding.wType := DBTYPE_BYTES;
end;
ftWideString, ftWideMemo:
begin
widestringValue := Param.AsWideString;
ParamSize := length(widestringValue)*2;
Binding.cbMaxLen := ParamSize;
CheckSize(Offset + 8 + ParamSize);
PData^.LengthValue := length(widestringValue);
move(widestringValue[1], PData^.Data[0], ParamSize);
if (Param.DataType = ftMemo) or (Param.DataType = ftString)
then Binding.wType := DBTYPE_STR
else Binding.wType := DBTYPE_BYTES;
end;
end;
inc(offset, ParamSize + 8);
end;
// À présent, il ne reste plus qu'à définir l'accesseur.
Cmd := ACmd;
Cnt.OleDbCheck((Cmd as IAccessor).CreateAccessor(DBACCESSOR_PARAMETERDATA,
Params.Count,
@Bindings[0],
0,
hAccessor,
nil));
Parameters.cParamSets := 1;
Parameters.HACCESSOR := hAccessor;
Parameters.pData := @FData[0];
end
else begin
Parameters.cParamSets := 0;
Parameters.HACCESSOR := 0;
Parameters.pData := nil;
end;
end;Le constructeur lit la collection TParams en entrée et initialise la structure Parameters qui sera utilisée pour exécuter la requête.
Une fois la requête exécutée, la valeur des paramètres de sortie sera automatiquement mise en jour à l'intérieur du buffer de TOleDbParams, grâce à l'accesseur. Il suffira donc de décoder le buffer pour mettre à jour la collection TParams avec les nouvelles valeurs :
procedure TOleDbParams.UpdateParams(Params: TParams);
var i : integer;
Param : TParam;
PData : PFieldData;
value : cardinal;
DateValue : TDatetime;
FloatValue : double;
StringValue : string;
WidestringValue : widestring;
begin
if Assigned(Params) and (Params.Count>0)
then begin
// On parcourt la liste des paramètres pour mettre à jour la valeur de paramètres de sortie
// en fonction du nouvel état du buffer.
for i := 0 to Params.Count -1 do
begin
Param := Params[i];
// On ne traite que les paramètres de sortie. Inutile de s'occuper de ceux qui sont en
// entrée seule.
if (Param.ParamType in [ptOutput, ptInputOutput]) and (i<= high(FParamInfo))
then begin
PData := @FData[FParamInfo[i].Offset];
if PData.NullStatus = DBSTATUS_S_ISNULL // Cas ou le paramètre vaut NULL
then Param.Clear
else begin
// Si le paramètre ne vaut pas null, il reste à copier la valeur du buffer
// en fonction du type de données
case Param.DataType of
ftWord, ftBoolean, ftSmallint: // Entier sur 2 octets
begin
value := 0;
move(PData.Data[0], value, 2);
Param.AsInteger := value;
end;
ftAutoInc, ftInteger, ftLargeint: // Entier sur 4 octets
begin
move(PData.Data[0], value, 4);
Param.AsInteger := value;
end;
ftDate, ftTime, ftDateTime: // Date
begin
move(PData^.Data[0], DateValue, 8);
Param.AsDateTime := DateValue;
end;
ftFloat,
ftCurrency,
ftBCD :
begin
move(PData^.Data[0], FloatValue, 8);
Param.AsFloat := FloatValue;
end;
ftBlob, ftGraphic, ftFmtMemo, ftBytes, ftVarBytes, ftString, ftMemo:
begin
SetLength(stringValue, PData.LengthValue);
move(PData.Data[0], stringValue[1], PData.LengthValue);
Param.AsString := stringValue;
end;
ftWideString, ftWideMemo:
begin
SetLength(WidestringValue, PData.LengthValue div 2);
move(PData.Data[0], WidestringValue[1], PData.LengthValue);
Param.AsString := WidestringValue;
end;
end;
end;
end;
end;
end;
end;Attention : Selon le provider OLEDB utilisé, le buffer ne sera mis à jour avec les paramètres de sortie qu'une fois l'exécution de la commande complètement terminée, et que le jeu de résultats aura été complètement lu (le Rowset a été libéré).
C'est notamment le cas avec SQL Server. Ça vient tout simplement de l'implémentation du protocole réseau utilisé pour envoyer les données au client : Le provider OLEDB décode le flux réseau au fur et à mesure que les données sont lues. Or dans le flux TDS, les valeurs des paramètres de sortie sont tout simplement transmises après les données des SELECT. De sorte qu'on ne peut pas connaître la valeur de ces paramètres tant qu'on n'a pas fini de lire toutes les données renvoyées par le select.
Cette approche est très basique. Les paramètres sont uniquement typés dans TParams à partir de leur valeur initiale. Pour un paramètre de sortie, ça signifie qu'il faut d'abord initialiser une valeur au paramètre pour pouvoir dimensionner le buffer intermédiaire.
Cependant, la technique présentée ici est suffisante pour ce tutoriel sur OleDb.
Finalement, l'exécution de la requête paramétrée s'effectue de la façon suivante :
// Si la requête est paramétrée, il faut initialiser les valeurs des paramètres. C'est le rôle
// de la classe TOleDbParams. Si elle n'attend pas de paramètre, TOleDbParams définit un jeu
// de paramètres vide.
OleDbParams := TOleDbParams.Create(Params, Cmd, self);
try
OleDbCheck(cmd.Execute(nil, IID_IRowset, OleDbParams.Parameters, nil, @unknown));
if Assigned(unknown)
then begin
RowSet := unknown as IRowSet;
unknown := nil;
// La source de données vient de rendre la main. On peut lire les données.
ds := TOleDbDataSet(CreateDataSet); // On instancie le DataSet qui sera retourné.
ds.LoadFromRowSet(RowSet, self, FetchSize); // Lecture des données.
RowSet := nil;
end
else ds := nil;
// Après l'exécution de la requête on met à jour les valeurs des paramètres de sortie.
OleDbParams.UpdateParams(Params);
finally
OleDbParams.Free;
end;II-F. Préparer les Requêtes▲
OLEDB, comme beaucoup d'API permet de préparer les requêtes SQL avant de les exécuter. Lorsqu'on prépare une requête, on commence par envoyer le texte de la requête au SGBD. Ce dernier va la compiler et mémoriser son plan d'exécution pour renvoyer un handle au client.
Ensuite, lors de chaque exécution, le client n'aura qu'à indiquer ce handle et le SGBD pourra faire l'exécution sans avoir à recompiler et recalculer le plan d'exécution.
Lorsque le client a fini d'utiliser une requête, il annule la préparation pour que le serveur détruise le plan d'exécution ainsi créé.
Pour préparer une commande OLEDB, il suffit d'appeler ICommandPrepare.Prepare à partir d'une commande déjà créée, puis d'appeler ICommandPrepare.Unprepare lorsque la commande n'est plus utilisée.
Cependant j'ai choisi de ne pas l'implémenter dans TOleDbConnection, principalement pour deux raisons :
- Tout d'abord, on comprend bien que préparer une commande nécessite des échanges d'informations supplémentaires avec le SGBD. La préparation d'une commande a un coût. Ce coût sera théoriquement rentabilisé sur les exécutions suivantes de la commande. Ce qui signifie que pour que la préparation de la commande présente un intérêt, il faut que cette dernière soit exécutée plusieurs fois. La documentation de SQL Server dit même que si la commande n'est pas exécutée au moins 3 fois, la préparation dégrade les performances. Or dans TOleDbConnection on crée une nouvelle commande à chaque appel à OpenSQL ou ExecSQL. Cette façon de travailler est déjà incompatible avec la préparation des commandes.
- Ensuite, avec SQL Server (qui rappelons-le est la cible principale de TOleDbConnection), préparer les requêtes ne sert généralement à rien. En effet, SQL Server utilise un mécanisme de mise en cache des plans d'exécution qui lui permet de se rendre compte automatiquement qu'il possède déjà un plan d'exécution pour une requête SQL préalablement exécutée, ce qui lui évite de la recompiler. Pour peu qu'on utilise en plus des requêtes paramétrées (soit manuellement, soit avec l'option PARAMETERIZATION à FORCED sur la base de données), préparer les requêtes serait vraiment contre performant.
II-G. Gestion des Transactions▲
À présent, il ne reste plus qu'à voir la gestion des transactions avec OLEDB. C'est très simple puisqu'il suffit d'appeler les méthodes de l'interface ITransactionLocal.
On peut obtenir l'interface ITransactionLocal à partir de la session en cours, en faisant simplement un QueryInterface (ou avec l'opérateur AS en Delphi). Attention cependant, cette interface est une interface facultative dans les spécifications OLEDB. Il se peut donc qu'elle ne soit pas disponible si la source de données ne gère pas les transactions.
II-G-1. Démarrer une transaction : StartTransaction▲
Pour démarrer une nouvelle transaction, on appelle la méthode StartTransaction :
// On appelle la méthode StartTransaction de l'interface OLEDB
OleDbCheck(FTransaction.StartTransaction(ISOLATIONLEVEL_READCOMMITTED, 0, nil, nil));On remarquera qu'on précise le niveau d'isolation de la transaction pour chaque transaction.
Nous allons encapsuler cet appel dans la classe TCustomOleDbConnection pour fournir une méthode publique StartTransaction :
procedure TCustomOleDbConnection.StartTransaction;
begin
CheckConnected;
// FTransaction vaut nil si le provider OLEDB ne gère pas les transactions
if Assigned(FTransaction)
then begin
try
// On trace le début de la transaction.
SQLLogger.TraceStartTransaction(TransactionTime);
// On appelle la méthode StartTransaction de l'interface OLEDB
OleDbCheck(FTransaction.StartTransaction(ISOLATIONLEVEL_READCOMMITTED, 0, nil, nil));
except
on e:exception do
begin
SQLLogger.TraceException(e);
raise;
end;
end;
end;
end;II-G-2. Valider une transaction : Commit▲
La validation d'une transaction en cours s'effectue évidemment avec la méthode Commit.
OleDbCheck(FTransaction.Commit(false, XACTTC_SYNC, 0));Lors de l'appel du commit, il faut préciser si on veut conserver le niveau d'isolation en cours ou revenir au niveau par défaut configuré pour la connexion.
Le deuxième paramètre sert à gérer le commit à deux phases pour les transactions distribuées ainsi que le commit asynchrone. Ce n'est pas l'objet de ce tutoriel, on se contentera d'utiliser la valeur XACTIC_SYNC pour faire un commit standard.
Le dernier paramètre est une valeur réservée qui doit toujours valoir 0.
Il ne reste plus qu'à ajouter une méthode Commit dans TCustomOleDbConnection :
procedure TCustomOleDbConnection.Commit;
begin
CheckConnected;
if Assigned(FTransaction)
then begin
try
SQLLogger.TraceCommitTransaction(TransactionTime);
OleDbCheck(FTransaction.Commit(false, XACTTC_SYNC, 0));
except
on e:exception do
begin
SQLLogger.TraceException(e);
raise;
end;
end;
end;
end;II-G-3. Annuler une transaction : Abort▲
Pour faire un rollback, il faut utiliser la méthode Abort :
OleDbCheck(FTransaction.Abort(nil, false, false));Nous allons l'encapsuler dans TCustomOleDbConnection avec la méthode Rollback :
procedure TCustomOleDbConnection.Rollback;
begin
// On vérifie que la connexion est bien ouverte.
CheckConnected;
if Assigned(FTransaction)
then begin
try
// On trace le rollback
SQLLogger.TraceRollbackTransaction(TransactionTime);
// On appelle Abort pour annuler la transaction.
OleDbCheck(FTransaction.Abort(nil, false, false));
except
on e:exception do
begin
SQLLogger.TraceException(e);
raise;
end;
end;
end;
end;III. Évaluation des résultats et exemples d'utilisations▲
Nous avons terminé l'implémentation des classes TOleDbConnection et TOleDbDataset. Voyons à présent quelques exemples d'utilisation qui nous permettront de tester nos accès base de données et évaluer les performances obtenues avec OleDb.
III-A. Environnement de tests▲
Pour effectuer les tests, nous allons travailler avec une instance SQLExpress installée en local. Les temps d'exécution des requêtes et de lecture des résultats seront directement dépendants de notre implémentation et non liés au réseau.
Nous allons travailler sur la base de données d'exemple de SQL 2005 : AdventureWorksLT.
Cette dernière peut être téléchargée sur Codeplex à l'adresse suivante :
https://archive.codeplex.com/
La classe TOleDbConnection a été instrumentée avec ETW. Nous allons regarder quelques cas d'utilisation simple et contrôler les traces générées dans ETWSqlProfiler.
III-B. Connexion/Déconnexion▲
Il suffit de renseigner la chaîne de connexion et de définir la propriété Connected à True :
var cnt : TOleDbConnection;
begin
// Création d'un nouvel objet TOleDbConnection
cnt := TOleDbConnection.Create(nil);
// Ici on demande la construction d'une nouvelle chaîne de connexion.
cnt.ConnectionString := TOleDbConnection.PromptConnexionString('',
Application.MainFormHandle);
// Enfin, on ouvre la connexion :
cnt.Connected := true;
// Maintenant on ferme la connexion.
cnt.Connected := false;Si on exécute cet exemple, on obtient la trace suivante :
On peut constater que les lignes vertes LOGIN et LOGOUT indiquent immédiatement que la connexion a été ouverte et fermée.
La colonne Delta indique le temps écoulé en millisecondes depuis la ligne précédente dans la trace. Comme chaque événement a été précédé d'un message d'information indiquant la chaîne de connexion concernée, on peut en déduire le temps d'ouverture de la connexion (moins de 4 ms) et le temps de déconnexion (moins de 1 ms).
En cas d'erreur au moment de la connexion à la base, l'exception est également automatiquement enregistrée dans la trace :
III-C. Exécuter une requête▲
Une fois la connexion ouverte, on peut exécuter une requête avec un simple appel à la méthode OpenSQL :
var
ds : TDataSet;
begin
// On exécute une requête SELECT quelconque.
ds := cnt.OpenSQL('select * from SalesLT.Customer');
// On travaille en mode déconnecté. Une fois la requête exécutée, on peut
// fermer la connexion et continuer à travailler sur le dataset.
// Le dataset renvoyé est un dataset bidirectionnel qu'on peut très bien
// afficher dans une DBGrid !
DBGrid1.Datasource.DataSet := nil;
DBGrid1.Datasource.DataSet := ds;Ici, on commence par ouvrir la connexion à la base, puis on lit la table SalesLT.Customer dans sa totalité.
OpenSQL renvoie un dataset en mémoire totalement déconnecté de la base de données. Ça signifie qu'on peut faire ce qu'on veut du dataset retourné : on peut garder autant de datasets ouverts qu'on le souhaite et on peut même fermer la connexion à la base. Le dataset reste toujours valide. Il peut même servir de table temporaire en mémoire.
De plus, contrairement à dbExpress, le dataset retourné est bidirectionnel. Ça signifie qu'on peut l'afficher dans une grille.
Voyons à présent la trace générée à l'exécution :
L'exécution de la requête a généré cinq lignes dans la trace :
- La première ligne correspond au début de la requête. On voit la requête qui est envoyée au SGBD.
- Lorsque le SGBD a fini de traiter la requête, il rend la main et on commence à lire les résultats. Cet instant est également tracé avec l'événement « Debut Fetch ».
- Le Fetch en lui-même prend un certain temps. Lorsqu'il est terminé, un nouvel événement indique la fin de la lecture des données : « Fin Fetch ».
- Enfin, l'exécution de la requête est terminée. Un nouvel événement dans la trace indique la fin de OpenSQL, en rappelant la requête exécutée et le nombre de lignes lues.
Examinons maintenant les temps d'exécution. Comme on peut le voir, l'exécution de la requête sur le SGBD a duré moins de 1 ms. Par contre ensuite, la lecture des résultats de la requête a pris environ 4 ms pour lire 440 lignes. De plus, on voit que chaque ligne lue occupe 1598 octets dans le dataset.
Cet exemple montre clairement que de nos jours, l'exécution d'une requête simple sur un SGBD est très rapide. Par contre on passe ensuite l'essentiel du temps de traitement à lire les résultats de la requête. Or la durée du fetch est étroitement liée à l'API utilisée. D'où l'importance de choisir une API performante.
Si nous n'avions pas travaillé en local, les temps de fetch auraient été encore plus importants.
III-D. Exécuter une requête paramétrée▲
Pour exécuter une requête paramétrée, il suffit de fournir une collection TParams en paramètres à OpenSQL.
Dans la requête, on utilise des « ? » à la place des paramètres. Il n'est pas possible de les nommer. C'est le standard prévu par Microsoft et qu'on retrouve aussi bien dans OLEDB que dans ADO.
Par exemple, si on veut obtenir la liste des produits mis en vente avant le 01/01/2000. On va effectuer une requête paramétrée en indiquant la date en paramètre :
var
Params : TParams;
Param : TParam;
ds : TDataSet;
begin
// On exécute une requête SELECT avec un paramètre DATE.
Params := TParams.Create;
try
TParam(Params.Add).AsDateTime := EncodeDate(2000, 1, 1);
ds := cnt.OpenSQL('select * from SalesLT.Product where SellStartDate < ?', Params);
finally
Params.free;
end;III-E. Messages d'informations▲
Certaines commandes SQL retournent des messages qui ne sont pas des datasets. On a vu que ces messages d'information remontent sous la forme d'erreurs d'exécution. La classe TOleDbConnection isole ces messages et les mémorise dans la propriété InfoMessages.
Par exemple, si on exécute une commande PRINT :
// On exécute une commande PRINT.
cnt.ExecSQL('PRINT ''Message de test''');
// Le message du PRINT est retourné dans InfoMessages.
ShowMessage(cnt.InfoMessages.Text);Il suffit alors de lire la propriété InfoMessages pour lire le message renvoyé. Cette propriété est réinitialisée à chaque exécution d'une commande SQL.
III-F. Lire un fichier EXCEL▲
TOleDbConnection a été conçue pour SQL Server. Cependant, le code reste valable pour n'importe quel provider OLEDB.
Par exemple, on peut utiliser le provider Microsoft Jet 4.0 pour lire un fichier EXCEL. Il suffit d'utiliser une chaîne de connexion du type :
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<Fichier excel à ouvrir>;Extended Properties=Excel 8.0Ensuite, on peut faire une requête SQL sur le classeur :
select * from [Feuil1$]IV. Conclusion▲
Dans cet article, nous avons vu les bases de OLEDB. Nous avons vu comment utiliser cette API bas niveau afin d'exécuter des requêtes SQL sur une base de données.
De premier abord, OLEDB est loin d'être simple. Mais si on s'y intéresse de plus près, il suffit de comprendre le fonctionnement du binding et des accesseurs. Après, le reste n'est pas plus compliqué qu'utiliser ADO.
Lorsqu'on utilise OLEDB directement, on peut obtenir des performances vraiment excellentes. Par exemple, on a pu exécuter une requête renvoyant tous les clients de la base AdventureWorksLT (440 lignes en tout) en 4 ms.
Dans le prochain article nous verrons comment OLEDB et SQL Server nous permettent d'effectuer des chargements de données en blocs. On pourrait ainsi insérer massivement des données dans une table, directement depuis une application Delphi, à la vitesse d'un bcp, DTS, ou autre SSIS.
V. Références▲
Le Tracing avec Event Tracing for Windows (ETW)Â :
https://fsoriano.developpez.com/articles/etw/delphi/
Comparatif des architectures des API d'accès aux données :
https://fsoriano.developpez.com/articles/db/comparatifapi//
Développer un DataSet en mémoire :
https://fsoriano.developpez.com/articles/db/dataset/delphi/
La documentation OLEDB sur MSDNÂ :
https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722784(v=vs.85)?redirectedfrom=MSDN
La base de données d'exemple AdventureWorksLT :
https://archive.codeplex.com/
VI. Remerciements▲
Je remercie particulièrement Nono40 pour sa relecture et ses conseils avisés !