Need Help with looping a recordset

Report · Jan 10, 2007

I am calling a webservice I created that calls a COM object and returns a ADODB recordset. I am having a tough time looping the reordset. I was hoping someone could lend a idea. I am having the same issue with looping over the structure that is returned.

Code is below. Calling page first.

Report · Jan 10, 2007

How many rows do you expect in resultSet? You are only capturing one row to a struct.
You should probably capture X rows to a query or to an array of structs.

First, what version of CF are you using?
Will you want to filter or manipulate the data or just display it?

Report · Jan 11, 2007

There will be multile records returned. I have not worked with ADODB recordsets in the past so I am not sure of the best way to handle them. I would prefer to handle it like a query.

I have a mixed enviroment. The ystem that calls the web service is running MX 6.1 and the machine that has the web service and the COM+ API is running MX 7.

I am not going to manipluate the data just display it.

Thanks!

Report · Jan 12, 2007

Well since you've got mixed CF versions and are just displaying the data, an array of structs should do nicely for now.

The attached snippets have been re-written accordingly...

Report · Jan 15, 2007

Thanks Miker. I am really stuck on this and it is becoming critical to get by it fast.

I ran the code and had the following problem. The first dump is also listed.

object of coldfusion.runtime.com.ComProxy
{00000556-0000-0010-8000-00aa006d2ea4}
Methods Close([out, retval]VOID)
Move([in]USERDEFINED, [in,optional]VARIANT, [out, retval]VOID)
Delete([in,optional]USERDEFINED, [out, retval]VOID)
_xResync([in,optional]USERDEFINED, [out, retval]VOID)
UpdateBatch([in,optional]USERDEFINED, [out, retval]VOID)
Release([out, retval]UI4)
MoveFirst([out, retval]VOID)
Seek([in]VARIANT, [in,optional]USERDEFINED, [out, retval]VOID)
MoveNext([out, retval]VOID)
Update([in,optional]VARIANT, [in,optional]VARIANT, [out, retval]VOID)
_xSave([in,optional]BSTR, [in,optional]USERDEFINED, [out, retval]VOID)
GetTypeInfoCount([out]UINT, [out, retval]VOID)
MoveLast([out, retval]VOID)
Open([in,optional]VARIANT, [in,optional]VARIANT, [in,optional]USERDEFINED, [in,optional]USERDEFINED, [in,optional]I4, [out, retval]VOID)
Invoke([in]I4, [in]USERDEFINED, [in]UI4, [in]UI2, [in]USERDEFINED, [out]VARIANT, [out]USERDEFINED, [out]UINT, [out, retval]VOID)
CancelUpdate([out, retval]VOID)
GetString([in,optional]USERDEFINED, [in,optional]I4, [in,optional]BSTR, [in,optional]BSTR, [in,optional]BSTR, [out, retval]BSTR)
AddRef([out, retval]UI4)
GetIDsOfNames([in]USERDEFINED, [in]I1, [in]UINT, [in]UI4, [out]I4, [out, retval]VOID)
GetTypeInfo([in]UINT, [in]UI4, [out]VOID, [out, retval]VOID)
Cancel([out, retval]VOID)
Clone([in,optional]USERDEFINED, [out, retval]USERDEFINED)
GetRows([in,optional]I4, [in,optional]VARIANT, [in,optional]VARIANT, [out, retval]VARIANT)
Find([in]BSTR, [in,optional]USERDEFINED, [in,optional]USERDEFINED, [in,optional]VARIANT, [out, retval]VOID)
Requery([in,optional]I4, [out, retval]VOID)
Save([in,optional]VARIANT, [in,optional]USERDEFINED, [out, retval]VOID)
Resync([in,optional]USERDEFINED, [in,optional]USERDEFINED, [out, retval]VOID)
QueryInterface([in]USERDEFINED, [out]VOID, [out, retval]VOID)
CompareBookmarks([in]VARIANT, [in]VARIANT, [out, retval]USERDEFINED)
Supports([in]USERDEFINED, [out, retval]BOOL)
AddNew([in,optional]VARIANT, [in,optional]VARIANT, [out, retval]VOID)
CancelBatch([in,optional]USERDEFINED, [out, retval]VOID)
_xClone([out, retval]USERDEFINED)
NextRecordset([out,optional]VARIANT, [out, retval]USERDEFINED)
MovePrevious([out, retval]VOID)

