large sql in vb to fill Excel cells ? - OO4O and Wizards(Archived)

I have a large sql statement to call from Excel 97. I use Visual Basic (newbie).
When I have a small sql it is ok (the cells are filling ok), but I don't know how to call a large sql. Can I call directly a sql file ? it would be easier. Or a package ? but how to retrieve info in cells ???
Here the code I'm trying :
------------------
Sub connect()
Dim OraSession As Object 'Declare variables as OLE Objects
Dim OraDatabase As Object
Dim OraDynaset As Object
Dim ColNames As Object
'my sqlStmt is defined here on several lines of 255 chars max, and separated with ' _' at the end (not the last line)
sqlStmt = "SELECT RAS.NAME, RAC.CUSTOMER_NUMBER, RAC.CUSTOMER_NAME, (raa.address1||decode(raa.address1,null,null,',')|| raa.postal_code||decode(raa.postal_code,null,null,',')|| raa.city||decode(raa.city,null,null,',')|| raa.country), ct.TRX_NUMBER, ct.TRX_DATE," _
cl.SALES_ORDER, cl.SALES_ORDER_DATE, SUM(cl.REVENUE_AMOUNT) "Somme Bulletin" FROM RA_CUSTOMER_TRX_ALL ct, RA_CUSTOMERS RAC, RA_CUSTOMER_TRX_LINES_ALL cl, RA_ADDRESSES_ALL RAA, RA_SALESREPS_ALL RAS WHERE cl.REVENUE_AMOUNT <>0 AND CL.revenue_amount is" _
not null AND cl.SALES_ORDER IS NOT NULL AND ct.COMPLETE_FLAG ='Y' AND ct.CUSTOMER_TRX_ID = cl.CUSTOMER_TRX_ID and exists (select 1 from RA_CUST_TRX_LINE_GL_DIST_ALL gl where cl.customer_trx_line_id = gl.customer_trx_line_id and GL.GL_DATE BETWEEN" _
TO_DATE('01/02/2002', 'DD/MM/YYYY') AND TO_DATE('28/02/2002', 'DD/MM/YYYY')) AND CT.PRIMARY_SALESREP_ID = RAS.SALESREP_ID(+) AND CT.ORG_ID = RAS.ORG_ID(+) AND ct.BILL_TO_CUSTOMER_ID = RAC.CUSTOMER_ID AND RAC.CUSTOMER_ID = RAA.CUSTOMER_ID GROUP BY" _
RAS.NAME, RAC.CUSTOMER_NUMBER, RAC.CUSTOMER_NAME, raa.address1, raa.postal_code, raa.city, raa.country, CT.TRX_NUMBER, ct.TRX_DATE, cl.SALES_ORDER, cl.SALES_ORDER_DATE ORDER BY RAS.NAME ASC, RAC.CUSTOMER_NUMBER ASC, RAC.CUSTOMER_NAME ASC, raa.address1," _
raa.postal_code, raa.city, raa.country, ct.TRX_NUMBER ASC, ct.TRX_DATE ASC, cl.SALES_ORDER ASC, cl.SALES_ORDER_DATE ASC"
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("ora11idb", "user/pass", 0&)
Set OraDynaset = OraDatabase.DbCreateDynaset(sqlStmt, 0&)
' Using field array, ie. ColNames("ename").value, is significantly faster than using field lookup, ie. EmpDynaset.fields("ename").value
Set ColNames = OraDynaset.Fields
' Place column headings on sheet
' note : Excel is installed in French language
For icols = 1 To ColNames.Count
'For icols = 1 To 2
Worksheets("Feuil1").Cells(1, icols).Value = ColNames(icols - 1).Name
Next
' Check if the current dynaset row is valid
If OraDynaset.BOF <> True And OraDynaset.EOF <> True Then
' Place data on sheet using CopyToClipboard
OraDynaset.CopyToClipboard -1
Worksheets("Feuil1").Visible = True
Sheets("Feuil1").Select
Range("A2").Select
Sheets("Feuil1").Paste
End If
End Sub
------------------
When I try this I get 'Compile error / Syntax error' at 'sqlStmt'
What is wrong ???
Thanks, Antoine
my e-mail please : antoine.theytaz at gmvs.migros.ch

