Parameterized Queries in VBA + ADODB + Oracle

Question

I'm new to working with Oracle 11g and I'm having a lot of issues getting a parameterized query to work smoothly.

this code works:

    Dim rs As ADODB.Recordset
    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter

    Set con = New ADODB.Connection
    With con
        .ConnectionString = GetConnection() '<-- the driver here is Driver={Oracle in OraClient11g_home1_32bit}
        .Open
    End With

    Set cmd = New ADODB.Command

    With cmd
        Set .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP WHERE DROP_ID = ?" 
        Set prm = .CreateParameter("dropID", adVarChar, adParamInput, 50, "P_SP19_5")
        .Parameters.Append prm
        Set rs = .Execute
    End With

But the actual query I want to run will reference the dropID parameter several times. To get it working, I would have to add the same paramerter over and over. Tell me there's a better way? I tried the following:

    With cmd
        Set .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP WHERE DROP_ID = :dropID" 
        Set prm = .CreateParameter("dropID", adVarChar, adParamInput, 50, "P_SP19_5")
        .Parameters.Append prm
        Set rs = .Execute
    End With

But it hits unspecified error when I try to execute into the rs.

Also, assume for my particular case, that stored procs is not the best option (even though it should be the best option :-/)

edit: The actual query is very long and in the interest of not making you hunt down all the :dropID references, I've reduced it here, but left enough to show the multiple references.

WITH 
--...
DropDim AS (
SELECT DROP_ID
     , DROP_NAME
     , SEASON_ID
     , SEASON_NAME
     , BRAND_ID
     , SEASON_YEAR
     , 'DROP_' || substr(DROP_ID, LENGTH(DROP_ID),1) AS LP_Join_Drop
     , SEASON_NAME || '_' || SEASON_YEAR AS LP_Join_Season
FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP
WHERE DROP_ID = :dropID),
--...
LYMap AS
(SELECT DC.DROP_ID
     , DC.CHANNEL_ID
     , BSD.SEASON_YEAR
     , BSD.SEASON_NAME
     , BSD.DROP_NAME
     , FW.WEEKENDINGDATE  AS LY_WEEKENDING_DATE
     , FW.YEARWEEK AS LY_YEARWEEK
FROM MPA_LINEPLAN.REF_DROP_CHANNEL DC
  INNER JOIN MPA_MASTER.FISCALWEEK FW
    ON FW.YEARWEEK BETWEEN DC.LY_START_DT AND DC.LY_END_DT
  INNER JOIN MPA_LINEPLAN.REF_BRAND_SEASON_DROP BSD ON BSD.DROP_ID = dc.DROP_ID
WHERE DC.DROP_ID = :dropID),

LLYMap AS
(SELECT DC.DROP_ID
     , DC.CHANNEL_ID
     , BSD.SEASON_YEAR
     , BSD.SEASON_NAME
     , BSD.DROP_NAME
     , FW.WEEKENDINGDATE  AS LLY_WEEKENDING_DATE
     , FW.YEARWEEK AS LLY_YEARWEEK
FROM MPA_LINEPLAN.REF_DROP_CHANNEL DC
  INNER JOIN MPA_MASTER.FISCALWEEK FW
    ON FW.YEARWEEK BETWEEN DC.LLY_START_DT AND DC.LLY_END_DT
  INNER JOIN MPA_LINEPLAN.REF_BRAND_SEASON_DROP BSD ON BSD.DROP_ID = dc.DROP_ID
WHERE DC.DROP_ID = :dropID  ),
--....

