IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Utiliser le modèle ADOX avec Visual Basic

Utiliser le modèle ADOX avec Visual Basic


précédentsommairesuivant

III. Rappels ADO

Sans que cet article couvre le modèle ADO, nous allons voir ici quelques notions qui nous seront utiles pour l'utilisation d'ADOX ou la récupération de schéma.

III-A. Propriétés statiques & dynamiques (Properties)

Dans le modèle ADO et a fortiori dans le modèle ADOX, de nombreux objets possèdent une collection un peu particulière : "Properties". Celle ci concerne des propriétés dites dynamiques par opposition aux propriétés habituelles des objets (statiques). Ces propriétés dépendent du fournisseur de données, elles ne sont en général accessibles qu'après la création de l'objet (et éventuellement l'application d'une méthode refresh sur la collection) voire après l'ouverture de l'objet.

On ne peut pas accéder à une propriété statique par l'intermédiaire de la collection Properties.

Un objet Property dynamique comporte quatre propriétés intégrées qui lui sont propres, à savoir :

  • La propriété Name qui est une chaîne identifiant la propriété
  • La propriété Type qui est un entier spécifiant le type de donnée de la propriété.
  • La propriété Value qui est un variant contenant la valeur de la propriété.
  • La propriété Attributes qui est une valeur de type Long indiquant les caractéristiques de propriétés spécifiques au fournisseur.

III-B. L'objet Connection

Dans son utilisation la plus courante la chaîne de connexion prend deux paramètres : le fournisseur et la source de données. Pourtant l'objet Connection comprend de nombreuses propriétés permettant de gérer le mode d'accès, la sécurité, le paramétrage Jet, etc...

Pour mieux appréhender l'objet Connection et ses propriétés, regardons le code suivant.

 
Sélectionnez
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection
With cnn1
	Debug.Print .Properties.Count
	.Provider = "Microsoft.Jet.OLEDB.4.0;"
	.ConnectionTimeout = 30
	.CursorLocation = adUseClient
	.Mode = adModeShareExclusive
	.Properties("Jet OLEDB:System database") = "D:\User\jmarc\tutorial\ADOX\system.mdw"
	Debug.Print .Properties.Count
	.Open "Data Source=D:\User\jmarc\tutorial\ADOX\baseheb.mdb ;User Id=Admin; Password="
	Debug.Print .Properties.Count
End With

Si vous exécutez ce code en l'état, vous allez avoir l'erreur "Le fournisseur indiqué est différent de celui déjà utilisé" [3220 - adErrCantChangeProvider]

Ceci vient du fait que l'appel de Properties.Count crée l'instance de l'objet et non le Set comme une erreur courante le fait penser. Or comme à ce moment le fournisseur n'a pas été défini, ADO utilises le fournisseur par défaut "MSDASQL.1". Dès lors, il n'est plus possible de modifier le fournisseur ce qui déclenche une erreur.

Règle n°1 : On doit toujours définir la propriété Provider en premier sur un objet Connection.

On enlève donc le premier Debug.Print et on exécute le code. On observe le résultat dans la fenêtre correction et on constate qu'il y a 27 propriétés avant l'ouverture et 94 après.

Règle n°2 : On valorise les propriétés statiques et les propriétés dynamiques dépendantes du fournisseur avant l'ouverture de la connexion.

En fait les propriétés dépendantes du fournisseur qu'il nous faut valoriser avant l'ouverture sont celles qui gèrent la sécurité (voir le chapitre correspondant).

Stricto sensu, on peut valoriser toutes ces propriétés lors de l'ouverture de la connexion, mais pour des raisons de maintenance (lisibilité) il est souvent utile de décomposer la connexion avant l'ouverture. La chaîne de connexion à passer dans ce cas serait :

 
Sélectionnez
Cnn1.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=D:\User\jmarc\tutorial\ADOX\baseheb.mdb;Mode=Share
Deny Read|Share Deny Write;Extended Properties="";Jet OLEDB:System
database=D:\User\jmarc\tutorial\ADOX\system.mdw;Jet OLEDB:Registry
Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet
OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False"

Règle n°3 : Lorsqu'on doit valoriser plusieurs propriétés de la connexion, on le fait en dehors de la chaîne de connexion.

Il y aurait encore de nombreuses choses à dire sur l'objet Connection, mais cela sort du cadre de cet article.

III-C. L'objet Command

