Saving order data to a database

[meburdack]

I need to automate saving our order data into a database. I have read ShopSite Pro v10 documentation and many posts in this forum. Based upon what I have read it appears that the best approach is to make use of the Order API. Is that correct? The store is hosted on a Windows server. Not having any experience with CGI, PHP, or Perl I need all the help I can get. Any suggestions as to which language would be better for the task would be greatly appreaciated. Sample scripts would be most helpful along with recommendations for any good language references for newbies. thanks in advance

[Jim]

The orders data is already being saved to a database, the ShopSite one in shopsite_db in the stores data directory. Can you explain more what you are trying to do? Maybe the xml nterface for getting the order information from the ShopSite database would fill your needs. See http://www.shopsite.com/help/10.2/en-US/sc/pro/orders.download.format.xml-auto.html

Almost any programming language can be used for the orderapi script as long as it can handle data being passed in a name, value pair format. A sample perl script, custom-dump.pl, is included with ShopSite so you can see the steps in processing the order name,value pairs. It just dumps the pairs to the screen but you could use it as an example for writing it to a database. The perl source can also be found at http://www.shopsite.com/help/10.2/en-US/sc/pro/custom-dump.pl. (A compiled C program called customdump.cgi is also included with each ShopSite install. You could enable it to see how the values are processed, however, the C code is not provided for it.)

You can see the online help at http://shopsite.com/help/10.2/en-US/sc/pro/index.htm . Note the special requirements for windows at http://www.shopsite.com/help/10.2/en-US/sc/pro/working/faq.orderapi.html#q2

If you have not had experience programming it may be beneficial to hire someone to do the programming for you. You can see a list of certified ShopSite designers at http://shopsite.com/csd.html. Most just do template designs but some of them also do custom programming. There are probably posts in this forum from some of them stating that they do custom programming.

[meburdack]

Interesting so I can just access the shopsite database directly? Some of our products require activation and I want to validate what they purchased as part of the activation process. With the xml interface, I would have to grab the order info in real time if it hadn't already been downloaded and saved. I like accessing the shopsite database directly if that doesn't cause problems. I can then save off what ever I need or just link back to the shopsite database.

I saw the comment in the documentation about your scripts are compiled binary. I wasn't sure what that meant. So I can write a console app in C# and that can be executed, yes?

I have experience programming but just not in the three script languages that I saw referenced. I'm somewhat of a newbie in the web programming but I'm learing everyday.

thanks

[Jim]

An order won't be in the ShopSite database until after the merchant has either accessed the Orders screen or downloaded the orders with a script. If you need to do something with the order when the shopper is checking out then you will need to use the Order API functionality.

You could probably do the order API script in C# if you are familiar with that language. Just read through the custom-dump.pl example file and you should be able to see the programming flow and what needs to be done to get the data and then you can do your processing on it. We do not support direct access to the ShopSite database or provide any information on its internal format. It is however an sqlite database so tools to work with it shouldn't be hard to find. But if you work on it directly you are on your own and any thing you do to the database could be wiped out on an upgrade since the upgrade code would not be aware of any modifications.

[meburdack]

thanks for the info. Perl and the Order API looks like the way to go. I should be able to pick and learn Perl without too much trouble. I'll stay away from the direct access of the database. thanks again, Mark

[meburdack]

I wrote a quick perl script that processes a Post request reading text from STDIN and writing the text to a text file. I configured shopsite to call the script and I placed a test order to capture the data sent via the order api. I wrote another script which reads the data from the text file, parses it and saves the data to a database. so far so good. I want to update the script to process order data from a Post request to test how shop site will call the script. how can I call my script similar to how shop site will call it? thanks mark

[Jim]

The only info I have on it is what is given in the on line help: http://www.shopsite.com/help/10.2/en-US/sc/pro/order.api.spec.html
The initial paragraphs of the Order API Specification say

From the point of view of the CGI, it's running like any other CGI called with the POST method. It's input is an '&' delimited series of name value pairs (name=value) and the whole thing is URL-encoded.

The CGI should find the pairs, determine the name and value for each, and decode the name and value. The CGI can then do whatever CGIs do. Anything written to standard output will go to the server, etc.

Following that is a list of the various pair names that are passed.

Note that the application will be run by the webserver user so it will need permissions such that the webserver can execute it.