Related

returning multiple pl/sql tables to VB using ADO + Oracle's OLE DB

Windows XP
VB 6
ADO 2.7
Oracle 9i Client (OLE DB 9.2.0.1)
Oracle DB 8.1.7.2
I have just upgraded a VB5/NT (RDO + MS ODBC for Oracle) app to VB6/XP (ADO + Oracle's OLE DB). The calls to stored procedures that returns tables are not working now. I get following error:
-214721900 ORA-06550: line 1, column 42:
PLS-00201:identifier 'LO_AUDIT_ID_TBL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Please provide hint/help. 
Can you post the stored procedure signature, the stored procedure code, and the ADO you're using to call it? I assume this is a stand-alone application, not an ASP app, right?
It appears that your stored procedure is having difficulty finding the (table?) LO_AUDIT_ID_TBL. Is this a variable in your stored procedure?
Justin 
Yes, the LO_AUDIT_ID_TBL is a variable (of table type) being returned from the stored procedure.
I tried changing case of table is in pl/sql.
I tried removing the table(s) from the Call statement...
and I get error message that there are missing parameters.
THANKS SO MUCH FOR YOUR HELP.
I have been reading OLE DB User's guide in which there are examples of Ref Cursors and has no documentation regarding how to achieve same using tables (in stead of Ref Curs). I am also looking for documentation that would describe all different Registry attributes that could be turned on or off to do things, but have not been able to find any.
I am trying to use the PLSQLRSet to retrieve rowsets from pl/sql tables. You can see that in my VB code below, in GetSPResultSet function.
The VB Code is:
Public Function Retrieve() As ADODB.Recordset
Dim inparam(-2 To -1) As Variant 'set to be ignored by GetSPResultSet method
Dim outparam(0) As Variant
Dim strSQL As String
strSQL = "{ CALL PckPLAudit.Sel_Smmry( ?,{resultset 1000, lo_audit_id_tbl, lo_audit_dt_tbl, lo_audit_type_tbl, " & _
"lo_audit_status_tbl, lo_qty_selected_tbl, lo_qty_deselect_tbl, lo_qty_sent_tbl, lo_qty_follow_tbl, " & _
"lo_qty_received_tbl, lo_qty_ncpl_tbl, lo_qty_security_tbl, lo_qty_closed_tbl, lo_qty_sentsec_tbl}, {resultset 1, lo_res_tbl} ) }"
Set rdoRs = mConnection.GetSPResultSet(strSQL, inparam(), outparam())
BuildCollection
Set Retrieve = rdoRs
End Function
--------------
Public Function GetSPResultSet(ByVal szSQL As String, InParam() As Variant, OutParam() As Variant) As ADODB.Recordset
'============================================================================
'submits a call to a stored procedure that returns table output parameters.
'out params are interpreted as ADODB.Recordset
'szSQL : SQL call to stored procedure
'InParam() : array of input parameter values
'OutParam() : array of output parameter values set in this function
'============================================================================
Dim qry As New ADODB.Command
Dim ParamIn As ADODB.Parameter
Dim ParamOut As ADODB.Parameter
Dim RS As ADODB.Recordset
Dim inti As Integer
Dim intj As Integer
Dim blnret As Boolean
Dim mErrors As New cErrors
Dim retVal As Double
'Dim itmp As Integer
Dim ParmType As DataTypeEnum
Dim i
On Error GoTo errGetSPResultSet
blnret = True
Set qry = New ADODB.Command
qry.ActiveConnection = mrdoCn
qry.CommandType = adCmdText
qry.CommandText = szSQL
'load rdoParameter object(s) from InParam array if InParam exists
For inti = 0 To UBound(InParam)
'qry.Parameters(i).Value = InParam(inti)
Set ParamIn = New ADODB.Parameter
Select Case TypeName(InParam(inti))
Case "Integer"
ParmType = adDouble
Case "Double"
ParmType = adDouble
Case "String"
ParmType = adChar
Case "Null"
ParmType = adDouble
Case "Date"
ParmType = adDate
Case "Long"
ParmType = adDouble 'jks 12/19/2002
End Select
Set ParamIn = qry.CreateParameter(ParamIn, ParmType, adParamInput)
ParamIn.Value = InParam(inti)
If TypeName(InParam(inti)) = "Null" Then
ParamIn.Size = 0
Else
ParamIn.Size = Len(InParam(inti))
End If
qry.Parameters.Append ParamIn
Next
For intj = 0 To UBound(OutParam)
Set ParamOut = New ADODB.Parameter
Select Case TypeName(OutParam(intj))
Case "Integer"
ParmType = adDouble
Case "Double"
ParmType = adDouble
Case "String"
ParmType = adChar
Case "Null"
ParmType = adEmpty
Case "Empty"
ParmType = adDouble
Case "Date"
ParmType = adDate
Case "Long"
ParmType = adDouble 'jks 12/19/2002
End Select
Set ParamOut = qry.CreateParameter(ParamOut, ParmType, adParamOutput, 255)
qry.Parameters.Append ParamOut
Next
'**************************************************************
'execute the stored procedure call
qry.Properties("PLSQLRSet") = True
Set RS = qry.Execute() 'rdOpenStatic, rdConcurReadOnly
qry.Properties("PLSQLRSet") = False'
For intj = 0 To UBound(OutParam)
OutParam(intj) = qry.Parameters.Item(inti)
inti = inti + 1
Next
If OutParam(0) <> 0 Then
If OutParam(0) = 999 Then
i = mErrors.DisplayError(Val(OutParam(0)), "SAM")
Else
MsgBox "Database returned error code " & OutParam(0) & "." & vbCrLf & " Unable to complete operation. "
End If
blnret = False
End If
If blnret Then
Set GetSPResultSet = RS
Else
Set GetSPResultSet = Nothing
End If
Set RS = Nothing
Set qry = Nothing
Set ParamIn = Nothing
Set ParamOut = Nothing
exitGetSPResultSet:
Exit Function
errGetSPResultSet:
blnret = ProcessError(Err)
If Not blnret Then Set GetSPResultSet = Nothing
Resume exitGetSPResultSet
End Function
---
The stored procedure is:
CREATE OR REPLACE PACKAGE BODY D5750PGM.PckPLAudit IS
PROCEDURE Sel_Smmry (
     lo_res_cd          out     number,
     LO_AUDIT_ID_TBL          out     pckclaudit.audit_id_tbl%type,
     lo_audit_dt_tbl          out     pckclaudit.audit_dt_tbl%type,
lo_audit_type_tbl     out     pckclaudit.audit_type_tbl%type,
     lo_audit_status_tbl     out     pckclaudit.audit_status_tbl%type,
     lo_qty_selected_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_deselect_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_sent_tbl          out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_follow_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_received_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_ncpl_tbl          out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_security_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_closed_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_qty_sentsec_tbl     out     pckclaudit.audit_smmry_qty_tbl%type,
     lo_res_tbl          out     pcktbtable_type.res_tbl%type
     )
IS
BEGIN
     PckAudit.Sel_Smmry (pckclglobal.lg_pl_proc, lo_res_cd,
     lo_audit_id_tbl,
     lo_audit_dt_tbl,
lo_audit_type_tbl,
     lo_audit_status_tbl,
     lo_qty_selected_tbl,
     lo_qty_deselect_tbl,
     lo_qty_sent_tbl,
     lo_qty_follow_tbl,
     lo_qty_received_tbl,
     lo_qty_ncpl_tbl,
     lo_qty_security_tbl,
     lo_qty_closed_tbl,
     lo_qty_sentsec_tbl,
     lo_res_tbl
     );
END Sel_Smmry;
END PckPLAudit;
----
also, pckclaudit.audit_id_tbl%type is defined as
TYPE audit_id_tbl_type IS TABLE OF disb_audit.disb_audit_id%TYPE
     INDEX BY BINARY_INTEGER ;
and disb_audit.disb_audit_id%TYPE is defined as
CREATE TABLE D5750.DISB_AUDIT
(
DISB_AUDIT_ID NUMBER(9,0) NOT NULL,
AUDIT_DT DATE NOT NULL,
AUDIT_CODE_TYPE_ID VARCHAR2(5) NOT NULL,
AUDIT_CODE_ID VARCHAR2(2) NOT NULL,
CRTN_ID VARCHAR2(8) NOT NULL,
CRTN_DT_TM DATE NOT NULL,
....
....
etc.
You cannot return table types from database using OraOLEDB
You will have to use Ref Cursor to be able to return multiple rows.
Chandar 
Thanks, Chandar.
Nimisha.

HypRetrieve returns -3  Excel VBA

I've written some VBA code to run a macro inside my Excel 2007 worksheet. I'm using Hyperion Smart View version 9.3.1.2.029. Here's the code I created to refresh my worksheet:
Sub refreshData()
'FileSystem.FileCopy "C:\ExpenseFcstDownload\Expense_Forecasting.txt", "C:\ExpenseFcstDownload\Expense_Forecasting_Previous.txt"
'Application.ScreenUpdating = False
Dim costCenterRowStart As Integer
Dim costCenterColStart As Integer
Dim costCenterCell As range
Dim forecastRowStart As Integer
Dim forecastColStart As Integer
Dim forecastCell As range
Dim fileName As String
Dim delimiter As String
Dim append As Boolean
Dim rollup As String
Dim i As Integer
costCenterRowStart = 2
costCenterColStart = 1
forecastRowStart = 6
forecastColStart = 1
fileName = "c:\Expense_Forecasting.txt"
delimiter = "|"
append = False
i = 0
Set costCenterCell = getCell(costCenterRowStart, costCenterColStart, Sheet4)
Set forecastCell = getCell(forecastRowStart, forecastColStart, Sheet2)
connect
While costCenterCell.Offset(i, 0) <> ""
range(forecastCell, forecastCell.End(xlDown)).Clear
'Sheet4.Select 'Cost Center Sheet
costCenterCell.Offset(i, 0).Copy
'Sheet2.Select 'Forecast Sheet
'forecastCell.Offset(0, 1).Select
range(forecastCell.Offset(0, 1), forecastCell.Offset(0, 1).End(xlDown)).Offset(0, -1).PasteSpecial (xlPasteValues)
'Range(forecastCell.Offset(0, 1).Address, forecastCell.Offset(0, 1).Address.End(xlDown)).Select
'Range(forecastCell.Offset(0, 1), forecastCell.Offset(0, 1).End(xlDown)).Offset(0, -1).PasteSpecial (xlPasteValues)
forecastCell.Offset(0, 7).Formula = "=VLOOKUP(TRIM(B6),'ACCOUNT MAPPING'!$A$5:$B$291,2,FALSE)"
forecastCell.Offset(0, 7).Copy
' forecastCell.Select
range(forecastCell, forecastCell.End(xlDown)).Offset(0, 7).PasteSpecial (xlPasteFormulas)
'Application.Wait Time + TimeSerial(0, 0, 30)
'do the refresh
While Len(forecastCell) = 12
retrieve
Set forecastCell = getCell(forecastRowStart, forecastColStart, Sheet2)
Wend
'Application.Wait Time + TimeSerial(0, 0, 30)
Set forecastCell = getCell(forecastRowStart, forecastColStart, Sheet2)
forecastCell.Offset(0, 7).Formula = "=VLOOKUP(TRIM(B6),'ACCOUNT MAPPING'!$A$5:$B$291,2,FALSE)"
forecastCell.Offset(0, 7).Copy
' forecastCell.Select
range(forecastCell, forecastCell.End(xlDown)).Offset(0, 7).PasteSpecial (xlPasteFormulas)
'Sheet2.Select
'forecastCell.Select
'range(forecastCell, forecastCell.End(xlToRight)).Select
'range(forecastCell, forecastCell.End(xlDown)).Select
'export to txt
append = i
exportText forecastCell, fileName, delimiter, append
'forecastCell.Offset(0, 7).Select
range(forecastCell.Offset(0, 7), forecastCell.End(xlDown)).Offset(0, 7).Clear
'forecastCell.Select
range(forecastCell, forecastCell.End(xlDown)).Clear
i = i + 1
Wend
disconnect
Application.ScreenUpdating = True
Workbooks(1).Save
'FileSystem.FileCopy "C:\ExpenseFcstDownload\Expense_Forecasting.txt", "P:\Expense\Load_Data\Expense_Forecasting.txt"
Application.Quit
Public Sub connect()
Dim x As Long
x = HypConnect("Forecast Data", "user1", "1234", "EssbaseSpendExpense")
End Sub
Public Sub disconnect()
Dim x As Long
x = HypDisconnect("Forecast Data", True)
End Sub
Public Sub retrieve()
Dim x As Long
x = HypRetrieve("Forecast Data")
End Sub
If I step through my code (F8), it works fine. If I try to run the macro by itself (F5), the HypRetrieve will randomly return -3. I've noticed that it seems to fail more often when I'm multitasking. If I keep the spreadsheet focused and don't move the mouse, it will sometimes work. I couldn't find any error description for -3 except that it's a local failure. I know others have gotten this type of error but I couldn't find out how they resolved it.
Thanks. 
Just a guess but make sure the active sheet is the one where you want to perform the retrieve.
Even though the first parameter is for sheet name, currently the VBA macros ignore that setting and default to the active sheet. Sheetname is a placeholder parameter for future use. 
At first I tried activating the sheet before the call to retreive but that didn't work. It turns out that you need to activate the application itself before retreiving.
AppActivate "Microsoft Excel"

API error in import script

I am attempting to concatenate the result of a dimension lookup onto the source account string. I have attached the script below to the account dimension in the import format using a data pump import script.
____________________________
'Function CCRule(strField, strRecord)
Dim StrCC
StrCC = (DW.Utilities.fParseString(strRecord,8,3,","))
If StrCC ="" Then
CCRule = StrField
Else
CCRule = StrField + "_" + API.SqlMgr.fMapItemLookup(800,"UD5",StrCC)
End If
End Function
_______________________________________________________
When i run this script i get the error "Object Required 'API'"
Please let me know your thoughts on what i am doing wrong here.
Thank you 
#1 = You cannot use API items in Import scripts
#2 = I am not 100% sure I understand what you are doing here, could you provide some sample data? There's probably another way to accomplish this. 
Thank you for your response. Our cost centers fall into three categories "C" "A" or "S" and the account mappings need to take these categories into consideration. Instead of concatenating the full cost center onto the account and mapping every combination I am hoping to append the category onto the account string. Below is some sample data of what i am hoping will occur.
Original Data File:
Account, CostCenter, amount
123, C998, $10
123, C778, $15
232, C656, $20
UD5 Map:
Source, Target
C998, C
C778, S
C656, C
Data File after Script
Account, CostCenter, Amount
123_C, C998, $10
123_S, C778, $15
232_C, C656, $20
Account map
source, target
123_C, hfmx
123_S, hfmy
123_A, hfmz
232_C, hfml
232_S, hfmm
232_A, hfmn
Please let me know if there is a better way to accomplish this. For user adoption purposes i would like to avoid conditional mapping if possible.
Thank you again for you guidance. 
and I assume there are more than 3 UD5 map records? :) It's also too bad the cost center type isn't just buried into the account with a unique ID character .......
You should able to use :
DW.DataAccess.farsKeySet(strSQL)
To execute your own SQL statement against the DataMap table to get at the information ..... 
Thanks, yeah, there are about 3000 cost centers and the "category" is only tracked offline in a spread sheet...making this a little more fun that it otherwise needs to be :) Thanks again for your help. I'll take a stab and that approach. 
I am running into the following error when i try the script below "Row cannot be located for updating. Some values may have been changed since it was last read" Please help. My desired end result is to concatenate the cost center type with the current account source string.
_____________________________________________________
Sub ImportAction(strEventName)
'------------------------------------------------------------------
'Hyperion FDM EVENT Script:
'
'Date Created:      7/2/2012 3:46: PM
'Change Log:
'
'Purpose:
'-----------------------------------------------------------------
If LCase(strEventName) = "postworktomainprocess" Then
' Get work table
     strWork = RES.PstrWorkTable
