19640

Volusion's generic SQL folder, functionality

I found this response very helpful

How to write join query in Volusion API

What I'm looking for is a way to add my own .SQL and .XSD files to the /vspfiles/schema/Generic folder and be able to pass parameters to it. Does anyone know if that's possible.

A very basic example of the SQL would be something like this...

select * from Orders where order_id = "-ORDERID-"

...and I'd be able to pass in the "-ORDERID-" as a variable.

Or even better the SQL file would just be this "-SQL-" and I could pass in the entire SQL string myself. Thanks!

Answer1:

Thanks user357034 for getting me here (I'd "up" your answer but I'm new and don't have any reputation). I wanted to post the code I used in case others run into this. And also get any feedback if you see anything that looks goofy here.

First, I created an ASP file like so

Dim orderid Dim status orderid = Request.QueryString("orderid") status = Request.QueryString("status") sql = " update Orders " & _ " set OrderStatus = '" + status + "' " & _ " where Orderid in (" + orderid + ") " ; set fs=Server.CreateObject("Scripting.FileSystemObject") set f=fs.OpenTextFile(Server.MapPath("./MY_FILE.sql"),2,true) f.WriteLine(sql) f.Close set f=Nothing set fs=Nothing

I FTPed that up to the "generic" folder on Volusion.

Next, in PHP, I call this file, similar to this...

$asp = file("http://MY_SITE/v/vspfiles/schema/generic/MY_FILE.asp? orderid=11,12&status=Processing"); foreach ( $asp as $line ) { echo ($line); }

NOTE: I already FTPed an XSD file to the same folder with the same name, like MY_FILE.xsd.

And finally, I make a web service call to my service, like this...

$url = "http://MY_SITE/net/WebService.aspx? Login=XXXX&EncryptedPassword=YYYYY&API_Name=Generic\MY_FILE"

Works great. I go into the Volusion admin site, look at the Orders 11 and 12, and they were updated. I'm using this method for several areas in Volusion where their API is lacking. Thanks!

Answer2:

While technically one could pass in text comprised of a complete SQL query, however I would strongly caution against such practice as it would open up your site to malicious activity and/or possible security issues. I would limit the scope to a specific query and only allow one or more parameters to be used.

To accomplish this you have to create an custom ASP page in Volusion that would gather the parameters from the user, in your case "order id" and then insert them into the set SQL query, write that SQL query as a text file to the server in the correct location as shown in https://stackoverflow.com/a/29134928/357034 and then execute the query. All this is done in the custom ASP page.

UPDATE: If you just want a simple order id query with all fields returned you don't even have to use the the SQL method as Volusion already has a built in method to return this data. You just have to use a custom ASP page to insert the order id parameter and then execute the URL with the parameter attached. In your ASP page you would insert the order id in place of the xxxxx

http://www.yoursiteurl.com/net/WebService.aspx?Login=name@yoursiteurl.com&EncryptedPassword=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxC&EDI_Name=Generic\Orders&SELECT_Columns=*&WHERE_Column=o.OrderID&WHERE_Value=xxxxx

Recommend

  • How to find last modified subfolder?
  • Moving and Renaming Files Using VBScript
  • How could we use ibpy for editing orders?
  • VBS script to rename files using the pathname
  • How to compare two rows or get the fields which value is not match with the rows that compared?
  • Is it possible to display more than one item in a cell with a foreach condition?
  • Getting CDATA XML Section Using Linq
  • Best approach: get the most recent N-side Record on a SQL Query on a N-1 relationship
  • Creating a table in “MSWord” document out of a text in a .txt file
  • DotNet version of Windows Scripting Host
  • Accessing the raw code in an MS Access application
  • windows “.lnk” shortcuts and batches don't mix
  • How do you copy a MS SQL 2000 database programmatically using C#?
  • How to fetch the alt value from an img using vba
  • Read files from URL
  • Pasting URLs That Have Been Scraped From a Webpage
  • ResponseBuilder is not working when used with entity object
  • Escaping single quotes in JDBC with MySql
  • Error “Reflect.defineMetadata” while trying to load a transient web worker
  • JSON Error when parsing “… has no method 'replace'”
  • How to plot large time series (thousands of administration times/doses of a medication)?
  • How to make R's read_csv2() recognise the text characters properly
  • Android Activity.onWindowFocusChanged doesn't get called from within TabHost
  • How to make jdk.nashorn.api.scripting.JSObject visible in plugin [duplicate]
  • C: Incompatible pointer type initializing
  • Is there any way to access browser form field suggestions from JavaScript?
  • Resize panoramic image to fixed size
  • Microsoft Visual Studio Community 2015 always crashes in Windows 10 if swithed to Visual FoxPro
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Circular dependency while pushing http interceptor
  • Compare two NSDates in iPhone
  • Linker errors when using intrinsic function via function pointer
  • Proper way to use connect-multiparty with express.js?
  • How to set the response of a form post action to a iframe source?
  • How do you join a server to an Active Directory (domain)?
  • coudnt use logback because of log4j
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • reshape alternating columns in less time and using less memory