0

I have a PHP application which connects to Volusion store and exports data using query string, and the query string in API like following

$url="http://www.domain.com/net/WebService.aspx?Login=jusername&EncryptedPassword=7602B3272D929D17B5138382F2AE4F4C6102A12AEB1F476293C16A5F731949B6&EDI_Name=Generic\Customers&SELECT_Columns=CustomerID,LastLogin";

Now I want to fetch data from multiple tables and my query is like 

SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.EmailAddress, Login_History.LastModified  FROM Customers JOIN Login_History ON (Login_History.CustomerID=Customers.Customerid)

Does anyone know how to write JOIN query in Volusion API connection URL?

Paul T. Rawkeen
  • 3,994
  • 3
  • 35
  • 51
Sherin
  • 401
  • 1
  • 11
  • 22
  • you cannot run an SQL call of this nature directly through the Volusion API. You can however run a SQL call via a saved text file. I explained this to your here... http://stackoverflow.com/questions/28936437/could-not-select-lastlogin-field-from-volusion-store-generic-customers-table/29056295#29056295 – user357034 Mar 18 '15 at 10:21
  • i tried that but not working . I contacted volusion support they said " The saved exports you can manually export only through the saved exports tab. Unfortunately we do not have a built in method of exporting them via the API". – Sherin Mar 18 '15 at 10:27
  • 1
    Sadly, your mistake was to contact V support for assistance. Most of them support staff don't know there own product. It is possible to do what I said. – user357034 Mar 18 '15 at 23:40

1 Answers1

4

Using notepad or similar editor copy and paste the following into it and save it as loginhistory.sql or whatever you want but it must have an .sql extension.

 SELECT Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.EmailAddress, Login_History.LastModified  FROM Customers JOIN Login_History ON (Login_History.CustomerID=Customers.Customerid)

This will work but certainly not correctly formatted. I'll leave it to you on writing a XSD file. Copy the following to a editor like Notepad and save it with the same name as above but with a .xsd extension. 

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
</xs:schema>

FTP the two files above to your Generic folder. 

/v/vspfiles/schema/Generic/

You can now run the SQL query via the API using the URL

http://www.yourdomain.com/net/WebService.aspx?Login=username&EncryptedPassword=abcdefghijklmnopqrstuvwxyz&EDI_Name=Generic\loginhistory

Be sure to edit in your specific API credentials in the URL.

user357034
  • 10,731
  • 19
  • 58
  • 72
  • 1
    thanks. what a crock of nonsense this api is. xml schemas manually uploaded through ftp? really volusion? who decided this? – kristopolous Feb 04 '16 at 08:12