'Get the current location's profile
With API.POVMgr.fCheckLocProf(API.POVMgr.PPOVLocation)
lngLocID = .lngLocKey
'Location equals SAP_TB
If .lngLocKey ="800" Then
     If RunCCRules(strWork) <> False Then
          RES.PlngActionType = 2
          RES.PstrActionValue = RES.PstrActionValue & "<big>Updated for missing cost center;</big>"
     End If
     
     
End If
End With
End If
End Sub
'***********************************************************************
Function RunCCRules(strWork)
Dim strSQL
Dim rsSQL
Dim strCostCenters
Dim strCurrAccount
strSQL = "SELECT " & strWork & ".AccountX, tCostCenterType.HFM_CostCenterType, tCostCenterType.HFM_CostCenter FROM " & strWork & _
                    " LEFT JOIN tCostCenterType ON " & strWork & ".UD1X = tCostCenterType.HFM_CostCenter" & _
                    " WHERE ((" & strWork & ".PartitionKey = '" & RES.PlngLocKey & "' AND " & _
                    strWork & ".PeriodKey = '" & RES.PdtePerKey & "' AND " & _
                    strWork & ".CatKey = '" & RES.PlngCatKey & "'))"
                    
'Get recordset matching account criteria
Set rsSQL = API.DataWindow.DataAccess.farsKeySet(strSQL)     
With rsSQL
     If Not .EOF And Not .Bof Then
          Do While Not .Eof