Put Properties Bookmark([in]VARIANT, [out, retval]VOID)
Index([in]BSTR, [out, retval]VOID)
Sort([in]BSTR, [out, retval]VOID)
Collect([in]VARIANT, [in]VARIANT, [out, retval]VOID)
DataSource([in]UNKNOWN, [out, retval]VOID)
Source([in]BSTR, [out, retval]VOID)
StayInSync([in]BOOL, [out, retval]VOID)
CursorLocation([in]USERDEFINED, [out, retval]VOID)
LockType([in]USERDEFINED, [out, retval]VOID)
ActiveConnection([in]VARIANT, [out, retval]VOID)
CursorType([in]USERDEFINED, [out, retval]VOID)
PageSize([in]I4, [out, retval]VOID)
AbsolutePosition([in]USERDEFINED, [out, retval]VOID)
MaxRecords([in]USERDEFINED, [out, retval]VOID)
CacheSize([in]I4, [out, retval]VOID)
Filter([in]VARIANT, [out, retval]VOID)
AbsolutePage([in]USERDEFINED, [out, retval]VOID)
DataMember([in]BSTR, [out, retval]VOID)
MarshalOptions([in]USERDEFINED, [out, retval]VOID)

Get Properties Bookmark([out, retval]VARIANT)
PageCount([out, retval]USERDEFINED)
Sort([out, retval]BSTR)
Source([out, retval]VARIANT)
StayInSync([out, retval]BOOL)
EOF([out, retval]BOOL)
CursorLocation([out, retval]USERDEFINED)
RecordCount([out, retval]USERDEFINED)
DataSource([out, retval]UNKNOWN)
LockType([out, retval]USERDEFINED)
CursorType([out, retval]USERDEFINED)
AbsolutePosition([out, retval]USERDEFINED)
PageSize([out, retval]I4)
Properties([out, retval]USERDEFINED)
MaxRecords([out, retval]USERDEFINED)
DataMember([out, retval]BSTR)
MarshalOptions([out, retval]USERDEFINED)
Fields([out, retval]USERDEFINED)
Collect([in]VARIANT, [out, retval]VARIANT)
CacheSize([out, retval]I4)
Filter([out, retval]VARIANT)
State([out, retval]I4)
Index([out, retval]BSTR)
BOF([out, retval]BOOL)
ActiveConnection([out, retval]VARIANT)
EditMode([out, retval]USERDEFINED)
AbsolutePage([out, retval]USERDEFINED)
Status([out, retval]I4)
ActiveCommand([out, retval]DISPATCH)

The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.

Error Occurred While Processing Request
Loop error.
Invalid collection "oDelivHist" - must be a valid struct or COM object.

The error occurred in C:\CFusionMX7\wwwroot\DelivHistoryTest.cfm: line 34

32 : <CFSET ArrayResize (aRetData, oDelivHist.recordCount)>
33 :
34 : <CFLOOP collection="oDelivHist" item="oHistLine">
35 :
36 : <CFDUMP var="#oHistLine#">

--------------------------------------------------------------------------------

Resources:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)
Remote Address 127.0.0.1
Referrer
Date/Time 15-Jan-07 08:12 PM

Stack Trace (click to expand)
at cfDelivHistoryTest2ecfm213726990.runPage(C:\CFusionMX7\wwwroot\DelivHistoryTest.cfm:34)

coldfusion.runtime.CfJspPage$InvalidLoopCollectionException: Loop error.
at coldfusion.runtime.CfJspPage._validatingMap(CfJspPage.java:1338)
at cfDelivHistoryTest2ecfm213726990.runPage(C:\CFusionMX7\wwwroot\DelivHistoryTest.cfm:34)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:152)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:349)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:225)
at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:51)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:86)
at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:27)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:69)
at coldfusion.filter.BrowserDebugFilter.invoke(BrowserDebugFilter.java:52)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:115)
at coldfusion.CfmServlet.service(CfmServlet.java:107)
at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:78)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:257)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:541)
at jrun.servlet.http.WebService.invokeRunnable(WebService.java:172)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:318)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:426)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:264)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

