[OLEDB] Problème driver OLEDB Oracle

Version imprimable

Bonjour,

Les 2 fournisseurs (providers) sont : Microsoft OleDb for Oracle et Oracle OleDb Provider qui est fait par Oracle lui-même. Celui de Ms semble le plus facile à prendre en main mais ne supporte pas bcp de choses comme les BLOB !! Donc, celui d'Oracle est conseillé mais ...
Le problème dans mon cas c'est lors de l'utilisation du type "_CommandPtr" d'ADO pour exécuter une procédure stockée; avec le même problème mais provider différent, le programme (en C++ ici) crashe avec Oracle Provider
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 strTmp = "Provider=OraOLEDB.Oracle;Password=christmas;Persist Security Info=True;User ID=system;Data Source=mabase"; _bstr_t bstrSQLServerConnect; _bstr_t bstrProc = ( L"sp_StartByteImport" );; _variant_t Final; bstrSQLServerConnect = (LPCTSTR) strTmp; m_status="Empty File"; _ConnectionPtr Conn1; _CommandPtr Cmd1; _RecordsetPtr Rs1; bool bvalid = false; _bstr_t bstrProc2 ="sp_AddAccountingInfo"; try { Conn1.CreateInstance( __uuidof( Connection ) ); Conn1->ConnectionString = bstrSQLServerConnect; Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty, adConnectUnspecified );//rjn: 4th param Cmd1.CreateInstance( __uuidof( Command ) ); Cmd1->ActiveConnection = Conn1; Cmd1->CommandText = _bstr_t( bstrProc ); Cmd1->CommandType = adCmdStoredProc; Cmd1->Parameters->Refresh(); Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (LPCTSTR)m_sfilename ); Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown ); bvalid = true; Final = Rs1->Fields->GetItem( _variant_t( 0L ) )->Value; strTmp.Format( "%s", CrackStrVariant( Final) ); } catch( CException *e ) { TCHAR szCause[255]; e->GetErrorMessage(szCause, 255); m_status=szCause; } catch( _com_error &e ) { m_status=e.ErrorMessage( ); } catch(...) { m_status="Error while executing the Import"; }
Le catch d'exception est exécuté lorsqu'on arrive à la ligne Cmd1->Parameters->Refresh();
Il y a donc problème avec Cmd1->Parameters car même exception lors long nPrmCount = Cmd1->Parameters->Count

C'est vrai c'est du VC++ mais le problème c'est avant tout Oracle (je pense), rien que les habitués d'Oracle peuvent le résoudre

18/04/2008, 08h20
randriano

En fait, les nombreuses limitations de MSADO me poussent à utiliser celui d'Oracle et c'est surement le constructeur du sgbd qui connait le mieux sa création : http://support.microsoft.com/kb/244661
mais ...

Cette erreur ne se produit pas seulement en C++ mais sous VB et Delphi également. Donc, s'agirait-il d'un bug d'Oracle même ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Private Sub cmd_get_Click() Dim intRoyalty As Integer str_empid = txt_empid.Text Set cmd = New ADODB.Command cmd.ActiveConnection = con cmd.CommandText = "rand.empdetails" cmd.CommandType = adCmdStoredProc cmd.Parameters.Refresh MsgBox "Nombre de param = " & cmd.Parameters.Count intRoyalty = Trim(str_empid) cmd.Parameters(0).Value = intRoyalty cmd.Execute txt_firstname = cmd.Parameters(1).Value Set cmd.ActiveConnection = Nothing End Sub
Pas de problème avec le provider Microsoft mais ERREUR SUR "cmd.Parameters.Refresh" AVEC CELUI D'ORACLE:
Le fournisseur ne peut pas obtenir les infos de paramètres et SetParameterInfo n'a pas été appelé

Et oui, sous VISUAL BASIC, le message d'erreur est assez explicite mais je ne comprend pas !!! Le provider d'Oracle semble avoir un problème avec Parameters