Beaucoup de programmeurs n'utilisent pas l'objet Command avec ADO préférant travailler directement avec l'objet Recordset. Dans le modèle ADOX, l'objet Command est indispensable. Nous allons donc aborder l'objet Command de façon assez détaillée sous l'angle suivant:

Utilisation de Command pour créer des requêtes stockées dans la base de données (paramétrées ou non)

Sachez toutefois que l'objet Command s'utilise dans de nombreuses autres situations. Nous allons commencer par voir l'objet Command en détail.

III-C-1. Généralités

Un objet command représente une commande spécifique à exécuter sur une source de données. Ceci peut être une instruction SQL ou une requête/procédure stockée dans la base ou créée à l'exécution. Un objet Command dépend toujours d'une connexion soit créée spécifiquement pour cette commande, soit d'une connexion déjà existante.

III-C-2. Propriétés

III-C-2-a. ActiveConnection

Définit la connexion utilisée pour l'objet Command. Cette propriété doit être passée à Nothing avant de changer la connexion d'un objet Command. Comme les objets Command héritent de certaines propriétés de la connexion, faites attention au paramétrage de celle-ci.

III-C-2-b. CommandText & CommandStream

Nous n'étudierons pas dans cet article la programmation de l'objet Stream. Sachez toutefois qu'il est indispensable pour la programmation Internet et l'utilisation du XML. Ces deux propriétés sont exclusives. La propriété CommandText contient le texte de la commande à exécuter. Ce texte peut être le nom d'une procédure stockée, une chaîne SQL etc...

III-C-2-c. CommandTimeout

La valeur est en secondes. S'applique en général sur l'objet Connection

III-C-2-d. CommandType

Donne le type de la commande. Cette propriété est très importante. En effet, il y aura une erreur récupérable si le paramètre donné est faux. De plus ne pas valoriser correctement cette propriété peut dégrader fortement les performances. Les valeurs peuvent être :

Constante Description
adCmdText CommandText correspond à la définition textuelle d'une commande ou d'un appel de procédure stockée
adCmdTable CommandText correspond au nom de table dont les colonnes sont toutes renvoyées par une requête SQL générée en interne.
adCmdTableDirect CommandText correspond à un nom de table dont les colonnes sont toutes renvoyées.
adCmdStoredProc CommandText correspond au nom d'une procédure stockée.
adCmdUnknown Valeur utilisée par défaut. Le type de commande de la propriété CommandText est inconnu
adCmdFile CommandText correspond au nom de fichier d'un Recordset permanent
adExecuteNoRecords CommandText correspond à une commande ou une procédure stockée qui en renvoie pas de ligne (par exemple, une commande qui insère uniquement des données). Si des lignes sont extraites, elles ne sont pas prises en compte et ne sont pas retournées. Toujours associée à adCmdText ou adCmdStoredProc

Attention toutefois, une procédure stockée peut être différemment interprétée selon les SGBD. Voir aussi la rubrique "Parameters".

III-C-2-e. Prepared

Détermine si une commande doit être Pré-compilée. N'est utile que si la commande doit être exécutée plusieurs fois. Attention, certains fournisseurs n'acceptent pas cette modification sans toutefois déclencher d'erreur.

III-C-2-f. State

Renvoie l'état de la commande (ouverte ou fermée)

III-C-3. Méthodes

III-C-3-a. Cancel

Annule l'exécution de la commande si celle-ci est asynchrone.

III-C-3-b. Execute

Execute la commande. De la forme command.Execute RecordsAffected, Parameters, Options

Remarques sur la méthode Execute

Vous aurez noté qu'il existe une méthode Execute sur l'objet Connection et sur l'objet Command. Elles sont sensiblement identiques si ce n'est :

      Une commande est réutilisable

      Un objet Command est nécessaire pour les requêtes paramétrées et les procédures stockées.

      Notez aussi qu'un recordset créé à l'aide de Connection.Execute hérite des propriétés de la connexion.

III-C-3-c. CreateParameter

Sert à créer un paramètre (voir explications plus loin).

De la forme command.CreateParameter (Name, Type, Direction, Size, Value)

Attention à ne pas faire une erreur courante avec cette méthode. Celle-ci crée un paramètre mais ne l'ajoute pas à la collection Parameters de l'objet Command. Ceci est d'ailleurs indispensable afin de pouvoir valoriser d'autres propriétés avant l'ajout.

III-C-4. Collection Properties

