Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
VBA – Passing Parameter to a SQL Stored Procedure (Read 12900 times)
KAndrews
YaBB Newbies
*


I Love SQL*XL

Posts: 1
VBA – Passing Parameter to a SQL Stored Procedure
05.04.06 at 15:26:20
 
VBA – Passing Parameter to a SQL Stored Procedure
Version - litSQLXL 4.0.40 & Addin SQL*XL 4.0.40

I am trying to process the following statement in SQL*XL (ref: SQL*XL the Oracle to Excel bridge: www.oraxcel.com/projects/sqlxl)
strBeginDate & strEndDate are variable dates which are used to query the output.
 
The code below was originally coded using the Excel Macro recorder and entering the values using the parameter dialog. I note the latest versions does not have a ‘Apply’ button and I assume this is why the code is not being recorded any more. The third line of the code below is reporting a 438 code.
 
Any suggestions would be welcome
 
SQLXL.Sql.Statements(1).Execute
SQLXL.Database.Parameters.BindVariables("Beginning_Date").Value = strBeginDate
SQLXL.Database.Parameters.Items("Beginning_Date").DataType = litTypeDate
SQLXL.Database.Parameters.BindVariables("Ending_Date").Value = strEnddate
SQLXL.Database.Parameters.Items("Ending_Date").DataType = litTypeDate
SQLXL.Database.Parameters.BindVariables("client").Value = "pcc"
SQLXL.Database.Parameters.BindVariables("pending").Value = "16"
SQLXL.Database.Parameters.Items("pending").DataType = litTypeNumber
 
KevinA  
Back to top
 
« Last Edit: 25.09.06 at 12:45:01 by Gerrit-Jan Linker »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: VBA Passing Parameter to a SQL Stored Proced
Reply #1 - 13.04.06 at 14:37:18
 
Dear Kevin,
 
This is clearly not correct. This code is first executing the statement (and dumping the data into Excel) and afterwards populating the parameters. The parameters should be created and populated before the statement is executed.
 
I have fixed this problem in SQL*XL 4.0.44. To test the fix I have recorded the execution of the following SQL statement:
select * from emp  where empno = :myempno  and sal = &mysal
 
As you can see I have used two types of variables here. A bind variable called myempno and a substitution variable called mysal.
 
The following VBA code was generated by the Excel macro recorder:
 
Code:
Sub Macro1()
  SQLXL.Sql.setText "select * from emp  where empno = :myempno  and sal = &mysal"
  Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "$A$1"
    .Transpose = False
    .SQLInNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  SQLXL.Sql.Statements(1).BindVariables.Add "myempno"
  SQLXL.Sql.Statements(1).SubstVariables.Add "mysal"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  With SQLXL.Sql.Statements(1).BindVariables("myempno")
    .DataType = litTypeNumber
    .Mode = litParamIn
    .Value = "14"
  End With
  SQLXL.Sql.Statements(1).SubstVariables("mysal").Value = "1300"
  SQLXL.Sql.Statements(1).Execute
End Sub 


 
Explanation of the code:
The setText method of the SQL object is used to give the SQL statement(s) to SQL*XL.
In the next few lines the target for the resulting data will be specified for the statement together with some target options.
Then you will see two statements to add the variables to the SubstVariables and a BindVariables collection of the statement object:
 SQLXL.Sql.Statements(1).BindVariables.Add "myempno"
  SQLXL.Sql.Statements(1).SubstVariables.Add "mysal"

After these statements the details of the parameters are set. Note that it is not necessary to code the datatype and mode of the Subst Variable. They are always parameters with mode in and a datatype of varchar (text).
After the values have been set to the parameters the statement is finally run.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: VBA Passing Parameter to a SQL Stored Proced
Reply #2 - 29.08.07 at 08:01:55
 
Another example of code generated with the macro recorder for statements with parameters. In this example 2 queries are run. The first with the parameter B2 to pick up the value of Excel cell B2. The second query has a named parameter called mydeptno.
 

Sub Macro1()
  SQLXL.Sql.setText "select * from scott.emp where deptno = :B2;    select * from scott.emp where deptno = :mydeptno;"
  Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "$B$4"
    .Transpose = False
    .SQLInNote = True
    .ShowNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  SQLXL.Sql.Statements(1).BindVariables.Add "B2"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  With SQLXL.Sql.Statements(1).BindVariables("B2")
    .DataType = litTypeOther
    .Mode = litParamInOut
    .Value = "20"
  End With
  SQLXL.Sql.Statements(1).Execute
  Set SQLXL.Sql.Statements(2).Target = Targets(litExcel)
  SQLXL.Sql.Statements(2).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "[Map1]Blad1!$B$7"
    .Transpose = False
    .SQLInNote = True
    .ShowNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  SQLXL.Sql.Statements(2).BindVariables.Add "mydeptno"
  With SQLXL.Sql.Statements(2)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  With SQLXL.Sql.Statements(2).BindVariables("mydeptno")
    .DataType = litTypeOther
    .Mode = litParamInOut
    .Value = "30"
  End With
  SQLXL.Sql.Statements(2).Execute
End Sub
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1