strCostCenter = Trim(.Fields("HFM_CostCenterType").Value)
               strCurrAccount = Trim(.Fields("AccountX").Value)
               If .Fields("HFM_CostCenterType").Value <> "" Then
                    .Fields("AccountX").Value = strCurrAccount & StrCostCenter
               .Update
               Else .Fields("AccountX").Value = strCurrAccount
.Update
          End If     
               
               .MoveNext
          Loop
     End If
End With
'Destroy the recordset object
Set rsSQL = Nothing
     
End Function
________________________________________________________________________
Edited by: user8714121 on Jul 2, 2012 4:39 PM 
Somethings about your code:
#1 - I think what you're trying to do is update ALL of the records at once, which would be most efficient?
#2 - I do not see anything that is creating the table 'tCostCenterType' ?
#3 - The update may fail because your query is joining two tables?? Not 100% sure if that would work, especially since I can't figure out where the one table comes from
#4 -
I might be over simplifying below, but I think that is what you want.
NOTE : Performance could be an issue with what I did below due to the querying against datamap table, though you could just do one select distinct to get all UD5 Src / Targ combinations, load it to a local array, and perform a search in the script.
Function CCRule(strField, strRecord)
'Declare working variables
Dim strCC
Dim strSQL
Dim rsSQL
Dim strUD5Targ
'Assign values
strCC = (DW.Utilities.fParseString(strRecord,8,3,","))
if trim(strCC) < > "" then
   'Query Data Map table for given location ("partition"), Dimension, and SrcKey
   'NOTE: This assumes EXPLICIT mapping is happening.  If there is some type of 'LIKE' or 'BETWEEN'
   ' more thought required here.
   strSQL = "SELECT TargKey from tDataMap where PartitionKey = " & RES.PlngLocKey & " and DimName = 'UD5' and SrcKey = '" & strCC & "'"
   'Get recordset matching account criteria
   set rsSQL = DW.DataAccess.farsFireHose(strSQL)
   'Initialize Target UD5
   strUD5Targ = ""
   'First record in recordset should be result we want since we limited with WHERE clause in query
   'NOTE: in ADO.NET we can do a ExecuteScalar call which only returns ONE result which would be perfect.
   'Would limit overhead of recordset, etc, etc, etc.......
   'NOTE : This *IS NOT* efficient for large amounts of imported rows as we're executing MANY queries here...
   '   If a lot of rows, perhaps a better idea is to read *ONCE* all of the distinct Src / Target UD5 values into an
   '   in-memory array and perform a search.....
   With rsSQL
   If Not .EOF And Not .Bof Then
     strUD5Targ = trim(.Fields("TargKey"))
   end if
   'Release memory
   Set rsSQL = Nothing
   if strUD5Targ = "" then CCRule = strField else CCRule = strField & "_" & strUD5Targ