18/04/2008, 17h07
Vincent Rogier

Bon, j'ai a tout hasard vérifié sur METALINK, et j'ai trouvé un papier stipulant que c'est un bug connu car Oracle a décidé de ne pas supporter cette propriété ADO.

Bon, je te fais un copier/coller ici de la note metalink :

Citation:

Subject: Using .Parameters.Refresh method fails with runtime error -2147217839 (80040e51)
Doc ID: Note:267370.1
Type: PROBLEM
Last Revision
Date: 03-NOV-2006
Status: PUBLISHED

Problem Description
-------------------
With the Oracle OLEDB provider 9.2.0.2.0 or higher,
Using Visual Basic, you would like to return a list of the parameters for a
stored procedure.

Here, under a sample code :

Private Sub Command1_Click()
On Error GoTo err:
Dim ConnString As String
Dim Conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Params As ADODB.Parameters
Dim Param As ADODB.Parameter

sConnString = "Provider=OraOLEDB.Oracle;User ID=username;Password=password;Data Source=Data_Source;PLSQLRSet=1;"
Set Conn = New ADODB.Connection
Set cmd = New ADODB.Command
Conn.open sConnString
cmd.ActiveConnection = Conn
cmd.CommandText = "pkg.proc"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set Params = cmd.Parameters
For Each Param In Params
Text1.Text = Text1.Text & Param.Name & vbCrLf
Next
err:
If Err.Number <> 0 Then
Text1.Text = "err number: " & Err.Number & " : " & Err.Source & " : " &
Err.Description
End If
End Sub

You get the error number: -2147217839(80040e51) : OraOLEDB : Provider cannot derive parameter
information and SetParameterInfo has not been called.

Solution Description
--------------------
The error is due to the .Parameters.Refresh method.
This is currently a limitation of our driver.

Explanation
-----------
This bug relates to ADO .Refresh property which should be used only
at the application design time and not in the production application.
The use of .Refresh property causes one or more round trips to the
database per call.

Using this fonctionality in production applications can cause
serious performance bottlenecks.
Based on these feedbacks, we had decided not to support this property.

References
----------
Bug 2156923
19/04/2008, 19h06
randriano

C'est vraiment génial (pas génial au sens de me faire sourire) ce que vous avez trouvé vicenzo car enfin un éclaircissement !

Citation:

c'est un bug connu car Oracle a décidé de ne pas supporter cette propriété ADO.

Ce qu'il me faut donc après une telle révélation c'est une parade à cette fonctionnalité car j'ai vraiment besoin d'un système qui me permet de connaître le nombre et les infos sur les paramètres d'une procédure (nom, type, INPUT ou OUTPUT, etc).
De mon côté, je vais donc fouiller dans les news d'Oracle pour voir ce qu'il a prévu pour ce cas !!
21/04/2008, 09h51
randriano

J'avais pensé comme solution : utiliser le fournisseur Microsoft pour Parameters.refresh et utiliser le fournisseur Oracle après mais c'est pas judicieux et cause bcp de problèmes.

Une petite curiosité: j'ai même pensé à tourner vers OCILIB mais bon, le programme deviendra propre à une BD Oracle, d'où l'avantage d'utiliser ADO !! Est-ce qu'une fonction équivalente à Refresh existe sous OCILIB, c-à-d obtenir les infos sur les paramètres d'une procédure ?
21/04/2008, 10h01
Vincent Rogier
OCILIB permet actuellement de décrire :
- des tables/vues
- des types de données nommés (eg. objets)
Je comptais rajouter prochainement le support des procédures stockées.

Dans tous les cas, il est possible de récupérer ces infos en passant par le catalogue Oracle via des requêtes SQL..
21/04/2008, 10h37
randriano

Citation:

Est-ce que vous pouvez poster un exemple, svp !!

il faut taper dans user_procedures (ou all_procedures) et user_arguments (ou all_arguments).

