oldie but still a baddie (ADO [NOT .net], Oracle)

right, I *have* trawled the archives and cannot find a definitive
answer.

Been landed with a wee bit of VB/ADO onto Oracle 8 (not been there for
5 years!).

need to use an ADO Command and Parameters object to call an Oracle
stored procedure. I thought it was only Date types I was having issues
with (may get back to this), but it seems I have problems with all
types...

e.g.

Given Oracle of:

CREATE TABLE PCG.TMPG_STAGE_TESTKP
(
BUSINESSDATE DATE NOT NULL,
OTHER VARCHAR2(255) NULL
)

CREATE OR REPLACE PROCEDURE PCG.TESTKP2
(n varchar)
AS

BEGIN -- executable part starts here

INSERT INTO
TMPG_STAGE_TESTKP
(
BusinessDate,
Other
)
VALUES
(
'10 may 2006',
n
);

END testKP2;

**********
VB:


Private Sub Command1_Click()
Dim Command As New ADODB.Command
Dim Param As New ADODB.Parameter

Dim OracleDataManager As New clsOracleDataManager

With Command
.CommandType = adCmdStoredProc
.CommandText = txtSQL.Text
End With


With Param
.Name = "n"
.Direction = adParamInput
.Type = adVarChar
.Value = txtParameter.Text
End With

Command.Parameters.Append Param

OracleDataManager.ExecuteCommandOnOracleConnection _
txtDataSource.Text, _
txtUserID.Text, _
txtPassword.Text, _
Command

Set Param = Nothing
Set Command = Nothing

End Sub


Private Sub ExecuteOnOracleConnection _
( _
DataSource As String, _
UserID As String, _
Password As String, _
Optional SQL As String = "", _
Optional Command As ADODB.Command _
)

Dim ConnectionString As String
Dim OracleData As New clsOracleData

On Error GoTo err_handler

'"Provider=MSDAORA;Data Source=serverName;User ID=MyUserID;
Password=MyPassword;"
'serverName e.g. "pcglnt02" - from TNSNames.Ora

ConnectionString = "Provider=OraOLEDB.Oracle;"
'ConnectionString = "Provider=MSDAORA;"
ConnectionString = ConnectionString & "Data Source=" & DataSource &
";"
ConnectionString = ConnectionString & "User ID=" & UserID & ";"
ConnectionString = ConnectionString & "Password=" & Password & ";"


With OracleData
.ConnectionString = ConnectionString
.Connect
If SQL <> "" Then
'Execute a SQL text string
.ExecuteSQL SQL, , Command
Else
If Not (Command Is Nothing) Then
.ExecuteSQL "", , Command
End If
End If

.Disconnect
End With

Set OracleData = Nothing


Tidy_up:

Exit Sub
err_handler:
Err.Raise Err.Number, Err.Source, App.EXEName & "." & ModuleName &
".ExecuteOnOracleConnection: " & Err.Description, Err.HelpContext
Resume Tidy_up

End Sub

Public Sub ExecuteSQL _
( _
ByVal SQL As String, _
Optional Connection As ADODB.Connection, _
Optional Command As ADODB.Command _
)

Dim RecordsAffected As Long
Dim rs As Recordset 'dummy, as we aren't returning records

On Error GoTo err_handler

If m_Connection Is Nothing Then
Call Connect
End If

If Connection Is Nothing Then
Set Connection = m_Connection
End If

If Command Is Nothing Then
'Set up as a text command just to execute SQL
Set Command = New ADODB.Command
Command.CommandType = adCmdText
Command.CommandText = SQL
End If

Set Command.ActiveConnection = Connection

'execute returning no records
'it will error I think, if you feed it a command that does..
Set rs = Command.Execute(RecordsAffected, adExecuteNoRecords)

Tidy_up:
Set rs = Nothing
Exit Sub
err_handler:
Err.Raise Err.Number, Err.Source, App.EXEName & "." & ModuleName &
".ExecuteSQL: " & Err.Description, Err.HelpContext
Resume Tidy_up


End Sub

**************



executing
call PCG.TESTKP2 ('xxxx')
/

SELECT
BUSINESSDATE,
OTHER
FROM PCG.TMPG_STAGE_TESTKP;

gives me this:

BUSINESSDATE OTHER
10/05/2006 xxxx

whilst calling the VB code instead, then the same SELECT as above SQL
on the Oracle side gives me this:

BUSINESSDATE OTHER
20/05/10 128


Any clues?? AT all??? I am sure I never used to have issues like this.
Changing the provider to MSDAORA gives

BUSINESSDATE OTHER
10/05/2006 128

not much of an improvement....

.

Relevant Pages

  • [Full-Disclosure] Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit ... Connection closed by foreign host. ... think what we want to execute. ...
    (Full-Disclosure)
  • Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit we are ... Connection closed by foreign host. ... think what we want to execute. ...
    (Bugtraq)
  • Re: ADODB Command memory leak
    ... _ConnectionPtr conn = NULL; ... // Execute statement. ... > My logic is such that the connection to the database is not maintained ... Create/Append command parameters ...
    (microsoft.public.data.ado)
  • Re: sp_help_job (HELP)
    ... it's defintely the Fill line that's causing this right? ... > The following code produces a "severe error occurred on the command ... problem (using the same connection id and pwd). ... I've tried to execute it ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADODB Command memory leak
    ... My logic is such that the connection to the database is not maintained ... between calls to the Execute() method. ... Create/Append command parameters ... Destroy the command object ...
    (microsoft.public.data.ado)