Declare a variable? `Set @foo = ?` and then use `@foo` in the script? IDK about Oracle, but that's probably what I'd do on SQL Server. — Mathieu Guindon, Jun 03 '19 at 16:29
@MathieuGuindon That's what I would think too, but nothing I try as the Oracle equivalent seems to work. I get back all kinds of `not all variables bound` errors. Amazingly, I can't find any working examples on the internet :-( — ArcherBird, Jun 03 '19 at 16:45
I am not understanding question. Where are using *same parameter over and over*. Why do qmarks not work? In both prepared statements only one parameter is used. Do you mean an SQL statement with more than one qmark? — Parfait, Jun 03 '19 at 16:49
@Parfait for simplicity, I only put a simple sql statement. The actual statement references the `dropID` (or qmark) variable 10+ times. The point is that I don't want to add the same parameter 10+ times to make the qmark version work. — ArcherBird, Jun 03 '19 at 16:50
Several times in *same* SQL query? Please show actual with certainly more than one `dropID` reference. — Parfait, Jun 03 '19 at 16:52
I agree with @Parfait - part of the goal of a [mcve] is to properly illustrate the actual problem at hand. Doesn't have to be your actual SQL statement or schema, but the question would definitely be clearer if the statement included mutliple references to the same parameter. — Mathieu Guindon, Jun 03 '19 at 16:57

Parfait · Accepted Answer · 2019-06-03T17:35:00.633

3

Continue to use the qmarks placeholder and simply use a for loop to append same parameter object. Specifically, qmarks correspond to the position placed in query. Assuming the below query

sql = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP" _
        & " WHERE DROP_ID = ? AND DROP_ID2 = ? AND DROP_ID3 = ?" 

With cmd
   Set .ActiveConnection = con
   .CommandType = adCmdText
   .CommandText = sql
   For i = 1 To 3  ' ADJUST TO NUMBER OF PARAMS
      Set prm = .CreateParameter("prm" & i, adVarChar, adParamInput, 50, "P_SP19_5")
      .Parameters.Append prm
   Next i
   Set rs = .Execute
End With

Alternatively, turn your query into a stored procedure (avoiding very large SQL string or text file read in VBA), then define one parameter.

Oracle

CREATE OR REPLACE PROCEDURE my_procedure_name(dropID IN VARCHAR2) IS
BEGIN
   ...long query using dropID (without any symbol)...
END;
/

VBA

With cmd
   Set .ActiveConnection = con
   .Properties("PLSQLRSet") = TRUE  
   .CommandType = adCmdText
   .CommandText = "{CALL my_procedure_name(?)}"       
   Set prm = .CreateParameter("prm", adVarChar, adParamInput, 50, "P_SP19_5")
   .Parameters.Append prm

   Set rs = .Execute
End With

edited Jun 03 '19 at 17:35

answered Jun 03 '19 at 16:58

Parfait

104,375
17
94
125

1

That works, but quickly gets messy if/when you start needing another parameter. I don't think I ever used them, but doesn't ADODB support named parameters? – Mathieu Guindon Jun 03 '19 at 17:00
@MathieuGuindon, unfortunately not. But I add a stored procedure version which OP can consider – Parfait Jun 03 '19 at 17:07
1

Ha! Deleting my draft then - was going to go with recommending a stored procedure... which *should* be the preferred way of doing *anything* SQL in VBA (or any language, really) anyway =) – Mathieu Guindon Jun 03 '19 at 17:08
Thanks for the suggestions. My company's DBAs make it hard to develop stored procs (i have to go through a bureaucratic process just to do simple DDLs, even for dev). I actually found another solution which works a bit better. I'm going to post it in case anyone else stumbles upon this same problem, but just wanted to thank you for taking time to answer my question! – ArcherBird Jun 04 '19 at 14:12
Re-try ODBC version without loop but with `:dropID` using `cmd.NamedParameters = True`. – Parfait Jun 04 '19 at 14:50
it seems stored proc really is the only way to go here. I recognize that it is also the correct way to go. So, I will concede to the DBA bureaucrats here and increase my development time quotes accordingly :-/ – ArcherBird Jun 04 '19 at 18:03

score 2 · Answer 2 · answered Jun 04 '19 at 14:18

2

The best solve for this was simply to stop using Oracle's ODBC driver and start using Oracle's OLEDB as a provider instead.

Old connection string: .ConnectionString = "Driver={Oracle in OraClient11g_home1_32bit};UID=MyUname;PWD=MyPW;DBQ=MyDB;"

New connection string: .ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=MyDB; User ID=MyUname;Password=MyPW;"

OraOLEDB supports named parameters, which is exactly what I was trying to get to in the first place. Now, I can reference parameter names in the SQL statement with : prefix.

With cmd
    Set .ActiveConnection = con
    .CommandType = adCmdText
    .CommandText = "SELECT * FROM MPA_LINEPLAN.REF_BRAND_SEASON_DROP WHERE DROP_ID =:dropID"
    Set prm = .CreateParameter("dropID", adVarChar, adParamInput, 50, "P_SP19_5")
    .Parameters.Append prm
    Set rs = .Execute
End With

This now works!

answered Jun 04 '19 at 14:18

ArcherBird

2,019
10
35

Interesting! But once again, your example only uses one parameter. Does this work for larger query with many `:dropID`? Please mention so in your answer. – Parfait Jun 04 '19 at 14:37
yes, this works with multiple references to the same parameter variable. I'll update my answer with a better example. – ArcherBird Jun 04 '19 at 14:39
1

Your example actually runs counter to this [SO answer](https://stackoverflow.com/a/1337641/1422451) nearly 10 years ago that OraOleDB only supports positional binding not named parameters. Possibly this is a version issue with OLEDB provider though. – Parfait Jun 04 '19 at 14:48
@Parfait well, you are correct sir. I jumped the gun - oraOLEDB is using positional binding even in this scenario. ***bashes head on desk*** – ArcherBird Jun 04 '19 at 15:09

Cole · Answer 3 · 2021-06-14T23:27:44.503

Another approach is to use a CTE to have all your scalar parameters and then join back to the table you are interested in:

-- Outer SELECT * due to limitations of ODBC drivers in VBA
SELECT *
FROM
(
 WITH lu as (
   SELECT ? as drop_id 
   FROM dual
    )
  )
  SELECT t1.*
  FROM mpa_lineplan.ref_brand_season_drop t1
  CROSS JOIN lu -- could be inner join or whatever type you are interested in
  WHERE t1.drop_id = lu.drop_id
)

Parameterized Queries in VBA + ADODB + Oracle

3 Answers3