Report · Jan 15, 2007

We'll that's weird.
Try replacing the line:
<CFSET ArrayResize (aRetData, oDelivHist.recordCount)>
with:
<CFSET ArrayResize (aRetData, oDelivHist.RecordCount.Value)>

If that doesn't work, just comment that line out for now. It's only there to provide a slight speed increase.

Report · Jan 15, 2007

Thanks for the quick reply

Here is what happened.

An exception occurred when accessing a Com object field.
The cause of this exception was that: java.lang.NoSuchFieldException: There is no property called RECORDCOUNT.VALUE.

so I commented it out and got this:

Loop error.
Invalid collection "oDelivHist" - must be a valid struct or COM object.

The error occurred in C:\CFusionMX7\wwwroot\DelivHistoryTest.cfm: line 33

31 :
32 :
33 : <CFLOOP collection="oDelivHist" item="oHistLine">
34 :
35 : <CFDUMP var="#oHistLine#">

What is weird is it has a recordcount of 4.

I really appreciate your help on this.

Report · Jan 15, 2007

There is something missing or a perhaps a typo somewhere.

Up until the failure point, the code should be identical to the original except for some variable name changes.

Are you still running on the original two servers?

Can you post the entire modified code?

Is there anyway I can test that com object here?

Change this:
<CFIF oDelivHist.recordCount NEQ 0>
to this:
<CFIF oDelivHist.recordCount NEQ 0>
<CFDUMP var="#oDelivHist.recordCount#">

Report · Jan 15, 2007

We are still running the same two servers. I have other services running on the machine without issue talking to the API's other functions.

Here is the latest dump. I will attach the modified code. Unfortunately there is no way I can give you to test the com object. It is under extreme lock down and only open to 2 IP's. Mine and the webserver.

<cffunction name="getDelivHistory" access="remote" returntype="array" >

<cfargument name="companyID" type="string" default="" required="true" hint="companyID">
<cfargument name="accountNumber" type="string" default="" required="true" hint="Customer account number">
<cfargument name="tankNumber" type="string" default="" required="true" hint="Customer tank number">
<cfargument name="fromDate" type="string" default="" required="true" hint="Start Date">
<cfargument name="toDate" type="string" default="" required="true" hint="End Date">
<cfargument name="maxRows" type="string" default="" required="true" hint="Max return rows">

<CFSCRIPT>
var aRetData = ArrayNew(1);
var zHistoryLine = StructNew();

var iCompanyID = trim(arguments.companyID);
var iAccountNum = trim(arguments.accountNumber);
var iTankNumber = trim(arguments.tankNumber);
/*
var iDteFromDate = trim(arguments.fromDate);
var iDteToDate = trim(arguments.toDate);
*/
var iDteFromDate = "1/1/2006";
var iDteToDate = "1/1/2007";
var iMaxRows = trim(arguments.maxRows);
var cSysUser = "WebUserTest";
var lRetCode = "";
</CFSCRIPT>

<CFOBJECT ACTION="CREATE" NAME="getDelivHist" CLASS="EnergyAPI.cDelivery">

<CFSET oDelivHist=getDelivHist.GetDeliveryHistory(iCompanyID, iAccountNum, iTankNumber, iDteFromDate, iDteToDate, iMaxRows, cSysUser, lRetCode)>

<CFDUMP var="#oDelivHist#">


<CFIF oDelivHist.recordCount NEQ 0>
<CFDUMP var="#oDelivHist.recordCount#">


<CFLOOP collection="oDelivHist" item="oHistLine">

<CFDUMP var="#oHistLine#">