else
   CCRule = strField
end if
End FunctionEdited by: beyerch2 on Jul 2, 2012 5:33 PM
Edited by: beyerch2 on Jul 2, 2012 5:34 PM
Edited by: beyerch2 on Jul 2, 2012 5:35 PM 
Problem could be with this line
Else .Fields("AccountX").Value = strCurrAccount
try splitting over 2 lines
Else
.Fields("AccountX").Value = strCurrAccount 
The provided script worked like a charm. Thank you very much for your help with this! 
Thank you for your help with this. This is SIGNIFICANTLY better than the path i was going down. 
the only thing I would note is to watch the performance of that. There are ways to make it better if it 'sucks'.
Also, if it did answer your problem feel free to assign points. If I get to 1,000 points I get a free slinky or something. :-)

ADO Recordset missing first column

I'm having a problem where ADO Recordsets returned by a query are missing the first specified field.
I'm using MDAC 2.7, Net8 8.1.7.0.0, Oracle ODBC driver 8.1.7.5.0.
Executing SQL like this
SELECT T1.APPLICATION_ACRONYM, T2.ROLE_NAME R FROM APPLICATION T1, APPLICATION_ROLE T2 WHERE T1.APPLICATION_ACRONYM = 'CIMS' AND T1.APPLICATION_ACRONYM = T2.APPLICATION_ACRONYM ORDER BY R
results in a recordset with the APPLICATION_ACRONYM field missing. If I add another field to the select statement, whatever the first field is is missing.
The problem occurs when I specify to use a static cursor, but doesn't happen if I specify to use a forwardonly cursor. It also seems to happen only with joins (doesn't happen returning just a table or view).
Is this an ODBC driver problem or an MDAC problem?
Example code (a VBS file):
Option Explicit
Const strSQL = "SELECT T1.APPLICATION_ACRONYM, T2.ROLE_NAME R FROM APPLICATION T1, APPLICATION_ROLE T2 WHERE T1.APPLICATION_ACRONYM = 'CIMS' AND T1.APPLICATION_ACRONYM = T2.APPLICATION_ACRONYM ORDER BY R"
Dim oADOConn, oADOCmd, oRecordSet, Field
Set oADOConn = CreateObject("ADODB.Connection")
oADOConn.Open "Provider=MSDASQL.1;Data Source=MyDSNname", "MyDBUserID", "MyDBPassword"
Set oADOCmd = CreateObject("ADODB.Command")
Set oRecordSet = CreateObject("ADODB.Recordset")
oADOCmd.CommandText = strSQL
oADOCmd.CommandType = 1
Set oADOCmd.ActiveConnection = oADOConn
oRecordSet.Open oADOCmd, , 0, -1
WScript.Echo "Opened recordset; result fields:"
For Each Field in oRecordset.Fields
WScript.Echo "[" & Field.Name & "]"
Next
oRecordSet.Close
oRecordSet.Open oADOCmd, , 3, -1
WScript.Echo "Opened recordset with static cursor; result fields:"
For Each Field in oRecordset.Fields
WScript.Echo "[" & Field.Name & "]"
Next
Jeff Johnson

