Method Community

Hi,

I have successfully used a third party development tool to access the Method API to obtain both the Table and Field Lists.  Using the same syntax for obtaining the data, however, gives the errors indicated below.  The built in third party function's description can be found via the link below which goes to an online help file.  It is designed to access http/https sites and be able to return XML.

http://support.alphasoftware.com/alphafivehelpv8/Functions/HTTP_POST_PAGE2().htm


I do not know whether this is a Method API issue or one that lays within how the third party function sends the information.  I have tried all the permutations I can with the strGroupByClause syntax,ie; no quotes, single quotes, double quotes, no field name, field name, ....

dim field_page as c
dim str3 as c
str3=""
str3="strCompanyAccount=AccountName&strLogin=LoginName&strPassword=PasswordName&Length=9000&strSessionID=''&strTable=Customer"
str3=str3+"&strFields=fullname,phone&strWhereClause=''&strGroupByClause=''&strHaving=''&strOrderBy=''"
field_Page=http_post_page2("https://www.methodintegration.com/MethodAPI/service.asmx/MethodAPISelect_XMLV2",str3)

''===================================================================================

Header information received:

HTTP/1.1 200 OK
Cache-Control: private, max-age=0
Content-Type: text/xml; charset=utf-8
ETag: ""
Server: Microsoft-IIS/7.0
X-AspNet-Version: 4.0.30319
X-Powered-By: ASP.NET
Date: Wed, 05 Jan 2011 14:59:43 GMT


Error when strGroupByClause=fullname field:

strGroupByClause=fullname
<?xml version="1.0" encoding="utf-8"?>
<string xmlns="http://tempuri.org/">&lt;?xml version="1.0" encoding="windows-1252" ?&gt;
&lt;MethodAPI response = "Failure. The SQL returned the following error:  An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'." &gt;
&lt;/MethodAPI&gt;</string>


Error when strGroupByClause=nothing:

strGroupByClause=''
<?xml version="1.0" encoding="utf-8"?>
<string xmlns="http://tempuri.org/">&lt;?xml version="1.0" encoding="windows-1252" ?&gt;
&lt;MethodAPI response = "Please make sure the fields entered in the group by clause is contained in your field list." &gt;
&lt;/MethodAPI&gt;</string>

Any thoughts?

Hi Mike,

If you've been able to retrieve Table and Field Lists using our MethodAPITableListV2 and MethodAPIFieldListV2 functions, I am not sure why you are having troubles using the MethodAPISelect_XMLV2, since it follows the same pattern. If in the browser you go to http://methodintegration.com/MethodAPI/Service.asmx?op=MethodAPISelect_XMLV2 , you can actually try to retrieve values from your table manually and see if it works for you (I've tested it with my account and parameters that you have provided in your example and had no problems with it). If it doesn't, tell me what errors are you getting. By the way, for group by statement, you don't need quotes, but for where statement, you do.

Example of parameters I've been entering
strCompanyAccount:CompanyName
strLogin:Login
strPassword:Password
strSessionID
strTable:Customer
strFields:fullname,phone
strWhereClause:fullname like '%locet%'
strGroupByClause:fullname,phone
strHaving
strOrderBy

Also, message "Please make sure the fields entered in the group by clause is contained in your field list." when you've entered the word "Nothing" into field GroupBy is a valid error, since there is no field name "Nothing" in Customer table, so it cannot group results by this fieldname. What you need to do is either leave groupby field blank, or enter all of the fields you are retrieving into field groupby separated by a comma.

By the way, is there any reason why you have to access our XML the hard way, by using some third party service, instead of referencing our webservice directly?

You can also find MethodAPI documentation here http://www.methodintegration.com/documentation/MethodAPIv2_0_Documentation.pdf

Thank you

Victor

I just spent a half hour replying (with interruptions) and due to what apparently is a timeout for this forum, when I chose to Post, it brought me to the sign in page.....and I lost what I had written....this is the second time this has happened.

This short message is just to let you know I was going to acknowledge your answer but I have to leave now until later tonight so cannot respond again for a while.

Try, try again right!? 

Thanks for responding so quickly Victor.

I too had used the browser to manually obtain the records with no errors.

The error you mention makes perfect sense now that you have explained the syntax.

Regarding the other error:
The SQL returned the following error:  An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'.

Any ideas of what that could be?


My intention is to use Method API to obtain data automatically (push of a button or when the program loads) to be used in other independent applications.  Currently I want to use it for QReportBuilder, a report writing tool based upon the application development tool I use to create software (Alpha5 from Alpha Software).  This report writer is very powerful and many users say it is much better than Crystal Reports even.  There are many limits in Method's built-in report writer that QReportBuilder does not have.  Although I can currently obtain data directly from a desktop installation of QuickBooks, being able to obtain data from a Method account will give me a much more powerful toolkit which will be able to satisfy almost every client's needs especially in regards to report building.

Hi Mike,

Error "The SQL returned the following error:  An expression of non-boolean type specified in a context where a condition is expected, near 'GROUP'." only means that SQL qeuery was written incorrectly. Usually SQL requests are written like following

SELECT fullname,phone FROM customer WHERE fullname='Alocet' GROUP BY fullname,phone

but if you make a mistake in your query and write something like following (there are tons of possibilities to write an incorrect SQL, this is just an example.)

SELECT fullname,phone,SUM(VacationHoursSecondsAvailable) FROM customer WHERE fullname='Alocet' GROUP BY fullname, phone, SUM(VacationHoursSecondsAvailable)

then you will get the error you've got. Unless you can reproduce the error by directly entering the values into our web service, I can't help. My guess the problem you are entering is in the values you entered into group by statement is somehow modified by third party service you are using, causing an invalid output.

Also, from the description you have given of an application you are trying to build, I see no problems with using our MethodAPI service directly, which will be not as a painfull as using it through third party application.

Thank you

Victor

Also, we are currently investigating the forum timeout issue you were experiencing. Can you please tell us what browser were you using, in order to help us resolve the issue?

Thank you

Victor

Sorry for the delayed response.  I use Mozilla FireFox 3.6.13 currently as my main browser.