<CFSCRIPT>
zHistoryLine = StructNew();
zHistoryLine.delivery_date = oHistLine.Fields.Item("delivery_date").Value;
zHistoryLine.ticket_num = oHistLine.Fields.Item("ticket_num").Value;
zHistoryLine.units = oHistLine.Fields.Item("units").Value;
zHistoryLine.cost_per_unit = oHistLine.Fields.Item("cost_per_unit").Value;
zHistoryLine.net_amount = oHistLine.Fields.Item("net_amount").Value;
zHistoryLine.tank_num = oHistLine.Fields.Item("tank_num").Value;

ArrayAppend (aRetData, zHistoryLine);
</CFSCRIPT>
</CFLOOP>
</CFIF>

<cfreturn aRetData>
</cffunction>

Thanks!

Report · Jan 15, 2007

Did the original code work as far as getDelivHistory returning without error?

It shouldn't have since it used lAccountNumber instead of iAccountNumber.

If it DID, then is lAccountNumber defined somewhere?

What was the result of the dump of oDelivHist.recordCount?

Report · Jan 16, 2007

No it had the error that you see. iAccountNumber was incorrect and should be iAccountNum so I corrected it..

The dump returned 4 which is the correct number of records to be returned.

Weird.

Report · Jan 16, 2007

The dump also returned type information -- which would be useful here.

Anyway, I was laboring under the delusion that the original code worked but only for one record set.
(My fault for relaxing my rule about requiring the exact error message.)

This is going to be difficult without any docs on EnergyAPI.cDelivery or without access to the system.
(If we swap information by PM or private email, I will share it with no one else.)

Anyway, I suspect that an additional function call is needed.