C'est la collection des propriétés dynamiques de l'objet Command. Comme il y en a beaucoup, nous n'allons pas toutes les voir en détail, mais certaines méritent que nous nous y arrêtions. Pourquoi?

Lors de l'utilisation d'un objet recordset, la définition des paramètres de celui-ci valorise un certain nombre de propriétés. Ceci n'est pas le cas sur un objet Command. En tout état de cause, ceci n'a pas d'importance si l'objet Command ne renvoie pas un recordset.

Bookmarkable Boolean Read/Write/Required FAUX

Permet d'utiliser un Recordset qui accepte les signets.

Jet OLEDB:Bulk Transactions Integer Read/Write/Required 0

Détermine si les requêtes action sont acceptées de façon partielle ou globale. De manière générale, les transactions partielles ne devaient jamais être autorisées.

Jet OLEDB:ODBC Pass-Through Statement Boolean Read/Write/Required FAUX

Défini les requêtes SQL directes

Notion SQL Direct

Les requêtes SQL direct étaient utilisées avec DAO pour écrire des requêtes n'ayant pas besoin d'être interprétées par Jet lors d'accès a des données externes. Comme une telle requête doit avoir une connexion vers la source de données, il n'est plus la peine de faire des requêtes SQL Direct.

Lock Mode Integer Read/Write/Required 1

Détermine dans une certaine mesure le mode de verrouillage. Cette propriété ne peut pas être dissociée de la propriété IsolationLevel

Others' Changes Visible Boolean Read/Write/Required FAUX

Permet l'utilisation multi-utilisateurs

III-C-5. Collection Parameters

III-C-5-a. Généralités

Les utilisateurs d'autres SGBD qu'Access veilleront à bien faire la différence entre procédure stockée et requête paramétrée.

Quoique regroupés dans la même collection, il existe deux types de paramètres. Les paramètres d'entrée, attendus par la procédure/requête pour pouvoir s'exécuter, et les paramètres de sortie qui peuvent êtres renvoyés par une procédure. Il convient de faire attention avec ceux-ci, une connexion n'acceptant jamais plus de deux objets Command ayant des paramètres de sortie (le paramètre de retour n'ayant pas d'influence).

III-C-5-b. Quelques méthodes de la collection.

Append

Ajoute un paramètre à la collection. De la forme

Command.Parameters.Append Name, Type, DefinedSize, Attrib

Il est possible d'utiliser la méthode CreateParameter dans le Append. Vous devez avoir typé votre paramètre avant ou lors de l'ajout à la collection. Il est préférable de valoriser votre paramètre avant de l'ajouter à la collection afin de ne pas solliciter le fournisseur.

Delete

Enlève un paramètre de la collection. On doit spécifier soit le nom, soit l'index du paramètre à retirer.

III-C-6. Objet Parameter

III-C-6-a. Propriétés

Attributes

Précise si le paramètre accepte les valeurs signées, binaires ou NULL.

Direction

