MSQL queries

[mshofmann]

So far I've tried two different approaches but neither are yielding the results I need.

Approach 1:
This approach gives me the schema, with metadata about the column I queried and a total row count, but no actual rows of data.

from membersuite_api_client.client import ConciergeClient 

client = ConciergeClient(access_key='xxxx', secret_key='xxxx', association_id='xxxx') 
client.request_session() 
query = 'select Owner.Owner.BAPNumber__c from SpeciesDetail__c' 
client.runSQL(query=query) 

Approach 2:
This approach just yields an empty list.

from membersuite_api_client.utils import submit_msql_query 
from membersuite_api_client.utils import get_new_client 
import os 

os.environ['MS_ACCESS_KEY'] = 'xxxx' 
os.environ['MS_SECRET_KEY'] = 'xxxx' 
os.environ['MS_ASSOCIATION_ID'] = 'xxxx' 

client2 = get_new_client() 
client2.request_session() 

submit_msql_query(query=query, client=client2)

[rerb]

Are you sure your query returns a non-empty result set? When submitted through the MemberSuite MSQL console, for instance.

Didn't realize anybody else was using this. Will have to take care of those outstanding issues now, I reckon.

[mshofmann]

Yes, in fact when I run this query
select Owner.BAPNumber__c, Species__c from Species__c where (( Owner.BAPNumber__c IS NOT NULL ))
in the console I get 2628 records, and when I use Approach 1 in my post I get all the metadata for those two columns in the query and then
'TotalRowCount': 2628
at the very bottom of the output.

[rerb]

The only MSQL queries supported are those that select OBJECT() or OBJECTS(). These results are turned into MemberSuiteObjects, with field data in the MemberSuiteObject.fields list.

For example:

$ python # Find all test users, and print the last name of the first one.
>>> from membersuite_api_client import utils
>>> test_users = utils.submit_msql_object_query("SELECT OBJECTS() FROM PortalUser WHERE FirstName = 'Test'")
>>> len(test_users)
4
>>> test_users[0]
<membersuite_api_client.security.models.PortalUser at 0x10a100a50>
>>> test_users[0].fields["LastName"]
User
>>>

To make the OBJECT() or OBJECTS() requirement visible, a number of functions and parameters have been renamed. These might be breaking changes for your application.

• ConciergeClient.runSQL is renamed ConciergeClient.execute_object_query.

• The 'query' parameter to execute_object_query is renamed to 'object_query'.

• utils.submit_msql_query is renamed to utils.submit_msql_object_query.

[mshofmann]

Thanks, having success now. Can the 400 row limit be removed?

[rerb]

Sorry for the delayed response. Thought I might get it fixed before getting back to you, but I haven't.

The 400 item limit is at line 99. You might have success increasing that to an absurdly high number, though you might also experience timeouts on longer running queries.

I think I might be able to remove the limit_to parameter from the code, replacing it by appending a LIMIT clause to whatever query is being submitted. Haven't worked it out yet, so I'm not sure.

Another approach is to use the logic in mixins.py. That's what we're doing in production. The ChunkQueryMixin knows how to handle long queries. Reading the source for membersuite_api_client/subscriptions/services.py might be helpful to see how it works now.