Temporarily replace the cfc function with the attached code.
Attach the results here (the page's html source please) or link to a site that shows the dump.

Report · Jan 16, 2007

Here is the HTML from a DUMP.

<CFSCRIPT>
aRetData = ArrayNew(1);
zHistoryLine = StructNew();

iCompanyID = 100;
iAccountNum = 9167;
iTankNumber = 1;
/*
var iDteFromDate = trim(arguments.fromDate);
var iDteToDate = trim(arguments.toDate);
*/
iDteFromDate = "1/1/2006";
iDteToDate = "1/1/2007";
iMaxRows = 0;
cSysUser = "WebUserTest";
lRetCode = "";
</CFSCRIPT>

<CFOBJECT ACTION="CREATE" NAME="getDelivHist" CLASS="EnergyAPI.cDelivery">

<CFSET oDelivHist = getDelivHist.GetDeliveryHistory (iCompanyID, iAccountNum, iTankNumber, iDteFromDate, iDteToDate, iMaxRows, cSysUser, lRetCode)>


<CFSET zCursLoc = oDelivHist.CursorLocation()>
<CFSET zDatMemb = oDelivHist.DataMember()>
<CFSET zFields = oDelivHist.Fields()>
<CFSET zRows = oDelivHist.GetRows()>

<CFDUMP var="#variables#">

I removed the function and ran the code locally on the API server.

Here is the test code

<CFSCRIPT>
aRetData = ArrayNew(1);
zHistoryLine = StructNew();

iCompanyID = 100;
iAccountNum = 9167;
iTankNumber = 1;
/*
var iDteFromDate = trim(arguments.fromDate);
var iDteToDate = trim(arguments.toDate);
*/
iDteFromDate = "1/1/2006";
iDteToDate = "1/1/2007";
iMaxRows = 0;
cSysUser = "WebUserTest";
lRetCode = "";
</CFSCRIPT>

<CFOBJECT ACTION="CREATE" NAME="getDelivHist" CLASS="EnergyAPI.cDelivery">

<CFSET oDelivHist = getDelivHist.GetDeliveryHistory (iCompanyID, iAccountNum, iTankNumber, iDteFromDate, iDteToDate, iMaxRows, cSysUser, lRetCode)>


<CFSET zCursLoc = oDelivHist.CursorLocation()>
<CFSET zDatMemb = oDelivHist.DataMember()>
<CFSET zFields = oDelivHist.Fields()>
<CFSET zRows = oDelivHist.GetRows()>

<CFDUMP var="#variables#">

Report · Jan 16, 2007

quote:

Originally posted by: mattf2007
Here is the HTML from a DUMP.

... ...
I removed the function and ran the code locally on the API server.

Here is the test code

... ...

<CFDUMP var="#variables#">

What I need is the result of <CFDUMP var="#variables#">.

Run that test program.

It should show a nice structured display of the variables with their hierarchy and types.

"View source" on that page. Copy the raw source and then paste it here using the "Attach code" feature.

Thanks,
-- MikeR

Report · Jan 16, 2007

Trying dump

Report · Jan 16, 2007

Excellent. That looks like GetRows will make our job easier.

Give me a little bit (doing this on spare time)...

Report · Jan 16, 2007

When I call the new CFC from the webservice I get this. Please not that I have other services calling the API and they are working fine.

Thanks again for all of your efforts.

Error Occurred While Processing Request
Could not perform web service invocation "getDelivHistory" because AxisFault faultCode: { http://schemas.xmlsoap.org/soap/envelope/}Server.generalException faultSubcode: faultString: Error attempting to create Java skeleton for CFC web service; nested exception is: coldfusion.xml.rpc.CFCInvocationException: [java.lang.NullPointerException : null] faultActor: faultNode: faultDetail: { http://xml.apache.org/axis/}hostname: debapi

The error occurred in D:\DBrothers.com\www\cfmx\api\deliveryHistory.cfml: line 54

52 : <cfinvokeargument name="fromDate" value="#form.tankNumber#">
53 : <cfinvokeargument name="toDdate" value="#form.tankNumber#">
54 : <cfinvokeargument name="maxRows" value="10">
55 : </cfinvoke>
56 :

--------------------------------------------------------------------------------

Please try the following:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322)
Remote Address 216.15.65.22
Referrer http://www.dbrothers.com/cfmx/api/deliveryHistory.cfml
Date/Time 16-Jan-07 05:35 PM

Stack Trace (click to expand)
at cfdeliveryHistory2ecfml251305458.runPage(D:\DBrothers.com\www\cfmx\api\deliveryHistory.cfml:54)

coldfusion.xml.rpc.ServiceProxy$ServiceInvocationException: Could not perform web service invocation "getDelivHistory" because AxisFault
faultCode: { http://schemas.xmlsoap.org/soap/envelope/}Server.generalException
faultSubcode:
faultString: Error attempting to create Java skeleton for CFC web service; nested exception is:
coldfusion.xml.rpc.CFCInvocationException: [java.lang.NullPointerException : null]
faultActor:
faultNode:
faultDetail:
{ http://xml.apache.org/axis/}hostname: debapi

at coldfusion.xml.rpc.ServiceProxy.invokeImpl(ServiceProxy.java:219)
at coldfusion.xml.rpc.ServiceProxy.invoke(ServiceProxy.java:132)
at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:1587)
at coldfusion.tagext.lang.InvokeTag.doEndTag(InvokeTag.java:372)
at cfdeliveryHistory2ecfml251305458.runPage(D:\DBrothers.com\www\cfmx\api\deliveryHistory.cfml:54)
at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:147)
at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:357)
at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:62)
at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:107)
at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48)
at coldfusion.filter.PathFilter.invoke(PathFilter.java:80)
at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:47)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28)
at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:35)
at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:43)
at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22)
at coldfusion.CfmServlet.service(CfmServlet.java:105)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:249)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
at jrun.servlet.jrpp.JRunProxyService.invokeRunnable(JRunProxyService.java:192)
at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348)
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451)
at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294)
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

Report · Jan 16, 2007

Mike,

I do not have the option to attach code. :(

What I need to send you is larger than Adobe will alow.

I need a address to send it to you.

Thanks,

-Matt

Report · Jan 16, 2007

Here is the source view

Report · Jan 16, 2007

Thanks as am I.

Report · Jan 16, 2007

Ok,
Create a new file on the API server using the attached code.

Run it and let me know what happens.

Report · Jan 17, 2007

As I am working on this in my off time I will need to try this this evening. I will be sure to let you know the result then. Thanks!

Adobe Community

Need Help with looping a recordset