ADO ResultSet with "script"

Hi,
we are migrating a MS SQL Server 2000 to Oracle 9i Rel. 2. The application is implemented with different Microsoft technologies (VB, ADO, .NET, ASP ...).
Currently we have the functionaliy to search around a point represented by a ZIP code (PLZ). To achieve this in an efficient way, we calculate the latitude and longitude ranges which determine a "square-ish" area around the given point.
The result is a set of ZIP codes. This ResultSet is created by the following "script". It is an example of a very simple "search around a point" dynamic query we send in one roundtrip from our VB component to the SQLServer (the left outer joins are useless in this particular query, but I left them to have a complete filter query from our live servers):
declare #minLat float,#maxLat float,#minLng float,#maxLng float
exec zipSquare 'D', 85614, 100, #minLat output, #maxLat output, #minLng output, #maxLng output
select top 501 c.car_id
FROM cars c WITH(NOLOCK)
LEFT OUTER JOIN makes mak WITH(NOLOCK) ON c.make_id = mak.make_id
LEFT OUTER JOIN models mod WITH(NOLOCK) ON c.model_id = mod.model_id
LEFT OUTER JOIN bodytypes bod WITH(NOLOCK) ON c.body_id = bod.body_id
LEFT OUTER JOIN bodycols col WITH(NOLOCK) ON c.bodycol_id = col.bodycol_id
WHERE carTinyPoolId IN (1,4,8)
AND c.make_id=9
AND c.model_id=16416
AND c.priceeuro>=5000
AND c.accident=0
AND zcountry='D'
AND c.state = 'A'
AND c.pub_start<=GETDATE()
AND c.pub_end >= GETDATE()-1
AND c.visibility=0
AND c.zlat between #minLat and #maxLat
AND c.zlng between #minLng and #maxLng
ORDER BY c.priceeuro,c.car_id
This script is assinged to a string variable XXX which is passed to the database to get the ResultSet:
dim bigSQLQuery as String
dim conn as ADODB.Connection
set conn = new ADODB.Connection
...
' code to create the bigSQLQuery according to the various search criteria
...
conn.ConnectionString = "FILE NAME=C:\config\connection.udl"
conn.open
rs.Open bigSQLQuery, conn , adOpenForwardOnly, adLockReadOnly
while not rs.EOF
...
' code to get the data
...
rs.MoveNext
wend
If rs.State <> adStateClosed Then rs.Close
If conn.State <> adStateClosed Then conn.Close
Set rs = Nothing
Set conn = Nothing
We want to have the same functionality in Oracle. We have created a similar stored procedure and expected a similar behaviour to get the desired results. Unfortunately, Oracle refuses to create result sets in an anonymous block. If we try a similar block, we get an error that the SELECT query is missing the INTO clause (ORA-06550: line 9, column 2: PLS-00428: an INTO clause is expected in this SELECT statement):
declare
minLat float;
maxLat float;
minLng float;
maxLng float;
begin
zipSquare ('D', 85614, 100, minLat, maxLat, minLng, maxLng);
select c.car_id
FROM cars c
LEFT OUTER JOIN makes mak ON c.make_id = mak.make_id
LEFT OUTER JOIN models mod ON c.model_id = mod.model_id
LEFT OUTER JOIN bodytypes bod ON c.body_id = bod.body_id
LEFT OUTER JOIN bodycols col ON c.bodycol_id = col.bodycol_id
WHERE carTinyPoolId IN (1,4,8)
AND c.make_id=9
AND c.model_id=16416
AND c.priceeuro>=5000
AND c.accident=0
AND zcountry='D'
AND c.state = 'A'
AND c.pub_start<=sysdate
AND c.pub_end >= sysdate-1
AND c.visibility=0
AND c.zlat between minLat and maxLat
AND c.zlng between minLng and maxLng
AND rownum <= 501
ORDER BY c.priceeuro,c.car_id;
end;
Of course, there are various workarounds for this problem, but we want to get the best way of solving the problem because of performance issues. Having another roundtrip for the coordinates is out of the question, because the client (VB component) is not at all interested in these intermediate results and the roundtrip overhead is unjustified.
Is there anybody with experience in such problems? Any help is welcome. Thanks.
Regards,
Andreas

Categories

Resources