Je te posterai un exemple entre midi et 14h

Bon un exemple tout bête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL> CREATE FUNCTION TEST_PROC
  2  (
  3     ID IN NUMBER,
  4     NAME IN VARCHAR2,
  5     CREATION IN DATE,
  6     PRICE IN OUT FLOAT,
  7     NB OUT NUMBER
  8  )
  9  RETURN
 10     NUMBER
 11  IS
 12  BEGIN
 13
 14     null;
 15
 16  END;
 17  /
 
Fonction créée.
 
SQL> SELECT
  2      POSITION  "POS",
  3      NVL(ARGUMENT_NAME, 'RETURN VALUE') "NAME",
  4      IN_OUT "MODE",
  5      DATA_TYPE "TYPE"
  6  FROM
  7      USER_ARGUMENTS a
  8  where
  9      OBJECT_NAME = 'TEST_PROC' and
 10      DATA_LEVEL   = 0
 11  ORDER BY
 12      POSITION;
 
      POS   NAME                        MODE       TYPE
--------   ---------------------  ---------- -----------------------
         0   RETURN VALUE             OUT         NUMBER
         1   ID                              IN            NUMBER
         2   NAME                         IN            VARCHAR2
         3   CREATION                   IN            DATE
         4   PRICE                         IN/OUT     FLOAT
         5   NB                             OUT         NUMBER
 
 
6 ligne(s) sélectionnée(s).
 
SQL>

Le tuyau des user_procedures/user_arguments m'a déjà aidé, avec cet ex, ça devient encore plus clair, voici un ex de comment connaitre le nombre de param:

Code:

1
2
3
4
5
6
CREATE OR REPLACE  PROCEDURE "RAND"."GETNOMBREPARAM"  
    (vProcName varchar2, vNumber out int)
is
begin
     select count(*) into vNumber from user_arguments where object_name = vProcName;
end;

J'espère que je n'aurais pas seulement à recréer la collection Parameters d'ADO en utilisant des requêtes SQL car Parameters.Count, Parameters(1).Value marchent très bien après Execute()

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
str_empid = txt_empid.Text
 
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandText = "rand.empdetails"
    cmd.CommandType = adCmdStoredProc
    'cmd.Parameters.Refresh
    'MsgBox "Nombre de param = " & cmd.Parameters.Count
 
    intRoyalty = Trim(str_empid)
 
    'cmd.Parameters(0).Type = adInteger
    'cmd.Parameters(0).Value = intRoyalty
 
    cmd.Parameters.Append cmd.CreateParameter("v_empid", adVarChar, adParamInput, 6, str_empid)
    cmd.Parameters.Append cmd.CreateParameter("v_result", adVarChar, adParamOutput, 30)
 
    cmd.Execute
 
    MsgBox cmd.Parameters(0).Value
 
    txt_firstname = cmd.Parameters(1).Value
 
    Set cmd.ActiveConnection = Nothing

21/04/2008, 16h18
Vincent Rogier

Attention, pour une fonction, avec ta requête, la valeur de retour sera comptabilisée...

Ajoutes le filtre ' and argument_name is not null ' pour n'avoir que les paramétres effectifs.
21/04/2008, 16h27
randriano

Citation:

la valeur de retour sera comptabilisée...

J'allais demander ça, merci !!
22/04/2008, 16h10
randriano

En fait, puisque c'était un bug du provider pour Oracle 9i, à quelle version ce bug est résolu ? Ne serait-ce pas donc la meilleure solution d'utiliser la version résolue ?
En fait, je suis entrain de travailler sur la reproduction des fonctions ADO liées à la collection Parameters: Parameters->Count et Parameters->Refresh() ! Pour que mon travail ne deviendrait inutile, c'est pour cela que je me demande si c'est déjà résolu ?
:?
22/04/2008, 16h27
Vincent Rogier

J'ai pas accès à la fiche du bug (c'est bizarre...).