Définit si le paramètre est un paramètre d'entrée, de sortie ou de retour. Attention à ne pas confondre un paramètre de retour(adParamReturnValue) qui est l'entier éventuellement renvoyé par une procédure (avec l'instruction Return) d'un paramètre de sortie (adParamOutput) qui est le (ou les) paramètre renvoyé par une procédure (en général des valeurs tirées de la base de données).

Name

Défini le nom du paramètre. N'est pas obligatoire

NumericScale

Donne la précision (nombre de chiffres à droite de la virgule) d'un paramètre numérique.

Size

Donne la taille en octets d'un paramètre dont le type est potentiellement de longueur variable (par exemple String). Ce paramètre est obligatoire pour les types de longueur indéterminée (String, Variant).

Elle doit être toujours valorisée avant ou lors de l'ajout à la collection.

Type

Comme son nom l'indique !

Ne vous emmêlez pas les pinceaux entre les chaînes adVarChar et les chaînes Unicodes adVarWChar

Value

De manière générale, valorisez cette propriété après l'ajout à la collection.

III-C-6-b. Méthode

AppendChunk

Permet d'accéder aux textes ou binaires longs.

III-C-7. Exemple

III-C-7-a. Requêtes paramétrées

Nous allons voir ici un exemple d'utilisation de requête paramétrée.

Prenons la requête paramétrée stockée dans la base nommée ReqFerie définie comme :

 
Sélectionnez
PARAMETERS DateCib DateTime;
SELECT tblFerie.DateFer
FROM tblFerie
WHERE (((tblFerie.DateFer)>DateValue([DateCib])))
ORDER BY tblFerie.DateFer;

La fonction suivante crée un recordset en utilisant l'objet Command.

 
Sélectionnez
Private Sub Command5_Click()
Dim cnn1 As ADODB.Connection, Comm1 As ADODB.Command, Param1 As Parameter,
MonRecordset As ADODB.Recordset
Set cnn1 = New ADODB.Connection
With cnn1
	.Provider = "Microsoft.Jet.OLEDB.4.0;"
	.ConnectionTimeout = 30
	.CursorLocation = adUseClient
	.IsolationLevel = adXactChaos
	.Mode = adModeShareExclusive
	.Properties("Jet OLEDB:System database") = "D:\User\jmarc\tutorial\ADOX\system.mdw"
	.Open "Data Source=D:\User\jmarc\tutorial\ADOX\baseheb.mdb ;User
	Id=Admin; Password="
End With
Set Comm1 = New ADODB.Command
With Comm1
	.ActiveConnection = cnn1
	.CommandType = adCmdStoredProc
	.CommandText = "ReqFerie"
End With
Set Param1 = New Parameter
With Param1
	.Direction = adParamInput
	.Type = adDate
	.Name = "DateCib"
End With
Comm1.Parameters.Append Param1
Comm1("DateCib").Value = #1/1/2002#
Set MonRecordset = Comm1.Execute
End Sub

Notez que tel que ce code est écrit le curseur est statique en lecture seule. Ceci vient du fait qu'il s'agit d'une requête Select dans le vrai sens du terme est que celle-ci n'est pas modifiable.

III-C-7-b. DDL

Je ne vais pas rentrer dans le détail du DDL dans cet article mais sachez qu'il est directement utilisable avec l'objet Command. Le code suivant créera une table avec un index:

 
Sélectionnez
With Comm1
	.ActiveConnection = cnn1
	.CommandType = adCmdText
	.CommandText = "CREATE TABLE DeuxièmeTable(Prénom TEXT,Nom TEXT,
	DateDeNaissance DATETIME,CONSTRAINT MaContrainteTable UNIQUE (Prénom, Nom,
	DateDeNaissance)"
End With
Set MonRecordset = Comm1.Execute

III-C-7-c. Procédure stockée

Imaginons la procédure stockée suivante (SQL-Server) :

 
Sélectionnez
CREATE PROCEDURE LoginValide
@UserName varchar(15),
@Password varchar(10)
As
if exists(Select * From TblUtilisateur
Where Nom = @UserName
And
MotDePasse = @Password)
return(1)
else
return(0) Go

Nous allons pouvoir l'utiliser de la façon suivante en visual basic.

 
Sélectionnez
Private Sub Command5_Click()
Dim cnn1 As ADODB.Connection, Comm1 As ADODB.Command, Param1 As Parameter,
MonRecordset As ADODB.Recordset
Dim compteur As Long
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;Data Source=srv;Initial Catalog=Hebdo;User
Id=sa;Password=; "
Set Comm1 = New ADODB.Command
With Comm1
	.ActiveConnection = cnn1
	.CommandType = adCmdStoredProc
	.CommandText = "LoginValide"
End With
For compteur = 1 To 3
	Set Param1 = New Parameter
	With Param1
		.Direction = Choose(compteur, adParamInput, adParamInput, adParamReturnValue)
		.Type = Choose(compteur, adVarChar, adVarChar, adInteger)
		.Name = Choose(compteur, "Nom", "Passe", "Reponse")
		.Size = Choose(compteur, 15, 10, 4)
	End With
	Comm1.Parameters.Append Param1
	Set Param1 = Nothing
Next compteur
Comm1("Nom").Value = txtUser.Text
Comm1("Passe").Value = txtPassword.Text
Comm1.Execute
If Comm1("Reponse").Value = 0 Then Exit Sub
...
End Sub

La seule différence vient du fait qu'une procédure stockée peut renvoyer d'autres choses qu'un recordset. Dans notre exemple la procédure attend deux paramètres (nom et mot de passe) et renvoie 0 ou 1 selon l'existence de l'enregistrement dans la table.

Il faut que la réponse soit du type ReturnValue et non Output puisqu'elle est le résultat de l'instruction Return.

N.B : Vous retrouverez souvent la notation Choose dans cet article.


précédentsommairesuivant

Copyright © 2003 bidou. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.