How to connect Volusion DB without using admin pan

Question

Looking for a way to connect a java based SQL program on my local computer to Volusion's SQL - so I can run reports without needing to use Volusion admin area import/export web page.

Is there a way to accomplish this?

Answer 1

Volusion doesn't publish any Db connection info, so the import/export area and the "API" are your only sources for data.

Answer 2

If you want to run a "static" SQL query, whether it be an update, insert, select or whatever you simply have to create a text file containing the SQL query along with a proper XSD file of the same name and SFTP it to the "Generic" folder of your site. You then run the Query by requesting it as so...

http://www.yoursiteurl.com/v/vspfiles/schema/Generic/nameoftextfile

You will need to save both the SQL and XSD files with the proper file extension (.sql and .xsd) but do not use the .sql extension when requesting it above.

If you want to run a query with parameters that let say come from an external source then its a bit more complex but it involves creating a ASP page which accepts the parameters, properly filters them and creates the SQL query text and writes it to a text file again with .sql extension and also writes the associated XSD file of the same name to the Generic folder and then executes the query in the same manner as above and returns the result if any to the source.

See my response to a similar question here...

Volusion API - Export Orders by Date Range

Answer 3

I found a workaround to this. You can create your own API calls with whatever SQL you want. First, you need to choose a filename to use....something like MyQuery. Then create a file named MyQuery.xsd, edit it with any text editor, paste in one of Volusion's sample XSDs (here is one https://support.volusion.com/hc/en-us/articles/209638947 note this file just has to exist in order for all this to work -- the actual layout of the file isn't so important for now -- you can tweak it later), and then FTP the XSD file up to your /vspfiles/schema/generic folder on the Volusion server.

Then you will also want to create a .SQL file with the same name, like MyQuery.sql. Same thing, edit it, enter your SQL query, and then FTP it to the same folder.

Once that's done, you can call MyQuery like any Volusion API call..something like this...

https://www.XXXX.com/net/WebService.aspx?Login=YYYYYYYY&EncryptedPassword=ZZZZZZ&EDI_Name=MyQuery

And that API call will return XML of the SQL result set (in the format of your XSD file). Let me know if this makes sense. Good luck!