Mais apparemment, pas de fix connu (car cela serait au moins mentionné sur le fiche de problème dont je t'ai envoyé une partie)...

En fait, je me demande si j'utilise un provider pour 10g, ça marchera-t-il avec ma base ??
__________________________________________
Entre temps, j'ai tenté de recréer par programme le Parameters.Refresh d'ADO. Et ça marche mais je crains d'avoir perdu du temps pour ça (si le bug fut fixé).

D'abord, il suffisait de créer les procédures scrutant user_arguments

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 create procedure getparamnumber(vProcName varchar2, vNumber out int)
is
begin
     select count(*) into vNumber from user_arguments where object_name = vProcName AND
       DATA_LEVEL   = 0 and argument_name is not null;
end;
/
-- procedure utile pour le Refresh
 create procedure getoraprocparams(vProcName varchar2, vPos int, vValue out varchar2, vArgName out varchar2, vDataType out varchar2, vSize out int, vIndex out int, vDirection out varchar2)
is
begin
select default_value, argument_name, data_type, char_length, position, in_out into vValue, vArgName, vDataType, vSize, vIndex, vDirection from user_arguments where object_name = vProcName AND position = vPos AND DATA_LEVEL = 0 and argument_name is not null;
end;

Ensuite, le tout c'est avec un programme utilisant le Provider qui ne supporte pas Refresh et les autres

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
long CBaseUtil::GetParameterNumber(std::wstring _wstrProcName)
{
    long iCount = -1;
    _CommandPtr cmdStoredProc;
 
    try
    {
        if (m_connDb != NULL)
        {
            if (m_connDb->GetState() ==  adStateOpen)
            {
                USES_CONVERSION;
                TESTHR(cmdStoredProc.CreateInstance( __uuidof (Command) ));
                cmdStoredProc->ActiveConnection = m_connDb;
                cmdStoredProc->CommandText = _L"RAND.GETPARAMNUMBER";
                cmdStoredProc->CommandType = adCmdStoredProc;
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vProcName", adVarChar, adParamInput, 30, CUtils::WStrToVariant(_wstrProcName, VT_BSTR)));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vNumber", adInteger, adParamOutput, 4));
 
                cmdStoredProc->Execute(NULL, NULL, adCmdStoredProc);
 
                iCount = cmdStoredProc->Parameters->Item[L"vNumber"]->Value;
            }
        }
    }
    catch (_com_error& e) 
    {
        AfxMessageBox(e.Description());
    }
    return iCount;
}
 
