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
– Mathieu Guindon, Commented Jun 3, 2019 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
– ArcherBird, Commented Jun 3, 2019 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
– Parfait, Commented Jun 3, 2019 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
– ArcherBird, Commented Jun 3, 2019 at 16:50
Several times in same SQL query? Please show actual with certainly more than one dropID reference. — Parfait
– Parfait, Commented Jun 3, 2019 at 16:52

Parfait · Accepted Answer · 2019-06-03 17:35:00Z

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 3, 2019 at 17:35

answered Jun 3, 2019 at 16:58

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mathieu Guindon Over a year ago

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?

Parfait Over a year ago

@MathieuGuindon, unfortunately not. But I add a stored procedure version which OP can consider

Mathieu Guindon Over a year ago

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 =)

ArcherBird Over a year ago

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!

Parfait Over a year ago

Re-try ODBC version without loop but with :dropID using cmd.NamedParameters = True.

|

ArcherBird · Accepted Answer · 2019-06-04 14:18:37Z

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 4, 2019 at 14:18

ArcherBird

2,1441 gold badge14 silver badges35 bronze badges

4 Comments

Parfait Over a year ago

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.

ArcherBird Over a year ago

yes, this works with multiple references to the same parameter variable. I'll update my answer with a better example.

Parfait Over a year ago

Your example actually runs counter to this SO answer nearly 10 years ago that OraOleDB only supports positional binding not named parameters. Possibly this is a version issue with OLEDB provider though.

ArcherBird Over a year ago

@Parfait well, you are correct sir. I jumped the gun - oraOLEDB is using positional binding even in this scenario. bashes head on desk

Cole · Accepted Answer · 2021-06-14 23:27:44Z

1

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
)

edited Jun 14, 2021 at 23:27

answered Jun 14, 2021 at 23:15

Cole

11.3k2 gold badges11 silver badges25 bronze badges

Collectives™ on Stack Overflow

Parameterized Queries in VBA + ADODB + Oracle

3 Answers 3

6 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related