bool CBaseUtil::SetOraclePrm(long _nIndex, _bstr_t _ArgName, DataTypeEnum _enDT, ParameterDirectionEnum _enPD, int _iS, _variant_t _value)
{
    bool bRet = false;
 
    try
    {
        m_cmdStorProc->Parameters->Append(m_cmdStorProc->CreateParameter(_ArgName.bstrVal, 
        (DataTypeEnum)_enDT, (ParameterDirectionEnum)_enPD, _iS, _value);
    }
    catch (_com_error& e) 
    {
        CUtils::ProcessException("CStorProc::SetOraclePrms()", (char*)e.Description(), e.ErrorMessage());
    }
 
    return bRet;
}
 
bool CBaseUtil::OracleRefresh(long _nIndex, std::wstring _wstrProcName)
{
    bool bRes = false;
    _variant_t bstrValue;
    _CommandPtr cmdStoredProc;
    _variant_t bstrArgName;
    _variant_t bstrDataType;
    _variant_t ivtSize;
    _variant_t ivtIndex;
    _variant_t bstrDirection;
 
    try
    {
        if (m_connDb != NULL)
        {
            if (m_connDb->GetState() ==  adStateOpen)
            {
                USES_CONVERSION;
                TESTHR(cmdStoredProc.CreateInstance( __uuidof (Command) ));
                cmdStoredProc->ActiveConnection = m_connDb;
                cmdStoredProc->CommandText = "RAND.GETORAPROCPARAMS";
                cmdStoredProc->CommandType = adCmdStoredProc;
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vProcName", adVarChar, adParamInput, 30, CUtils::WStrToVariant(_wstrProcName, VT_BSTR)));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vPos", adInteger, adParamInput, 4, _nIndex));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vValue", adVarChar, adParamOutput, 30));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vArgName", adVarChar, adParamOutput, 30));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vDataType", adVarChar, adParamOutput, 30));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vSize", adInteger, adParamOutput, 4));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vIndex", adInteger, adParamOutput, 4));
                cmdStoredProc->Parameters->Append(cmdStoredProc->CreateParameter(L"vDirection", adVarChar, adParamOutput, 30));
 
                cmdStoredProc->Execute(NULL, NULL, adCmdStoredProc);
 
                bstrValue = cmdStoredProc->Parameters->Item[L"vValue"]->Value;
                bstrArgName = cmdStoredProc->Parameters->Item[L"vArgName"]->Value;
                bstrDataType = cmdStoredProc->Parameters->Item[L"vDataType"]->Value;
                ivtSize = cmdStoredProc->Parameters->Item[L"vSize"]->Value;
                ivtIndex = cmdStoredProc->Parameters->Item[L"vIndex"]->Value;
                bstrDirection = cmdStoredProc->Parameters->Item[L"vDirection"]->Value;
 
                CStorProcPrm prmStorProc;
                prmStorProc.SetValue(bstrValue);
                std::wstring wstrName = bstrArgName.bstrVal;
                prmStorProc.SetName(wstrName);
 
                DataTypeEnum enDataType = adVarChar;//set varchar for default
                std::wstring wstrType = bstrDataType.bstrVal;
                if (wstrType.compare(L"NUMBER") == 0)
                    enDataType = adInteger;
                else
                    if (wstrType.compare(L"VARCHAR2") == 0)
                        enDataType = adVarChar;
                    else
                        if (wstrType.compare(L"DATE") == 0)
                            enDataType = adDate;
                        //else => et encore d'autres types à gérer
                prmStorProc.SetType(enDataType);
 
                prmStorProc.SetSize(ivtSize.intVal);
                prmStorProc.SetIndex(ivtIndex.intVal - 1);//-1 : 'cos there are +1 when calling
 
                ParameterDirectionEnum enDirection;
                std::wstring wstrDir = bstrDirection.bstrVal;
                if (wstrDir.compare(L"IN") == 0)
                    enDirection = adParamInput;
                else
                    if (wstrDir.compare(L"OUT") == 0)
                        enDirection = adParamOutput;
                    else
                        if (wstrDir.compare(L"IN/OUT") == 0)
                            enDirection = adParamInputOutput;
                        /*RETURNED VALUE is not managed
                        else
                            enDirection = adParamReturnValue;*/
                prmStorProc.SetDirection(enDirection);
 
                //REFRESH EQUIVALENCE
                SetOraclePrm(_nIndex, bstrArgName, enDataType, enDirection, ivtSize.intVal, bstrValue);
 
                bRes = true;
            }
        }
    }
    catch (_com_error& e) 
    {
        AfxMessageBox(e.Description());
    }
    return bRes;
}

Bon, c'est de l'ADO donc un peu d'ActiveX (les types _variant_t et les autres). Pour Refresh, il s'agit donc de mettre la fonction OracleRefresh() dans une boucle qui tourne au nombre des paramètres de la procédure stockée:

int nCount = GetParameterNumber(CUtils::ToUpper(a_wstrStorProcName));

for (long j = 0 ; j < nCount; j ++)
{
OracleRefresh(j + 1, a_wstrStorProcName);//+1 'cos index starts by 1 in Oracle
}
a_wstrStorProcName est une variable chaine qui doit respecter la casse du nom de la procédure.
Qu'en pensez-vous les amis ?